pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedNOTIFY in multi-statement PQexec() not sent outside of transaction
10+ messages / 3 participants
[nested] [flat]
* NOTIFY in multi-statement PQexec() not sent outside of transaction
@ 2020-04-20 17:13 John Muehlhausen <jgm@jgm.org>
2020-04-20 18:26 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: John Muehlhausen @ 2020-04-20 17:13 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
macOS 10.13.6 (17G11023)
PostgreSQL 10.5 (Debian 10.5-2.pgdg90+1) on x86_64-pc-linux-gnu, compiled
by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
Recreate with the following script. Expected behavior with TEST_DSN set to
your database. To see the broken behavior also set TEST_BREAK=1
---
# python 3.6
# set TEST_DSN env var
import os
import sys
import select
import psycopg2 # 2.8.4
from multiprocessing import Process, Queue
def notifier(input,output):
with psycopg2.connect(os.environ['TEST_DSN']) as conn:
conn.autocommit=True
with conn.cursor() as cur:
input.get()
if 'TEST_BREAK' in os.environ:
# docs seem to indicate that an implied transaction
# will not exist if there is an explicit begin/commit?
cur.execute(("notify __test; "
"begin; select pg_advisory_lock(7777); "
"select pg_advisory_unlock(7777); commit"))
else:
# this version works but introduces a race condition
# into my test suite where I am not sure that I am "in"
# a PQexec() but might instead be between such calls
cur.execute("notify __test")
cur.execute(( "select pg_advisory_lock(7777); "
"select pg_advisory_unlock(7777)"))
print('notifier unlocked', file=sys.stderr)
output.put(())
with psycopg2.connect(os.environ['TEST_DSN']) as conn:
conn.autocommit=True
with conn.cursor() as cur:
cur.execute("listen __test; select pg_advisory_lock(7777)")
input, output = Queue(), Queue()
p = Process(target=notifier, args=(output,input))
p.start()
output.put(())
print('waiting for notification',file=sys.stderr)
select.select([conn],[],[])
conn.poll()
print('notification received',file=sys.stderr)
cur.execute("select pg_advisory_unlock(7777)")
input.get()
p.join()
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: NOTIFY in multi-statement PQexec() not sent outside of transaction
2020-04-20 17:13 NOTIFY in multi-statement PQexec() not sent outside of transaction John Muehlhausen <jgm@jgm.org>
@ 2020-04-20 18:26 ` David G. Johnston <david.g.johnston@gmail.com>
2020-04-20 19:32 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction John Muehlhausen <jgm@jgm.org>
0 siblings, 1 reply; 10+ messages in thread
From: David G. Johnston @ 2020-04-20 18:26 UTC (permalink / raw)
To: John Muehlhausen <jgm@jgm.org>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Monday, April 20, 2020, John Muehlhausen <jgm@jgm.org> wrote:
>
> # docs seem to indicate that an implied transaction
> # will not exist if there is an explicit begin/commit?
> cur.execute(("notify __test; "
> "begin; select pg_advisory_lock(7777); "
> "select pg_advisory_unlock(7777); commit"))
>
>
A more comprehensive reading of the docs finds:
“ 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. “.
David J.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: NOTIFY in multi-statement PQexec() not sent outside of transaction
2020-04-20 17:13 NOTIFY in multi-statement PQexec() not sent outside of transaction John Muehlhausen <jgm@jgm.org>
2020-04-20 18:26 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction David G. Johnston <david.g.johnston@gmail.com>
@ 2020-04-20 19:32 ` John Muehlhausen <jgm@jgm.org>
2020-04-20 19:42 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: John Muehlhausen @ 2020-04-20 19:32 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Wrapping the notify in a transaction does not fix it. Shouldn't the notify
be available as soon as the containing transaction is committed?
cur.execute(("begin; notify __test; commit; "
"begin; select pg_advisory_lock(7777); "
"select pg_advisory_unlock(7777); commit"))
On Mon, Apr 20, 2020 at 1:26 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Monday, April 20, 2020, John Muehlhausen <jgm@jgm.org> wrote:
>
>>
>> # docs seem to indicate that an implied transaction
>> # will not exist if there is an explicit begin/commit?
>> cur.execute(("notify __test; "
>> "begin; select pg_advisory_lock(7777); "
>> "select pg_advisory_unlock(7777); commit"))
>>
>>
> A more comprehensive reading of the docs finds:
>
> “ 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. “.
>
> David J.
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: NOTIFY in multi-statement PQexec() not sent outside of transaction
2020-04-20 17:13 NOTIFY in multi-statement PQexec() not sent outside of transaction John Muehlhausen <jgm@jgm.org>
2020-04-20 18:26 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction David G. Johnston <david.g.johnston@gmail.com>
2020-04-20 19:32 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction John Muehlhausen <jgm@jgm.org>
@ 2020-04-20 19:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-04-20 19:56 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction John Muehlhausen <jgm@jgm.org>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2020-04-20 19:42 UTC (permalink / raw)
To: John Muehlhausen <jgm@jgm.org>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: NOTIFY in multi-statement PQexec() not sent outside of transaction
2020-04-20 17:13 NOTIFY in multi-statement PQexec() not sent outside of transaction John Muehlhausen <jgm@jgm.org>
2020-04-20 18:26 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction David G. Johnston <david.g.johnston@gmail.com>
2020-04-20 19:32 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction John Muehlhausen <jgm@jgm.org>
2020-04-20 19:42 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-04-20 19:56 ` John Muehlhausen <jgm@jgm.org>
2020-04-20 20:10 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction Tom Lane <tgl@sss.pgh.pa.us>
2020-04-20 20:27 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: John Muehlhausen @ 2020-04-20 19:56 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Tom, thanks for the explanation!
My perspective as a libpq user is that multi-statement PQexec() should have
the same effects as multiple PQexec() calls other than for the former
dropping the results of all but the most recent statement. Rule (a)
appears to break this assumption, and at minimum this difference should be
documented?
As a practical matter, what I was trying to do is signal a test case that
Postgres is in the middle of processing a transaction string. Rule (a)
seems to mean that NOTIFY cannot be used for this. Is there some other
mechanism? I don't want to depend on fragile timer-based logic such as
"I've waited long enough so surely it is blocked in PQexec()"... that kind
of thing always gets my test cases into trouble. This particular test case
disrupts TCP during this state to ensure that reset logic works from this
state.
Why do you think that it would break more clients than it fixes? Surely it
is rare to end an explicit transaction containing a notify in a
multi-statement command? For all other cases the behavior would be
unchanged.
Thanks again,
John
On 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.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: NOTIFY in multi-statement PQexec() not sent outside of transaction
2020-04-20 17:13 NOTIFY in multi-statement PQexec() not sent outside of transaction John Muehlhausen <jgm@jgm.org>
2020-04-20 18:26 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction David G. Johnston <david.g.johnston@gmail.com>
2020-04-20 19:32 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction John Muehlhausen <jgm@jgm.org>
2020-04-20 19:42 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction Tom Lane <tgl@sss.pgh.pa.us>
2020-04-20 19:56 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction John Muehlhausen <jgm@jgm.org>
@ 2020-04-20 20:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-04-20 20:27 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction John Muehlhausen <jgm@jgm.org>
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 2020-04-20 20:10 UTC (permalink / raw)
To: John Muehlhausen <jgm@jgm.org>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
John Muehlhausen <jgm@jgm.org> writes:
> My perspective as a libpq user is that multi-statement PQexec() should have
> the same effects as multiple PQexec() calls other than for the former
> dropping the results of all but the most recent statement.
Well, that's not even close to true, because of the rules about statements
within a multi-statement string getting merged into single transactions.
> As a practical matter, what I was trying to do is signal a test case that
> Postgres is in the middle of processing a transaction string.
I'm not really sure why you want to go about that in this particular way;
under ordinary circumstances the client app wouldn't see the notify until
after PQexec returned, anyway. (Yes, I realize that there are ways around
that, but still, you can't really do anything with the connection until
PQexec returns.)
Perhaps you could achieve a similar effect using single-row mode,
ie break the connection after collecting a few rows from a multi-row
query? That's not PQexec at all of course, but if you use PQsendQuery
then the server doesn't know the difference.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: NOTIFY in multi-statement PQexec() not sent outside of transaction
2020-04-20 17:13 NOTIFY in multi-statement PQexec() not sent outside of transaction John Muehlhausen <jgm@jgm.org>
2020-04-20 18:26 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction David G. Johnston <david.g.johnston@gmail.com>
2020-04-20 19:32 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction John Muehlhausen <jgm@jgm.org>
2020-04-20 19:42 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction Tom Lane <tgl@sss.pgh.pa.us>
2020-04-20 19:56 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction John Muehlhausen <jgm@jgm.org>
2020-04-20 20:10 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-04-20 20:27 ` John Muehlhausen <jgm@jgm.org>
2020-04-20 20:39 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: John Muehlhausen @ 2020-04-20 20:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
>
>
>
> > My perspective as a libpq user is that multi-statement PQexec() should
> have
> > the same effects as multiple PQexec() calls other than for the former
> > dropping the results of all but the most recent statement.
>
> Well, that's not even close to true, because of the rules about statements
> within a multi-statement string getting merged into single transactions.
>
My meaning was that with appropriate begin/commit augmentation, the user
should be able to achieve the same effects with multi-statement, and that
Rule (a) makes this unachievable, at least in the case of NOTIFY.
My goal is to write a test case where the service being tested (which is a
child process of the testing process) is known to be blocking in a PQexec
call, and during this exact state the testing process disrupts the TCP
connection between the service and postgres in various ways, to be sure
that the service recovers.
I'm not sure how to know that PQexec() has entered and not yet returned in
my service process other than to examine the effects of a partially
complete PQexec() itself. If I can't use NOTIFY then perhaps I can sniff
for the network traffic sent at the beginning of PQexec(), or perhaps there
is some extra libpq logging I could enable to know that PQexec() is in
process?
Still, even without considering my usage, I find it odd that this would not
be considered a bug, for the reason in my first sentence above. The
current behavior means that the user has to think about transactions "and
something else" when reasoning about the effects of statements. I would
vote to remove the "and something else" which would then remove the need to
augment the current documentation. As it stands, at minimum, the
documentation needs to warn the user that similar notify effects are
unachievable in the multi-statement realm?
-John
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: NOTIFY in multi-statement PQexec() not sent outside of transaction
2020-04-20 17:13 NOTIFY in multi-statement PQexec() not sent outside of transaction John Muehlhausen <jgm@jgm.org>
2020-04-20 18:26 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction David G. Johnston <david.g.johnston@gmail.com>
2020-04-20 19:32 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction John Muehlhausen <jgm@jgm.org>
2020-04-20 19:42 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction Tom Lane <tgl@sss.pgh.pa.us>
2020-04-20 19:56 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction John Muehlhausen <jgm@jgm.org>
2020-04-20 20:10 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction Tom Lane <tgl@sss.pgh.pa.us>
2020-04-20 20:27 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction John Muehlhausen <jgm@jgm.org>
@ 2020-04-20 20:39 ` David G. Johnston <david.g.johnston@gmail.com>
2020-04-22 15:38 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction John Muehlhausen <jgm@jgm.org>
0 siblings, 1 reply; 10+ messages in thread
From: David G. Johnston @ 2020-04-20 20:39 UTC (permalink / raw)
To: John Muehlhausen <jgm@jgm.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Mon, Apr 20, 2020 at 1:27 PM John Muehlhausen <jgm@jgm.org> wrote:
>
>>
>> > My perspective as a libpq user is that multi-statement PQexec() should
>> have
>> > the same effects as multiple PQexec() calls other than for the former
>> > dropping the results of all but the most recent statement.
>>
>
> Still, even without considering my usage, I find it odd that this would
> not be considered a bug, for the reason in my first sentence above. The
> current behavior means that the user has to think about transactions "and
> something else" when reasoning about the effects of statements. I would
> vote to remove the "and something else" which would then remove the need to
> augment the current documentation. As it stands, at minimum, the
> documentation needs to warn the user that similar notify effects are
> unachievable in the multi-statement realm?
>
The deeper into the architecture the change the more resistant to change it
should be. This is pretty low-level and the benefits don't seem that
great. It is functional, and the usage pattern involved, multi-statement
commands, is one that is actively discouraged - so from my perspective
classifying this as a bug that the core team should fix is unappealing. If
someone wants to offer up a feature enhancement patch for critique then
we'll see.
David J.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: NOTIFY in multi-statement PQexec() not sent outside of transaction
2020-04-20 17:13 NOTIFY in multi-statement PQexec() not sent outside of transaction John Muehlhausen <jgm@jgm.org>
2020-04-20 18:26 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction David G. Johnston <david.g.johnston@gmail.com>
2020-04-20 19:32 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction John Muehlhausen <jgm@jgm.org>
2020-04-20 19:42 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction Tom Lane <tgl@sss.pgh.pa.us>
2020-04-20 19:56 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction John Muehlhausen <jgm@jgm.org>
2020-04-20 20:10 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction Tom Lane <tgl@sss.pgh.pa.us>
2020-04-20 20:27 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction John Muehlhausen <jgm@jgm.org>
2020-04-20 20:39 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction David G. Johnston <david.g.johnston@gmail.com>
@ 2020-04-22 15:38 ` John Muehlhausen <jgm@jgm.org>
0 siblings, 0 replies; 10+ messages in thread
From: John Muehlhausen @ 2020-04-22 15:38 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Thanks for the thoughts David. I assume there is a regression test suite
for postgres? In addition to documentation, can a test case be added that
will ensure the present behavior remains unchanged until someone thinks
about fixing it? This will serve as another reminder about the issue?
On Mon, Apr 20, 2020 at 3:39 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Mon, Apr 20, 2020 at 1:27 PM John Muehlhausen <jgm@jgm.org> wrote:
>
>>
>>>
>>> > My perspective as a libpq user is that multi-statement PQexec() should
>>> have
>>> > the same effects as multiple PQexec() calls other than for the former
>>> > dropping the results of all but the most recent statement.
>>>
>>
>
>> Still, even without considering my usage, I find it odd that this would
>> not be considered a bug, for the reason in my first sentence above. The
>> current behavior means that the user has to think about transactions "and
>> something else" when reasoning about the effects of statements. I would
>> vote to remove the "and something else" which would then remove the need to
>> augment the current documentation. As it stands, at minimum, the
>> documentation needs to warn the user that similar notify effects are
>> unachievable in the multi-statement realm?
>>
>
> The deeper into the architecture the change the more resistant to change
> it should be. This is pretty low-level and the benefits don't seem that
> great. It is functional, and the usage pattern involved, multi-statement
> commands, is one that is actively discouraged - so from my perspective
> classifying this as a bug that the core team should fix is unappealing. If
> someone wants to offer up a feature enhancement patch for critique then
> we'll see.
>
> David J.
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: NOTIFY in multi-statement PQexec() not sent outside of transaction
2020-04-20 17:13 NOTIFY in multi-statement PQexec() not sent outside of transaction John Muehlhausen <jgm@jgm.org>
2020-04-20 18:26 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction David G. Johnston <david.g.johnston@gmail.com>
2020-04-20 19:32 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction John Muehlhausen <jgm@jgm.org>
2020-04-20 19:42 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction Tom Lane <tgl@sss.pgh.pa.us>
2020-04-20 19:56 ` Re: NOTIFY in multi-statement PQexec() not sent outside of transaction John Muehlhausen <jgm@jgm.org>
@ 2020-04-20 20:27 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: David G. Johnston @ 2020-04-20 20:27 UTC (permalink / raw)
To: John Muehlhausen <jgm@jgm.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
>
>
> Thanks again,
> John
>
> On 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."
[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.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2020-04-22 15:38 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-04-20 17:13 NOTIFY in multi-statement PQexec() not sent outside of transaction John Muehlhausen <jgm@jgm.org>
2020-04-20 18:26 ` David G. Johnston <david.g.johnston@gmail.com>
2020-04-20 19:32 ` John Muehlhausen <jgm@jgm.org>
2020-04-20 19:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-04-20 19:56 ` John Muehlhausen <jgm@jgm.org>
2020-04-20 20:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-04-20 20:27 ` John Muehlhausen <jgm@jgm.org>
2020-04-20 20:39 ` David G. Johnston <david.g.johnston@gmail.com>
2020-04-22 15:38 ` John Muehlhausen <jgm@jgm.org>
2020-04-20 20:27 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox