BUG #16258: exec_simple_query does not call ProcessCompletedNotifies after CommitTransactionCommand

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #16258: exec_simple_query does not call ProcessCompletedNotifies after CommitTransactionCommand

PG Bug reporting form
The following bug has been logged on the website:

Bug reference:      16258
Logged by:          RekGRpth
Email address:      [hidden email]
PostgreSQL version: 12.2
Operating system:   Docker alpine edge
Description:        

to reproduse

psql -c "begin;select pg_notify('channel', concat_ws(' = ', 'a',
clock_timestamp()::text));commit;select pg_sleep(10);begin;select
pg_notify('channel', concat_ws(' = ', 'b',
clock_timestamp()::text));commit;select now(), clock_timestamp();"

Reply | Threaded
Open this post in threaded view
|

Re: BUG #16258: exec_simple_query does not call ProcessCompletedNotifies after CommitTransactionCommand

Tom Lane-2
PG Bug reporting form <[hidden email]> writes:
> psql -c "begin;select pg_notify('channel', concat_ws(' = ', 'a',
> clock_timestamp()::text));commit;select pg_sleep(10);begin;select
> pg_notify('channel', concat_ws(' = ', 'b',
> clock_timestamp()::text));commit;select now(), clock_timestamp();"

I think the main reason why this isn't reporting any notifications
is that you forgot to issue a LISTEN.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: BUG #16258: exec_simple_query does not call ProcessCompletedNotifies after CommitTransactionCommand

RekGRpth
No, it wasn't. I listen in another connection and receive both notifications but only after 10 seconds!
with bst regrds, Rek>pth


пт, 14 февр. 2020 г. в 20:20, Tom Lane <[hidden email]>:
PG Bug reporting form <[hidden email]> writes:
> psql -c "begin;select pg_notify('channel', concat_ws(' = ', 'a',
> clock_timestamp()::text));commit;select pg_sleep(10);begin;select
> pg_notify('channel', concat_ws(' = ', 'b',
> clock_timestamp()::text));commit;select now(), clock_timestamp();"

I think the main reason why this isn't reporting any notifications
is that you forgot to issue a LISTEN.

                        regards, tom lane
Reply | Threaded
Open this post in threaded view
|

Re: BUG #16258: exec_simple_query does not call ProcessCompletedNotifies after CommitTransactionCommand

Tom Lane-2
RekGRpth <[hidden email]> writes:
> No, it wasn't. I listen in another connection and receive both
> notifications but only after 10 seconds!

Doesn't sound like a bug to me.  The sending backend will only
send out notifications when it goes idle.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: BUG #16258: exec_simple_query does not call ProcessCompletedNotifies after CommitTransactionCommand

RekGRpth
Ok, thanks. But I expect to receive notification at every transaction commit!
with bst regrds, Rek>pth


пт, 14 февр. 2020 г. в 20:36, Tom Lane <[hidden email]>:
RekGRpth <[hidden email]> writes:
> No, it wasn't. I listen in another connection and receive both
> notifications but only after 10 seconds!

Doesn't sound like a bug to me.  The sending backend will only
send out notifications when it goes idle.

                        regards, tom lane
Reply | Threaded
Open this post in threaded view
|

Re: BUG #16258: exec_simple_query does not call ProcessCompletedNotifies after CommitTransactionCommand

David G Johnston

пт, 14 февр. 2020 г. в 20:36, Tom Lane <[hidden email]>:
RekGRpth <[hidden email]> writes:
> No, it wasn't. I listen in another connection and receive both
> notifications but only after 10 seconds!

Doesn't sound like a bug to me.  The sending backend will only
send out notifications when it goes idle

 Does a non-interactive psql session ever go idle mid-script?

On Friday, February 14, 2020, RekGRpth <[hidden email]> wrote:
Ok, thanks. But I expect to receive notification at every transaction commit!
with bst

This seems reasonable on its face and I don’t see any documentation regarding this aspect of timing.  All the docs says is listen/notify only take effect after the transaction they are in commits.  Absent further info one can reasonably assume that they take effect immediately after commit and not at the first idle moment subsequent to the commit.

David J.

Reply | Threaded
Open this post in threaded view
|

Re: BUG #16258: exec_simple_query does not call ProcessCompletedNotifies after CommitTransactionCommand

