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

Re: Synthesize support for Statement.getGeneratedKeys()?

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: Michael Paesold <mpaesold(at)gmx(dot)at>
Cc: Ken Johanson <pg-user(at)kensystem(dot)com>, pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Synthesize support for Statement.getGeneratedKeys()?
Date: 2007-01-22 12:30:04
Message-ID: EFC27EC0-3877-4F9C-AEDC-FDAD1D6C306F@fastcrypt.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
On 22-Jan-07, at 4:16 AM, Michael Paesold wrote:

> 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.
>
Yes, agreed, Ken was just curious how it is being done now.
> 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.

Yes, this would be the best solution.
> Best Regards
> Michael Paesold
>
>
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>


In response to

Responses

pgsql-jdbc by date

Next:From: Michael PaesoldDate: 2007-01-22 13:37:51
Subject: Re: Synthesize support for Statement.getGeneratedKeys()?
Previous:From: Dave CramerDate: 2007-01-22 12:28:28
Subject: Re: Synthesize support for Statement.getGeneratedKeys()?

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