Re: How to create "auto-increment" field WITHOUT a sequence object? - Mailing list pgsql-general

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)