Re: Proposal: RETURNING primary_key()

On Sun, Apr 3, 2016 at 9:49 PM, Igal @ Lucee.org <igal(at)lucee(dot)org> wrote:

> On 4/3/2016 4:34 PM, Dave Cramer wrote:
>
>
> On 4/3/2016 8:21 AM, Dave Cramer wrote:
>
>>
>> I'd like to turn this question around. Are there good reasons to use -ng
>> over pgjdbc ?
>>
>> As to your question, you may be interested to know that pgjdbc is more
>> performant than ng.
>>
>> That's good to know, but unfortunately pgjdbc is unusable for us until
>> https://github.com/pgjdbc/pgjdbc/issues/488 is fixed.
>>
>> Also, as I mentioned in the ticket, I can't imagine RETURNING * being
>> performant if, for example, I INSERT a large chunk of data like an image
>> data or an uploaded file.
>>
>>
> Thanks for the reminder!
>
> So I"m guessing the reason to use ng is to avoid returning * ?
>
>
> I'm not sure if you're serious or if you're just trying to be "cute".
> This ticket should still be fixed. It really doesn't make any sense to me
> that the driver will just blindly append "RETURNING *" to the query.
>
> If I want to return all of the columns from an UPDATE or an INSERT -- then
> I will add "RETURNING *" myself. And if I don't add it, then I probably
> don't want the driver to second guess me, or to think that it knows better
> than I do what I want. If I wanted software that thinks that it knows what
> I want better than I do -- then I would stick with SQL Server rather than
> switch to Postgres.
>
> The driver used to work until someone decided to append "RETURNING *" to
> the SQL code and make it unusable in many cases.
>
> Was there any discussion on this before it was added?
>

​Except the main problem you describe is one where you WANT the driver to
be smart and understand that even though you've asked it to return
generated keys the statement you've provided it is one that incapable of
doing so. Thus you do want it to interpret what you've told it and to do
what you mean and not what you say.

Obviously the problem is solvable - you yourself have said other's have
solved it. That is one piece of good news - the other piece is that
PostgreSQL, and the JDBC driver in question, is open source software.

Somehow the driver needs to determine, reliably and ideally inexpensively,
how to effect:

"This parameter is ignored if the SQL statement is not an INSERT statement,
or an SQL statement able to return auto-generated keys (the list of such
statements is vendor-specific)."

https://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#prepareStatement(java.lang.String,%20int)

Discussions and patches exploring how to go about that are welcomed.

I do think that issue 488 needs to separate out and fix the non-conformance
to the API that is present - namely not ignoring the "int" argument when
the supplied statement is not capable (i.e., not an INSERT statement) - and
posted such (and a bit more) on the issue itself.

​David J.
​