Problems with question marks in operators (JDBC, ECPG, ...)
Hello,
I've been trying to use the new JSONB format using JDBC, and ran into
trouble with the question mark operators (?, ?| and ?&).
I realise there has already been a discussion about this (actually, it was
about hstore, not jsonb, but that's more or less the same problem):
- /messages/by-id/51114165.4070106@abshere.net
-
http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-February/000048.html
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
driver anyway.
I think this problem might actually affect a number of other places,
unfortunately. I must admit I don't know the SQL specifications very well
(a quick look at a draft seemed to suggest the question mark was indeed a
reserved character, but this is probably out of context), and this isn't
about finding out who is right or who is wrong, but from a practical point
of view, this also seemed to affect other kinds of clients, for example:
- Perl:
http://blog.endpoint.com/2015/01/dbdpg-escaping-placeholders-with.html
- JavaScript: https://github.com/tgriesser/knex/issues/519
Of course, there can be workarounds in some cases, but even if they work,
they can be quite awkward, especially if they differ from one language to
another (in particular if you want to be able to re-use the same query from
multiple languages).
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.)
I realise it's a bit late to raise this concern, considering that these
operators have been around for a few versions now (at least as far as
hstore), but wouldn't it be better to provide official alternative
notations altogether, something that is less likely to 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).
Best wishes,
Bruno.
____ ECPG test output:
** Using query: SELECT ('{"key1":123,"key2":"Hello"}'::jsonb ->>
?::text)::text
Result should be 123 for 'key1': 123
Result should be empty for 'key3':
** Using query: SELECT ('{"key1":123,"key2":"Hello"}'::jsonb ?
?::text)::text
SQL error: syntax error at or near "$1" on line 52
SQL error: invalid statement name "mystmt3" on line 55
Result should be true for 'key1':
SQL error: invalid statement name "mystmt3" on line 59
Result should be false for 'key3':
SQL error: invalid statement name "mystmt3" on line 62
____ ECPG test code:
#include <stdio.h>
#include <stdlib.h>
int main()
{
EXEC SQL BEGIN DECLARE SECTION;
char* target = "unix:postgresql://localhost/mydatabase";
char result1[2048];
int result1_ind;
char *key1_str = "key1";
char *key3_str = "key3";
char *stmt2 = "SELECT ('{\"key1\":123,\"key2\":\"Hello\"}'::jsonb
->> ?::text)::text";
char *stmt3 = "SELECT ('{\"key1\":123,\"key2\":\"Hello\"}'::jsonb ?
?::text)::text";
EXEC SQL END DECLARE SECTION;
EXEC SQL WHENEVER SQLWARNING SQLPRINT;
EXEC SQL WHENEVER SQLERROR SQLPRINT;
EXEC SQL CONNECT TO :target AS testdb;
printf("\n\n** Using query: %s\n\n", stmt2);
EXEC SQL PREPARE mystmt2 FROM :stmt2;
result1[0] = 0;
EXEC SQL EXECUTE mystmt2 INTO :result1 :result1_ind USING :key1_str;
printf("Result should be 123 for 'key1': %s\n", result1);
result1[0] = 0;
EXEC SQL EXECUTE mystmt2 INTO :result1 :result1_ind USING :key3_str;
printf("Result should be empty for 'key3': %s\n", result1);
EXEC SQL DEALLOCATE PREPARE mystmt2;
printf("\n\n** Using query: %s\n\n", stmt3);
EXEC SQL PREPARE mystmt3 FROM :stmt3;
result1[0] = 0;
EXEC SQL EXECUTE mystmt3 INTO :result1_ind USING :key1_str;
printf("Result should be true for 'key1': %s\n", result1);
result1[0] = 0;
EXEC SQL EXECUTE mystmt3 INTO :result1_ind USING :key3_str;
printf("Result should be false for 'key3': %s\n", result1);
EXEC SQL DEALLOCATE PREPARE mystmt3;
EXEC SQL DISCONNECT ALL;
return 0;
}
Not sure what the point of this is: as you indicated the ship has sailed so
to speak
Dave Cramer
dave.cramer(at)credativ(dot)ca
http://www.credativ.ca
On 15 May 2015 at 15:14, Bruno Harbulot <bruno@distributedmatter.net> wrote:
Show quoted text
Hello,
I've been trying to use the new JSONB format using JDBC, and ran into
trouble with the question mark operators (?, ?| and ?&).
I realise there has already been a discussion about this (actually, it was
about hstore, not jsonb, but that's more or less the same problem):
- /messages/by-id/51114165.4070106@abshere.net
-
http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-February/000048.htmlFrom 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
driver anyway.I think this problem might actually affect a number of other places,
unfortunately. I must admit I don't know the SQL specifications very well
(a quick look at a draft seemed to suggest the question mark was indeed a
reserved character, but this is probably out of context), and this isn't
about finding out who is right or who is wrong, but from a practical point
of view, this also seemed to affect other kinds of clients, for example:
- Perl:
http://blog.endpoint.com/2015/01/dbdpg-escaping-placeholders-with.html
- JavaScript: https://github.com/tgriesser/knex/issues/519
Of course, there can be workarounds in some cases, but even if they work,
they can be quite awkward, especially if they differ from one language to
another (in particular if you want to be able to re-use the same query from
multiple languages).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.)I realise it's a bit late to raise this concern, considering that these
operators have been around for a few versions now (at least as far as
hstore), but wouldn't it be better to provide official alternative
notations altogether, something that is less likely to 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).Best wishes,
Bruno.
____ ECPG test output:
** Using query: SELECT ('{"key1":123,"key2":"Hello"}'::jsonb ->>
?::text)::textResult should be 123 for 'key1': 123
Result should be empty for 'key3':** Using query: SELECT ('{"key1":123,"key2":"Hello"}'::jsonb ?
?::text)::textSQL error: syntax error at or near "$1" on line 52
SQL error: invalid statement name "mystmt3" on line 55
Result should be true for 'key1':
SQL error: invalid statement name "mystmt3" on line 59
Result should be false for 'key3':
SQL error: invalid statement name "mystmt3" on line 62____ ECPG test code:
#include <stdio.h>
#include <stdlib.h>int main()
{
EXEC SQL BEGIN DECLARE SECTION;
char* target = "unix:postgresql://localhost/mydatabase";
char result1[2048];
int result1_ind;
char *key1_str = "key1";
char *key3_str = "key3";
char *stmt2 = "SELECT ('{\"key1\":123,\"key2\":\"Hello\"}'::jsonb
->> ?::text)::text";
char *stmt3 = "SELECT ('{\"key1\":123,\"key2\":\"Hello\"}'::jsonb
? ?::text)::text";
EXEC SQL END DECLARE SECTION;EXEC SQL WHENEVER SQLWARNING SQLPRINT;
EXEC SQL WHENEVER SQLERROR SQLPRINT;
EXEC SQL CONNECT TO :target AS testdb;printf("\n\n** Using query: %s\n\n", stmt2);
EXEC SQL PREPARE mystmt2 FROM :stmt2;result1[0] = 0;
EXEC SQL EXECUTE mystmt2 INTO :result1 :result1_ind USING :key1_str;
printf("Result should be 123 for 'key1': %s\n", result1);result1[0] = 0;
EXEC SQL EXECUTE mystmt2 INTO :result1 :result1_ind USING :key3_str;
printf("Result should be empty for 'key3': %s\n", result1);EXEC SQL DEALLOCATE PREPARE mystmt2;
printf("\n\n** Using query: %s\n\n", stmt3);
EXEC SQL PREPARE mystmt3 FROM :stmt3;result1[0] = 0;
EXEC SQL EXECUTE mystmt3 INTO :result1_ind USING :key1_str;
printf("Result should be true for 'key1': %s\n", result1);result1[0] = 0;
EXEC SQL EXECUTE mystmt3 INTO :result1_ind USING :key3_str;
printf("Result should be false for 'key3': %s\n", result1);EXEC SQL DEALLOCATE PREPARE mystmt3;
EXEC SQL DISCONNECT ALL;
return 0;
}
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 15 May 2015 at 16:21, Robert Haas <robertmhaas@gmail.com> wrote:
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.
In that case my vote is new operators. This has been a sore point for the
JDBC driver
Dave Cramer
dave.cramer(at)credativ(dot)ca
http://www.credativ.ca
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 15 May 2015 at 16:35, Robert Haas <robertmhaas@gmail.com> wrote:
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 driverI 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.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.
Dave Cramer
dave.cramer(at)credativ(dot)ca
http://www.credativ.ca
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 15 May 2015 at 16:41, Robert Haas <robertmhaas@gmail.com> wrote:
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?We parse for strings; the ?? just adds to the parsing load which we really
try to avoid.
Dave Cramer
dave.cramer(at)credativ(dot)ca
http://www.credativ.ca
On 15 May 2015 at 16:44, Dave Cramer <pg@fastcrypt.com> wrote:
On 15 May 2015 at 16:41, Robert Haas <robertmhaas@gmail.com> wrote:
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?We parse for strings; the ?? just adds to the parsing load which we
really try to avoid.
The ?? is just harder to deal with because ? is part of the JDBC spec as a
placeholder
Dave Cramer
Show quoted text
dave.cramer(at)credativ(dot)ca
http://www.credativ.ca
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:
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?
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.
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 "<?>".)
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.
Best wishes,
Bruno.
On Fri, May 15, 2015 at 1:45 PM, Dave Cramer <pg@fastcrypt.com> wrote:
On 15 May 2015 at 16:44, Dave Cramer <pg@fastcrypt.com> wrote:
On 15 May 2015 at 16:41, Robert Haas <robertmhaas@gmail.com> wrote:
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?We parse for strings; the ?? just adds to the parsing load which we
really try to avoid.
The ?? is just harder to deal with because ? is part of the JDBC spec as
a placeholder
Whenever I ponder this I always come back to the idea of having a driver
(or driver mode) that integrates with the Java API that JDBC specifies but
whose parsing implementation adheres to libpq. This would, intentionally,
be a driver that could not be used with "portable" source code but would
allow people who are OK with binding tightly with PostgreSQL to talk in its
native language.
As for alternative operators maybe pgJDBC should put one or more extensions
out on PGXN that would be considered an official compatibility mode that
developers can write against and setup as dependency. Avoids each
application developing its own mapping rules and the resultant problems
that could result in doing so. At worse it at least makes the issue more
visible if done fully.
I'm not particularly in favor of deprecating the existing operators though
I haven't given it that much thought either. Since using them results in
syntax errors the harm in allowing them seems fairly minimal. The use of
"?" as an operator is normally done for solid reasons and clarity is not
something to be discarded for everyone when only a subset are affected.
David J.
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
-----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-----
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Sun, May 17, 2015 at 9:15 AM, Greg Sabino Mullane <greg@turnstep.com>
wrote:
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.
It which case you would write (I think, not tested and not part of the
test suite that I can see...):
a ???? b ...
There was some discussion about ?? vs \?:
https://github.com/pgjdbc/pgjdbc/pull/187
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()"
http://postgresql.nabble.com/Alias-hstore-s-to-so-that-it-works-with-JDBC-td5743863i20.html
The commit that added "??":
https://github.com/pgjdbc/pgjdbc/pull/227
David J.
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 driverUm, 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).
If this was indeed the case, it would certainly make sense to deprecate
these operators, so as not to be incompatible with the specifications.
Independently of the specifications, it seems that a number of libraries
and framework developers (again, not just JDBC) have this expectation
anyway.
Considering the number of problems question marks in operators cause
downstream, I think their use should be discouraged. Of course, the problem
remains for existing operators. I would suggest providing new, alternative
operators that don't have such issues, and eventually deprecate the
operators that cause those problems.
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).
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). (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.)
Best wishes,
Bruno.
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.htmlI'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.
On 18 May 2015 at 18:49, David G. Johnston <david.g.johnston@gmail.com>
wrote:
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.htmlI'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
