BUG #16258: exec_simple_query does not call ProcessCompletedNotifies after CommitTransactionCommand

PG Bug reporting form <noreply@postgresql.org> 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

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 <tgl@sss.pgh.pa.us>:

RekGRpth <rekgrpth@gmail.com> 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

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

пт, 14 февр. 2020 г. в 20:36, Tom Lane <tgl@sss.pgh.pa.us>:

пт, 14 февр. 2020 г. в 20:36, Tom Lane <tgl@sss.pgh.pa.us>:

RekGRpth <rekgrpth@gmail.com> 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 <rekgrpth@gmail.com> 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.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

пт, 14 февр. 2020 г. в 20:36, Tom Lane <tgl@sss.pgh.pa.us>:

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

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 <tgl@sss.pgh.pa.us>:

On Friday, February 14, 2020, RekGRpth <rekgrpth@gmail.com> 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.

On Sat, Feb 15, 2020 at 4:39 AM RekGRpth <rekgrpth@gmail.com> 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

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 <jeff.janes@gmail.com>:

oops! with with command

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));select
1/0;commit;select now(), clock_timestamp();"

nothing notifies are received!

with bst regrds, Rek>pth

вт, 18 февр. 2020 г. в 07:48, RekGRpth <rekgrpth@gmail.com>: