Message-ID: <4DB1BFC2.7020806@emolecules.com>
Date: Fri, 22 Apr 2011 10:49:54 -0700
From: Craig James <craig_james@emolecules.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US;
	rv:1.9.2.15) Gecko/20110303 Thunderbird/3.1.9
MIME-Version: 1.0
To: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: select for update
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

I thought I understood "select ... for update," but maybe not.

We have a number of separate databases and a unique integer identifier that's supposed to be global across all databases.  A single "archive" database is used to issue the next available ID when a process wants to create a new object.  The sequence of operations goes like this (pseudo-code):

   /* start a transaction *
   begin;
  
   /* see if an objectid has been returned for re-use */
   select objectid from archive where db_id is null limit 1 for update

   /* no ID available?  issue a new one */
   if (objectid is null)
     new_id = select nextval('object_id_sequence')
     insert into archive(objectid, db_id) values('new_id', 'new_id')

   /* ID available? claim it */
   else
     update archive set db_id = this_db_id where objectid

   commit

The problem is that very occasionally the same ID will be issued twice.  I don't see how this can be.  Doesn't the "for update" guarantee that no other process can claim that same row?

Thanks,
Craig