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

On Wednesday 16 August 2006 10:59 am, elein wrote:
> On Mon, Aug 14, 2006 at 02:46:17PM -0700, Adrian Klaver wrote:
> > On Monday 14 August 2006 01:59 pm, Brad Nicholson wrote:
> > > 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 :-)
> >
> > I am still working through this stuff myself, but the following excerpt
> > from the documentation would seem to contradict what you are saying. See
> > the part marked with ***. t2 should see a new max(id) after t1 commits
> > and therefore insert(x+1) would succeed.
> >
> > http://www.postgresql.org/docs/8.1/interactive/sql-select.html#SQL-FOR-UP
> >DATE-SHARE
> >
> > "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).***"
>
> If this is true the solution for a transactional, gapless sequence is
> possible for table.gl_id  where updated from count.gl_id.  It is simple.
> However, it *depends* on the fact that the second transaction getting the
> newly updated record from the first transaction.  It seems pretty clear,
> not counting aggregates, that this is true from this doc snippet.  Speak
> now, if someone doesn't read it this way!  I'd like to understand why.
>
> If it weren't true, there would also be a workaround which caught a
> duplicate value and tried again, looping.
>
> I may publish the gapless sequence technique on general bits if there is no
> discrepancy in the understanding of the status of the second transaction's
> row value (updated).
>
> --elein
> elein@varlena.com

After I discovered that aggregates did not work I did some simple tests
updating a single row table. As I far as I could determine the docs hold
true :) I only ran three transactions at a time but each saw the incremented
value from the previous transaction.
--
Adrian Klaver
aklaver@comcast.net