Обсуждение: Problems with question marks in operators (JDBC, ECPG, ...)

<div dir="ltr">Hello,<br /><br />I've been trying to use the new JSONB format using JDBC, and ran into trouble with the
questionmark operators (?, ?| and ?&).<br />I realise there has already been a discussion about this (actually, it
wasabout hstore, not jsonb, but that's more or less the same problem):<br />- <a
href="http://www.postgresql.org/message-id/51114165.4070106@abshere.net">http://www.postgresql.org/message-id/51114165.4070106@abshere.net</a><br
/>-<a
href="http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-February/000048.html">http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-February/000048.html</a><br
/><br/><br />From what I gather, the JDBC team seems to think that using ? in operators is not in line with the SQL
standards,but the outcome on the PostgreSQL list team suggested that a fix could be implemented in the PostgreSQL JDBC
driveranyway.<br /><br />I think this problem might actually affect a number of other places, unfortunately. I must
admitI don't know the SQL specifications very well (a quick look at a draft seemed to suggest the question mark was
indeeda reserved character, but this is probably out of context), and this isn't about finding out who is right or who
iswrong, but from a practical point of view, this also seemed to affect other kinds of clients, for example:<br />-
Perl:<a
href="http://blog.endpoint.com/2015/01/dbdpg-escaping-placeholders-with.html">http://blog.endpoint.com/2015/01/dbdpg-escaping-placeholders-with.html</a><br
/>-JavaScript: <a
href="https://github.com/tgriesser/knex/issues/519">https://github.com/tgriesser/knex/issues/519</a><br/>Of course,
therecan be workarounds in some cases, but even if they work, they can be quite awkward, especially if they differ from
onelanguage to another (in particular if you want to be able to re-use the same query from multiple languages).<br
/><br/>As far, as I can tell, question mark operators are also incompatible with PostgreSQL's ECPG when using dynamic
SQL.<a
href="http://www.postgresql.org/docs/current/static/ecpg-dynamic.html">http://www.postgresql.org/docs/current/static/ecpg-dynamic.html</a><br
/>(I'mpasting an example at the end of this message, tried with a PostgreSQL 9.4 server.)<br /><br />I realise it's a
bitlate to raise this concern, considering that these operators have been around for a few versions now (at least as
faras hstore), but wouldn't it be better to provide official alternative notations altogether, something that is less
likelyto conflict with most client implementations? Perhaps a function or a notation similar to what 'CAST(x AS y)' is
to'x::y' would be suitable if other symbols aren't better (although I think a short operator would still be
preferable).<br/><br /><br />Best wishes,<br /><br />Bruno.<br /><br /><br /><br /><br />____ ECPG test output:<br
/><br/>** Using query: SELECT ('{"key1":123,"key2":"Hello"}'::jsonb ->> ?::text)::text<br /><br />Result should
be123 for 'key1': 123<br />Result should be empty for 'key3': <br /><br /><br />** Using query: SELECT
('{"key1":123,"key2":"Hello"}'::jsonb? ?::text)::text<br /><br />SQL error: syntax error at or near "$1" on line 52<br
/>SQLerror: invalid statement name "mystmt3" on line 55<br />Result should be true for 'key1': <br />SQL error: invalid
statementname "mystmt3" on line 59<br />Result should be false for 'key3': <br />SQL error: invalid statement name
"mystmt3"on line 62<br /><br /><br /><br />____ ECPG test code:<br /><br /><br />#include <stdio.h><br />#include
<stdlib.h><br/><br />int main()<br />{<br />    EXEC SQL BEGIN DECLARE SECTION;<br />        char* target =
"unix:postgresql://localhost/mydatabase";<br/>        char result1[2048];<br />        int result1_ind;<br />       
char*key1_str = "key1";<br />        char *key3_str = "key3";<br />        char *stmt2 = "SELECT
('{\"key1\":123,\"key2\":\"Hello\"}'::jsonb->> ?::text)::text";<br />        char *stmt3 = "SELECT
('{\"key1\":123,\"key2\":\"Hello\"}'::jsonb? ?::text)::text";<br />    EXEC SQL END DECLARE SECTION;<br />    <br />   
EXECSQL WHENEVER SQLWARNING SQLPRINT;<br />    EXEC SQL WHENEVER SQLERROR SQLPRINT;<br />    EXEC SQL CONNECT TO
:targetAS testdb;<br />    <br /><br />    printf("\n\n** Using query: %s\n\n", stmt2);<br />    EXEC SQL PREPARE
mystmt2FROM :stmt2;<br />    <br />    result1[0] = 0;<br />    EXEC SQL EXECUTE mystmt2 INTO :result1 :result1_ind
USING:key1_str;<br />    printf("Result should be 123 for 'key1': %s\n", result1);<br />    <br />    result1[0] =
0;<br/>    EXEC SQL EXECUTE mystmt2 INTO :result1 :result1_ind USING :key3_str;<br />    printf("Result should be empty
for'key3': %s\n", result1);<br />    <br />    EXEC SQL DEALLOCATE PREPARE mystmt2;<br /><br /><br />    printf("\n\n**
Usingquery: %s\n\n", stmt3);<br />    EXEC SQL PREPARE mystmt3 FROM :stmt3;<br />    <br />    result1[0] = 0;<br />   
EXECSQL EXECUTE mystmt3 INTO :result1_ind USING :key1_str;<br />    printf("Result should be true for 'key1': %s\n",
result1);<br/>    <br />    result1[0] = 0;<br />    EXEC SQL EXECUTE mystmt3 INTO :result1_ind USING :key3_str;<br
/>   printf("Result should be false for 'key3': %s\n", result1);<br />    <br />    EXEC SQL DEALLOCATE PREPARE
mystmt3;<br /><br />    EXEC SQL DISCONNECT ALL;<br />    <br />    return 0;<br />}<br /></div> 

On Fri, May 15, 2015 at 4:13 PM, Dave Cramer <pg@fastcrypt.com> wrote:
> Not sure what the point of this is: as you indicated the ship has sailed so
> to speak

Well, if we were to agree this was a problem, we could introduce new,
less-problematic operator names and then eventually deprecate the old
ones.  Personally, it wouldn't take a lot to convince me that if a
certain set of operator names is problematic for important connectors,
we should avoid using those and switch to other ones.  I expect others
on this mailing list to insist that if the connectors don't work,
that's the connector drivers fault for coding their connectors wrong.
And maybe that's the right answer, but on the other hand, maybe it's a
little myopic.  I think the discussion is worth having.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, May 15, 2015 at 4:23 PM, Dave Cramer <pg@fastcrypt.com> wrote:
>> Well, if we were to agree this was a problem, we could introduce new,
>> less-problematic operator names and then eventually deprecate the old
>> ones.  Personally, it wouldn't take a lot to convince me that if a
>> certain set of operator names is problematic for important connectors,
>> we should avoid using those and switch to other ones.  I expect others
>> on this mailing list to insist that if the connectors don't work,
>> that's the connector drivers fault for coding their connectors wrong.
>> And maybe that's the right answer, but on the other hand, maybe it's a
>> little myopic.  I think the discussion is worth having.
>
> In that case my vote is new operators. This has been a sore point for the
> JDBC driver

I guess JDBC has the same problem as Perl and JavaScript here: ?
signals a bind variable.  The next question is, why isn't there some
escaping mechanism for that, like writing ?? or \? or something?

I ask because, you know, suppose you write this:

INSERT INTO foo VALUES ('How many pickled peppers did Peter Piper pick?');

Or alternatively this:

INSERT INTO foo VALUES ($$If Peter piper picked a peck of pickled
peppers, where's the peck of pickled peppers Peter Piper picked?$$);

Those have also got question marks in them.  Do they also get
interpreted as bind variables?

I don't really want to take a violently strong position on this
without understanding what's really going on here.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

> As far, as I can tell, question mark operators are also incompatible
> with PostgreSQL's ECPG when using dynamic SQL.
> http://www.postgresql.org/docs/current/static/ecpg-dynamic.html
> (I'm pasting an example at the end of this message, tried with a
> PostgreSQL 9.4 server.)

Indeed it is. The question mark is used in ecpg to denote a variable to
be filled-in by the process. I'm not completely sure if this was in the
standard or only implemented because several (not sure if all) other
precompiler used it as well.

Michael
-- 
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Meskes at (Debian|Postgresql) dot Org
Jabber: michael.meskes at gmail dot com
VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL

On Fri, May 15, 2015 at 4:38 PM, Dave Cramer <pg@fastcrypt.com> wrote:
>> I don't really want to take a violently strong position on this
>> without understanding what's really going on here.
>>
> Well our solution was to use ?? but that does mean we have to do some extra
> parsing which in a perfect world wouldn't be necessary.

So what about strings quoted with '' or $$ or $something$ - how would
you handle those?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Bruno Harbulot <bruno@distributedmatter.net> wrote:
> On Fri, May 15, 2015 at 9:41 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Fri, May 15, 2015 at 4:38 PM, Dave Cramer <pg@fastcrypt.com> wrote:

>>> Well our solution was to use ?? but that does mean we have to
>>> do some extra parsing which in a perfect world wouldn't be
>>> necessary.

It seems like maybe we missed a trick when we dealt with this; the
Java Specification (the language spec, not the API spec) seems to
say that curly braces should be used for this sort of thing.  So
The Java Way would seem to be to have used {?} or {question_mark}
or some such as our product-specific way of dealing with this.
That probably would reduce the JDBC parsing overhead, since it
must look for curly braces for the standard escapes, anyway (like
a date literal being {d '2015-05-15'}).

That would be kinda ugly, since if you wanted to use the ?||
operator you would need to write that in your prepared statement as
{?}||.  That seems only moderately more confusing than the current
need to write it as ??||, though.

But the opportunity to do that up-front was missed and, besides, we
have other connectors to worry about.

>> So what about strings quoted with '' or $$ or $something$ - how
>> would you handle those?
>
> I hadn't realised that the JDBC driver allowed the ? operator to
> be escaped as ??. It seems to work indeed (at least with version
> 9.4-1201 of the JDBC driver).
>
> $$....?$$ also works. I guess the JDBC drivers tries to parse
> literals first and escapes them accordingly.

Yeah; regardless of what escape is used, the JDBC driver still
needs to deal with finding literals and treating them differently.

> That said, I'd still suggest providing new operators and
> deprecating the ones containing a question mark if possible.
> (There are 8 distinct operator names like this: "?-", "?&", "?",
> "?#", "?||", "?-|", "?|" and "<?>".)

That would lower the burden on every connector to do something
about this.

> I think it would be nicer to have a single mechanism that can be
> used consistently across multiple languages (?? doesn't work for
> ECPG, for example), considering that ? as a placeholder seems
> quite common.

I don't know how practical it would be for all connectors to use
the same escape syntax.  They all need to have some way to do it if
they want to allow the operators containing a question mark to be
used, but if we're going to allow it in SQL operators it may be
more sane to allow each connector to figure out what is the best
escape.

I lean toward deprecating those operators in favor of ones without
the problem character, and some years down the line dropping the
old (deprecated) operators.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Bruno Harbulot <bruno@distributedmatter.net> writes:
> That said, I'd still suggest providing new operators and deprecating the
> ones containing a question mark if possible. (There are 8 distinct operator
> names like this: "?-", "?&", "?", "?#", "?||", "?-|", "?|" and "<?>".)

There are more in contrib ...
        regards, tom lane

On 05/15/2015 04:35 PM, Robert Haas wrote:
> I guess JDBC has the same problem as Perl and JavaScript here: ?
> signals a bind variable.  The next question is, why isn't there some
> escaping mechanism for that, like writing ?? or \? or something?


FTR, Perl's DBD::Pg lets you do this:
   $dbh->{pg_placeholder_dollaronly} = 1; # disable ? placeholders   $sth = $dbh->prepare(q{SELECT * FROM mytable WHERE
lseg1?# lseg2   AND name = $1});   $sth->execute('segname');
 

cheers

andrew

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


Andrew Dunstan wrote:
> FTR, Perl's DBD::Pg lets you do this:
> $dbh->{pg_placeholder_dollaronly} = 1; # disable ? placeholders

You can also simply escape placeholders in DBD::Pg with a backslash:

$dbh->prepare(q{SELECT * FROM mytable WHERE lseg1 \?# lseg2 AND name = ?});

Dave Cramer wrote:
> Well our solution was to use ?? but that does mean we have to do some
> extra parsing which in a perfect world wouldn't be necessary.

That's not a good solution as '??' is a perfectly valid operator. ISTR 
seeing it used somewhere in the wild, but I could be wrong.

> 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

- -- 
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201505171212
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAlVYvmQACgkQvJuQZxSWSsj8SwCdEL3f0JvSlVQERpn+KJIaILzj
GqAAni9qcZ8PLixSLmGoXEQr8tnVZ2RI
=YJfa
-----END PGP SIGNATURE-----

David G. Johnston <david.g.johnston@gmail.com> wrote:
> On Mon, May 18, 2015 at 3:31 PM, Bruno Harbulot <bruno@distributedmatter.net>wrote:

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

> ​"​CREATE OPERATOR is a PostgreSQL extension. There are no
> provisions for user-defined operators in the SQL standard."

Exactly.  The standard specifies the characters to use for the
predicates that it defines, and provides no mechanism for adding
additional predicates; but who in the world would want to exclude
all extensions to the standard?

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

We're talking about a different specification that has question
marks as parameter placeholders.  That's in the Java Database
Connector (JDBC) specification.  (It is apparently also specified
in other documents, although I'm not familiar enough with those to
comment.)  Note that it would create all sorts of pain if both the
SQL statements and a connector issuing them used the same
convention for substituting parameters; it is a *good* thing that
plpgsql and SQL function definitions use a different convention
than JDBC!

The JDBC spec provides for escapes using curly braces (including
product-specific escapes); it seems like a big mistake for us to
have chosen a completely different mechanism for escaping the
question mark character in a SQL statement.  Perhaps the least
painful path would be to add support for {?} as the escape for a
question mark, and a connection option to supplement that with
support for the legacy \? escape.  I would bet a lot of money that
even with an "if" test for that option, the curly brace escape
would be faster than what's there now (when the option was not
set).  Some operators would look a little funny in Java string
literals, but that's not so bad.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Mike Blackwell <mike.blackwell@rrd.com> writes:
> See for example
> http://docs.oracle.com/cd/B19306_01/text.102/b14218/cqoper.htm#i997330,
> Table 3-1, third row, showing the precedence of '?'.  Further down the
> page, under "Fuzzy" see "Backward Compatibility Syntax".

If I'm reading that right, that isn't a SQL-level operator but an operator
in their text search query language, which would only appear in SQL
queries within string literals (compare tsquery's query operators in PG).
So it wouldn't be a hazard for ?-substitution, as long as the substituter
was bright enough to not change string literals.
        regards, tom lane

<div dir="ltr"><br /><div class="gmail_extra"><br /><div class="gmail_quote">On Tue, May 19, 2015 at 7:22 PM, Tom Lane
<spandir="ltr"><<a href="mailto:tgl@sss.pgh.pa.us" target="_blank">tgl@sss.pgh.pa.us</a>></span> wrote:<br
/><blockquoteclass="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid
rgb(204,204,204);padding-left:1ex"><spanclass="">Mike Blackwell <<a
href="mailto:mike.blackwell@rrd.com">mike.blackwell@rrd.com</a>>writes:<br /> > See for example<br /> > <a
href="http://docs.oracle.com/cd/B19306_01/text.102/b14218/cqoper.htm#i997330"
target="_blank">http://docs.oracle.com/cd/B19306_01/text.102/b14218/cqoper.htm#i997330</a>,<br/> > Table 3-1, third
row,showing the precedence of '?'.  Further down the<br /> > page, under "Fuzzy" see "Backward Compatibility
Syntax".<br/><br /></span>If I'm reading that right, that isn't a SQL-level operator but an operator<br /> in their
textsearch query language, which would only appear in SQL<br /> queries within string literals (compare tsquery's query
operatorsin PG).<br /> So it wouldn't be a hazard for ?-substitution, as long as the substituter<br /> was bright
enoughto not change string literals.<br /><br />                         regards, tom lane<br /></blockquote></div><br
/></div><divclass="gmail_extra">That's how I read it too. I've tried this little test: <a
href="http://sqlfiddle.com/#!4/7436b/4/0">http://sqlfiddle.com/#!4/7436b/4/0</a><br/><br />CREATE TABLE test_table (<br
/> id INTEGER PRIMARY KEY,<br />  name VARCHAR(100)<br />);<br /><br />INSERT INTO test_table (id, name) VALUES (1,
'Nicole');<br/>INSERT INTO test_table (id, name) VALUES (2, 'Nicholas');<br />INSERT INTO test_table (id, name) VALUES
(3,'Robert');<br />INSERT INTO test_table (id, name) VALUES (4, 'Michael');<br />INSERT INTO test_table (id, name)
VALUES(5, 'Nicola');<br /><br />CREATE INDEX idx_test_table_name ON test_table(name) INDEXTYPE IS CTXSYS.CONTEXT;<br
/><br/>SELECT * FROM test_table WHERE CONTAINS(name, '?Nicolas', 1) > 0;<br /><br /><br /></div><div
class="gmail_extra">Fuzzymatching works indeed, but the question mark is part of the literal (similarly to % when using
LIKE).<br/><br /></div><div class="gmail_extra">Best wishes,<br /><br /></div><div class="gmail_extra">Bruno.<br
/></div><divclass="gmail_extra"><br /></div></div> 

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


Dave Cramer opined:
> 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

Ha ha ha ha ha! That's a good one. We still have clients on Postgres 7!
Five years is way too short to replace something that major.

> Agreed a patch would be the first place to start

That, or just running the idea up the flagpole on -general. I'm a fairly 
strong -1 at the moment, but will listen to arguments.

- -- 
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201505191448
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAlVbhfcACgkQvJuQZxSWSsguxwCgrMPQKa9tTfMcuv4jRnqM6mNg
OvAAmgLNpa2vsiv+bkd7p4bUEx7op9ax
=sr9j
-----END PGP SIGNATURE-----

"Greg Sabino Mullane" <greg@turnstep.com> writes:
> Dave Cramer opined:
>> 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

> Ha ha ha ha ha! That's a good one. We still have clients on Postgres 7!
> Five years is way too short to replace something that major.

Yeah, that's a big problem for this line of thought.  Even if we had
consensus today, the first release that would actually contain alternative
operators would be 9.6, more than a year out (since 9.5 is past feature
freeze now).  It would take several years after that before there would be
any prospect of removing the old ones, and several years more before PG
versions containing the old operators were out of support.

Now there are different ways you could look at this.  From the perspective
of a particular end user, you could imagine instituting a shop policy of
not using the operators containing '?' as soon as you had a release where
there were alternatives.  So in that context you might have a fix
available as soon as 9.6 came out.  But from the perspective of a driver
author who has to support queries written by other people, the problem
would not be gone for at least ten years more.  Changing the driver's
behavior sounds like a more practical solution.
        regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

Bruno Harbulot asked for a devil's advocate by saying:
> 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.

Well, one could argue that it *is* their problem, as they should be using 
the standard Postgres way for placeholders, which is $1, $2, $3...

> Recommending that all drivers implement \? as a semi-standard workaround is
> actually a much more difficult problem than it seems: it requires following
> the development of each project, making the case to each community
> (assuming they're all open source), and reasonable in-depth knowledge of
> their respective implementation, also assuming that \? won't cause further
> problems there (of course, all that is easier if you're already working on
> that particular project).

That's actually where we are right now. And it's not really our job to 
make the case to each community - it is the responsibility of each project 
to solve the problem, presumably because of pressure from their users.
The "\?" would only be a recommendation (but a pretty good one - the Perl 
folk talked a good bit about the best solution and researched if \? had 
the potential to cause any other problems).

> Even according to what you're saying this issue has required a first
> workaround back in 2008, and another one earlier this year, probably due to
> concerns that weren't spotted when implementing the first workaround (this
> also presumably requires users to run a fairly recent version of this
> connector now).

True enough regarding the two changes. But the system worked well, in that 
someone had a problem, raised a bug, and it got fixed. I'm not sure I see 
the point about requiring recent versions of the connector - that's true 
for lots of bug fixes and features. This one at least is fairly optional 
with many existing workarounds (e.g. use $1, quote things in a different way).

> (It looks like PHP/PDO is another one to add to the list:
> https://bugs.php.net/bug.php?id=62493, it's been open for almost 3 years.)

Some days I almost feel sorry for PHP.

- -- 
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 201505191503
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAlVbiRUACgkQvJuQZxSWSsjHYgCg1GfnDrdefkoedpigxYiuBMFX
794An3XWPXA0dVRk0JI6/Ik5Jb7SOLO7
=Amfe
-----END PGP SIGNATURE-----

On 05/19/2015 02:22 PM, Tom Lane wrote:
> Mike Blackwell <mike.blackwell@rrd.com> writes:
>> See for example
>> http://docs.oracle.com/cd/B19306_01/text.102/b14218/cqoper.htm#i997330,
>> Table 3-1, third row, showing the precedence of '?'.  Further down the
>> page, under "Fuzzy" see "Backward Compatibility Syntax".
> If I'm reading that right, that isn't a SQL-level operator but an operator
> in their text search query language, which would only appear in SQL
> queries within string literals (compare tsquery's query operators in PG).
> So it wouldn't be a hazard for ?-substitution, as long as the substituter
> was bright enough to not change string literals.
>
>             


Yeah. What would be nice would be to have a functional notation 
corresponding to the operators, so you would be able to write
   something."?>"(a,b)

and it would mean exactly the same thing, including indexability, as
   a ?> b

I presume that wouldn't give the drivers a headache.

cheers

andrew

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> I did find some alternatives discussed a couple of years back, like
> {postgres qm} and <operator("?")>; the later simply being to allow the
> operator to be quoted inside "operator()"

Yes, we (DBD::Pg) looked at using at some of the JDBC-ish alternatives 
like the (very verbose) vendor escape clauses, but settled on the simplicity of 
a single backslash in the end. See part of the discussion here:

http://www.nntp.perl.org/group/perl.dbi.users/2014/12/msg37057.html

- -- 
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201505191520
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAlVbjQQACgkQvJuQZxSWSsgYhACfUfztfxZBQEwESqRYkfRco29M
pAUAoO9qA5IWN96UXsh9iASspiEYfAfF
=k8Gl
-----END PGP SIGNATURE-----

On May 19, 2015 07:04:56 PM Greg Sabino Mullane wrote:
> Bruno Harbulot asked for a devil's advocate by saying:
> > 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.
> 
> Well, one could argue that it *is* their problem, as they should be using
> the standard Postgres way for placeholders, which is $1, $2, $3...

Shirley you are joking: Many products use JDBC as an abstraction layer 
facilitating (mostly) seamless switching between databases. I know the product 
I worked on did. Are you advocating that every single statement should use 
"SELECT * FROM foo WHERE bar = $1" on pg and "SELECT * FROM foo WHERE bar = ?" 
on every other database?

A database is only as valuable as the the part of the outside world it can 
interact with. Large parts of the data-consuming world are developed in java 
using JDBC. If your opinion is that JDBC developers should adapt themselves to 
pg then you instantaneously diminish the value of pg.

jan

On 20/05/15 07:37, Jan de Visser wrote:
> On May 19, 2015 07:04:56 PM Greg Sabino Mullane wrote:
>> Bruno Harbulot asked for a devil's advocate by saying:
>>> 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.
>> Well, one could argue that it *is* their problem, as they should be using
>> the standard Postgres way for placeholders, which is $1, $2, $3...
> Shirley you are joking: Many products use JDBC as an abstraction layer
> facilitating (mostly) seamless switching between databases. I know the product
> I worked on did. Are you advocating that every single statement should use
> "SELECT * FROM foo WHERE bar = $1" on pg and "SELECT * FROM foo WHERE bar = ?"
> on every other database?
>
> A database is only as valuable as the the part of the outside world it can
> interact with. Large parts of the data-consuming world are developed in java
> using JDBC. If your opinion is that JDBC developers should adapt themselves to
> pg then you instantaneously diminish the value of pg.
>
> jan
>
>
>
I prefer the $1 approach, others can't use that, and there are 
situations where I could not either.

So, how about defaulting to the '?' approach, but have a method to 
explicitly set the mode - to switch to using '$'?



Cheers,
Gavin

Gavin Flower <GavinFlower@archidevsys.co.nz> wrote:

> I prefer the $1 approach, others can't use that, and there are
> situations where I could not either.
>
> So, how about defaulting to the '?' approach, but have a method
> to explicitly set the mode - to switch to using '$'?

Are you suggesting that we implement something other than what is
described in these documents for prepared statement parameters?:

http://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html

http://download.oracle.com/otn-pub/jcp/jdbc-4_1-mrel-spec/jdbc4.1-fr-spec.pdf

If so, I strongly oppose that.  If we are not going to deprecate
use of the question mark character for operators, we need some
nonstandard hack to our JDBC implementation, but an alternative
syntax for specifying PreparedStatement and CallableStatement
parameters seems entirely the wrong way to go.

The issue here is what to do about the difficulties in using JDBC
prepared statements in combination with the PostgreSQL extension of
operator names containing question marks.  Using a double question
mark is not horrible as a solution.  It may not be what we would
have arrived at had the discussion taken place on the pgsql-jdbc
list rather than underneath a github pull request, but we can
only move forward from where we are.

Out of curiosity, how long has the ?? solution been implemented in
a driver jar file available as a public download?  What are the
guidelines for what discussion belongs on the pgsql-jdbc list and
what discussion belongs on github?  Is someone interested in
participating in the discussions leading to decisions about our
JDBC connector expected to follow both?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


Jan de Visser wrote:
>> Well, one could argue that it *is* their problem, as they should be using
>> the standard Postgres way for placeholders, which is $1, $2, $3...

> Shirley you are joking: Many products use JDBC as an abstraction layer 
> facilitating (mostly) seamless switching between databases. I know the product 
> I worked on did. Are you advocating that every single statement should use 
> "SELECT * FROM foo WHERE bar = $1" on pg and "SELECT * FROM foo WHERE bar = ?" 
> on every other database?

I'm not joking, and don't call me Shirley. If you are running into situations 
where you have question mark operators in your queries, you have already lost 
the query abstraction battle. There will be no seamless switching if you 
are using jsonb, hstore, ltree, etc. My statement was more about pointing out 
that Postgres already offers a complete placeholder system, which drivers 
are free to implement if they want.

> A database is only as valuable as the the part of the outside world it can 
> interact with. Large parts of the data-consuming world are developed in java 
> using JDBC. If your opinion is that JDBC developers should adapt themselves to 
> pg then you instantaneously diminish the value of pg.

Well, they will have to adapt to one way or another: using ?? or \? is doing 
so, and the other solution (Postgres adapting itself to the driver by 
deprecating the ? operator) is not realistically likely to happen.

- -- 
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201505191718
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAlVbq4AACgkQvJuQZxSWSsgrXgCaA6MTvbDeg2aMf+/HFnxutrqH
P1sAoLZB1w5+UXHMxXqW/Ex0q7GwoFds
=IOpS
-----END PGP SIGNATURE-----

On May 19, 2015 09:31:32 PM Greg Sabino Mullane wrote:
> Jan de Visser wrote:
> >> Well, one could argue that it *is* their problem, as they should be using
> >> the standard Postgres way for placeholders, which is $1, $2, $3...
> > 
> > Shirley you are joking: Many products use JDBC as an abstraction layer
> > facilitating (mostly) seamless switching between databases. I know the
> > product I worked on did. Are you advocating that every single statement
> > should use "SELECT * FROM foo WHERE bar = $1" on pg and "SELECT * FROM
> > foo WHERE bar = ?" on every other database?
> 
> I'm not joking, and don't call me Shirley. If you are running into
> situations where you have question mark operators in your queries, you have
> already lost the query abstraction battle. There will be no seamless
> switching if you are using jsonb, hstore, ltree, etc. My statement was more
> about pointing out that Postgres already offers a complete placeholder
> system, which drivers are free to implement if they want.

I must have misunderstood you <strike>Shirley</strike> Greg, because to me it 
parsed as if you were suggesting (paraphrasing) "ah forget about those pesky 
standardized drivers and their pesky syntax requirements. Just use ours like a 
big boy".

I understand that once you start using '?' as (part of) operator names in your 
queries you're not portable anymore. I just thought that your proposed 
solution was to throw all portability out the window. But I was probably 
(hopefully?) wrong.

jan

<div dir="ltr"><div class="gmail_extra"><br /><div class="gmail_quote">On Tue, May 19, 2015 at 10:31 PM, Greg Sabino
Mullane<span dir="ltr"><<a href="mailto:greg@turnstep.com" target="_blank">greg@turnstep.com</a>></span>
wrote:<br/><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid
rgb(204,204,204);padding-left:1ex"><spanclass=""><br /></span>If you are running into situations<br /> where you have
questionmark operators in your queries, you have already lost<br /> the query abstraction battle. There will be no
seamlessswitching if you<br /> are using jsonb, hstore, ltree, etc.<br /></blockquote></div><br /></div><div
class="gmail_extra">Actually,no, you haven't quite lost that battle, or rather, that battle doesn't even need to take
place.<br/><br /></div><div class="gmail_extra">You can still use common tools for operations that are not really
RDMBS-specificAND use PostgreSQL extensions on a case-by-case basis depending on your application requirements.<br
/></div><divclass="gmail_extra">Some of these tools already allow you to tweak slightly their capabilities by
implementingdialects, and let you use specific features if required.<br /><br />I think this is a major advantage of
havingthese extensions in PostgreSQL: you can have the best of both worlds. It's not so much about being able to switch
toanother RDMBS, it's about not having to re-implement the bulk of the structure when you just want to benefit from a
fewadditional extensions (for example, mixing the classic RDBMS model with the JSON store model).<br /></div><div
class="gmail_extra"><br/></div><div class="gmail_extra">Best wishes,<br /><br /></div><div
class="gmail_extra">Bruno.<br/></div><div class="gmail_extra"><br /><br /></div></div> 

On Tue, May 19, 2015 at 5:34 PM, Bruno Harbulot
<bruno@distributedmatter.net> wrote:
> Users of question mark operators are already admitting their application and
> code isn't portable (since they are specific to PostgreSQL and its
> extensions). The problem has more to do with how the other tools around
> handle these customisations. For example, it can be useful to have a model
> based on Hibernate in Java and be able to use ? operators for specific
> features. (Other tools like SQLAlchemy in Python also allow you to have
> customisations specific to the RDMBS platform, while being able to use the
> core features in a more platform-neutral way.)
>
> It turns out that you can indeed use ? in JSONB with a custom Hibernate
> query, you just need to double-escape it as follows: ? becomes ?? and has to
> be escaped as \?\?, but \ has to be escaped itself...
>
>         SQLQuery query = session
>                 .createSQLQuery("SELECT
> CAST((CAST('{\"key1\":123,\"key2\":\"Hello\"}' AS jsonb) \\?\\? CAST(? AS
> text)) AS BOOLEAN)");
>         query.setString(0, "key1");

I think we should be more focused on this part of the issue.  It seems
to me that it's a good idea for connectors to have an escaping
mechanism.  Pretty much any syntax that supports funny characters that
do magical things should also have a way to turn the magic off when
it's not wanted.  But it's not a bad thing either for the core project
to try to steer around operator names that are likely to require
frequent use of that escaping mechanism.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Jeff Janes <jeff.janes@gmail.com> writes:
> What if something like this was made to work?
>  select '{"3":5}'::jsonb operator("pg_catalog"."?") '3';
> (Where the double quotes around the ? would be tolerated, which they
> currently are not)

> Is there a reason it can't be made to work?

It could be made to work, I'm sure, but I fail to see why any user
would prefer to write that over ?? or \? or {?} or pretty much any
of the other notations that've been suggested.  It's ten times as
many keystrokes ...
        regards, tom lane

Dave Cramer <pg@fastcrypt.com> writes:
> Back to the issue at hand. Does anyone have a recommendation for a
> replacement operator besides ?

The bikeshedding potential here might be the worst part of the whole
thing.  Still, if we can agree on reasonable substitute names, I wouldn't
be against it, even with the huge lead time in mind.

> When I first noticed this one thought was to create duplicate operators
> specifically for the use of the JDBC driver.

> I had dismissed this at the time, now I'm not so sure

If you mean fixing the problem with an extension that adds replacement
operators without any core code changes, I'm afraid probably not.
It would work okay for operators that are not indexable, but not for
those that can be indexed.  (I think only a couple of the existing problem
operators are indexable, but that's enough to make the idea not fly.)

The difficulty with indexable operators is that there is no provision for
multiple operators sharing the same strategy "slot" in an opclass.  So
the only way to add additional operators to an opclass is to give them
new strategy numbers, which requires teaching the opclass' support
functions to know about those numbers.  This would be just a minor change
(add some case labels) but it *is* a change in the core code.
        regards, tom lane

Dave Cramer <pg@fastcrypt.com> writes:
> Notably absent from the discussion is ODBC upon which JDBC was modelled and
> probably predates any use of ? as an operator

<historical-nitpicking>

It would be a mistake to imagine that operators containing '?' are some
johnny-come-lately.  The <?> operator for tintervals can be traced back
at least to Postgres v4r2 (1994), which is the oldest tarball I have at
hand.  Most of the current list are geometric operators that were added
by Tom Lockhart in 1997.  The only ones that aren't old enough to vote
are the JSONB ones we added last year.

Not that the problem's not real, but these operators predate any attempt
to make Postgres work with ODBC or JDBC or any other connector.  Otherwise
we might've thought better of using '?'.

</historical-nitpicking>
        regards, tom lane

On 05/20/2015 03:34 PM, Tom Lane wrote:
> Dave Cramer <pg@fastcrypt.com> writes:
>> Notably absent from the discussion is ODBC upon which JDBC was modelled and
>> probably predates any use of ? as an operator
> <historical-nitpicking>
>
> It would be a mistake to imagine that operators containing '?' are some
> johnny-come-lately.  The <?> operator for tintervals can be traced back
> at least to Postgres v4r2 (1994), which is the oldest tarball I have at
> hand.  Most of the current list are geometric operators that were added
> by Tom Lockhart in 1997.  The only ones that aren't old enough to vote
> are the JSONB ones we added last year.
>
> Not that the problem's not real, but these operators predate any attempt
> to make Postgres work with ODBC or JDBC or any other connector.  Otherwise
> we might've thought better of using '?'.
>
> </historical-nitpicking>

Yeah, I knew they were pretty old.

When did the SQL standard add any mention of ?

cheers

andrew

Andrew Dunstan <andrew@dunslane.net> writes:
> When did the SQL standard add any mention of ?

It's in SQL92.  I don't have a copy of SQL89, or whatever the previous
spec was, to look at.

(So you could argue that Yu and Chen should've removed ? from the set of
allowed operator characters when they grafted SQL syntax onto Postgres.
But they didn't ...)
        regards, tom lane

Andrew Dunstan <andrew@dunslane.net> wrote:
> On 05/20/2015 03:34 PM, Tom Lane wrote:

>> The <?> operator for tintervals can be traced back at least to
>> Postgres v4r2 (1994), which is the oldest tarball I have at
>> hand.  Most of the current list are geometric operators that
>> were added by Tom Lockhart in 1997.

> When did the SQL standard add any mention of ?

FWIW, the first public, production release of Java in 1995 used it
for parameters.  ODBC 1.0 was released in 1992.  I would guess that
the question mark for parameters was there from the beginning, but
can't swear to it before 1995.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

> available as soon as 9.6 came out.  But from the perspective of a driver
> author who has to support queries written by other people, the problem
> would not be gone for at least ten years more.  Changing the driver's
> behavior sounds like a more practical solution.

Even if it means breaking the standard?

Michael
-- 
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Meskes at (Debian|Postgresql) dot Org
Jabber: michael.meskes at gmail dot com
VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL

[Sorry for being late to the party, travelling does take away too much
time sometimes.]

On 19.05.2015 21:04, Greg Sabino Mullane wrote:
> Bruno Harbulot asked for a devil's advocate by saying:
>> 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.
> 
> Well, one could argue that it *is* their problem, as they should be using
> the standard Postgres way for placeholders, which is $1, $2, $3...

As Bruno already pointed out one could also argue that they just try to
accept what the standard asked them for.

I fail to see how such a way of arguing brings us closer to a solution,
though.

Michael
-- 
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Meskes at (Debian|Postgresql) dot Org
Jabber: michael.meskes at gmail dot com
VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL