Re: Synthesize support for Statement.getGeneratedKeys()?

Hello,

I've just come to realize that Statement.getGeneratedKeys() is not
supported in the current PG and/or JDBC driver. Does someone know if
this is a limitation of PG, or its protocol, or just not yet implemented
in the JDBC driver? I'm just wondering where, if at all (if I have
enough brain cells that is :), I could try to offer a patch or ideas..

Then question 2; I did see a discussion where it was suggested that we
could get roughly the same effect by issuing a
SELECT currval('<sequence-name>'); after the DML...
http://archives.postgresql.org/pgsql-jdbc/2005-10/msg00035.php
Would it then be feasible internal to the JDBC driver to (create an
option that would enable) always implicitly append that query to the
(String)sql arg of Statment.executeUpdate(String sql, String[]
columnNames)? I mean, just internally attempt to create the same
behavior as what this method should be doing?

Question 3 is, it seems like option two would only return the last
created key, not set of keys, in the case where multiple rows were
inserted.. is this accurate?

Unfortunately if I cant find a way to make my target-app work with PG
(without adding PG-specific modifications for getting keys), I'm
probably not going to be able to make the switch to PG unfortunately -
the code I'm working with makes really, really extensive use of
retrieved keys..

Thank you,
Ken

Ken Johanson wrote:
> Hello,
>
> I've just come to realize that Statement.getGeneratedKeys() is not
> supported in the current PG and/or JDBC driver. Does someone know if
> this is a limitation of PG, or its protocol, or just not yet implemented
> in the JDBC driver? I'm just wondering where, if at all (if I have
> enough brain cells that is :), I could try to offer a patch or ideas..

It would be possible to implement that using the RETURNING clause
supported in recent versions of the server.

See here:
http://archives.postgresql.org/pgsql-jdbc/2006-10/msg00035.php
http://archives.postgresql.org/pgsql-jdbc/2006-10/msg00037.php

Unfortunately, all my available spare time went into implementing
support for standards_conforming_strings in the 8.2 release cycle.

> Then question 2; I did see a discussion where it was suggested that we
> could get roughly the same effect by issuing a
> SELECT currval('<sequence-name>'); after the DML...
> http://archives.postgresql.org/pgsql-jdbc/2005-10/msg00035.php
> Would it then be feasible internal to the JDBC driver to (create an
> option that would enable) always implicitly append that query to the
> (String)sql arg of Statment.executeUpdate(String sql, String[]
> columnNames)? I mean, just internally attempt to create the same
> behavior as what this method should be doing?

As was discussed before, a solution just for sequences is not general
enough, therefore the RETURNING thing.

> Question 3 is, it seems like option two would only return the last
> created key, not set of keys, in the case where multiple rows were
> inserted.. is this accurate?

Seems correct. And that's one of the objections to the idea.

> Unfortunately if I cant find a way to make my target-app work with PG
> (without adding PG-specific modifications for getting keys), I'm
> probably not going to be able to make the switch to PG unfortunately -
> the code I'm working with makes really, really extensive use of
> retrieved keys..

Could you comment on my mail above (the second one) -- what API-methods
does your software use, actually?

Best Regards
Michael Paesold

Michael Paesold wrote:
..
> It would be possible to implement that using the RETURNING clause
> supported in recent versions of the server.
>
> See here:
> http://archives.postgresql.org/pgsql-jdbc/2006-10/msg00035.php
> http://archives.postgresql.org/pgsql-jdbc/2006-10/msg00037.php
>
> Unfortunately, all my available spare time went into implementing
> support for standards_conforming_strings in the 8.2 release cycle.
>

And that is a feature which I VERY much appreciate having... thank you, btw!

>
> Could you comment on my mail above (the second one) -- what API-methods
> does your software use, actually?
>

Yes, of the four executeUpdates() that you listed in your email
discussion, it uses executeUpdate(String sql, String[] columnNames)
exclusively (the easy one :-)).

Aside from that, if there were a way to get the backend to support the
others (for example executeUpdate(String sql, int[] columnIdx)), that
would be a best investment of everyone's time. But my vote right now
would be for an implementation of (..,String[] columnNames) so that I
can get bootstrapped with PG :)

