Thread: 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@progress.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@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
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
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@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.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@enterprisedb.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
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@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.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 >
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@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
>
> ---------------------------(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
>
Thanks everyone for the quick help and explanations.
-----Original Message-----
From: Mark Lewis [mailto:mark.lewis@mir3.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
P.S.
If I call statement.execute(), it tells me the return value is a result
set. And I can get this result set (it's not null). But when I try to
use it, it fails. Calling "first()" throws a NPE, calling "getRow()" or
"next()" both fail too.
It appears that the JDBC result set of an "INSERT ... RETURNING" command
is unusable.
-----Original Message-----
From: Mike Clements
Sent: Tuesday, March 06, 2007 4:06 PM
To: PostgreSQL JDBC List
Subject: RE: [JDBC] Fetching generated keys
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@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.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.lewis@mir3.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
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@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.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.lewis@mir3.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
Yet even more info:
Because the returned result set *appeared* to have my data in it, I
tried not calling "next()" at all. This worked - example:
WORKS:
rs = stmt.execute();
pk = rs.getLong(1);
FAILS:
rs = stmt.execute();
rs.next(); // this throws an exception
pk = rs.getLong(1);
According to the JDK API docs for package java.sql, all results sets
should start with the cursor just before the first row. Thus one is
supposed to call "next()" to get the first row. This is how all the
result sets I've worked with in the past function.
I can work around this for now. But I'm curious - is it a bug?
Also there appears to be another bug, since calling first() on this
result set throws an NPE, but it should either return false or throw a
SQLException if it's a forward-only result set. Same goes for next() -
if there are no more rows it should return FALSE, not throw an
exception.
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Mike Clements
Sent: Tuesday, March 06, 2007 4:26 PM
To: PostgreSQL JDBC List
Subject: Re: [JDBC] Fetching generated keys
P.S.
If I call statement.execute(), it tells me the return value is a result
set. And I can get this result set (it's not null). But when I try to
use it, it fails. Calling "first()" throws a NPE, calling "getRow()" or
"next()" both fail too.
It appears that the JDBC result set of an "INSERT ... RETURNING" command
is unusable.
-----Original Message-----
From: Mike Clements
Sent: Tuesday, March 06, 2007 4:06 PM
To: PostgreSQL JDBC List
Subject: RE: [JDBC] Fetching generated keys
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@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.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.lewis@mir3.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
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
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
Attachment
Actually that only worked intermittently. Most of the time that fails
too.
Sometimes it works if I don't call next(), most of the time it fails
even if I do. Looks like a bug, so I'll follow up with a simple test
case.
-----Original Message-----
From: Mike Clements
Sent: Tuesday, March 06, 2007 5:25 PM
To: Mike Clements; PostgreSQL JDBC List
Subject: RE: [JDBC] Fetching generated keys
Yet even more info:
Because the returned result set *appeared* to have my data in it, I
tried not calling "next()" at all. This worked - example:
WORKS:
rs = stmt.execute();
pk = rs.getLong(1);
FAILS:
rs = stmt.execute();
rs.next(); // this throws an exception
pk = rs.getLong(1);
According to the JDK API docs for package java.sql, all results sets
should start with the cursor just before the first row. Thus one is
supposed to call "next()" to get the first row. This is how all the
result sets I've worked with in the past function.
I can work around this for now. But I'm curious - is it a bug?
Also there appears to be another bug, since calling first() on this
result set throws an NPE, but it should either return false or throw a
SQLException if it's a forward-only result set. Same goes for next() -
if there are no more rows it should return FALSE, not throw an
exception.
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Mike Clements
Sent: Tuesday, March 06, 2007 4:26 PM
To: PostgreSQL JDBC List
Subject: Re: [JDBC] Fetching generated keys
P.S.
If I call statement.execute(), it tells me the return value is a result
set. And I can get this result set (it's not null). But when I try to
use it, it fails. Calling "first()" throws a NPE, calling "getRow()" or
"next()" both fail too.
It appears that the JDBC result set of an "INSERT ... RETURNING" command
is unusable.
-----Original Message-----
From: Mike Clements
Sent: Tuesday, March 06, 2007 4:06 PM
To: PostgreSQL JDBC List
Subject: RE: [JDBC] Fetching generated keys
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@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.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.lewis@mir3.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
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
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@ejurka.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@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.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