Re: SQL-question: returning the id of an insert querry

Are X & Y two different connections?
If you execute 2 statements on the same connection and then get currval()
it will give the last generated id.

Ex.
On 1 connection:
INSERT INTO A (fld) VALUES (val); -- id generated = 1
INSERT INTO A (fld) VALUES (val2); -- id generated = 2
SELECT currval('SA');
2

On 2 connections:
conn1.execute("INSERT INTO A (fld) VALUES (val)") -- id generated = 1
conn2.execute("INSERT INTO A (fld) VALUES (val2)") -- id generated = 2
conn1.execute("SELECT currval('SA')")
1
conn2.execute("SELECT currval('SA')")
2


David Green
Sage Automation, Inc


-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Scott Chapman
Sent: Monday, November 10, 2003 10:09 AM
To: Alvaro Herrera
Cc: Martijn van Oosterhout; Andreas Fromm; pgsql-general@postgresql.org
Subject: Re: [GENERAL] SQL-question: returning the id of an insert
querry


On Sunday 09 November 2003 10:52, Alvaro Herrera wrote:
> On Sun, Nov 09, 2003 at 10:26:51AM -0800, Scott Chapman wrote:
> > On Sunday 09 November 2003 03:13, Martijn van Oosterhout wrote:
> > > After you've done the insert on the address table, you can use
> > > currval('address_id_seq') (or equivalent) to get the ID. Ofcourse
> > > you have to have used nextval() for the original insert.
> >
> > What if someone else inserts another address before I get the
> > currval? I'm out of luck then, right?
>
> No, currval is concurrency-safe.  That's exactly what sequences are
> for.

I just want to clarify what I mean here to make sure I understand this
right. I have a table, A, that has a ID field which defaults to nextval
of a sequence, SA.

Chronological events here:

X inserts a new record into A.
Y inserts a new record into A.
X fetches currval of the SA. What value does X get in this case, the one
from X's insert or Y's?

Scott



---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match