Re: Patch for LISTEN/NOTIFY race condition?

Meetesh Karia wrote:
> Hi all,
>
> After upgrading to 8.3 and Slony 1.2.13, one of our import processes
> started constantly failing because of deadlock. Our import process
> (pl/pgsql function) just uses COPY FROM to populate a temp table and
> then inserts from that (without any special locks, etc). And, the
> deadlock error showed that the import process was waiting on sl_event
> and a slon process was waiting on pg_listener.
>
> I found the following thread in the archives:
> http://archives.postgresql.org/pgsql-bugs/2008-03/msg00117.php and it
> seemed like it could be the cause of the problem.
>
> Is the patch available anywhere? Also, if it will be available in
> 8.3.2, is there any information on when that will be released?

Hmm, AFAICS the patch should be present on 8.3.1, because it was tagged
on March 14th and the patch was committed on March 12.

2008-03-12 17:11 tgl

* doc/src/sgml/ref/prepare_transaction.sgml (1.6.2.1),
src/backend/commands/async.c (1.138.2.1):

Fix LISTEN/NOTIFY race condition reported by Laurent Birtz, by postponing
pg_listener modifications commanded by LISTEN and UNLISTEN until the end
of the current transaction. This allows us to hold the ExclusiveLock on
pg_listener until after commit, with no greater risk of deadlock than there
was before. Aside from fixing the race condition, this gets rid of a
truly ugly kludge that was there before, namely having to ignore
HeapTupleBeingUpdated failures during NOTIFY. There is a small potential
incompatibility, which is that if a transaction issues LISTEN or UNLISTEN
and then looks into pg_listener before committing, it won't see any resulting
row insertion or deletion, where before it would have. It seems unlikely
that anyone would be depending on that, though.

This patch also disallows LISTEN and UNLISTEN inside a prepared transaction.
That case had some pretty undesirable properties already, such as possibly
allowing pg_listener entries to be made for PIDs no longer present, so
disallowing it seems like a better idea than trying to maintain the behavior.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support