Thread: Current value of a sequence?

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?)


--
Adam Haberlach            | At either end of the social spectrum there lies
adam@newsnipple.com       | a leisure class.  -- Eric Beck 1965
http://www.newsnipple.com |
'88 EX500    '00 >^<      | http://youlook.org

Adam Haberlach <adam@newsnipple.com> writes:
> 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)

If you aren't very concerned about cross-transaction safety,

    select last_value + increment_by from seq;

is approximately right.

There is a *lot* of discussion about this in the mailing list archives.

            regards, tom lane

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@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

On Sun, 3 Jun 2001 19:48:42 +0000 (UTC), Adam Haberlach <adam@newsnipple.com>:

> 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?)


This is the way sequences are supposed to work.
currval is the current value of the sequence as seen
by the current session. That's why it is not defined
until you select nextval first.

Remember that a sequence is not guaranteed to not
have spaces...

If you want to find the last number from the sequence
which was inserted in to a table, you should select max()
on that column.