Horton

Thread

  1. [Q] Sequences, last_value and inserts

    Roderick A. Anderson <raanders@acm.org> — 2002-11-01T19:50:30Z

    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..."

  2. Re: [Q] Sequences, last_value and inserts

    Bruno Wolff III <bruno@wolff.to> — 2002-11-01T20:02:43Z

    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.

  3. Re: [Q] Sequences, last_value and inserts

    Douglas McNaught <doug@mcnaught.org> — 2002-11-01T20:22:43Z

    "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

  4. Re: [Q] Sequences, last_value and inserts

    Roderick A. Anderson <raanders@acm.org> — 2002-11-01T20:23:31Z

    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..."

  5. Re: [Q] Sequences, last_value and inserts

    Marie G. Tuite <marie.tuite@edisonaffiliates.com> — 2002-11-01T20:32:56Z

    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
>

  6. Re: [Q] Sequences, last_value and inserts

    Gregory Wood <gregw@com-stock.com> — 2002-11-01T20:48:05Z

    > 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

  7. Re: [Q] Sequences, last_value and inserts

    Roderick A. Anderson <raanders@acm.org> — 2002-11-01T20:52:38Z

    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..."

  8. Re: [Q] Sequences, last_value and inserts

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-11-01T20:58:36Z

    "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

  9. Re: [Q] Sequences, last_value and inserts

    Bruno Wolff III <bruno@wolff.to> — 2002-11-01T21:17:55Z

    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.

  10. Re: [Q] Sequences, last_value and inserts

    Roderick A. Anderson <raanders@acm.org> — 2002-11-01T23:44:23Z

    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..."