On Thu, 2006-08-17 at 12:12 -0400, Merlin Moncure wrote: > On 8/17/06, Dawid Kuroczko <qnex42@xxxxxxxxx> wrote: > > On 8/17/06, Merlin Moncure <mmoncure@xxxxxxxxx> wrote: > > > On 8/16/06, Dawid Kuroczko <qnex42@xxxxxxxxx> wrote: > > > > -- then create a function to retrieve the values: > > > > CREATE FUNCTION gseq_nextval(t text) RETURNS integer AS $$ > > > > DECLARE > > > > n integer; > > > > BEGIN > > > > SELECT INTO n gseq_value+1 FROM gapless_seq WHERE gseq_name = t > > > > FOR UPDATE; > > > > UPDATE gapless_seq SET gapless_value = n WHERE gseq_name = t; > > > > RETURN n; > > > > END; > > > > $$ STABLE LANGUAGE PLpgsql; > > > > > > > > > > the problem here is if you have two concurrent transactions which call > > > this funtion, it is possible for them both to return the same sequence > > > number in read comitted mode. Using this funtion outside of > > > transactions is no different that using a sequence except that it is > > > slower. > > > > Hmm, I think you are wrong. There is a SELECT ... FOR UPDATE; > > The first-to-obtain the gapless sequence transaction will establish > > a lock onthe "tax_id" row. The other transaction will block until > > the first transaction finishes (and the row is updated) and will > > establish the row lock on it. > > yes, you are right...i didnt think the problem through properly. Lets just hope the performance on a concurrent system is not a requirement of such a system... Brad.
