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

On 8/17/06, Dawid Kuroczko <qnex42@gmail.com> wrote:
> On 8/17/06, Merlin Moncure <mmoncure@gmail.com> wrote:
> > On 8/16/06, Dawid Kuroczko <qnex42@gmail.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.

yes, you are right...i didnt think the problem through properly.

merlin