Either way, I will be very happy, of course, to do some beta testing.

> Best Regards
> Michael Paesold
>
>
>
>

Ken Johanson wrote:
> Michael Paesold wrote:
>> Could you comment on my mail above (the second one) -- what
>> API-methods does your software use, actually?
>>
>
> Yes, of the four executeUpdates() that you listed in your email
> discussion, it uses executeUpdate(String sql, String[] columnNames)
> exclusively (the easy one :-)).
>
> Aside from that, if there were a way to get the backend to support the
> others (for example executeUpdate(String sql, int[] columnIdx)), that
> would be a best investment of everyone's time. But my vote right now
> would be for an implementation of (..,String[] columnNames) so that I
> can get bootstrapped with PG :)
>
> Either way, I will be very happy, of course, to do some beta testing.

I might be able to find some time to get this done during Christmas
holidays. I will report back next week.

Best Regards
Michael Paesold

Michael Paesold wrote:
>
> I might be able to find some time to get this done during Christmas
> holidays. I will report back next week.
>
> Best Regards
> Michael Paesold
>

Michael, sorry for my late response. Just to say thank you, and I'm back
online to test this as soon as you'd like. Seems like we both have taken
a break from the software world during Christmas :)

Ken

Michael Paesold wrote:
> Ken Johanson wrote:
>> Michael Paesold wrote:
>>> Could you comment on my mail above (the second one) -- what
>>> API-methods does your software use, actually?
>>>
>>
>> Yes, of the four executeUpdates() that you listed in your email
>> discussion, it uses executeUpdate(String sql, String[] columnNames)
>> exclusively (the easy one :-)).
>>

Michael, just wondering if you'd found some time to look into
implementing this; and if it is in CVS please let me know; I'm anxious
to test it out.

Thank you,
ken

Michael Paesold wrote:
> Ken Johanson wrote:
>> Hello,
>>
>> I've just come to realize that Statement.getGeneratedKeys() is not
>> supported in the current PG and/or JDBC driver. Does someone know if
>> this is a limitation of PG, or its protocol, or just not yet
>> implemented in the JDBC driver? I'm just wondering where, if at all
>> (if I have enough brain cells that is :), I could try to offer a patch
>> or ideas..
>
> It would be possible to implement that using the RETURNING clause
> supported in recent versions of the server.
>
> See here:
> http://archives.postgresql.org/pgsql-jdbc/2006-10/msg00035.php
> http://archives.postgresql.org/pgsql-jdbc/2006-10/msg00037.php
>
> Unfortunately, all my available spare time went into implementing
> support for standards_conforming_strings in the 8.2 release cycle.
>

Michael, does it look like you might have the time for this in the next
couple months? If not and no one else more familar/rofiecient with the
private API will have time, then I giuess I should dig in and try...
even if I need to ramp up to the server protocol to do it.

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?).

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?

Thanks again,
Ken

>
> 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?).
>
> 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?
>

Answering my own question here... sort of :)
http://www.postgresql.org/docs/current/static/sql-insert.html

In the case of inserting multiple values (say 3 rows), is there a clever
way to get the last 3 sequences via native SQL? Or would the JDBC code
need to synthesize them?: (very simplified)

...exec
rs.next();//single key returned
Object id = rs.getObject();
Object[] keys = new Object[insertedRowCount];
for (; keys.length; i++)
keys[i] = increment(id);
myPGResultSet.populate(userDeclaredKeyNameOrIdx, keys);//hypothetical -
i didn't look at the API yet, sorry

Ken Johanson wrote:
> Michael Paesold wrote:
>> Ken Johanson wrote:
>>> Hello,
>>>
>>> I've just come to realize that Statement.getGeneratedKeys() is not
>>> supported in the current PG and/or JDBC driver. Does someone know if
>>> this is a limitation of PG, or its protocol, or just not yet
>>> implemented in the JDBC driver? I'm just wondering where, if at all
>>> (if I have enough brain cells that is :), I could try to offer a
>>> patch or ideas..
>>
>> It would be possible to implement that using the RETURNING clause
>> supported in recent versions of the server.
>>
>> See here:
>> http://archives.postgresql.org/pgsql-jdbc/2006-10/msg00035.php
>> http://archives.postgresql.org/pgsql-jdbc/2006-10/msg00037.php
>>
>> Unfortunately, all my available spare time went into implementing
>> support for standards_conforming_strings in the 8.2 release cycle.
>>
>
> Michael, does it look like you might have the time for this in the next
> couple months? If not and no one else more familar/rofiecient with the
> private API will have time, then I giuess I should dig in and try...
> even if I need to ramp up to the server protocol to do it.

