2011/7/1 Chris Travers <chris.trav...@gmail.com> > On Fri, Jul 1, 2011 at 1:16 AM, Dmitriy Igrishin <dmit...@gmail.com> > wrote: > > 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? > > Just because it locks less doesn't mean that it doesn't lock. > > The point is: if gaps are acceptable then the sequences which exist > outside of transactions are idea. If gaps are not acceptable, you > have to lock and force transactions through the system serially which > means a possibility of deadlocks and performance issues. These issues > are inherent in gapless numbering because you can't get a gapless > sequence when things roll back without such locks. > Then I don't clearly understand the existence of locks (the LOCK command, SELECT FOR UPDATE clause and so on) if the usage of them gives only problems...
> > > 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. > > True. But the point us that you MUST lock on insert to get gapless > sequences, and this creates inherent problems in terms of performance > and concurrency, so that you should not use it unless you really have > no other choice (i.e. because the tax authorities demand it). > Sure, but, again, why LOCK and SELECT FOR UPDATE exists ? > > Best Wishes, > Chris Travers > -- // Dmitriy.