Re: Fetching generated keys

Thanks everyone for the quick help and explanations.

-----Original Message-----
From: Mark Lewis [mailto:mark(dot)lewis(at)mir3(dot)com]
Sent: Monday, March 05, 2007 5:29 PM
To: Mike Clements
Cc: A.M.; PostgreSQL JDBC List
Subject: Re: [JDBC] Fetching generated keys

On Mon, 2007-03-05 at 17:19 -0500, Mike Clements wrote:
...
> The FAQ you posted suggests that "currval" uses a level of isolation
> that is more strict than the default "read committed". If so, setting
> isolation level to serializable would be unnecessary. Is that true? Or

> should I do it just to be safe? I'd hate to do it if unnecessary due
> to the performance and locking implications.

Yes, currval definitely returns the last value returned by the sequence
in the current transaction. Anything done in other transactions is
ignored.

Just for kicks, I did a simple test with two psql sessions to
demonstrate:

psql1: BEGIN TRANSACTION;
psql1: SELECT nextval('my_seq'); -- returns 4988
psql2: BEGIN TRANSACTION;
psql2: SELECT nextval('my_seq'); -- returns 4989
psql1: SELECT currval('my_seq'); -- returns 4988

(also tested with psql2 committing the transaction before psql1 reads
currval. Made no difference.)

-- Mark Lewis