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

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.

merlin