Re: Synthesize support for Statement.getGeneratedKeys()?

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: Ken Johanson <pg-user(at)kensystem(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org, Michael Paesold <mpaesold(at)gmx(dot)at>
Subject: Re: Synthesize support for Statement.getGeneratedKeys()?
Date: 2007-01-23 11:32:40
Message-ID: 0838A2A1-6BE0-42B2-979A-7F2A40A22E55@fastcrypt.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc


On 23-Jan-07, at 2:00 AM, Ken Johanson wrote:

>>>>> 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.
>>>>
>
>
> I think that, given everyone's input (including Vit's, thanks) and
> mention of possible variation on query, possible need to parse for
> table/column names, and/or need to call database metadata / or
> result set metadata (to get keys?) (which require another trip to
> the server?)... this might be out of my league. Well, even if I did
> get it working, it likely would not work in every case (triggers
> etc), and would eventually be replaced when V4 protocol comes around.
>
> Unless one of the PG folks can prescribe, in exact terms, the very
> best way to execute this (after which I would build out the actual
> patch)... then I may have to bow out of this (it's complex / error
> prone enough to frighten lil'ol me, and time is a bit short on my
> end too I'm afraid).

If we can implement the one which does specify the keys that would be
useful. Statement.getGeneratedKeys( columns )
>
> Perhaps it's better for everyone if we lobby to have the backend/
> protocol to add this natively (as you all have suggested). So..
>
> Does anyone know if the actual server core natively has the ability
> to build created-keys resultsets (without having to modify the
> query / RETURNS), or is this truly a protocl bottleneck?...
>
I don't know for absolute certainty but I highly suspect that it
does, since it can return the columns returned via insert returning

Dave
> Thanks,
> Ken
>
>
>

In response to

Browse pgsql-jdbc by date

  From Date Subject
Next Message Mark Lewis 2007-01-23 15:33:31 Re: Applet Connectivity - PLEASE help
Previous Message Ken Johanson 2007-01-23 07:00:13 Re: Synthesize support for Statement.getGeneratedKeys()?