Re: Best approach for a "gap-less" sequence

Jorge Godoy wrote:
> Chris <dmagick@gmail.com> writes:
>
> > I'm not sure what type of lock you'd need to make sure no other transactions
> > updated the table (see
> > http://www.postgresql.org/docs/8.1/interactive/sql-lock.html) but "in theory"
> > something like this should work:
> >
> > begin;
> > select id from table order by id desc limit 1;
> > insert into table (id, blah) values (id+1, 'blah');
> > commit;
>
> This is part of the solution, yes.  But I would still need locking this table
> so that no other concurrent transaction gets another "id".  I don't want to
> lock the main table -- as I believe you're suggesting -- because I want it to
> be searchable and updatable while I'm inserting new data.

So you have to hold a lock that conflicts with itself, but not with
ACCESS SHARE which is the lock acquired by SELECT.  I think the first
one on the list with these two properties is SHARE UPDATE EXCLUSIVE.
Have a look at the list yourself:

http://www.postgresql.org/docs/8.1/static/explicit-locking.html

Note the tip at the end of the table:

Tip:  Only an ACCESS EXCLUSIVE lock blocks a SELECT (without FOR
UPDATE/SHARE) statement.

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