Re: using sequences

I have read the manual about sequences and I thought I understood both
their purpose and how to use them. When I perform inserts, the sequence
is updated appropriately. However, I can't seem to directly access the
sequence myself. I always seem to get this message:

be_db=# select currval('news_news_id_seq');
ERROR: news_news_id_seq.currval is not yet defined in this session

Can someone explain what is going on?

Thank you,

Erik

On Fri, Jun 13, 2003 at 04:26:47PM -0400, Erik Price wrote:

<snip>
> be_db=# select currval('news_news_id_seq');
> ERROR: news_news_id_seq.currval is not yet defined in this session
>
> Can someone explain what is going on?
if i understood it right, the sequence values are separate for each
session. so you can easily call currval() w/o having to care what
other sessions do w/ the seq. this of course requires counting up
the seq w/ nextval().

you can get the global status w/ qeuring the sequence relation directly:

SELECT news_news_id_seq.last_value;

cu
--
---------------------------------------------------------------------
Enrico Weigelt == metux ITS
Webhosting ab 5 EUR/Monat. UUCP, rawIP und vieles mehr.

phone: +49 36207 519931 www: http://www.metux.de/
fax: +49 36207 519932 email: contact(at)metux(dot)de
cellphone: +49 174 7066481 smsgate: sms(dot)weigelt(at)metux(dot)de
---------------------------------------------------------------------
Diese Mail wurde mit UUCP versandt. http://www.metux.de/uucp/

At 22:26 13.06.2003, Erik Price said:
--------------------[snip]--------------------
>I have read the manual about sequences and I thought I understood both
>their purpose and how to use them. When I perform inserts, the sequence
>is updated appropriately. However, I can't seem to directly access the
>sequence myself. I always seem to get this message:
>
>be_db=# select currval('news_news_id_seq');
>ERROR: news_news_id_seq.currval is not yet defined in this session
>
>Can someone explain what is going on?
--------------------[snip]--------------------

A sequence is a funny thing. If you SELECT nextval('sequence_name'), it
will return a value that is guaranteed unique (for this sequence), across
all parallel accesses and transactions that may do the same at almost the
same moment. SELECT currval('sequence_name') however is connection-bound,
which means it will _always_ return the last value that has been obtained
_by_this_connection_ (regardless of transactions).

If you consider this you will see the need that you _first_ execute
nextval() at least once, before currval() can be queried - it's simply not
defined before. And that's what the message says anyway.

If you have a serial field, you may safely
INSERT INTO TABLE (cols) VALUE (vals)
SELECT currval('table_id_sequence') as "row_id"
and you will retrieve the serial ID that has been obtained by the previous
insert.

HTH,

--
>O Ernest E. Vogelsinger
(\) ICQ #13394035
^ http://www.vogelsinger.at/

Erik Price schrieb:
> I have read the manual about sequences and I thought I understood both
> their purpose and how to use them. When I perform inserts, the sequence
> is updated appropriately. However, I can't seem to directly access the
> sequence myself. I always seem to get this message:
>
> be_db=# select currval('news_news_id_seq');
> ERROR: news_news_id_seq.currval is not yet defined in this session
>
> Can someone explain what is going on?
I think the other explenations where pretty good. However there *is* a way
to retrieve the "current value" of sequence regardless whether nextval was
called or not:

SELECT last_value FROM news_news_id_seq;

Regards
Thomas

On Fri, Jun 13, 2003 at 16:26:47 -0400,
Erik Price <eprice(at)ptc(dot)com> wrote:
> I have read the manual about sequences and I thought I understood both
> their purpose and how to use them. When I perform inserts, the sequence
> is updated appropriately. However, I can't seem to directly access the
> sequence myself. I always seem to get this message:
>
> be_db=# select currval('news_news_id_seq');
> ERROR: news_news_id_seq.currval is not yet defined in this session
>
> Can someone explain what is going on?

If you are trying to do this you probably don't really understand sequences.
There aren't any common good reasons for trying to get the value of a sequence
before you have generated a value in the current session.

At 23:03 13.06.2003, Thomas Kellerer said:
--------------------[snip]--------------------
>I think the other explenations where pretty good. However there *is* a way
>to retrieve the "current value" of sequence regardless whether nextval was
>called or not:
>
>SELECT last_value FROM news_news_id_seq;
--------------------[snip]--------------------

Yup, but it's not always 100% accurate - at the time you're receiving the
result set another process/connection could already have updated the value.
You don't have a guarantee of uniqueness when SELECTing (except you're
using some collaboration-unfriendly lock...)

--
>O Ernest E. Vogelsinger
(\) ICQ #13394035
^ http://www.vogelsinger.at/

Ernest E Vogelsinger wrote:

> A sequence is a funny thing. If you SELECT nextval('sequence_name'), it
> will return a value that is guaranteed unique (for this sequence), across
> all parallel accesses and transactions that may do the same at almost the
> same moment. SELECT currval('sequence_name') however is connection-bound,
> which means it will _always_ return the last value that has been obtained
> _by_this_connection_ (regardless of transactions).
>
> If you consider this you will see the need that you _first_ execute
> nextval() at least once, before currval() can be queried - it's simply not
> defined before. And that's what the message says anyway.
>
> If you have a serial field, you may safely
> INSERT INTO TABLE (cols) VALUE (vals)
> SELECT currval('table_id_sequence') as "row_id"
> and you will retrieve the serial ID that has been obtained by the previous
> insert.

