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

On 8/17/06, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On 8/16/06, Dawid Kuroczko <qnex42(at)gmail(dot)com> wrote:
> > -- then create a function to retrieve the values:
> > CREATE FUNCTION gseq_nextval(t text) RETURNS integer AS $$
> > DECLARE
> > n integer;
> > BEGIN
> > SELECT INTO n gseq_value+1 FROM gapless_seq WHERE gseq_name = t
> > FOR UPDATE;
> > UPDATE gapless_seq SET gapless_value = n WHERE gseq_name = t;
> > RETURN n;
> > END;
> > $$ STABLE LANGUAGE PLpgsql;
> >
>
> the problem here is if you have two concurrent transactions which call
> this funtion, it is possible for them both to return the same sequence
> number in read comitted mode. Using this funtion outside of
> transactions is no different that using a sequence except that it is
> slower.

Hmm, I think you are wrong. There is a SELECT ... FOR UPDATE;
The first-to-obtain the gapless sequence transaction will establish
a lock onthe "tax_id" row. The other transaction will block until
the first transaction finishes (and the row is updated) and will
establish the row lock on it.

"FOR UPDATE" effectively serializes access to this row for all
transactions wanting to update it, even in read commited
mode. Your statement would be true if I didn't use "SELECT
... FOR UPDATE"; but just a plain SELECT there.

Regards,
Dawid

PS: http://www.postgresql.org/docs/8.1/interactive/sql-select.html#SQL-FOR-UPDATE-SHARE