Date: Fri, 1 Nov 2002 10:54:07 -0600
From: Bruno Wolff III <bruno@wolff.to>
To: Chris Gamache <cgg007@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: FIFO Queue Problems
Message-ID: <20021101165407.GA32463@wolff.to>
Mail-Followup-To: Chris Gamache <cgg007@yahoo.com>,
	pgsql-sql@postgresql.org
References: <20021101143348.A7426@goldfisch.at>
	<20021101145630.50886.qmail@web13801.mail.yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20021101145630.50886.qmail@web13801.mail.yahoo.com>
User-Agent: Mutt/1.3.25i

On Fri, Nov 01, 2002 at 06:56:30 -0800,
  Chris Gamache <cgg007@yahoo.com> wrote:
> 
> The problem occurrs when two of the processes grab the exact same row at the
> exact same instant. It happens roughly 1 out of 1000 times. I'm not sure if
> setting the transactions to serializable would fix the problem since it occurrs
> between different postmasters.

I played with this a bit and it looks like you want transaction isolation
level serializable and have the application retry when necessary.

Doing a select for update won't work cleanly. What will happen is that
you will get 0 rows returned when there is a conflict and you will need
to detect that same as you would by changing the isolation level.

What happens in the select for update case is you first rewrite the select
to use order by and limit to pick the minimum row since you can't use
for update when returning an aggregate. Then when the matching row is
found there is a possible lock contention with another update. If this
happens then when the other update commits the locked row is rechecked and
will no longer match and gets removed from the list of rows returned (leaving
no rows).