| Subject: | LISTEN/NOTIFY stops working if you enable AutoCommit and later disenable it |
If you use the LISTEN/NOTIFY async notification system of Postgres, it
fails if your program at any time executes any queries within a block
where AutoCommit is off. The attached program can be used to
demonstrate this effect. It is expected that no NOTIFY be delivered
while the client is within a transaction, so AutoCommit must be turned
back off to receive these notifications.
Run the program as-is, and in another window connect to database
template1 using psql and issue the command "notify vktest". You will
see nothing happens in the program window except possibly a timeout of
the loop. If you look at the pg_listener table you will see that a
pending notification is waiting for the client program. It will never
be delivered.
Now, run the same program after commenting out the line for
$dbh->{AutoCommit}=0 and issue the same NOTIFY from the psql command
line. You will see immediate delivery of the message to the program
client every time you issue the NOTIFY. The pg_listener table will show
no messages pending, as expected.
Workaround is to use begin_work() and commit() instead of AutoCommit.
This is painful when using the wonderful scoping rules of Perl to
automatically commit the transaction at end of block when AutoCommit is
set as a local() variable within the block.
DBD::Pg version: 1.41
FreeBSD 5.4/amd64
Perl 5.8.5
Message body not shown because it is not plain text.