Unfortunately I did not find time to work on this over Christmas
holidays and I will be quite busy at work for the next two or three
months or so. Thus, if you have time to work on this yourself, please go
ahead!

> 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. ;-)

Best Regards
Michael Paesold

>> 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. ;-)
>

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?

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.

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?)

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).

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).

Thanks,
Ken

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
>

Dave Cramer wrote:
>>
>> 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 ?
>>

Yes, ideally. I'd prefer not to try and synthesize the values from only
a single (last or first row's) key, since sequences can have increment
values other than one, and because OIDs are not predictable (are they?).
Other server generated key types also seem to make the synthetic idea
unfeasible.

>> 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 ?

I'm thinking what you're thinking; get the current value; however the
increment I mention is just in case I cant get more than one curval... I
don't know, can I get 3 values from RETURN if I insert three VALUEs in
one query??

>>
>> 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.
>>

Agreed.

On 21-Jan-07, at 2:06 PM, Ken Johanson wrote:

> Dave Cramer wrote:
>>>
>>> 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 ?
>>>
>
> Yes, ideally. I'd prefer not to try and synthesize the values from
> only a single (last or first row's) key, since sequences can have
> increment values other than one, and because OIDs are not
> predictable (are they?). Other server generated key types also seem
> to make the synthetic idea unfeasible.

I wouldn't worry about OID's first of all, they are no longer the
default on user tables, secondly, they are not indexed.
>
>>> 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 ?
>
> I'm thinking what you're thinking; get the current value; however
> the increment I mention is just in case I cant get more than one
> curval... I don't know, can I get 3 values from RETURN if I insert
> three VALUEs in one query??
hmmm good question, one which I doubt the Sun people thought about. I
wouldn't bother trying to return any more than the last one.
>
>>>
>>> 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.
>>>
>
> Agreed.
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

>>>> 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 ?
>>
>> I'm thinking what you're thinking; get the current value; however the
>> increment I mention is just in case I cant get more than one curval...
>> I don't know, can I get 3 values from RETURN if I insert three VALUEs
>> in one query??
> hmmm good question, one which I doubt the Sun people thought about. I
> wouldn't bother trying to return any more than the last one.
>>

Well, unless server generated keys can only be numeric (increment by
one, i.e predictable) (which is the only kind I've used), then I think
it's essential that we somehow can get each of the generated keys.

As for Sun/others, I do know that this feature works well in other
dbms's - every one I've used supports it, and with multiple rows
inserted. But I've only ever used numeric server generated keys so I
don't know if other types are supported on those DBs.

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?

ken

On 21-Jan-07, at 7:52 PM, Ken Johanson wrote:

>
>>>>> 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 ?
>>>
>>> I'm thinking what you're thinking; get the current value; however
>>> the increment I mention is just in case I cant get more than one
>>> curval... I don't know, can I get 3 values from RETURN if I
>>> insert three VALUEs in one query??
>> hmmm good question, one which I doubt the Sun people thought
>> about. I wouldn't bother trying to return any more than the last one.
>>>
>
>
> Well, unless server generated keys can only be numeric (increment
> by one, i.e predictable) (which is the only kind I've used), then I
> think it's essential that we somehow can get each of the generated
> keys.
>
> As for Sun/others, I do know that this feature works well in other
> dbms's - every one I've used supports it, and with multiple rows
> inserted. But I've only ever used numeric server generated keys so
> I don't know if other types are supported on those DBs.
OK, I should look at the spec before making statements above. This is
possible in postgres, you can get whatever values were auto generated
by the insert
>
> 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')

Dave
>
> ken
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

>> 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?)

Thank you,
ken

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

On 22-Jan-07, at 12:09 AM, 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?

Well, this is widely debated, but in Postgresql since the sequence
cannot be rolled back (easily) you have to design for gaps. Take for
instance the case where you cache sequences for speed. If you drop
that connection you will lose all the cached values.
>
> 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?)

You have to call the sequence n times.
>
> Thank you,
> ken
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

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
>

Dave Cramer schrieb:
>
> 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.

You are right, sorry. I wanted to be sure that he did not try to do the
same thing for the JDBC driver.

Best Regards
Michael Paesold

Hello.

I have few thoughts I'd like to share. Everything below is my IMHO.
First of all, parsing input DDL (or requiring protocol support) is
needed to get full support for all range of DDLS. But now PostgreSQL
does not support this feature for any DLLs.
Then you can always detect is the support is needed on the first user
call - depending on which function is called to prepare/execute the
statement.
Why don't you want to do the next:
For

PreparedStatement <http://java.sun.com/javase/6/docs/api/java/sql/PreparedStatement.html> *prepareStatement*(String <http://java.sun.com/javase/6/docs/api/java/lang/String.html> sql,
int[] columnIndexes)
throws SQLException <http://java.sun.com/javase/6/docs/api/java/sql/SQLException.html>

(ans similar Statement call)
simply call sql + " RETURNING *"
and then filter out column indexes
Of course, this won't handle the case when statement already contains
RETURNING, cases for non-INSERT and so on (I am not an expert to
describe all possible cases).
But now NO cases are working and this change will make some work
properly and I, personally, don't see a problem in others giving some
other error message (say, incorrect SQL text for statements already
containing RETURNING) then they are doing now.
When the protocol will be changed (extended), you can use that but what
does prevent you to implement the most widely used (as for me) case,
given it will be implemented properly?

>>>> 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).

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?...

