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

Brad Nicholson wrote:

> On Mon, 2006-08-14 at 16:08 -0400, Berend Tober wrote:
>
>>Jorge Godoy wrote:
>>
>>
>>>Chris <dmagick(at)gmail(dot)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 --
>>
>>Wouldn't SELECT ... FOR UPDATE give you the row lock you need without
>>locking the table?
>
>
> Nope, concurrent transactions won't work.
>
> Let current max id = x
>
> Transaction 1 (t1) does a select max(id) for update, gets a lock on the
> last tuple at the time of the select, and gets x as a value for max id
>
> Transaction 2 (t2) does a select max(id) for update, has to wait for t1
> to release its lock.
>
> t1 inserts (x+1) as the new max id of the table. t1 releases its lock
>
> t2 is granted the lock on the tuple it has been waiting for, which
> contains the max id of x
>
> t2 tries to insert a value of x+1, insert fails (if it doesn't, you
> really want to have a close look at your constraints :-)

I see. The FOR UPDATE form is not applicable with aggregates.

I was looking at this as if he uses a separate table to keep track of
the most-recently-issued sequence value, as the original post specified
was the case, whereas using the MAX aggregate, as suggested
subsequently, implies calculating the most-recently-used sequence value
from the table that has all the earlier sequence values in it.

The original question stated he "...acquire(d) a SHARE ROW EXCLUSIVE
lock", which locks the whole table, so my thought was to suggest that
locking the entire table was not necessary -- rather only the row. Of
course, if the control table has only one row, then this may not matter.

But since nothing beats empirical evidence, I tried it using

BEGIN;
SELECT column1 FROM test.table1 FOR UPDATE;
UPDATE test.table1 SET column1=column1+1;
COMMIT;

executed line-by-line alternating between two separate pgAdmin SQL
windows, and concurrency is properly accounted for.

Regards,
Berend Tober