Re: currval of sequence "my_seq" is not yet defined in this session

On Mon, Apr 28, 2008 at 4:33 PM, Nacef LABIDI <nacef.l@gmail.com> wrote:
> Hi all,
>
> I am writing some code to get the current value of a sequence using the
> currval function to use this value in a following insert on another table.
> But I am having this message "currval of sequence "my_seq" is not yet
> defined in this session".
>
> Can someone explain me what is the problem with that and by the way explain
> me the definition of a session in postgres.
>
> I have tried to avoid he problem using a stocked function and a trigger but
> I really want to avoid using triggers for debugging issues.

By your mentioning sessions I assume you've read the bit of the docs
on sequence functions, and are somewhat familiar with the three
functions, nextval, currval, and setval.

Now, mental exercise time.  You connect to the database.  This is YOUR
session.  I connect to the same database.  This is my session. There
is a sequence that will give out 6 as its next value.  The last value
it gave out (to someone other than either of us) was 5.  We both
execute " select currval('someseq'); "

What should we get back?  How would we use it in a safe way if we got
it?  Can you see the problem here?  The current value, if we could get
it, would be the same, and neither of us could safely do anything with
it.  What we need is the NEXT value.   you select nextval('someseq')
and so do I. Let's suppose you get 6, and I get 7.  It could be the
other way around, but we don't care. We can both go off and do what we
want with our own values.  After the nextval, currval for you will
return 6, and for me it will return 7.