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