Re: NOTIFY in multi-statement PQexec() not sent outside of transaction

>
>
> Thanks again,
> John
>
> On Mon, Apr 20, 2020 at 2:42 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> John Muehlhausen <jgm(at)jgm(dot)org> writes:
>> > Wrapping the notify in a transaction does not fix it. Shouldn't the
>> notify
>> > be available as soon as the containing transaction is committed?
>>
>> A notify message will be sent to the client when the backend is
>> (a) idle (*not* in the middle of processing a transaction string) and
>> (b) not in an open transaction.
>>
>> You appear to be unhappy with point (a), but I think changing that
>> would break more clients than it fixes. It's intentional that
>> we don't send notifies mid-command, and this sure looks like a
>> variant of mid-command from here.
>>
>
On Mon, Apr 20, 2020 at 12:56 PM John Muehlhausen <jgm(at)jgm(dot)org> wrote:

Rule (a) appears to break this assumption, and at minimum this difference
> should be documented?
>

It is [1] though the comment itself is in a place where LISTEN is the topic
of interest, not NOTIFY. It seems like a similar note or paragraph needs
to be placed where people using LISTEN would be typically looking.

"At present, NotificationResponse can only be sent outside a transaction,
and thus it will not occur in the middle of a command-response series,
though it might occur just before ReadyForQuery."

[1]
https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-ASYNC

Specifically, in 52.2.2.1 Multiple Statements In A Simple Query:

... "Conversely, if a BEGIN appears in a multi-statement Query message,
then it starts a regular transaction block that will only be terminated by
an explicit COMMIT or ROLLBACK, whether that appears in this Query message
or a later one. If the BEGIN follows some statements that were executed as
an implicit transaction block, those statements are not immediately
committed; in effect, they are retroactively included into the new regular
transaction block."

Add something like:

Finally, if multiple explicit transactions are present in a single Simple
Query command then statements that send events that take effect at commit
(i.e., LISTEN and NOTIFY) will not happen until just before the
ReadyToQuery event that ends the current Simple Query command.

(I'm now less certain this is a good change but I'll leave it out there for
discussion)

Then, in the NOTIFY documentation [2]:

"Firstly, if a NOTIFY is executed inside a transaction, the notify events
are not delivered until and unless the transaction is committed."

It feels like "... the notify events are not sent until ..." would be
clearer. And then add:

Furthermore, if the transaction containing the NOTIFY is part of a
multi-transaction command the notify event will be sent post-command
completion.

Specifically:

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 (Furthermore, if
the transaction containing the NOTIFY is part of a multi-transaction
command the notify event will be sent post-command completion.) This is
appropriate, since if the transaction is aborted, all the commands within
it have had no effect, including NOTIFY. But it can be disconcerting if one
is expecting the notification events to be delivered immediately. Secondly,

[2] https://www.postgresql.org/docs/12/sql-notify.html

David J.