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

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-UPDATE-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




> --
> Adrian Klaver
> aklaver@comcast.net
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>