Thanks,
Ken

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 the RETURNING clause that I'll be appending to the query, to get
gen'd keys, anyone have any advise for best practice for when to quote
the column names? Are there any pre-built util methods in the driver
that I can use to scan for identifiers that need quoting? (whitespace
char, reserved words, etc)?

Thanks,

Ken

In implementing Statement.executeUpdate(String sql,String[]
columnNames), does any know how I could go about sending the sql and
subsequent RETURNING clause directly to a stream? Just so I can avoid
double-buffering the query (allocated in the sql, and second in the
StringBuffer I'd be appending the RETURNING clause to). I do see that
there are ASCII and Unicode char streams and I'm also not sure how to
delegate to those or if a highr level method (network) would do that..

gratsi,

ken

http://onnet.cc/AbstractJdbc3Statement.patch
http://onnet.cc/AbstractJdbc2Statement.patch

For some reason I'm getting an empty result set with this.. the javadocs
are a tad sparse so I may not be able to resolve this for a day or two..
anyone better at with this api? I'm also not sure if this quick and
dirty change will correctly handle PreparedStmts.

ken

Ken,

Do you have a test case ? What do the server logs show ?

Dave
On 26-Jan-07, at 2:12 AM, Ken Johanson wrote:

> http://onnet.cc/AbstractJdbc3Statement.patch
> http://onnet.cc/AbstractJdbc2Statement.patch
>
> For some reason I'm getting an empty result set with this.. the
> javadocs are a tad sparse so I may not be able to resolve this for
> a day or two.. anyone better at with this api? I'm also not sure if
> this quick and dirty change will correctly handle PreparedStmts.
>
>
> ken
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

Dave Cramer wrote:
> Ken,
>
> Do you have a test case ? What do the server logs show ?
>

Hi Dave,

My test case is:

String url = "jdbc:postgresql://127.0.0.1:5432/test?stringtype=unspecified";
String clz = "org.postgresql.Driver";
String dml = "insert into contact (rowid,logonname) values (DEFAULT,'bar')";
String[] keys = {"rowid"};
Connection con = DriverManager.getConnection(url, "user", "pass");
Statement stmt = con.createStatement();
int affected = stmt.executeUpdate(dml, keys);
ResultSet rs = stmt.getGeneratedKeys();
if (rs.next())
out.println(rs.getObject(1));
else
out.println("NO ROWS, INSERTED "+affected);

A ResultSet is returned but has no rows. The insert does succeed and a
sequence generated key increments in the 'rowid' column.

I think the issue is just that my code is not calling through the
correct private methods that would populate a result.

Also I noticed that so far my mods have not implemented
Stmt.NO_GENERATED_KEYS & RETURN_GENERATED_KEYS;

On a related note, do you know how I could call though a Stream method
instead of the String/Charsequence execs?

Thanks,
Ken

On Thu, 25 Jan 2007, Ken Johanson wrote:

> In implementing Statement.executeUpdate(String sql,String[] columnNames),
> does any know how I could go about sending the sql and subsequent RETURNING
> clause directly to a stream? Just so I can avoid double-buffering the query
> (allocated in the sql, and second in the StringBuffer I'd be appending the
> RETURNING clause to).

Don't worry about this minor overhead. So much other manipulation and
object creation happens (even forgetting the network trip) that this will
be in the noise.

> I do see that there are ASCII and Unicode char streams
> and I'm also not sure how to delegate to those or if a highr level method
> (network) would do that..
>

These are for sending data (parameters) to the server for things like
large text fields.

Kris Jurka

On Fri, 26 Jan 2007, Ken Johanson wrote:

> http://onnet.cc/AbstractJdbc3Statement.patch
> http://onnet.cc/AbstractJdbc2Statement.patch

I can't get the first patch to apply cleanly at all, erroring with

patching file AbstractJdbc3Statement.java
patch: **** Premature `---' at line 33; check line numbers at line 21

And even to get to that point I had to change from windows \ to unix /
directory separators. Could we get a patch that is easy to apply?

> For some reason I'm getting an empty result set with this.. the javadocs are
> a tad sparse so I may not be able to resolve this for a day or two.. anyone
> better at with this api? I'm also not sure if this quick and dirty change
> will correctly handle PreparedStmts.
>

I wouldn't be surprised if the driver is confused by an INSERT command
completion tag also having a result set. If you can produce a working
patch I'll take a closer look.

Kris Jurka

>> In implementing Statement.executeUpdate(String sql,String[]
>> columnNames), does any know how I could go about sending the sql and
>> subsequent RETURNING clause directly to a stream? Just so I can avoid
>> double-buffering the query (allocated in the sql, and second in the
>> StringBuffer I'd be appending the RETURNING clause to).
>
> Don't worry about this minor overhead. So much other manipulation and
> object creation happens (even forgetting the network trip) that this
> will be in the noise.
>

Hmm, this might be an area I'd enjoy contributing code for.. if there
are places now where there is avoidable double or triple allocation
(like in my patch) (where it could instead be handled by streams and/or
finer grained chunks), and since the driver (apparently) already has
some stream based utils... maybe I could try to improve those.

I know from experience with other tools that unnec allocation can play
havoc under high load and/or very large data sets, using (resizeable)
buffers (like StringBuffer). When that code gets optimized the
application becomes much more responsive.

If you know of any places in the driver that could benefit from this,
please let me know.

ken

Hi Kris, were you able to look at this? If time wont permit that I'll
dig back in; though I'd prefer not to duplicate any work your're doing,
of course.

Thank,
ken

Kris Jurka wrote:
>
>
> On Fri, 26 Jan 2007, Ken Johanson wrote:
>
>> http://onnet.cc/AbstractJdbc3Statement.patch
>> http://onnet.cc/AbstractJdbc2Statement.patch
>
> I can't get the first patch to apply cleanly at all, erroring with
>
> patching file AbstractJdbc3Statement.java
> patch: **** Premature `---' at line 33; check line numbers at line 21
>
> And even to get to that point I had to change from windows \ to unix /
> directory separators. Could we get a patch that is easy to apply?
>
>
>> For some reason I'm getting an empty result set with this.. the
>> javadocs are a tad sparse so I may not be able to resolve this for a
>> day or two.. anyone better at with this api? I'm also not sure if this
>> quick and dirty change will correctly handle PreparedStmts.
>>
>
> I wouldn't be surprised if the driver is confused by an INSERT command
> completion tag also having a result set. If you can produce a working
> patch I'll take a closer look.
>
> Kris Jurka

Kris,

Strange about the patch. Aside from the import stmts, only the method
below is changed in that source file:

public int executeUpdate(String sql, String columnNames[]) throws
SQLException
{
//fix-me "The driver will ignore the array if the SQL statement is
not an INSERT statement"
//fix me : impl NO_GENERATED_KEYS & RETURN_GENERATED_KEYS
if (columnNames==null || columnNames.length==0)
return executeUpdate(sql);
//should never reallocate
StringBuffer s = new
StringBuffer(12+sql.length()+(columnNames.length*32));
s.append(sql);
s.append('\n');
s.append("RETURNING");
s.append(' ');
for (int i=0; i<columnNames.length; i++)
{
if (i!=0)
s.append(',');
s.append(columnNames[i]);
}
return executeUpdateGetResults(s.toString());
//throw new PSQLException(GT.tr("Returning autogenerated keys is
not supported."), PSQLState.NOT_IMPLEMENTED);
}

ken

On Mon, 29 Jan 2007, Ken Johanson wrote:

> Hi Kris, were you able to look at this? If time wont permit that I'll dig
> back in; though I'd prefer not to duplicate any work your're doing, of
> course.

Taking another look the obvious problem is that you haven't touched
AbstractJdbc3Statement.getGeneratedKeys. It still reads:

public ResultSet getGeneratedKeys() throws SQLException
{
return createDriverResultSet(new Field[0], new Vector());
}

So an empty ResultSet is not surprising.

Kris Jurka

Kris Jurka wrote:
>
>
> On Mon, 29 Jan 2007, Ken Johanson wrote:
>
>> Hi Kris, were you able to look at this? If time wont permit that I'll
>> dig back in; though I'd prefer not to duplicate any work your're
>> doing, of course.
>
> Taking another look the obvious problem is that you haven't touched
> AbstractJdbc3Statement.getGeneratedKeys. It still reads:
>
> public ResultSet getGeneratedKeys() throws SQLException
> {
> return createDriverResultSet(new Field[0], new Vector());
> }
>
> So an empty ResultSet is not surprising.
>
> Kris Jurka

Kris, sorry my response is late. Sometimes I get distracted by life :-)

