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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
> >
> >
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Oliver Jowett | 2005-10-24 20:31:51 | Re: Speed up fixes |
Previous Message | Dave Cramer | 2005-10-24 19:49:06 | Re: Speed up fixes |