Thread: [Q] Sequences, last_value and inserts
PostgreSQL 7.2.1 RHL 7.3 DBI, DBD::Pg 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). TIA, Rod -- "Open Source Software - Sometimes you get more than you paid for..."
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.
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..."
"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
select last_value from domain_info_tbl_key_seq;
This returns the same value as currval.
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Doug McNaught
> Sent: Friday, November 01, 2002 2:23 PM
> To: Roderick A. Anderson
> Cc: Bruno Wolff III; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] [Q] Sequences, last_value and inserts
>
>
> "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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
> 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..."