Thread: JDBC keygen select
Hello,
I'm a newbie on PostGRE but have experience using SQL Server & Oracle
via JDBC. Something that works fine on these does not work in PostGRE so
I'm looking for advice.
In my schema all primary keys are integers and the DB automatically
assigns values (using sequences or identities). When I insert into these
tables via JDBC I do not specify any value for the primary key, and I
use the RETURN_GENERATED_KEYS flag so the generated key is provided in
the RecordSet returned from the insert command - something like this:
pStmt = itsDbConn.prepareStatement(
"insert into tbl (col1) values (?)",
Statement.RETURN_GENERATED_KEYS);
pStmt.setString(1, "foo");
count = pStmt.executeUpdate();
if(count > 0)
{
rs = pStmt.getGeneratedKeys();
rs.next();
pk = rs.getLong(1);
}
This works on SQL Server & Oracle but *not* on PostGRE SQL. In the
latter, it throws an exception in prepareStatement() saying "this method
is not yet implemented".
So my question is, how does one do this? This keygen approach is
important for performance, scalability and robustness. Launching a
separate SQL command to fetch the generated key has performance
problems. Self-generating the keys has problems with concurrency across
multiple clients.
Thanks
Michael R. Clements
Principal Architect, Actional Corp.
mclements@actional.com
FREE! Actional SOAPstation Developer Version
Web services routing, security, transformation and versioning
http://www.actional.com/sstdownload
Mike,
Well, until we get insert returning implemented (allegedly soon), the
protocol doesn't support returning any values without another round
trip to the db
So you have two choices here.
1) get the ID before and insert it ie select nextval('sequence_name')
and insert the value explicitly. If you cache sequences you can cache
them on the connection too ( more work )
2) get the ID after using currval('sequence_name')
P.S. Using PostGRE is frowned upon the name is either Postgres, or
PostgreSQL
Dave
On 22-Oct-05, at 4:57 PM, Mike Clements wrote:
> Hello,
>
> I'm a newbie on PostGRE but have experience using SQL Server & Oracle
> via JDBC. Something that works fine on these does not work in
> PostGRE so
> I'm looking for advice.
>
> In my schema all primary keys are integers and the DB automatically
> assigns values (using sequences or identities). When I insert into
> these
> tables via JDBC I do not specify any value for the primary key, and I
> use the RETURN_GENERATED_KEYS flag so the generated key is provided in
> the RecordSet returned from the insert command - something like this:
>
> pStmt = itsDbConn.prepareStatement(
> "insert into tbl (col1) values (?)",
> Statement.RETURN_GENERATED_KEYS);
> pStmt.setString(1, "foo");
> count = pStmt.executeUpdate();
> if(count > 0)
> {
> rs = pStmt.getGeneratedKeys();
> rs.next();
> pk = rs.getLong(1);
> }
>
> This works on SQL Server & Oracle but *not* on PostGRE SQL. In the
> latter, it throws an exception in prepareStatement() saying "this
> method
> is not yet implemented".
>
> So my question is, how does one do this? This keygen approach is
> important for performance, scalability and robustness. Launching a
> separate SQL command to fetch the generated key has performance
> problems. Self-generating the keys has problems with concurrency
> across
> multiple clients.
>
> Thanks
>
> Michael R. Clements
> Principal Architect, Actional Corp.
> mclements@actional.com
> FREE! Actional SOAPstation Developer Version
> Web services routing, security, transformation and versioning
> http://www.actional.com/sstdownload
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
Dave,
Thanks for the info. Right now I'm issuing a select currval('foo') after the insert to get the generated PK. This works
butthe extra SQL round trip slows down insert performance quite a bit.
Mike
> -----Original Message-----
> From: Dave Cramer [mailto:pg@fastcrypt.com]
> Sent: Monday, October 24, 2005 11:20 AM
> To: Mike Clements
> Cc: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] JDBC keygen select
>
> Mike,
>
> Well, until we get insert returning implemented (allegedly
> soon), the
> protocol doesn't support returning any values without another round
> trip to the db
>
> So you have two choices here.
>
> 1) get the ID before and insert it ie select
> nextval('sequence_name')
> and insert the value explicitly. If you cache sequences you
> can cache
> them on the connection too ( more work )
> 2) get the ID after using currval('sequence_name')
>
> P.S. Using PostGRE is frowned upon the name is either Postgres, or
> PostgreSQL
>
> Dave
> On 22-Oct-05, at 4:57 PM, Mike Clements wrote:
>
> > Hello,
> >
> > I'm a newbie on PostGRE but have experience using SQL
> Server & Oracle
> > via JDBC. Something that works fine on these does not work in
> > PostGRE so
> > I'm looking for advice.
> >
> > In my schema all primary keys are integers and the DB automatically
> > assigns values (using sequences or identities). When I insert into
> > these
> > tables via JDBC I do not specify any value for the primary
> key, and I
> > use the RETURN_GENERATED_KEYS flag so the generated key is
> provided in
> > the RecordSet returned from the insert command - something
> like this:
> >
> > pStmt = itsDbConn.prepareStatement(
> > "insert into tbl (col1) values (?)",
> > Statement.RETURN_GENERATED_KEYS);
> > pStmt.setString(1, "foo");
> > count = pStmt.executeUpdate();
> > if(count > 0)
> > {
> > rs = pStmt.getGeneratedKeys();
> > rs.next();
> > pk = rs.getLong(1);
> > }
> >
> > This works on SQL Server & Oracle but *not* on PostGRE SQL. In the
> > latter, it throws an exception in prepareStatement() saying "this
> > method
> > is not yet implemented".
> >
> > So my question is, how does one do this? This keygen approach is
> > important for performance, scalability and robustness. Launching a
> > separate SQL command to fetch the generated key has performance
> > problems. Self-generating the keys has problems with concurrency
> > across
> > multiple clients.
> >
> > Thanks
> >
> > Michael R. Clements
> > Principal Architect, Actional Corp.
> > mclements@actional.com
> > FREE! Actional SOAPstation Developer Version
> > Web services routing, security, transformation and versioning
> > http://www.actional.com/sstdownload
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 4: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
> >
>
>
>
When Postgres eventually does support keygen as you mentioned, which form is more likely to be supported?
ps = itsDBConnection.prepareStatement(sql, {"myKeyColumn"});
OR
ps = itsDBConnection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
In the first, the caller declares which columns he wants back so there is no possibility of any ambiguity. In the
second,the caller relies on the database to decide which column will be returned, and we hope the DB knows which one
thecaller wants (most likely the primary key column).
> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Mike Clements
> Sent: Monday, October 24, 2005 12:51 PM
> To: Dave Cramer
> Cc: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] JDBC keygen select
>
> Dave,
>
> Thanks for the info. Right now I'm issuing a select
> currval('foo') after the insert to get the generated PK. This
> works but the extra SQL round trip slows down insert
> performance quite a bit.
>
> Mike
>
> > -----Original Message-----
> > From: Dave Cramer [mailto:pg@fastcrypt.com]
> > Sent: Monday, October 24, 2005 11:20 AM
> > To: Mike Clements
> > Cc: pgsql-jdbc@postgresql.org
> > Subject: Re: [JDBC] JDBC keygen select
> >
> > Mike,
> >
> > Well, until we get insert returning implemented (allegedly
> > soon), the
> > protocol doesn't support returning any values without
> another round
> > trip to the db
> >
> > So you have two choices here.
> >
> > 1) get the ID before and insert it ie select
> > nextval('sequence_name')
> > and insert the value explicitly. If you cache sequences you
> > can cache
> > them on the connection too ( more work )
> > 2) get the ID after using currval('sequence_name')
> >
> > P.S. Using PostGRE is frowned upon the name is either Postgres, or
> > PostgreSQL
> >
> > Dave
> > On 22-Oct-05, at 4:57 PM, Mike Clements wrote:
> >
> > > Hello,
> > >
> > > I'm a newbie on PostGRE but have experience using SQL
> > Server & Oracle
> > > via JDBC. Something that works fine on these does not work in
> > > PostGRE so
> > > I'm looking for advice.
> > >
> > > In my schema all primary keys are integers and the DB
> automatically
> > > assigns values (using sequences or identities). When I
> insert into
> > > these
> > > tables via JDBC I do not specify any value for the primary
> > key, and I
> > > use the RETURN_GENERATED_KEYS flag so the generated key is
> > provided in
> > > the RecordSet returned from the insert command - something
> > like this:
> > >
> > > pStmt = itsDbConn.prepareStatement(
> > > "insert into tbl (col1) values (?)",
> > > Statement.RETURN_GENERATED_KEYS);
> > > pStmt.setString(1, "foo");
> > > count = pStmt.executeUpdate();
> > > if(count > 0)
> > > {
> > > rs = pStmt.getGeneratedKeys();
> > > rs.next();
> > > pk = rs.getLong(1);
> > > }
> > >
> > > This works on SQL Server & Oracle but *not* on PostGRE SQL. In the
> > > latter, it throws an exception in prepareStatement()
> saying "this
> > > method
> > > is not yet implemented".
> > >
> > > So my question is, how does one do this? This keygen approach is
> > > important for performance, scalability and robustness. Launching a
> > > separate SQL command to fetch the generated key has performance
> > > problems. Self-generating the keys has problems with concurrency
> > > across
> > > multiple clients.
> > >
> > > Thanks
> > >
> > > Michael R. Clements
> > > Principal Architect, Actional Corp.
> > > mclements@actional.com
> > > FREE! Actional SOAPstation Developer Version
> > > Web services routing, security, transformation and versioning
> > > http://www.actional.com/sstdownload
> > >
> > > ---------------------------(end of
> > > broadcast)---------------------------
> > > TIP 4: Have you searched our list archives?
> > >
> > > http://archives.postgresql.org
> > >
> > >
> >
> >
> >
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>
Mike,
Apparently there is a patch floating around that will implement
INSERT ... RETURNING
So I'd say that it is more likely that number 1 gets implemented as
it would seem to me easier to do if we know exactly which column to
return.
Dave
On 2-Feb-06, at 9:07 AM, Mike Clements wrote:
> When Postgres eventually does support keygen as you mentioned,
> which form is more likely to be supported?
>
> ps = itsDBConnection.prepareStatement(sql, {"myKeyColumn"});
>
> OR
>
> ps = itsDBConnection.prepareStatement(sql,
> Statement.RETURN_GENERATED_KEYS);
>
> In the first, the caller declares which columns he wants back so
> there is no possibility of any ambiguity. In the second, the caller
> relies on the database to decide which column will be returned, and
> we hope the DB knows which one the caller wants (most likely the
> primary key column).
>
>> -----Original Message-----
>> From: pgsql-jdbc-owner@postgresql.org
>> [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Mike Clements
>> Sent: Monday, October 24, 2005 12:51 PM
>> To: Dave Cramer
>> Cc: pgsql-jdbc@postgresql.org
>> Subject: Re: [JDBC] JDBC keygen select
>>
>> Dave,
>>
>> Thanks for the info. Right now I'm issuing a select
>> currval('foo') after the insert to get the generated PK. This
>> works but the extra SQL round trip slows down insert
>> performance quite a bit.
>>
>> Mike
>>
>>> -----Original Message-----
>>> From: Dave Cramer [mailto:pg@fastcrypt.com]
>>> Sent: Monday, October 24, 2005 11:20 AM
>>> To: Mike Clements
>>> Cc: pgsql-jdbc@postgresql.org
>>> Subject: Re: [JDBC] JDBC keygen select
>>>
>>> Mike,
>>>
>>> Well, until we get insert returning implemented (allegedly
>>> soon), the
>>> protocol doesn't support returning any values without
>> another round
>>> trip to the db
>>>
>>> So you have two choices here.
>>>
>>> 1) get the ID before and insert it ie select
>>> nextval('sequence_name')
>>> and insert the value explicitly. If you cache sequences you
>>> can cache
>>> them on the connection too ( more work )
>>> 2) get the ID after using currval('sequence_name')
>>>
>>> P.S. Using PostGRE is frowned upon the name is either Postgres, or
>>> PostgreSQL
>>>
>>> Dave
>>> On 22-Oct-05, at 4:57 PM, Mike Clements wrote:
>>>
>>>> Hello,
>>>>
>>>> I'm a newbie on PostGRE but have experience using SQL
>>> Server & Oracle
>>>> via JDBC. Something that works fine on these does not work in
>>>> PostGRE so
>>>> I'm looking for advice.
>>>>
>>>> In my schema all primary keys are integers and the DB
>> automatically
>>>> assigns values (using sequences or identities). When I
>> insert into
>>>> these
>>>> tables via JDBC I do not specify any value for the primary
>>> key, and I
>>>> use the RETURN_GENERATED_KEYS flag so the generated key is
>>> provided in
>>>> the RecordSet returned from the insert command - something
>>> like this:
>>>>
>>>> pStmt = itsDbConn.prepareStatement(
>>>> "insert into tbl (col1) values (?)",
>>>> Statement.RETURN_GENERATED_KEYS);
>>>> pStmt.setString(1, "foo");
>>>> count = pStmt.executeUpdate();
>>>> if(count > 0)
>>>> {
>>>> rs = pStmt.getGeneratedKeys();
>>>> rs.next();
>>>> pk = rs.getLong(1);
>>>> }
>>>>
>>>> This works on SQL Server & Oracle but *not* on PostGRE SQL. In the
>>>> latter, it throws an exception in prepareStatement()
>> saying "this
>>>> method
>>>> is not yet implemented".
>>>>
>>>> So my question is, how does one do this? This keygen approach is
>>>> important for performance, scalability and robustness. Launching a
>>>> separate SQL command to fetch the generated key has performance
>>>> problems. Self-generating the keys has problems with concurrency
>>>> across
>>>> multiple clients.
>>>>
>>>> Thanks
>>>>
>>>> Michael R. Clements
>>>> Principal Architect, Actional Corp.
>>>> mclements@actional.com
>>>> FREE! Actional SOAPstation Developer Version
>>>> Web services routing, security, transformation and versioning
>>>> http://www.actional.com/sstdownload
>>>>
>>>> ---------------------------(end of
>>>> broadcast)---------------------------
>>>> TIP 4: Have you searched our list archives?
>>>>
>>>> http://archives.postgresql.org
>>>>
>>>>
>>>
>>>
>>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 3: Have you checked our extensive FAQ?
>>
>> http://www.postgresql.org/docs/faq
>>
>>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
Hi, Dave,
Dave Cramer wrote:
> Mike,
>
> Apparently there is a patch floating around that will implement INSERT
> ... RETURNING
>
> So I'd say that it is more likely that number 1 gets implemented as it
> would seem to me easier to do if we know exactly which column to return.
I'd also prefer that second way, as it additionally allows returing more
than one column (as a resultset), especially together with the multi-row
insert extension that's also discussed.
>> ps = itsDBConnection.prepareStatement(sql, {"myKeyColumn"});
ps = itsDBConnection.prepareStatement(sql+" RETURNING myKeyColumn");
HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org