Re: Fetching generated keys

On 5-Mar-07, at 5:19 PM, Mike Clements wrote:

> I understand that currval is the way to select the value that was used
> for the inserted row. I've been doing this for quite some time now
> - it
> works.
>
> BUT... That works only in a perfect world with perfectly isolated
> transactions. The real world doesn't meet this theoretical perfection.
> The default transaction isolation level of Postgres is "read
> committed",
> which means somebody else's insert (and sequence increment), once
> committed, could be read by my transaction.
>
> 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.
sequences in general use a level of isolation which is different.
They cannot be rolled back after incrementing.
>
> Also, is there any way the JDBC driver will (someday?) support the
> Connection.preparedStatement() commands that would eliminate this
> entire
> issue *and* do it all in a single call, obviating the need to send a
> separate SQL command just to get the generated key?
Someone will have to generate SQL to get the key. Either the driver
or the user.

So you have two choices.

Generated the key before inserting, and insert it
insert using the default, or nextval and use currval to get the value
inserted.

Dave
>
> Thanks for the tip on INSERT... RETURNING but at first glance it seems
> to be unique to Postgres, not supported by other databases we talk to
> (DB2, Oracle, etc.). I could be wrong...
>
> -----Original Message-----
> From: pgsql-jdbc-owner(at)postgresql(dot)org
> [mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of A.M.
> Sent: Monday, March 05, 2007 4:58 PM
> To: PostgreSQL JDBC List
> Subject: Re: [JDBC] Fetching generated keys
>
>
> On Mar 5, 2007, at 15:08 , Mike Clements wrote:
>
>
> This is basically a FAQ. But don't be embarrassed- I asked the same
> thing six years ago:
> http://archives.postgresql.org/pgsql-general/2002-03/msg01257.php
>
> From the docs:
> http://www.postgresql.org/docs/current/static/functions-sequence.html
> currval() - Return the value most recently obtained by nextval for
> this
> sequence in the current session. (An error is reported if nextval has
> never been called for this sequence in this session.) Notice that
> because this is returning a session-local value, it gives a
> predictable
> answer whether or not other sessions have executed nextval since the
> current session did.
>
> So, currval() will always return the correct last primary key integer
> inserted in your current transaction.
>
> If you are using 8.2, you can also try the easier-to-use
> INSERT...RETURNING... syntax.
>
> Also, you're better off staying away from lastval(), because its
> return
> value is ambiguous if you use any triggers:
> http://people.planetpostgresql.org/xzilla/index.php?/archives/169-Is-
> lastval-evil.html
>
> Cheers,
> M
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>