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

BUG #16258: exec_simple_query does not call ProcessCompletedNotifies after CommitTransactionCommand

From

PG Bug reporting form

Date:

14 February 2020, 09:27:40

The following bug has been logged on the website:

Bug reference:      16258
Logged by:          RekGRpth
Email address:      rekgrpth@gmail.com
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();"

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

From

Tom Lane

Date:

14 February 2020, 18:20:36

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

Re: BUG #16258: exec_simple_query does not call ProcessCompletedNotifiesafter CommitTransactionCommand

From

RekGRpth

Date:

14 February 2020, 18:23:18

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>:

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

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

From

Tom Lane

Date:

14 February 2020, 18:36:32

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

Re: BUG #16258: exec_simple_query does not call ProcessCompletedNotifiesafter CommitTransactionCommand

From

RekGRpth

Date:

14 February 2020, 18:37:44

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>:

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

Re: BUG #16258: exec_simple_query does not call ProcessCompletedNotifiesafter CommitTransactionCommand

From

"David G. Johnston"

Date:

14 February 2020, 20:14:53

пт, 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.

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

From

Tom Lane

Date:

14 February 2020, 20:30:59

"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

Re: BUG #16258: exec_simple_query does not call ProcessCompletedNotifiesafter CommitTransactionCommand

From

RekGRpth

Date:

15 February 2020, 08:42:28

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>:

"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

Re: BUG #16258: exec_simple_query does not call ProcessCompletedNotifiesafter CommitTransactionCommand

From

"David G. Johnston"

Date:

15 February 2020, 18:26:46

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.

Re: BUG #16258: exec_simple_query does not call ProcessCompletedNotifiesafter CommitTransactionCommand

From

Jeff Janes

Date:

15 February 2020, 21:15:50

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

Re: BUG #16258: exec_simple_query does not call ProcessCompletedNotifiesafter CommitTransactionCommand

From

RekGRpth

Date:

18 February 2020, 05:48:06

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>:

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

Re: BUG #16258: exec_simple_query does not call ProcessCompletedNotifiesafter CommitTransactionCommand

From

RekGRpth

Date:

25 February 2020, 05:51:55

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>:

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>:
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