Re: How to create "auto-increment" field WITHOUT a sequence object?

2011/7/1 Chris Travers <chris(dot)travers(at)gmail(dot)com>

> On Fri, Jul 1, 2011 at 1:16 AM, Dmitriy Igrishin <dmitigr(at)gmail(dot)com>
> wrote:
> > Hey Chris,
> >
> >> The suggestion of using for
> >> update is a good one, but it doesn't entirely get rid of the problem,
> >> which is inherent in ensuring gapless numbering in a system with
> >> concurrent transactions.
> >
> > Why not?
>
> Just because it locks less doesn't mean that it doesn't lock.
>
> The point is: if gaps are acceptable then the sequences which exist
> outside of transactions are idea. If gaps are not acceptable, you
> have to lock and force transactions through the system serially which
> means a possibility of deadlocks and performance issues. These issues
> are inherent in gapless numbering because you can't get a gapless
> sequence when things roll back without such locks.
>
Then I don't clearly understand the existence of locks (the LOCK
command, SELECT FOR UPDATE clause and so on) if the usage
of them gives only problems...

>
> > I mean the following solution:
> >
> > CREATE TABLE myseq(tabnm text not null, lastid integer not null);
> >
> > INSERT INTO myseq SELECT 'mytab', 0; -- initialization
> >
> > CREATE OR REPLACE FUNCTION public.myseq_nextval(a_tabnm text)
> > RETURNS integer
> > LANGUAGE sql
> > STRICT
> > AS $function$
> > UPDATE myseq SET lastid = li + 1 FROM
> > (SELECT lastid li FROM myseq WHERE tabnm = $1 FOR UPDATE) foo
> > RETURNING lastid;
> > $function$
> >
> > -- Test
> >
> > dmitigr=> BEGIN;
> > BEGIN
> > dmitigr=> SELECT myseq_nextval('mytab');
> > myseq_nextval
> > ---------------
> > 1
> > (1 row)
> >
> > dmitigr=> ROLLBACK;
> > ROLLBACK
> > dmitigr=> SELECT * FROM myseq;
> > tabnm | lastid
> > -------+--------
> > mytab | 0
> > (1 row)
> >
> > So, with this approach you'll get a lock only on INSERT.
>
> True. But the point us that you MUST lock on insert to get gapless
> sequences, and this creates inherent problems in terms of performance
> and concurrency, so that you should not use it unless you really have
> no other choice (i.e. because the tax authorities demand it).
>
Sure, but, again, why LOCK and SELECT FOR UPDATE exists ?

>
> Best Wishes,
> Chris Travers
>

--
// Dmitriy.