Re: Synthesize support for Statement.getGeneratedKeys()?

Hi Ken
On 20-Jan-07, at 12:43 PM, Ken Johanson wrote:

>
>>> If someone has pointers to where the augmentaion code should be
>>> placed (org.tgresql.jdbc3.Jdbc3ResultSetMetadata I presume), and
>>> also the protocol (separate query to the server?, or append
>>> RETURNING clause to the DML?).
>> Right now, the server protocol does not have a separate facility
>> for the RETURNING functionality. Therefore you have to append a
>> RETURNING clause to the query string.
>>> Also I cant remember - is it true that when inserting multuple
>>> VALUES, that only the first (or last) SEQUENCE can be retrived,
>>> or can I populate a resultset using the RETURNING data from by
>>> the server?
>> I suppose it should work with multiple VALUES, but you can simple
>> try. ;-)
>

We've pretty much avoided trying to parse the SQL up until now, and
I'm still not sure it's a good idea. My sense is that this will
burden the driver for everyone. How many people actually want this
implemented ?
>
> For the PG & SQL masters out there, I am brain storming this...
>
> 1) It seems implicit that I will have to scan the input query for
> an existing RETURNING clause, and replace it if it exists; is there
> any pre-built query parsing helpers that would help with this? Or
> for efficiency I would need to do an indexOf that scans from the
> end of the CharSequence... thoughts?

There is some parsing already done to break the query up into pieces,
perhaps just adding it there ?
>
> 2) Any queries where simply appending a RETURNING can somehow spoof
> functionality (UPDATEs, etc), or cause unintended results? Is it
> always permissible for RETURNING to be last? (i.e can it appear
> after other user clauses, LIMIT, etc)
>
> 3) Is there a) an efficient RETURNING clause to pre-populate the
> generated-keys result set, or b) should I synthesize it.

I take it from this that you are intending on returning all generated
columns?, just generated columns that have keys ?
>
> 4) If 3b is required, then besides incrementing (by one) sequences,
> any suggestions on how to correctly synthesize other increment
> values? Other key types (OIDs, etc?)

Are you suggesting here that you are planning on incrementing the
sequences by 1 ? Why not just let the insert occur and get the
currval of the sequence ?
>
> 5) To be absolutely sure, inserting multiple values then getting
> the sequence back (RETURNING) will happen atomically in the server,
> correct?
> (to avoid getting another transaction's keys).
You don't want to return the sequence, as it can be changed by
another transaction, you want to return the value in the row that was
inserted.
>
> If someone wants to write up a spec on how this should work then
> I'll try to implement it in code. I can work without a spec but
> I'll make more incorrect assumptions (not being PG proficient yet).

Dave
> Thanks,
> Ken
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>