[Q] Sequences, last_value and inserts

On Fri, Nov 01, 2002 at 11:50:30 -0800,
"Roderick A. Anderson" <raanders@acm.org> wrote:

I'm inserting some values into a table with a serial field. This is being
done using Perl and the DBI. I then pull back using 'last_value' what I
hope is the value for the insert. The thought came to mind that if the
database is getting hit pretty hard there may be another access of the
sequence and last_value will be wrong 'for me'.
Question is how do I insure I get the correct last-value? Enclose in a
BEGIN/END block? (How's that done using Perl? - my DBI book isn't at
hand).

You want to use currval. This can be used within a single session safely.

"Roderick A. Anderson" <raanders@acm.org> writes:

On Fri, 1 Nov 2002, Bruno Wolff III wrote:

You want to use currval. This can be used within a single session safely.

I must be thick today.

select currval from domain_info_tbl_key_seq;
ERROR: Attribute 'currval' not found

Try:

select currval('domain_info_tbl_key_seq');

If you read the docs, you would know this. ;)

-Doug

On Fri, 1 Nov 2002, Bruno Wolff III wrote:

You want to use currval. This can be used within a single session safely.

I must be thick today.

select currval from domain_info_tbl_key_seq;
ERROR: Attribute 'currval' not found

Rod
--
"Open Source Software - Sometimes you get more than you paid for..."

select last_value from domain_info_tbl_key_seq;

This returns the same value as currval.

select last_value from domain_info_tbl_key_seq;

This returns the same value as currval.

In most cases, yes. However:

"Also, last_value will reflect the latest value reserved by any backend,
whether or not it has yet been returned by nextval."

http://www.postgresql.org/idocs/index.php?sql-createsequence.html

It is possible that someone else has changed the sequence value before you
read it, meaning you will not get the value you just inserted (like you
would with currval), but the value of the *other* insert.

This is precisely the problem that the original question was posed to avoid.

Greg

On 1 Nov 2002, Doug McNaught wrote:

select currval('domain_info_tbl_key_seq');

If you read the docs, you would know this. ;)

Documentation. Don't leave home without it. Well I did not knowing I was
going to get time to work on this. :-)

Rod
--
"Open Source Software - Sometimes you get more than you paid for..."

"Marie G. Tuite" <marie.tuite@edisonaffiliates.com> writes:

select last_value from domain_info_tbl_key_seq;
This returns the same value as currval.

Au contraire --- the values are not necessarily the same, for exactly
the reason the OP was concerned about. last_value will be the last
value any backend got from nextval(); currval is the last value *your*
backend got from nextval().

regards, tom lane

On Fri, Nov 01, 2002 at 12:52:38 -0800,
"Roderick A. Anderson" <raanders@acm.org> wrote:

On 1 Nov 2002, Doug McNaught wrote:

select currval('domain_info_tbl_key_seq');

If you read the docs, you would know this. ;)

Documentation. Don't leave home without it. Well I did not knowing I was
going to get time to work on this. :-)

Its on the web. Since you have access to email, I suspect that you have web
access as well. The 7.3 documentation on sequence functions can be found at:
http://developer.postgresql.org/docs/postgres/functions-sequence.html

Even is you are using earlier versions of postgresql this documentation
should apply. This section of the documentation is relatively new, and
may not be in your copy of the documention depending on what version of
postgresql yours corresponds to.

On Fri, 1 Nov 2002, Bruno Wolff III wrote:

Its on the web. Since you have access to email, I suspect that you have web
access as well. The 7.3 documentation on sequence functions can be found at:
http://developer.postgresql.org/docs/postgres/functions-sequence.html

You know I just became gainfully employeed again and so I'm breaking in a
new Workstation. I've added the developers docs to my Mozilla bookmarks.
Not as comfortable or nice as thumbing my way through real paper but
usable. :-)

Rod
--
"Open Source Software - Sometimes you get more than you paid for..."