Re: Problems with question marks in operators (JDBC, ECPG, ...)

On 18 May 2015 at 18:49, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Mon, May 18, 2015 at 3:31 PM, Bruno Harbulot <
> bruno(at)distributedmatter(dot)net> wrote:
>
>> On Sun, May 17, 2015 at 5:15 PM, Greg Sabino Mullane <greg(at)turnstep(dot)com>
>> wrote:
>>
>>
>>>
>>> > In that case my vote is new operators. This has been a sore point for
>>> the
>>> > JDBC driver
>>>
>>> Um, no, new operators is a bad idea. Question marks are used by hstore,
>>> json, geometry, and who knows what else. I think the onus is solely on
>>> JDBC to solve this problem. DBD::Pg solved it in 2008 with
>>> the pg_placeholder_dollaronly solution, and earlier this year by allowing
>>> backslashes before the question mark (because other parts of the stack
>>> were
>>> not able to smoothly implement pg_placeholder_dollaronly.) I recommend
>>> all drivers implement \? as a semi-standard workaround.
>>>
>>> See also:
>>> http://blog.endpoint.com/2015/01/dbdpg-escaping-placeholders-with.html
>>>
>>>
>> I'm not sure the onus is solely on JDBC. Using question marks in
>> operators clearly has required a number of connectors to implement their
>> own workarounds, in different ways. This also seems to affect some
>> libraries and frameworks that depend on those connectors (and for which the
>> workarounds may even be more convoluted).
>>
>> My main point was that this is not specific to JDBC. Considering that
>> even PostgreSQL's own ECPG is affected, the issue goes probably deeper than
>> it seems. I'm just not convinced that passing the problem onto connectors,
>> libraries and ultimately application developers is the right thing to do
>> here.
>>
>> In the discussion on the OpenJDK JDBC list two years ago (
>> http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-February/000050.html
>> ), Lance Andersen said "There is nothing in the SQL standard that would
>> support the use of an '?' as anything but a parameter marker.". It might be
>> worth finding out whether this is indeed the case according to the SQL
>> specifications (I'm afraid I'm not familiar with these specifications to do
>> it myself).
>>
>
> ​"​CREATE OPERATOR is a PostgreSQL extension. There are no provisions for
> user-defined operators in the SQL standard."
>
> http://www.postgresql.org/docs/9.4/interactive/sql-createoperator.html
>
> ​And by extension if indeed the standard does require the use of "?" for
> parameters we are in violation there because the backend protocol deals
> with $# placeholders and not "?"​
>
> ​I too do not know enough here.
>
> Note that it would not be enough to change the existing operators - any
> use of "?" would have to be forbidden including those created by users.​
>
> The first step on this path would be for someone to propose a patch adding
> alternative operators for every existing operator that uses "?". If this
> idea is to move forward at all that patch would have to be accepted. Such
> a patch is likely to see considerable bike-shedding. We then at least
> provide an official way to avoid "?" operators that shops can make use of
> at their discretion. Removing the existing operators or forbidding custom
> operators is a separate discussion.
>
> David J.​
>
>
It would seem that choosing ? for operators was ill advised; I'm not
convinced that deprecating them is a bad idea. If we start now, in 5 years
they should be all but gone

Agreed a patch would be the first place to start

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca