Re: Current value of a sequence?

this is a faq (i know, i asked it at least twice...:) --

On Sun, Jun 03, 2001 at 12:34:22PM -0700, Adam Haberlach wrote:
> I'm trying to find the next value that will be assigned by a sequence
> without actually incrementing it (yes, I know it isn't atomic-safe
> and all)
>
> I had a lot of trouble finding information about sequences in
> the current documentation (maybe it is in the A4 PDF version or
> something). When I finally resorted to trolling through the
> source code, and found currval, it seems to have an odd (IMHO)
> behavior:
>
> zipcode=# select currval('messages_pkey_seq');
> ERROR: messages_pkey_seq.currval is not yet defined in this session
>
> ...however, if I select nextval() first, it works. Is this
> lazy caching? Is there a good way to find the current value of
> sequence without querying directly (and possibly breaking in
> future implementations?)

usually what you're trying to do is insert a record in tableA
that has a sequence, and then also insert some referring records
into tableB and tableC -- in which case insert to tableA first,
then

insert into tableA(...) values (...);

insert into tableB(..., tableArefID, ...)
values (..., currval(seq), ...)

insert into tableC(..., tableArefID, ...)
values (..., currval(seq), ...)

the reason is, as you say, "atomic-safe and all" -- if you do

select last_value+increment_by from seq;

another parallell process could bump the counter before you have
a chance to use it. this ultimately leads to Bad Things.

--
#95: We are waking up and linking to each other. We are watching. But
we are not waiting. -- www.cluetrain.com

will(at)serensoft(dot)com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!