Unlisten / listen in a transaction failure

[Greg Sabino Mullane]

I came across some unusual behavior with listen. Basically, if you
unlisten and listen inside of a transaction, new notices are not
picked up right away - but they will show up if you send yourself
a notice. It also works as expected if you unlisten, commit, and
then re-listen. Tested on 9.1 and 9.2. Demo psql script:

listen abc; \t
\! psql -p 5491 -c 'notify abc'
select * from pg_listening_channels();

begin; unlisten *; listen abc; commit;
\! psql -p 5491 -c 'notify abc'
select * from pg_listening_channels();

notify abc;

Output of above on 9.1 with psql -e:

listen abc;
LISTEN
Showing only tuples.
NOTIFY
select * from pg_listening_channels();
abc

Asynchronous notification "abc" received from server process with PID 10879.

begin;
BEGIN
unlisten *;
UNLISTEN
listen abc;
LISTEN
commit;
COMMIT
NOTIFY
select * from pg_listening_channels();
abc

notify abc;
NOTIFY
Asynchronous notification "abc" received from server process with PID 10882.
Asynchronous notification "abc" received from server process with PID 10876.

[Tom Lane]

Post by Greg Sabino Mullane
I came across some unusual behavior with listen. Basically, if you
unlisten and listen inside of a transaction, new notices are not
picked up right away - but they will show up if you send yourself
a notice. It also works as expected if you unlisten, commit, and

Huh. If you run this in an assert-enabled build, it gets an assert
failure:

regression=# listen abc;
LISTEN
regression=# notify abc;
NOTIFY
Asynchronous notification "abc" received from server process with PID 19048.
regression=# begin; unlisten *; listen abc; commit;
BEGIN
UNLISTEN
LISTEN
COMMIT
regression=# notify abc;
The connection to the server was lost. Attempting reset: Failed.

The Assert is

TRAP: FailedAssertion("!(MyProcPid == (asyncQueueControl->backend[MyBackendId].pid))", File: "async.c", Line: 1821)

which shows that we aren't actually registered in the global array of
listeners, even though we think we should be.

I think the problem is that we do Exec_ListenPreCommit, then
Exec_UnlistenAllCommit, then Exec_ListenCommit --- and the second
of these undoes our registration as a listener. That needs rethinking.
Looking at it, the backendHasExecutedInitialListen flag seems pretty
badly thought out too. It looks to me like we'd be better off with a
flag defined like "amRegisteredListener" that tracks whether we're
currently in the array or not, and during AtCommit_Notify() we shouldn't
deregister as a listener until we've scanned all the pending actions and
know whether we are ending in a no-listens state or not.

regards, tom lane