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

On Thu, 2006-08-17 at 12:12 -0400, Merlin Moncure wrote:
> On 8/17/06, Dawid Kuroczko <qnex42(at)gmail(dot)com> wrote:
> > 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.
>
> yes, you are right...i didnt think the problem through properly.

Lets just hope the performance on a concurrent system is not a
requirement of such a system...

Brad.