Re: Fetching generated keys

But will it be transactionally safe?

Consider 2 transactions, A and B, each doing:

Start transaction
Insert into tbl...
Select lastval('sequence...
commit

The table's primary key has a default value which is defined by the
sequence - not provided by the caller.

Now suppose they get executed by Postgres in this order:

A:    insert into tbl...
B:    insert into tbl...
B:    select lastval('sequence'...
B:    commit
A:    select lastval('sequence'...
A:    commit

Because the default transaction isolation is "read committed", in the
above example A will get B's primary key value.

I'm asking what's the recommended best practice to work around this. I
could set the transaction isolation to "serializable" but is that
recommended?

Thanks

-----Original Message-----
From: Heikki Linnakangas [mailto:hlinnaka@gmail.com] On Behalf Of Heikki
Linnakangas
Sent: Monday, March 05, 2007 4:09 PM
To: Mike Clements
Cc: PostgreSQL JDBC List
Subject: Re: [JDBC] Fetching generated keys

Mike Clements wrote:
> Up to now, we were selecting the current value of the sequence
> immediately after the insert. I thought this was safe because
> transactions should be isolated. But now I realize this could
> potentially fail because the default transaction isolation is "read
> committed". Thus if another connection inserts into the same table,
> causing the sequence to increment, if it commits before we read the
> sequence value, we might read the wrong value (the value as
> incremented by the other transaction, not the value as it was for our
own insert).

You should use the lastval-function. It retrieves the last value
returned in your connection. "SELECT lastval('sequence')"

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com