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

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