Tom Lane-2
"David G. Johnston" <[hidden email]> writes:
>> пт, 14 февр. 2020 г. в 20:36, Tom Lane <[hidden email]>:
>>> Doesn't sound like a bug to me.  The sending backend will only
>>> send out notifications when it goes idle

>  Does a non-interactive psql session ever go idle mid-script?

Sure.  "idle" here means that the backend is done with the current
command from the client.  I think the OP's problem largely stems from
trying to do two transactions in one simple-query message, which is
an abuse of that feature to begin with.  (We've discussed taking away
the ability to do that altogether, and while I've resisted that on
backwards-compatibility grounds, it might well happen someday.)

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: BUG #16258: exec_simple_query does not call ProcessCompletedNotifies after CommitTransactionCommand

RekGRpth
No, it doesn't. The quote from official documentation: "NOTIFY interacts with SQL transactions in some important ways. Firstly, if a NOTIFY is executed inside a transaction, the notify events are not delivered until and unless the transaction is committed." And nothing about any "idle"!

And how about several transaction in new procedures?

with bst regrds, Rek>pth


пт, 14 февр. 2020 г. в 22:31, Tom Lane <[hidden email]>:
"David G. Johnston" <[hidden email]> writes:
>> пт, 14 февр. 2020 г. в 20:36, Tom Lane <[hidden email]>:
>>> Doesn't sound like a bug to me.  The sending backend will only
>>> send out notifications when it goes idle

>  Does a non-interactive psql session ever go idle mid-script?

Sure.  "idle" here means that the backend is done with the current
command from the client.  I think the OP's problem largely stems from
trying to do two transactions in one simple-query message, which is
an abuse of that feature to begin with.  (We've discussed taking away
the ability to do that altogether, and while I've resisted that on
backwards-compatibility grounds, it might well happen someday.)

                        regards, tom lane
Reply | Threaded
Open this post in threaded view
|

Re: BUG #16258: exec_simple_query does not call ProcessCompletedNotifies after CommitTransactionCommand

David G Johnston
On Friday, February 14, 2020, RekGRpth <[hidden email]> wrote:
No, it doesn't.

Its capable of doing so.  Use —file instead of —command for your test.

Also, can you please stop top-posting.  The convention for this list is to inline or bottom-post.

David J.

Reply | Threaded
Open this post in threaded view
|

Re: BUG #16258: exec_simple_query does not call ProcessCompletedNotifies after CommitTransactionCommand

Jeff Janes
In reply to this post by RekGRpth
On Sat, Feb 15, 2020 at 4:39 AM RekGRpth <[hidden email]> wrote:

And how about several transaction in new procedures?

That does seem like a legitimate gripe.

create or replace procedure foo() LANGUAGE plpgsql as $$
  begin 
  FOR i IN 1..100 LOOP
    perform pg_notify('channel','HI');
    perform pg_sleep(1); 
    commit;
  end loop;
end; $$;
CALL foo();

An actively polling listener gets all notification all at once at the end.  (I just use psql with a "LISTEN channel;" and the repeatedly execute ";" to get it to poll)

 Cheers,

Jeff
Reply | Threaded
Open this post in threaded view
|

Re: BUG #16258: exec_simple_query does not call ProcessCompletedNotifies after CommitTransactionCommand

RekGRpth
Thanks for the tip! I learned outer code of function exec_simple_query and I seen "idle" condition of calling ProcessCompletedNotifies! In my postgres job scheduler https://github.com/RekGRpth/pg_task I copied exec_simple_query and modified it to call ProcessCompletedNotifies after CommitTransactionCommand and it works!
with bst regrds, Rek>pth


сб, 15 февр. 2020 г. в 23:16, Jeff Janes <[hidden email]>:
On Sat, Feb 15, 2020 at 4:39 AM RekGRpth <[hidden email]> wrote:

And how about several transaction in new procedures?

That does seem like a legitimate gripe.

create or replace procedure foo() LANGUAGE plpgsql as $$
  begin 
  FOR i IN 1..100 LOOP
    perform pg_notify('channel','HI');
    perform pg_sleep(1); 
    commit;
  end loop;
end; $$;
CALL foo();

An actively polling listener gets all notification all at once at the end.  (I just use psql with a "LISTEN channel;" and the repeatedly execute ";" to get it to poll)

 Cheers,

Jeff