Thanks for pointing that out. I missed the obvious. The following
replacement for that methods works; I can get the generated keys (in
conjunction with my previous patches).

I have not tested this exhaustively, nor given though about the
correctness of the null-normalization below; is this enough for you or
someone to commit the changes?

Thanks,
Ken

public ResultSet getGeneratedKeys() throws SQLException
{
return result==null ?
createDriverResultSet(new Field[0], new Vector())
: result.getResultSet();
}

On Tue, 13 Feb 2007, Ken Johanson wrote:

> I have not tested this exhaustively, nor given though about the correctness
> of the null-normalization below; is this enough for you or someone to commit
> the changes?
>

No, you've provided the sketch of a solution, but it's not appropriate for
the driver until you've resolved things like quoting column names, putting
in a version check for a server that supports RETURNING, and added some
test cases. Some or all of this can be done by the committer, but the
more work the committer must do the less likely it's going to happen.

Kris Jurka

Kris Jurka wrote:
>
>
> On Tue, 13 Feb 2007, Ken Johanson wrote:
>
>> I have not tested this exhaustively, nor given though about the
>> correctness of the null-normalization below; is this enough for you or
>> someone to commit the changes?
>>
>
> No, you've provided the sketch of a solution, but it's not appropriate
> for the driver until you've resolved things like quoting column names,
> putting in a version check for a server that supports RETURNING, and
> added some test cases. Some or all of this can be done by the
> committer, but the more work the committer must do the less likely it's
> going to happen.
>
> Kris Jurka
>

