On Fri, Jul 1, 2011 at 1:16 AM, Dmitriy Igrishin <dmitigr@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 ?
