Обсуждение: 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@metux.de cellphone: +49 174 7066481 smsgate: sms.weigelt@metux.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/
On Fri, Jun 13, 2003 at 16:26:47 -0400, Erik Price <eprice@ptc.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.
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
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/
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
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
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@metux.de cellphone: +49 174 7066481 smsgate: sms.weigelt@metux.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@ptc.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