Ah, now it makes perfect sense. However, for sake of experiment, when I
try just that, I get an error message that I'm inserting a duplicate
primary key.

Here's my table:

Table "public.news"
+-[ RECORD 1 ]--------------------
| Column | news_id
| Type | integer
| Modifiers | not null default nextval('public.news_news_id_seq'::text
+-[ RECORD 2 ]----------------
| Column | news_date
| Type | timestamp without time zone
| Modifiers | not null
+-[ RECORD 3 ]--------------------
| Column | expire_date
| Type | date
| Modifiers | not null
+-[ RECORD 4 ]---------------------
| Column | news_title
| Type | character varying(64)
| Modifiers | not null default ''
+-[ RECORD 5 ]-----------------------
| Column | news_info
| Type | text
| Modifiers | not null
+-[ RECORD 6 ]----------------------
| Column | user_id
| Type | integer
| Modifiers | not null
+-----------+-----------

And here's my INSERT statement:

be_db=# INSERT INTO news (news_date, expire_date, news_title, news_info,
user_id) VALUES (NOW(),'6/14/2003','sometitle here','some news here',1);

And here's the error message:

ERROR: Cannot insert a duplicate key into unique index news_pkey

What do you make of that? Thanks for helping me understand better about
sequences.

Erik

Bruno Wolff III wrote:

> If you are trying to do this you probably don't really understand sequences.
> There aren't any common good reasons for trying to get the value of a sequence
> before you have generated a value in the current session.

It seems you're right that I dont understand them. However, I don't
have any intent of calling it like this (without having generated a
value), rather, I was simply curious and playing around with the
commandline client, and following some instructions I had seen in
another post. I didn't realize that it needed to be "activated" for the
current session.

Erik

On Fri, Jun 13, 2003 at 05:28:33PM -0400, Erik Price wrote:

<snip>
> be_db=# INSERT INTO news (news_date, expire_date, news_title, news_info,
> user_id) VALUES (NOW(),'6/14/2003','sometitle here','some news here',1);
hmm. did you try it w/ SELECT instead of VALUES ?

cu
--
---------------------------------------------------------------------
Enrico Weigelt == metux ITS
Webhosting ab 5 EUR/Monat. UUCP, rawIP und vieles mehr.

phone: +49 36207 519931 www: http://www.metux.de/
fax: +49 36207 519932 email: contact(at)metux(dot)de
cellphone: +49 174 7066481 smsgate: sms(dot)weigelt(at)metux(dot)de
---------------------------------------------------------------------
Diese Mail wurde mit UUCP versandt. http://www.metux.de/uucp/

On Fri, Jun 13, 2003 at 17:29:01 -0400,
Erik Price <eprice(at)ptc(dot)com> wrote:
>
>
> Bruno Wolff III wrote:
>
> >If you are trying to do this you probably don't really understand
> >sequences.
> >There aren't any common good reasons for trying to get the value of a
> >sequence
> >before you have generated a value in the current session.
>
> It seems you're right that I dont understand them. However, I don't
> have any intent of calling it like this (without having generated a
> value), rather, I was simply curious and playing around with the
> commandline client, and following some instructions I had seen in
> another post. I didn't realize that it needed to be "activated" for the
> current session.

"activated" isn't really the right word. When you call nextval you are
giving a number that is unique. No one else gets to see that number
(until you save it somewhere) which prevents contention between different
connections trying to get unique numbers from the same sequence.
currval is just a way to see the last value of nextval that was given to
you. If one hasn't been given to you, then there isn't anything for
currval to return.

Related to this is that you should only use the uniqueness of sequence
values. You shouldn't expect them to be continuous and you shouldn't
expect them to be monotonicly increasing other than within a single
session.

Hi Erik,

Erik Price wrote:
> I have read the manual about sequences and I thought I understood both
> their purpose and how to use them. When I perform inserts, the sequence
> is updated appropriately. However, I can't seem to directly access the
> sequence myself. I always seem to get this message:
>
> be_db=# select currval('news_news_id_seq');
> ERROR: news_news_id_seq.currval is not yet defined in this session
>
> Can someone explain what is going on?

This is a FAQ. Although it is mentioned clearly in the documentation,
it is often overread:

currval() is only valid after nextval() in your transaction.
This is to get the id for your last insert or whatever and
_not_ the id of some other insert concurrently running.

Does this help?

Regards
Tino Wildenhain

Bruno Wolff III wrote:

>>It seems you're right that I dont understand them. However, I don't
>>have any intent of calling it like this (without having generated a
>>value), rather, I was simply curious and playing around with the
>>commandline client, and following some instructions I had seen in
>>another post. I didn't realize that it needed to be "activated" for the
>>current session.
>
>
> "activated" isn't really the right word. When you call nextval you are
> giving a number that is unique. No one else gets to see that number
> (until you save it somewhere) which prevents contention between different
> connections trying to get unique numbers from the same sequence.
> currval is just a way to see the last value of nextval that was given to
> you. If one hasn't been given to you, then there isn't anything for
> currval to return.
>
> Related to this is that you should only use the uniqueness of sequence
> values. You shouldn't expect them to be continuous and you shouldn't
> expect them to be monotonicly increasing other than within a single
> session.

Thank you for that very clear explanation. I now understand both points
(esp about not depending on the values to be sequential outside of a
single session, which I hadn't considered before).

Regards,

Erik