gapless sequence without locking the whole table

create table therecord_lastid
(
last_id int default 0,
constraint pk_therecord_lastid primary key (last_id)
);
insert into therecord_lastid values(0);



CREATE OR REPLACE FUNCTION get_therecord_lastid()
RETURNS int AS
$$
declare
x int;
begin
update therecord_lastid set last_id = last_id + 1;
select last_id into x from therecord_lastid;
return x;
end;
$$
LANGUAGE 'plpgsql' ;





CREATE TABLE testrecord
(
testrecord_id integer NOT NULL DEFAULT get_therecord_lastid(),
name character varying(20) NOT NULL,
CONSTRAINT pk_testrecord PRIMARY KEY (testrecord_id)
);



begin;
insert into testrecord(name) values('see');
insert into testrecord(name) values('great');
end;

select * from testrecord