Re: lock in access exclusive and sequence question

From: "Creager, Robert S" <CreagRS(at)LOUISVILLE(dot)STORTEK(dot)COM>

> I'm creating a script which will re-claim sequence numbers in a table by
> 'packing' the existing sequence numbers. My questions is if I lock the
> table in access exclusive mode, and an insert into that table occurs after
> the lock, with the insert be blocked before or after the nextval is
chosen?

If you do something like INSERT INTO foo VALUES (nextval('foo_seq')) it
seems to be before the nextval is chosen (based on testing against 7.1). I
don't know that this is guaranteed to remain the case, but it would seem
unlikely to change.

However - just doing a SELECT nextval('foo_seq') is unblocked so it will
depend how inserts/updates are performed.

I suppose you might also have a case where rules/triggers or the like could
evaluate a nextval() before an insert (triggers yes, rules probably not
*although that's just guesswork*)

Have you considered just extending the sequence numbers so you don't run
out? There's an example in my PostgreSQL notes linked from
techdocs.postgresql.org and also IIRC might well be in the pgsql cookbook.

HTH

- Richard Huxton