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

Re: Synthesize support for Statement.getGeneratedKeys()?

From: Michael Paesold <mpaesold(at)gmx(dot)at>
To: Ken Johanson <pg-user(at)kensystem(dot)com>
Cc: Dave Cramer <pg(at)fastcrypt(dot)com>, pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Synthesize support for Statement.getGeneratedKeys()?
Date: 2007-01-22 09:16:22
Message-ID: 45B480E6.2010009@gmx.at (view raw or flat)
Thread:
Lists: pgsql-jdbc
Ken Johanson wrote:
> 
>>> As an aside, how do PG jdbc users get the server generated keys? Or 
>>> does everyone use some kind of UUID system (which I think is 
>>> generally regarded as detrimental to indexes/memory under high load 
>>> and large DB sizes - compared to int/bigint)? Or do PG users using 
>>> some standard or server-specific (RETURNING) SQL clause?
>>
>> either create the key ahead of time select nextval('sequence') and 
>> insert it explicitly, or insert the row and  then  select 
>> currval('sequence')
>>
> 
> 
> That makes sense; the sequence is retrieved and it internally increments 
> - regardless of whether the key was actually inserted or not. I'm 
> personally not used to this though, it allows for actual keys in the 
> database to possibly have gaps (if the key want actually used / rollback 
> etc). Thats trivial / innocuous I guess, but I'm just used to having 
> sequential keys tables. Would this require two trips to the server, or 
> can we handle in one excecuteUpdate?
> 
> My real question is, what about the case where multiple VALUES are 
> inserted; if I have 3 values should I call the sequence 3 times? What is 
> the most efficient was to do that? (Can I do it in a single query?)

I don't think you should use "currval" or "nextval" at all. A general 
solution in the JDBC driver should even work in the case of triggers 
that interfere with the value of a sequence. Or which might change the 
value actually inserted into the table. Just think of an insert trigger 
that uses a sequence for a second time.

There is only one way to reliably get the database generated values: the 
RETURNING clause.

So my basic suggestion was to rewrite a query written as:
"INSERT INTO tab VALUES (...)"
into
"INSERT INTO tab VALUES (...) RETURNING x"

With x being either (a) what the user specified using the Java API (i.e. 
any column names) or (b) the primary key column(s) (or other columns 
having a "DEFAULT currval(...)").
The second case (b) I would leave for later, since it requires parsing 
the query and finding the table which will be inserted into. And you 
would have to use database meta data to find the columns to return.

Of course, there should be a minimum amount of parsing to detect if the 
query is a valid INSERT query and does not already have a different 
RETURNING clause.

Another option would be to convince backend developers to add a way to 
specify a "RETURNING clause" on the protocol level, i.e. without having 
to change the query string.

Best Regards
Michael Paesold



In response to

Responses

pgsql-jdbc by date

Next:From: Dave CramerDate: 2007-01-22 12:28:28
Subject: Re: Synthesize support for Statement.getGeneratedKeys()?
Previous:From: Michael SchmidtDate: 2007-01-22 05:44:26
Subject: Re: pg_dump in PostgreSQL version 8.2 not working in Java app

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