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.