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

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.
>
> 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).

Best Wishes,
Chris Travers