Thread: currval question

I am trying to get the last value updated by an column auto-incrementing
with nextval(). In MySQL, you'd use LAST_INSERT_ID() - in Postgre, currval()
appears to do the trick.

Is this maintained on a per-connection basis? For example, user A inserts
and the nextval() updates to 5, user B does 2 inserts, updating nextval() to
7. When user A calls currval() they should get 5 if the updates are per-cnx.
What does psql do under the hood here?

thx
a

Hi Andy,

this is AFAIK on a per transaction basis.

HTH
Tino Wildenhain

PS: I often use a plpsql script for creating table entrys, this helps if
you have many foreign keys and need some checks and the last id too. It
looks roughly like this:

CREATE FUNCTION ...

nextid=nextval(''sequence'');

insert into table ... (nextid, ... ) ;

return nextid;


this way you can use the function in another insert, immediately using its
return
value for insert in the other table.



--On Montag, 16. September 2002 18:14 -0400 Andy Kriger
<akriger@greaterthanone.com> wrote:

> I am trying to get the last value updated by an column auto-incrementing
> with nextval(). In MySQL, you'd use LAST_INSERT_ID() - in Postgre,
> currval() appears to do the trick.
>
> Is this maintained on a per-connection basis? For example, user A inserts
> and the nextval() updates to 5, user B does 2 inserts, updating nextval()
> to 7. When user A calls currval() they should get 5 if the updates are
> per-cnx. What does psql do under the hood here?
>
> thx
> a
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

Andy,

I don't know exactly what's under the hood, but I know that currval() won't
even return a result unless you've used nextval() in your session. If
currval() did not operate solely within the scope of the session, it stands
to reason that it would return a result... It's behavior (from the driver's
seat) is consistent with a well conceived concurrency model. This should be
easy enough to test with a couple of terminals. My money is on Postgres! :)

Regards,
Garo.

on 9/16/02 3:14 PM, Andy Kriger at akriger@greaterthanone.com wrote:

> I am trying to get the last value updated by an column auto-incrementing
> with nextval(). In MySQL, you'd use LAST_INSERT_ID() - in Postgre, currval()
> appears to do the trick.
>
> Is this maintained on a per-connection basis? For example, user A inserts
> and the nextval() updates to 5, user B does 2 inserts, updating nextval() to
> 7. When user A calls currval() they should get 5 if the updates are per-cnx.
> What does psql do under the hood here?
>
> thx
> a
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

=-=-==-=-=-==

Xapnet Internet Solutions
1501 Powell St., Suite N
Emeryville, CA 94608

Tel - (510) 655-9771
Fax - (510) 655-9775
Web - http://www.xapnet.com

"Andy Kriger" <akriger@greaterthanone.com> writes:

> I am trying to get the last value updated by an column
> auto-incrementing with nextval(). In MySQL, you'd use
> LAST_INSERT_ID() - in Postgre, currval() appears to do the trick.

Yes, currval is the thing to use to do this.

> Is this maintained on a per-connection basis? For example, user A
> inserts and the nextval() updates to 5, user B does 2 inserts,
> updating nextval() to 7. When user A calls currval() they should get
> 5 if the updates are per-cnx.  What does psql do under the hood
> here?

currval is per connection.  User A's currval would get them 5 and user
B would get 7.  Basically as long as you are using the same connection
currval will do "The right thing"TM.

Jason

thx - i was eventually able to verify this is per connection