This patch adds server version checking and only basic quoting to
AbstractJdbc3Statement. No test cases.

It only checks for server version 8 or newer as I couldn't easily find
the threshold for the RETURNING support. It also only quotes the key
array if it contains spaces.

Doe anyone know if there is a scanner method to check for quotable
identifiers? Did server 8 first implement RETURNING?

What other concerns should I apply to this?

Thanks,
ken

Ken Johanson wrote:
> Kris Jurka wrote:
>>
>>
>> On Tue, 13 Feb 2007, Ken Johanson wrote:
>>
>>> I have not tested this exhaustively, nor given though about the
>>> correctness of the null-normalization below; is this enough for you
>>> or someone to commit the changes?
>>>
>>
>> No, you've provided the sketch of a solution, but it's not
>> appropriate for the driver until you've resolved things like quoting
>> column names, putting in a version check for a server that supports
>> RETURNING, and added some test cases. Some or all of this can be
>> done by the committer, but the more work the committer must do the
>> less likely it's going to happen.
>>
>> Kris Jurka
>>
>
> This patch adds server version checking and only basic quoting to
> AbstractJdbc3Statement. No test cases.
>
> It only checks for server version 8 or newer as I couldn't easily find
> the threshold for the RETURNING support.

8.1.5 does not have it. I suppose it is 8.2 feature. You can check by
looking into 8.1 and 8.2 documentation, and, I suppose, "what's new"
for 8.2

