Skip site navigation (1) Skip section navigation (2)

Re: JDBC keygen select

From: "Mike Clements" <mclements(at)actional(dot)com>
To: "Dave Cramer" <pg(at)fastcrypt(dot)com>
Cc: <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: JDBC keygen select
Date: 2005-10-24 19:50:47
Message-ID: 06B12D1D68BCCA4CB1F60BB4EA509768245289@pq-exch01.actional.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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(at)fastcrypt(dot)com] 
> Sent: Monday, October 24, 2005 11:20 AM
> To: Mike Clements
> Cc: pgsql-jdbc(at)postgresql(dot)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(at)actional(dot)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
> >
> >
> 
> 
> 

pgsql-jdbc by date

Next:From: Oliver JowettDate: 2005-10-24 20:31:51
Subject: Re: Speed up fixes
Previous:From: Dave CramerDate: 2005-10-24 19:49:06
Subject: Re: Speed up fixes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group