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

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.