Re: FTS trigger works 1 at a time, but fails with bulk insert script

[Adrian Klaver]

On 10/2/18 10:34 AM, Malik Rumi wrote:
I have set up Postgres FTS on a Django/Python web site, and it works as 
expected except for this one thing. When I wrote a script to bulk insert 
legacy docs, the script works fine but the FTS trigger does not fire. I 
have to go back and open each document one at a time to get them indexed.


What am I missing to make this work? Thanks.


Have no idea as there is not enough information.

To begin with:

1) What is code below?

2) What is the trigger definition and on what table?

3) What is the function the trigger is calling?

4) For good measure what version of Postgres?



             BEGIN
               SELECT setweight(to_tsvector(NEW.title), 'A') ||
                      setweight(to_tsvector(NEW.content), 'B') ||
                      setweight(to_tsvector(NEW.category), 'D') ||
                      setweight(to_tsvector(COALESCE(string_agg(tag.tag, 
', '), '')), 'C')

               INTO NEW.search_vector
               FROM ktab_entry AS entry
                 LEFT JOIN ktab_entry_tags AS entry_tags ON 
entry_tags.entry_id = entry.id 
                 LEFT JOIN ktab_tag AS tag ON tag.id  = 
entry_tags.tag_id

               WHERE entry.id  = NEW.id
               GROUP BY entry.id , category;
               RETURN NEW;
             END;


