Re: Best approach for a "gap-less" sequence - Mailing list pgsql-general

From Brad Nicholson
Subject Re: Best approach for a "gap-less" sequence
Date
Msg-id 1155589148.29917.18.camel@dba5.int.libertyrms.com
Whole thread Raw
In response to Re: Best approach for a "gap-less" sequence  (Berend Tober <btober@seaworthysys.com>)
Responses Re: Best approach for a "gap-less" sequence
Re: Best approach for a "gap-less" sequence
List pgsql-general
On Mon, 2006-08-14 at 16:08 -0400, Berend Tober wrote:
> 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 --
>
> 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 :-)

Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.




pgsql-general by date:

Previous
From: marcelo Cortez
Date:
Subject: wal files on temporary tables
Next
From: Alvaro Herrera
Date:
Subject: Re: select updates pg_stat_database