>> It only checks for server version 8 or newer as I couldn't easily
find
>> the threshold for the RETURNING support.
>
> 8.1.5 does not have it. I suppose it is 8.2 feature. You can check by
> looking into 8.1 and 8.2 documentation, and, I suppose, "what's new"
> for 8.2

Yes, it is new in 8.2.

Yours,
Laurenz Albe

This patch adds the Major and Minor version checking, for server >=8.2.

If there is a more correct method of checking for quote-able
identifiers, or anything else I'm missing, please let me know.

Thanks,
Ken

Ken Johanson wrote:
> Do you know what built-in method I can choose to determine what
> identifiers need quoting?
>

There isn't one, but I was thinking about it some more and I think we
might want to quote everything. Otherwise we won't be able to
distinguish between columns that are created with quotes and those
without. Consider a table like:

CREATE TABLE tab(a int, "A" int);

Without parsing the query and doing some metadata lookups we won't know
what the columns are. I'd rather not try to do this and the behavior
matches our existing handling of DatabaseMetaData methods. Although we
might get some complaints, I'm OK with it.

Right now the only place we quote identifiers in
jdbc3/PSQLSavepoint#getPGName. I would suggest creating a method in
org.postgresql.core.Utils named appendEscapedIdentifier that works like
appendEscapedString and using it in both places.

On Thu, 15 Feb 2007, Ken Johanson wrote:

> This patch adds the Major and Minor version checking, for server >=8.2.
>

The correct version check should be written:
if (connection.haveMinimumServerVersion("8.2"))

I'm also not sure about the getGeneratedKeys method. Should we throw an
Exception instead of returning an empty ResultSet if there weren't any
generated keys? Also do we need to do better tracking of what comes from
generated keys vs just regular results? Once people start using this
functionality we'll need some better error checking.

Kris Jurka

Kris Jurka wrote:
>
> The correct version check should be written:
> if (connection.haveMinimumServerVersion("8.2"))
>

I will correct this.

> I'm also not sure about the getGeneratedKeys method. Should we throw an
> Exception instead of returning an empty ResultSet if there weren't any
> generated keys?

I checked and the spec says:
"If this Statement object did not generate any keys, an empty ResultSet
object is returned."
If there is an implied rules elsewhere that it is not this simple, I
dont know. Otherwise it seems correct.

Also do we need to do better tracking of what comes
> from generated keys vs just regular results?

I believe you are correct; if that method is called not-after
executeUpdate(String sql, ?), it should throw and exception.. or
something... the spec does not seem to elaborate on this.

Once people start using
> this functionality we'll need some better error checking.

Agreed in full.

Kris Jurka wrote:
>
>
> Right now the only place we quote identifiers in
> jdbc3/PSQLSavepoint#getPGName. I would suggest creating a method in
> org.postgresql.core.Utils named appendEscapedIdentifier that works like
> appendEscapedString and using it in both places.
>

Would you care to code this up; I think you have a clearly understanding
of how it should work.

Thanks,
Ken

On Thu, 15 Feb 2007, Ken Johanson wrote:

> Kris Jurka wrote:
>>
>> Right now the only place we quote identifiers in
>> jdbc3/PSQLSavepoint#getPGName. I would suggest creating a method in
>> org.postgresql.core.Utils named appendEscapedIdentifier that works like
>> appendEscapedString and using it in both places.
>
> Would you care to code this up; I think you have a clearly understanding of
> how it should work.
>

I have added the appendEscapedIdentifier method discussed above.

Kris Jurka