*/“None of you has faith until he loves for his brother or his neighbor 
what he loves for himself.”/*



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: metadata about creation and size of tables

[Adrian Klaver]

On 10/2/18 1:38 PM, Martin Mueller wrote:
I’ve looked at the documentation to find where to find some data that 
are very to find Mysql:  the creation and modification data of a table 
and the size of particular tables.


Where do I find an answer to the question “which is the last table I 
created” or “when did I last modify this table?”  In the data directory, 


Postgres does not track these times. You can search the list archives 
for past discussions on the pros and cons.


tables seem to have numbers, but there doesn’t seem to be an easy 
mapping of those numbers to the table names.

From:

https://www.postgresql.org/docs/10/static/functions-admin.html#FUNCTIONS-ADMIN-DBOBJECT

select pg_relation_filepath('id_test'), pg_relation_filenode('id_test');
 pg_relation_filepath | pg_relation_filenode 




--+-- 




 base/733941/2976140  |  2976140



I thumbed through the documentation, but didn’t see any heading that was 
likely to have that information.  Is there some where  a “table of 
tables” that lets you look up various metadata?


https://www.postgresql.org/docs/10/static/catalogs.html

For tables in particular:

https://www.postgresql.org/docs/10/static/catalog-pg-class.html

There is also:

https://www.postgresql.org/docs/10/static/information-schema.html


And in psql there are the \ commands:

https://www.postgresql.org/docs/10/static/app-psql.html

In psql \? will get you a list of these with descriptions

As an example:

test_(aklaver)> \d id_test
 Table "public.id_test"
 Column |   Type| Collation | Nullable | 
Default

+---+---+--+-
 id | integer   |   | not null | 
nextval('id_test_id_seq'::regclass)

 fld_1  | character varying |   |  |
 fld_2  | boolean   |   |  |

test_(aklaver)> \d+ id_test
 Table "public.id_test"
 Column |   Type| Collation | Nullable | 
Default   | Storage  | Stats target | Description

+---+---+--+-+--+--+-
 id | integer   |   | not null | 
nextval('id_test_id_seq'::regclass) | plain|  |
 fld_1  | character varying |   |  | 
 | extended |  |
 fld_2  | boolean   |   |  | 
 | plain|  |




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: How can I get and handle the status of sql statements that run in plpgsql ?

[Adrian Klaver]

On 10/2/18 1:47 PM, David Gauthier wrote:

Hi:
psql (9.6.7, server 9.5.2) on linux

How does one get the status of an sql statement executed in plpgsql?  If 
that status is cryptic, how can that be translated to something which 
someone could understand?   Finally, how can I effectively do a start 
transaction and either rollback or commit based on the results of the 
sql statements run?






Of course I don't know what the  and 
"something_went_wrong" pieces look like, or they even make sense with 
how this sort of thing shold be properly handled in plpgsql.  Also, in 


The below(read to bottom of the page) might help:

https://www.postgresql.org/docs/10/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING


my trials, it appears that plpgsql doesn't like "start transaction".  So 
how is that piece done ?


Thanks in Advance for any help !




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: How can I get and handle the status of sql statements that run in plpgsql ?

[Christopher Browne]

On Tue, 2 Oct 2018 at 16:48, David Gauthier  wrote:
>
> Hi:
> psql (9.6.7, server 9.5.2) on linux
>
> How does one get the status of an sql statement executed in plpgsql?  If that 
> status is cryptic, how can that be translated to something which someone 
> could understand?   Finally, how can I effectively do a start transaction and 
> either rollback or commit based on the results of the sql statements run?

For the situations you describe, I suggest that it is simple enough to
embrace the fact that stored functions run inside a pre-existing
transaction context.

You do not need to rollback or to return errors; you merely need to
raise the exceptions.

If *any* piece of the logic encountered an exception, then the
transaction has fallen into an exception state, and will automatically
be rolled back.

You can "lean on this"; either:
a) All of the logic passed with flying colours, and the transaction
may happily proceed, or
b) If any problem comes up along the way, the transaction is cancelled.

In PostgreSQL 11 (not yet a production release), there now exist
stored procedures that allow having BEGIN/COMMIT logic within a
procedure:
https://www.postgresql.org/docs/11/static/sql-createprocedure.html

I would think it likely that you'd want to use a mixture of stored
functions, that do some work on the assumption that it will all either
succeed or fail, and then use a stored procedure to do transactional
control on top of that.

But as things stand today, the transaction control will need to take
place in whatever layer you are using to control things.  So, if
you're using an app written in Python to control things, you'd submit
the BEGIN/COMMIT on the Python side, and the stored functions run
within transaction context.  And next year, when PG11 is available,
perhaps the BEGIN/COMMIT could reside in a stored procedure, so that
the Python code gets a bit simpler.  (Or s/Python/Java/g, or
s/Python/PHP/g as needed...)
-- 

When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

How can I get and handle the status of sql statements that run in plpgsql ?

[David Gauthier]

Hi:
psql (9.6.7, server 9.5.2) on linux

How does one get the status of an sql statement executed in plpgsql?  If
that status is cryptic, how can that be translated to something which
someone could understand?   Finally, how can I effectively do a start
transaction and either rollback or commit based on the results of the sql
statements run?

create or replace function xfer_savings_to_checking(acct_no text, howmuch
float)
 returns text as $$

  declare
savings_balance float;
checking_balance float;
  begin

select balance into savings_balance from savings_acct_info where acct =
acct_no;
sql_status = 
if(something_went_wrong) then
  raise exception 'select savings statement was bad "%"',sql_status;
  return 'error';
end if;

select balance into checking_balance from checking_acct_info where acct
= acct_no;
sql_status = 
if(something_went_wrong) then
  raise exception 'select checking statement was bad "%"',sql_status;
  return 'error';
end if;

if(howmuch > saving_balance) then
  raise notice 'Hey, you dont have that much to xfer !  You only have
%',savings_balance;
  return 'error';
end if;

start transaction;

  update savings_acct_info set balance = balance - howmuch where acct =
acct_no;
  sql_status = 
  if(something_went_wrong) then
raise exception 'updating savings acct "%"',sql_status;
rollback;
return 'error';
  end if;

  update checking_acct_info set balance = balance + howmuch where acct
= acct_no;
  sql_status = 
  if(something_went_wrong) then
raise exception 'updating checking acct "%"',sql_status;
rollback;
return 'error';
  end if;

commit;

  end;
$$ language plpgsql;

Of course I don't know what the  and
"something_went_wrong" pieces look like, or they even make sense with how
this sort of thing shold be properly handled in plpgsql.  Also, in my
trials, it appears that plpgsql doesn't like "start transaction".  So how
is that piece done ?

Thanks in Advance for any help !

metadata about creation and size of tables

[Martin Mueller]

I’ve looked at the documentation to find where to find some data that are very 
to find Mysql:  the creation and modification data of a table and the size of 
particular tables.

Where do I find an answer to the question “which is the last table I created” 
or “when did I last modify this table?”  In the data directory, tables seem to 
have numbers, but there doesn’t seem to be an easy mapping of those numbers to 
the table names.

I thumbed through the documentation, but didn’t see any heading that was likely 
to have that information.  Is there some where  a “table of tables” that lets 
you look up various metadata?

Re: pg_sleep() inside plpgsql block - pro & cons

[Francisco Olarte]

On Tue, Oct 2, 2018 at 3:44 PM, pinker  wrote:
...
> Yes, I do try to convince them to do it outside the db, that's the reason
> I'm looking for some support here :) I'm not sure those 2 reasons are enough
> to convince them, wanted to be prepared...

Well, not knowing the logic I cannot be sure, but you can try the "it
depends on isolation level" I pointed to.

> You know it's always time to do refactoring and in this case it's a lot of
> time because of the amount and complexity of the logic in db.

Not having seen it, I trust your word. But I doubt the dessign can be
correct, it seems a case of "all I know is pl/xx, so I'll do
everything I can in it. I got one product with a similar problem in
Oracle.

But unless the logic is really perverse, it seems to me you could do
two procs, the normal one ( without the pg_sleep in a loop ), and a
wrapping one testing for the "data appears" condition and calling the
first, and call the second in a loop. But again, details will be
needed and trusting your words I do not want them on the list ;->  .

Francisco Olarte.

FTS trigger works 1 at a time, but fails with bulk insert script

[Malik Rumi]

I have set up Postgres FTS on a Django/Python web site, and it works as
expected except for this one thing. When I wrote a script to bulk insert
legacy docs, the script works fine but the FTS trigger does not fire. I
have to go back and open each document one at a time to get them indexed.

What am I missing to make this work? Thanks.

BEGIN
  SELECT setweight(to_tsvector(NEW.title), 'A') ||
 setweight(to_tsvector(NEW.content), 'B') ||
 setweight(to_tsvector(NEW.category), 'D') ||
 setweight(to_tsvector(COALESCE(string_agg(tag.tag, ',
'), '')), 'C')
  INTO NEW.search_vector
  FROM ktab_entry AS entry
LEFT JOIN ktab_entry_tags AS entry_tags ON
entry_tags.entry_id = entry.id
LEFT JOIN ktab_tag AS tag ON tag.id = entry_tags.tag_id
  WHERE entry.id = NEW.id
  GROUP BY entry.id, category;
  RETURN NEW;
END;


*“None of you has faith until he loves for his brother or his neighbor what
he loves for himself.”*

Re: Postgres 11 procedures and result sets

[Merlin Moncure]

On Mon, Oct 1, 2018 at 6:57 PM Tom Lane  wrote:
> Jan Kohnert  writes:
> > I have a question regarding the new stored procedures in Postgres 11 (I 
> > tested
> > beta4):
> > I'd like to know if it is somehow possible to get a (or possibly more) 
> > result
> > set from selects within the SP, as it is possible in MariaDB, MySQL, or SQL
> > Server.
>
> Not there as of v11, other than the refcursor approach you already know
> about.  We hope to have something nicer worked out for v12.  There
> are a lot of compatibility issues to sort through :-(

There are a few other ways of dealing with this.

If the data being returned isn't very large, you can stuff multiple
'datasets' into a single json.  I do this all the time today, with
functions.  Yet another tactic is to create temp tables (maybe ON
COMMIT DROP) and refer to those tables after calling the procedure.  I
would strongly consider this if the returned data was large and the
function/procedure was not called at a high rate (making system
catalog thrash in issue).  I would probably use these tactics,
especially the json style return, even after multi-result style
invocation were to drop.

merlin

how to identify the timeline of specified recovery_target_time when do multiple PITR

[magodo]

I read the document about recovery configuration, it says:

   recovery_target_timeline (string)

   ...

   Other than that you only need to set this parameter in complex re-
   recovery situations, where you need to return to a state that itself
   was reached after a point-in-time recovery. See Section 25.3.5 for
   discussion.

   ...

Andin section 25.3.5 it says:

   ...

   If you wish to recover into some child timeline (that is, you want
   to return to some state that was itself generated after a recovery
   attempt), you need to specify the target timeline ID in
   recovery.conf.

   ...

Therefore, suppose I am going to recover to a point of some child
timeline, to identify the point, I have to specify either
recovery_target_name or recovery_target_time, and also specify the
recovery_target_timeline.

It is more like a tuple like (recovery_target_time,
recovery_target_timeline), that specify a real point among all history
branches. Am I understand this correctly?

If yes, what I want to ask is that, though the timeline is increasing
between different recovery, but each timestamp corresponds to a
timeline ID, one by one. So if I get a recovery_target_time, why should
I still specify the recovery_target_timeline? 

Suppose following illustration:

 A B
BASE-+-+--o1 (recover to A)  1
 | |   C
 +.|...+---o2 (regret, recover to B) 2
   |   |
   +...|..--o3 (regret again, recover to C)  3
   | 
   + 4


Legend:

   BASE: basebackup
   A-Z: recovery point
   ---: active wal histroy (continuous among branches)
   ...: inactive wal history
   oN: point to do PITR

If am at "o3", and I want to recover to "C", if I don't specify
timeline ID, then i will ends up with state as "o1". Only if I specify
timeline ID as "2", then I can get the state as I expect (as
illustrated). 

Why not just recover along the history from timeline 1 to the timeline
specified by recovery target? The only reason I can imagine is that
there is a chance that there are two active timeline at same point, but
what is the use case of that(if it exists)?

Re: Postgres trigger side-effect is occurring out of order with row-level security select policy

[Carl Sverre]

Dean,
Thank you for the pointer towards visibility/volatility.  I think that
completely explains the effect that I am seeing in my repro.  I
experimented with using a VOLATILE function for the SELECT RLS using
statement and while it completely solves my issue, it incurs too high a
cost for query execution due to the RLS policy no longer being inlined into
the scan.

I have documented your answer and my experimentation on the stack overflow
answer:
https://stackoverflow.com/questions/52565720/postgres-trigger-side-effect-is-occurring-out-of-order-with-row-level-security-s

Feel free to make edits/suggestions if you feel I missed something in
summarizing the solution.  Also, this thread is still open to anyone who
can provide a solution which does not incur an optimization penalty -
however based on my new understanding of the underlying behavior I don't
believe this is possible.

Thank's to everyone for their contribution in figuring this out - much
appreciated.

Carl Sverre

http://www.carlsverre.com


On Mon, Oct 1, 2018 at 4:02 AM Dean Rasheed 
wrote:

> The real issue here is to do with the visibility of the data inserted
> by the trigger function from within the same command. In general, data
> inserted by a command is not visible from within that same command.
>
> The easiest way to see what's going on is with a simple example.
> Consider the following (based on the original example, but without any
> RLS):
>
>
> DROP TABLE IF EXISTS a,b;
>
> CREATE TABLE a (id text);
> CREATE TABLE b (id text);
>
> CREATE OR REPLACE FUNCTION reproHandler() RETURNS TRIGGER AS $$
> BEGIN
> RAISE NOTICE USING MESSAGE = 'inside trigger handler';
> INSERT INTO b (id) VALUES (NEW.id);
> RETURN NEW;
> END;
> $$ LANGUAGE plpgsql;
>
> CREATE TRIGGER reproTrigger BEFORE INSERT ON a
> FOR EACH ROW EXECUTE PROCEDURE reproHandler();
>
> CREATE OR REPLACE FUNCTION check_b1(text) RETURNS boolean AS $$
> BEGIN
>   RETURN (EXISTS (SELECT * FROM b WHERE b.id = $1));
> END
> $$ LANGUAGE plpgsql STABLE;
>
> CREATE OR REPLACE FUNCTION check_b2(text) RETURNS boolean AS $$
> BEGIN
>   RETURN (EXISTS (SELECT * FROM b WHERE b.id = $1));
> END
> $$ LANGUAGE plpgsql VOLATILE;
>
> INSERT INTO a VALUES ('xxx')
>   RETURNING id, check_b1(id), check_b2(id),
> (EXISTS (SELECT * FROM b WHERE b.id = a.id));
>
> NOTICE:  inside trigger handler
>  id  | check_b1 | check_b2 | exists
> -+--+--+
>  xxx | f| t| f
> (1 row)
>
> INSERT 0 1
>
>
> Notice that the functions check_b1() and check_b2() are identical,
> except that check_b1() is declared STABLE and check_b2() is declared
> VOLATILE, and that makes all the difference. Quoting from the
> documentation for function volatility [1]:
>
> For functions written in SQL or in any of the standard procedural
> languages, there is a second important property determined by the
> volatility category, namely the visibility of any data changes that
> have been made by the SQL command that is calling the function. A
> VOLATILE function will see such changes, a STABLE or IMMUTABLE
> function will not.
>
> [1] https://www.postgresql.org/docs/10/static/xfunc-volatility.html
>
> Also notice that the inline EXISTS query behaves in the same way as
> the STABLE function -- i.e., it does not see changes made in the
> current query.
>
> So returning to the RLS example, because the RLS SELECT policy is
> defined using inline SQL, it cannot see the changes made by the
> trigger. If you want to see such changes, you need to define a
> VOLATILE function to do the RLS check.
>
> Regards,
> Dean
>

Re: CREATE TABLE AS SELECT hangs

[derek]

Tom.

Thanks so much for your response. Your theory appears to have been 
correct, and it is working like a champ now.


Best,

Derek

On 10/1/2018 4:29 PM, Tom Lane wrote:

derek  writes:

I am trying to populate/create a database with CREATE TABLE AS SELECT
like so:
 CREATE TABLE test_table AS
 SELECT row_number() over() as gid, cells.geom
 FROM test_geom_function(1,2,3) AS cells;
This works on one database instance, but not on another. On the database
it doesn't work on it seems to hang on executing and when I cancel it I get
 ERROR:  canceling statement due to user request
 CONTEXT:  while inserting index tuple (6,13) in relation
 "pg_type_typname_nsp_index"
 SQL state: 57014

Hmm (pokes around) ... That error context message seems to only be
possible if we were blocked waiting for some other transaction.
I theorize that you have an uncommitted transaction someplace that
has created the same table name.  Cancelling it would fix things.

regards, tom lane

Re: regarding bdr extension

[Andreas Kretschmer]

Am 02.10.2018 um 15:29 schrieb Adrian Klaver:




does this pgdg repository useful for configuration of bdr v3 ?


BDR is a third party extension from 2nd Quadrant, they would be the 
ones to ask about configuring. 


i said it already, BDR3 is not for public, only for our customers. You 
will need a own support contract.



Durgamahesh Manne, please contact us, if you are interesst in BDR version 3.


Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: pg_sleep() inside plpgsql block - pro & cons

[pinker]

Francisco Olarte wrote
> I do some similar things, but I sleep outside of the
> database, is there a reason this can not be done?
> 
> Francisco Olarte.

Yes, I do try to convince them to do it outside the db, that's the reason
I'm looking for some support here :) I'm not sure those 2 reasons are enough
to convince them, wanted to be prepared...
You know it's always time to do refactoring and in this case it's a lot of
time because of the amount and complexity of the logic in db.




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: regarding bdr extension

[Adrian Klaver]

On 10/1/18 9:00 AM, Durgamahesh Manne wrote:





  Hi  sir

* Since you are on Ubuntu why not use the PGDG repo?:

  in my company env  i am using pg installer to manage the postgres servers


You have not said, but I am going to guess you are talking about 
Postgres Installer from 2nd Quadrant. I could not find any docs on it, 
but they do have an email for asking questions:


pginstal...@2ndquadrant.com



does this pgdg repository useful for configuration of bdr v3 ?


BDR is a third party extension from 2nd Quadrant, they would be the ones 
to ask about configuring.




If yes then i can use it for bdr

please let me know some information about pgdg repository in detail

* Does opt/PostgreSQL/10/share/postgresql/extension/bdr.control exist?

    bdr.control  file not available


Regards

Durgamahesh Manne








--
Adrian Klaver
adrian.kla...@aklaver.com

Re: pg_sleep() inside plpgsql block - pro & cons

[Francisco Olarte]

Hi:

On Tue, Oct 2, 2018 at 12:10 PM, pinker  wrote:
> There is second time I see that somebody uses pg_sleep function inside
> plpgsql block. This case is quite similar to the last one - it's some kind
> of wait for data to be loaded. After pg_sleep there is a check if some
> condition is true, if not procedure goes to sleep again. As a result an
> average duration of this function is 1,5h...
> I'm trying to gather pros and cons regarding using pg_sleep this way. What's
> coming to my mind are only 2 cons:
> * clog contention
> * long running open transactions (which is quite good described in here:
> https://www.simononsoftware.com/are-long-running-transactions-bad/)
> So maybe you'll add some more to the list?

With so few details, nothing much can be said. Cons, if the proc is
something like do-stuff wait for data to appear, do more stuff, I
think the function will also need read-commited or something similar
to see the data appear, and fail under serializable. Pattern certainly
smells funny. I do some similar things, but I sleep outside of the
database, is there a reason this can not be done?

Francisco Olarte.

pg_sleep() inside plpgsql block - pro & cons

[pinker]

Hi!
There is second time I see that somebody uses pg_sleep function inside
plpgsql block. This case is quite similar to the last one - it's some kind
of wait for data to be loaded. After pg_sleep there is a check if some
condition is true, if not procedure goes to sleep again. As a result an
average duration of this function is 1,5h...
I'm trying to gather pros and cons regarding using pg_sleep this way. What's
coming to my mind are only 2 cons:
* clog contention
* long running open transactions (which is quite good described in here:
https://www.simononsoftware.com/are-long-running-transactions-bad/)

So maybe you'll add some more to the list?



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Postgres trigger side-effect is occurring out of order with row-level security select policy

[Dean Rasheed]

On Mon, 1 Oct 2018 at 21:45, Carl Sverre  wrote:
> Dean,
> Thank you for the pointer towards visibility/volatility.  I think that 
> completely explains the effect that I am seeing in my repro.  I experimented 
> with using a VOLATILE function for the SELECT RLS using statement and while 
> it completely solves my issue, it incurs too high a cost for query execution 
> due to the RLS policy no longer being inlined into the scan.
>
> I have documented your answer and my experimentation on the stack overflow 
> answer:
> https://stackoverflow.com/questions/52565720/postgres-trigger-side-effect-is-occurring-out-of-order-with-row-level-security-s
>

I had a quick look at that and found a bug in your implementation. The
RLS check function is defined as follows:

CREATE OR REPLACE FUNCTION rlsCheck(id text) RETURNS TABLE (id text) AS $$
select * from b where b.id = id
$$ LANGUAGE sql VOLATILE;

which is incorrect because of the ambiguous reference to "id". That
final "id" will, by default, refer to the table column b.id, not the
parameter "id". Thus that function will return every row of b, and
your check won't be doing what you want. That's also going to hurt
performance, but you didn't provide enough information to diagnose the
actual performance problem that you are seeing.

In any case, the above needs to be written as

CREATE OR REPLACE FUNCTION rlsCheck(text) RETURNS TABLE (id text) AS $$
select id from b where b.id = $1
$$ LANGUAGE sql VOLATILE;

to work as expected.

Regards,
Dean