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

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?

From "http://www.postgresql.org/docs/8.1/interactive/sql-select.html":

"FOR UPDATE/FOR SHARE Clause

...FOR UPDATE causes the rows retrieved by the SELECT statement to be
locked as though for update. This prevents them from being modified or
deleted by other transactions until the current transaction ends. That
is, other transactions that attempt UPDATE, DELETE, or SELECT FOR UPDATE
of these rows will be blocked until the current transaction ends. Also,
if an UPDATE, DELETE, or SELECT FOR UPDATE from another transaction has
already locked a selected row or rows, SELECT FOR UPDATE will wait for
the other transaction to complete, and will then lock and return the
updated row (or no row, if the row was deleted). ..."

Regards,
Berend Tober