Fetching generated keys

In Postgres the Connection.prepareStatement() calls that return
generated keys are not supported. Because of this we need a workaround
to get the generated keys for inserts into tables that use sequences to
auto-generate their primary keys.

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).

What is the best workaround for this? Ideally the JDBC calls should be
supported because (1) we would only need a single round trip and (2)
it's transactionally safe. But without that, what is the recommended
best practice workaround?

I believe I could set the transaction isolation level to "serializable",
but this seems heavy handed. Is that really the best option?

Thanks,

Michael Clements
Principal Architect, Actional Products
http://www.progress.com
mclement(at)progress(dot)com

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

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(at)gmail(dot)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

On 5-Mar-07, at 4:08 PM, Heikki Linnakangas wrote:

> 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')"
>
I think you should be using currval( 'sequence_name') in the same
connection. It will not be the wrong one! It does the "right thing".
I believe lastval does not allow you to specify the sequence.

Dave
> --
> Heikki Linnakangas
> EnterpriseDB http://www.enterprisedb.com
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

On Mar 5, 2007, at 15:08 , Mike Clements wrote:

> In Postgres the Connection.prepareStatement() calls that return
> generated keys are not supported. Because of this we need a workaround
> to get the generated keys for inserts into tables that use
> sequences to
> auto-generate their primary keys.
>
> 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).
>
> What is the best workaround for this? Ideally the JDBC calls should be
> supported because (1) we would only need a single round trip and (2)
> it's transactionally safe. But without that, what is the recommended
> best practice workaround?
>
> I believe I could set the transaction isolation level to
> "serializable",
> but this seems heavy handed. Is that really the best option?

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

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

> 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.
>
currval will do the correct thing, it doesn't matter the order.

and if you do have 8.2 insert returning does the right thing too
Dave
> 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(at)gmail(dot)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
>
> ---------------------------(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
>

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.

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?

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

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

Heikki Linnakangas <heikki(at)enterprisedb(dot)com> writes:
> 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')"

I think you meant "currval". In any case the point is that these are
session-local and so Mike's concern is unfounded.

regards, tom lane

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

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
>

Hi everyone - one more question.

I can prepare this "INSERT ... RETURNING" statement, but I can't run it
or fetch the results from the JDBC driver.

I figured it would have to work one of 2 ways:

1. call executeUpdate() and then getGeneratedKeys().

2. call executeQuery() and use the result set.

But both fail!

If I call statement.executeUpdate(), it throws an exception saying it
unexpectedly returned a result set.

If I call statement.executeQuery(), it throws an exception saying the
connection is already closed ?!?!

What is the right way to get the results back from an insert using the
"INSERT ... RETURNING" clause?

Thanks

-----Original Message-----
From: pgsql-jdbc-owner(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Mike Clements
Sent: Monday, March 05, 2007 6:10 PM
To: Mark Lewis
Cc: PostgreSQL JDBC List
Subject: Re: [JDBC] 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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Tue, 6 Mar 2007, Mike Clements wrote:

> 2. call executeQuery() and use the result set.
>
> If I call statement.executeQuery(), it throws an exception saying the
> connection is already closed ?!?!
>

I have no idea what you're testing, but the attached works fine for me.
Could you provide individual test cases for any and all problems you are
seeing?

Kris Jurka

Kris,

I wrote a simple test program and schema but I can't reliably reproduce
the problem. The problem I'm seeing is intermittent. When calling next()
on the result set, it sometimes throws an exception saying the DB
connection has been closed. Of course it hasn't been closed, but the
problem is now so intermittent I can't reliably reproduce it.

If I end up being able to reproduce it consistently I'll post the sample
code.

Regards,

-----Original Message-----
From: Kris Jurka [mailto:books(at)ejurka(dot)com]
Sent: Tuesday, March 06, 2007 5:42 PM
To: Mike Clements
Cc: PostgreSQL JDBC List
Subject: Re: [JDBC] Fetching generated keys

On Tue, 6 Mar 2007, Mike Clements wrote:

> 2. call executeQuery() and use the result set.
>
> If I call statement.executeQuery(), it throws an exception saying the
> connection is already closed ?!?!
>

I have no idea what you're testing, but the attached works fine for me.
Could you provide individual test cases for any and all problems you are
seeing?

Kris Jurka

On Mar 6, 2007, at 18:35 , Mike Clements wrote:

> Kris,
>
> I wrote a simple test program and schema but I can't reliably
> reproduce
> the problem. The problem I'm seeing is intermittent. When calling
> next()
> on the result set, it sometimes throws an exception saying the DB
> connection has been closed. Of course it hasn't been closed, but the
> problem is now so intermittent I can't reliably reproduce it.
>
> If I end up being able to reproduce it consistently I'll post the
> sample
> code.

You may be crashing the server. Which server version is this?

-M

It is version 8.2.1. But I realized my error was quite silly. The
intermittent problem was that the expression evaluator in my debugger
had the expressions "rs.first()", "rs.next()" etc. So the moment the
code hit my breakpoint the expressions were invoked which broke the
result set before my code got a chance to use it. I didn't see this
because the expression window was minimized :(

Now it's working great.

-----Original Message-----
From: pgsql-jdbc-owner(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of A.M.
Sent: Tuesday, March 06, 2007 4:34 PM
To: PostgreSQL JDBC List
Subject: Re: [JDBC] Fetching generated keys

On Mar 6, 2007, at 18:35 , Mike Clements wrote:

> Kris,
>
> I wrote a simple test program and schema but I can't reliably
> reproduce the problem. The problem I'm seeing is intermittent. When
> calling
> next()
> on the result set, it sometimes throws an exception saying the DB
> connection has been closed. Of course it hasn't been closed, but the
> problem is now so intermittent I can't reliably reproduce it.
>
> If I end up being able to reproduce it consistently I'll post the
> sample code.

You may be crashing the server. Which server version is this?

-M

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster