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

[Dmitriy Igrishin <dmitigr@xxxxxxxxx>]

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?

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.

dmitigr=> CREATE TABLE mytab(id integer not null DEFAULT myseq_nextval('mytab'));
CREATE TABLE
dmitigr=> INSERT INTO mytab DEFAULT VALUES;
INSERT 0 1
dmitigr=> INSERT INTO mytab DEFAULT VALUES;
INSERT 0 1
dmitigr=> SELECT * FROM mytab;
 id
----
  1
  2
(2 rows)

-- 
// Dmitriy.