Re: NOTIFY in multi-statement PQexec() not sent outside of transaction - Mailing list pgsql-bugs
| From | David G. Johnston |
|---|---|
| Subject | Re: NOTIFY in multi-statement PQexec() not sent outside of transaction |
| Date | |
| Msg-id | CAKFQuwZRoG3jrhq2s+VUpkDgWO9-CxMc+9yfUiDWK03DDrNj0w@mail.gmail.com Whole thread Raw |
| In response to | Re: NOTIFY in multi-statement PQexec() not sent outside of transaction (John Muehlhausen <jgm@jgm.org>) |
| List | pgsql-bugs |
Thanks again,JohnOn Mon, Apr 20, 2020 at 2:42 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:John Muehlhausen <jgm@jgm.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@jgm.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."
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,
David J.
pgsql-bugs by date: