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

On Mon, May 18, 2015 at 3:31 PM, Bruno Harbulot <bruno@distributedmatter.net> wrote:

On Sun, May 17, 2015 at 5:15 PM, Greg Sabino Mullane <greg@turnstep.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.​