Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
[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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On 19 May 2015 at 19:18, Jan de Visser j...@de-visser.net wrote: 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 strikeShirley/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 Using anything other than ? in JDBC is a non-starter you might as well just stop supporting java entirely. Back to the issue at hand. Does anyone have a recommendation for a replacement operator besides ? 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 Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On Wed, May 20, 2015 at 8:29 AM, Bruno Harbulot br...@distributedmatter.net wrote: On Tue, May 19, 2015 at 10:50 PM, David G. Johnston david.g.johns...@gmail.com wrote: On Tue, May 19, 2015 at 2:34 PM, Bruno Harbulot br...@distributedmatter.net wrote: While I can imagine a Java PostgreSQL driver that would use the libpq syntax, I can't see it being able to have any useful sort of half-compatibility with JDBC, whether it mimics its interfaces or not. I'm not sure it would be very useful at all, considering how much the existing tooling the the Java world relies on JDBC. I won't claim to have studied this in great detail but there is a lot more to the JDBC spec beyond the semantics of PreparedStatement.parse(String). No need to throw out the baby with the bath water and reinvent ResultSet, Connection and various other interfaces that are perfectly usable before and after a suitable query has been fully parsed. When I say setInteger(1, new Integer(1000)) I don't care whether I had to write SELECT ? AS int_val OR SELECT $1 AS int_val; though the later has the nice property of providing corresponding numbers so that I would write something like SELECT $1 AS int_val, $1 AS int_val_2 and not be forced to write setInteger(2, new Integer(1000)) to pass in a value to the second - but identical - parameter. Maybe it violates the semantics defined by the API - which I could be making too lightly of - but having the same mechanics involved to solve the same problem - with only minor semantic nuances to remember seems within the realm of reasonable. Yes, you're probably right. Nevertheless, I'm not sure why anyone would switch to that format, knowing that other tools that are on top of JDBC would certainly not work very well (e.g. Groovy SQL, JOOQ, or ORMs like Hibernate, ...). Hadn't really pondered those :( though to be honest a compatibility layer to write out sequential $# instead of ? doesn't seem that difficult - but it would depend on the codebase of the tool. Unfortunately the driver wouldn't be in a position to do the work. David J
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On Tue, May 19, 2015 at 5:34 PM, Bruno Harbulot br...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On Tue, May 19, 2015 at 9:51 PM, Dave Cramer p...@fastcrypt.com wrote: Actually the issue is what to do about a number of connectors which use a fairly standard '?' as a placeholder. Notably absent from the discussion is ODBC upon which JDBC was modelled and probably predates any use of ? as an operator Ah, good point. I must admit I don't normally use ODBC, but I've given it a try, and it doesn't look good regarding the question mark. Maybe I simply don't know how it should be escaped, but my attempts (shown below) didn't work. This is run using PowerShell. Of all those tests, only the first one works (it's not using the question mark, just to make sure something worked). Interestingly, the question mark in the pseudo-column name (Does it work?) doesn't cause problems. (The errors are slightly different depending on the attempt.) Best wishes, Bruno. __ Output *** Test query: SELECT ('{key1:123,key2:Hello}'::jsonb - ?::text)::text AS Does it work? Does it work? - 123 *** Test query: SELECT ('{key1:123,key2:Hello}'::jsonb ? ?::text)::text AS Does it work? Exception calling Fill with 1 argument(s): ERROR [42601] ERROR: syntax error at or near $1; Error while preparing parameters *** Test query: SELECT ('{key1:123,key2:Hello}'::jsonb \? ?::text)::text AS Does it work? Exception calling Fill with 1 argument(s): ERROR [42601] ERROR: syntax error at or near \; Error while preparing parameters *** Test query: SELECT ('{key1:123,key2:Hello}'::jsonb ?? ?::text)::text AS Does it work? Exception calling Fill with 1 argument(s): ERROR [42601] ERROR: syntax error at or near $1; Error while preparing parameters *** Test query: SELECT ('{key1:123,key2:Hello}'::jsonb {?} ?::text)::text AS Does it work? Exception calling Fill with 1 argument(s): ERROR [07002] The # of binded parameters the # of parameter markers *** Test query: SELECT ('{key1:123,key2:Hello}'::jsonb {'?'} ?::text)::text Exception calling Fill with 1 argument(s): ERROR [HY000] ODBC escape convert error __ PowerShell script function test_query($query) { $conn = New-Object System.Data.Odbc.OdbcConnection try { $conn.ConnectionString = DSN=PostgreSQL35W $conn.Open() Write-Output *** Test query: $query; $cmd = New-Object System.Data.Odbc.OdbcCommand($query, $conn) $cmd.Parameters.Add(key, key1) | out-null $ds = New-Object System.Data.DataSet (New-Object system.Data.odbc.odbcDataAdapter($cmd)).fill($ds) | out-null $ds.Tables[0] | Format-Table } catch { Write-Output $_.Exception } finally { if ($conn.State -eq 'Open' ) { $conn.Close() } } Write-Output } test_query(@' SELECT ('{key1:123,key2:Hello}'::jsonb - ?::text)::text AS Does it work? '@) test_query(@' SELECT ('{key1:123,key2:Hello}'::jsonb ? ?::text)::text AS Does it work? '@) test_query(@' SELECT ('{key1:123,key2:Hello}'::jsonb \? ?::text)::text AS Does it work? '@) test_query(@' SELECT ('{key1:123,key2:Hello}'::jsonb ?? ?::text)::text AS Does it work? '@) test_query(@' SELECT ('{key1:123,key2:Hello}'::jsonb {?} ?::text)::text AS Does it work? '@) test_query(@' SELECT ('{key1:123,key2:Hello}'::jsonb {'?'} ?::text)::text '@)
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On 05/20/2015 03:34 PM, Tom Lane wrote: Dave Cramer p...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
Andrew Dunstan and...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
Andrew Dunstan and...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
Jeff Janes jeff.ja...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On Wed, May 20, 2015 at 7:04 PM, Jeff Janes jeff.ja...@gmail.com wrote: 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? I'm not sure whether that could be made to work, but wouldn't that defeat the point of using operators, i.e. something rather short, as opposed to functions? (That's also partly one of the arguments against too much escaping: over-complicating what's intended to be a relatively simple notation, as the Hibernate example I mentioned earlier showed: SELECT . \\?\\? .) Best wishes, Bruno.
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On Fri, May 15, 2015 at 1:23 PM, Dave Cramer p...@fastcrypt.com wrote: On 15 May 2015 at 16:21, Robert Haas robertmh...@gmail.com wrote: On Fri, May 15, 2015 at 4:13 PM, Dave Cramer p...@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 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? Cheers, Jeff
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On Wed, May 20, 2015 at 5:46 PM, Robert Haas robertmh...@gmail.com wrote: 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. I certainly don't disagree it's a good idea for connectors to have an escaping mechanism, but the problem here is that there's a blurred line regarding whose magic it is. It would make sense for connectors to allow for their magic to be escaped, but it turns out that the magic they do is a very close match to what seems to be in the SQL spec under the Dynamic SQL section. It could be argued that ? should be always escaped anyway, even in a direct SQL query, simply not to make it conflict with Dynamic SQL, but there doesn't seem to be such a mechanism in the SQL spec as far as I can see (and always having to escape the end result doesn't really make sense). More practically, getting connectors to add an escape mechanism can work for some connectors where the authors are more reactive and where the user base can also upgrade quickly (e.g. Perl's DBD::Pg), but the hopes of getting ODBC and JDBC and whatever depends on them to adapt are extremely low. (I'm also not sure if ECPG is used much compare to libpq, but in principle, not being able to use these operators there isn't great.) In contrast, providing a new set of operators (that wouldn't have this problem) should be doable with a rather smooth transition (since CREATE OPERATOR can be run on existing installations, if backporting the new operators is needed). (The existing operators wouldn't have to be removed in the short term, if ever.) Best wishes, Bruno.
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On Wed, May 20, 2015 at 11:13 AM, Tom Lane t...@sss.pgh.pa.us wrote: Jeff Janes jeff.ja...@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 ... Because it is a completely general solution using the existing escaping infrastructure, on both ends, except for this one small exception. It is ugly, but so is leaning toothpick syndrome where you have to escape your escapes from someone else's escapes. Anyway, I've never looked at code written to use JDBC and thought Boy, that sure is pretty. Cheers, Jeff
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
Dave Cramer p...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
Dave Cramer p...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On Tue, May 19, 2015 at 10:31 PM, Greg Sabino Mullane g...@turnstep.com wrote: 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. Actually, no, you haven't quite lost that battle, or rather, that battle doesn't even need to take place. You can still use common tools for operations that are not really RDMBS-specific AND use PostgreSQL extensions on a case-by-case basis depending on your application requirements. Some of these tools already allow you to tweak slightly their capabilities by implementing dialects, and let you use specific features if required. I think this is a major advantage of having these extensions in PostgreSQL: you can have the best of both worlds. It's not so much about being able to switch to another RDMBS, it's about not having to re-implement the bulk of the structure when you just want to benefit from a few additional extensions (for example, mixing the classic RDBMS model with the JSON store model). Best wishes, Bruno.
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
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 strikeShirley/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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
David G. Johnston david.g.johns...@gmail.com wrote: On Mon, May 18, 2015 at 3:31 PM, Bruno Harbulot br...@distributedmatter.netwrote: In the discussion on the OpenJDK JDBC list two years ago ( http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-February/50.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On 18 May 2015 at 18:49, David G. Johnston david.g.johns...@gmail.com wrote: On Mon, May 18, 2015 at 3:31 PM, Bruno Harbulot br...@distributedmatter.net wrote: On Sun, May 17, 2015 at 5:15 PM, Greg Sabino Mullane g...@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/50.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
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On 19 May 2015 at 10:23, Kevin Grittner kgri...@ymail.com wrote: David G. Johnston david.g.johns...@gmail.com wrote: On Mon, May 18, 2015 at 3:31 PM, Bruno Harbulot br...@distributedmatter.netwrote: In the discussion on the OpenJDK JDBC list two years ago ( http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-February/50.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. Perhaps reviewing https://github.com/pgjdbc/pgjdbc/pull/187 might help understand why we chose ?? Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On Tue, May 19, 2015 at 3:23 PM, Kevin Grittner kgri...@ymail.com wrote: David G. Johnston david.g.johns...@gmail.com wrote: On Mon, May 18, 2015 at 3:31 PM, Bruno Harbulot br...@distributedmatter.netwrote: In the discussion on the OpenJDK JDBC list two years ago ( http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-February/50.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? I was certainly not suggesting custom operators should be excluded. I was suggesting using something that was actually not incompatible with the SQL standards (and, even with standards aside, the expectations implementors have regarding the question mark, since it affects other tools too). 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! Actually, we were not just talking about JDBC. I don't know the specifications in details, but the SQL:201x (preliminary) documents linked from https://wiki.postgresql.org/wiki/Developer_FAQ#Where_can_I_get_a_copy_of_the_SQL_standards.3F seem to have some information. The Foundation document (Section 4.25 Dynamic SQL concepts) says that dynamic parameters are represented by a question mark. In addition, the BNF grammar available at http://www.savage.net.au/SQL/sql-2003-2.bnf.html#dynamic%20parameter%20specification also says: dynamic parameter specification::= question mark I'm not familiar enough with these documents to know whether I'm missing some context, but it would seem that the question mark is a reserved character, beyond the scope of JDBC (at the very least, it seems incompatible with Dynamic SQL and its implementation in ECPG). Best wishes, Bruno.
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
Mike Blackwell mike.blackw...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
A Google search suggests Oracle 9.x supports a unary '?' operator (fuzzy match), so the use of '?' in an operator name is not without precedent. __ *Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley* 1750 Wallace Ave | St Charles, IL 60174-3401 Office: 630.313.7818 mike.blackw...@rrd.com http://www.rrdonnelley.com http://www.rrdonnelley.com/ * mike.blackw...@rrd.com* On Tue, May 19, 2015 at 10:03 AM, Bruno Harbulot br...@distributedmatter.net wrote: On Tue, May 19, 2015 at 3:23 PM, Kevin Grittner kgri...@ymail.com wrote: David G. Johnston david.g.johns...@gmail.com wrote: On Mon, May 18, 2015 at 3:31 PM, Bruno Harbulot br...@distributedmatter.netwrote: In the discussion on the OpenJDK JDBC list two years ago ( http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-February/50.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? I was certainly not suggesting custom operators should be excluded. I was suggesting using something that was actually not incompatible with the SQL standards (and, even with standards aside, the expectations implementors have regarding the question mark, since it affects other tools too). 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! Actually, we were not just talking about JDBC. I don't know the specifications in details, but the SQL:201x (preliminary) documents linked from https://wiki.postgresql.org/wiki/Developer_FAQ#Where_can_I_get_a_copy_of_the_SQL_standards.3F seem to have some information. The Foundation document (Section 4.25 Dynamic SQL concepts) says that dynamic parameters are represented by a question mark. In addition, the BNF grammar available at http://www.savage.net.au/SQL/sql-2003-2.bnf.html#dynamic%20parameter%20specification also says: dynamic parameter specification::= question mark I'm not familiar enough with these documents to know whether I'm missing some context, but it would seem that the question mark is a reserved character, beyond the scope of JDBC (at the very least, it seems incompatible with Dynamic SQL and its implementation in ECPG). Best wishes, Bruno.
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On 19 May 2015 at 13:15, Mike Blackwell mike.blackw...@rrd.com wrote: A Google search suggests Oracle 9.x supports a unary '?' operator (fuzzy match), so the use of '?' in an operator name is not without precedent. Interesting argument. There is considerable precedent where we take the position that just because xyz supports it we don't. Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On Tue, May 19, 2015 at 1:36 PM, Kevin Grittner kgri...@ymail.com wrote: Gavin Flower gavinflo...@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. I'll repeat my earlier comment that having a mode that allows for libpq syntax while still conforming to the JDBC class API would have value for those users willing to admit their application and code is not portable (and if they are using these operators it is not) and would rather conform as closely to native PostgreSQL language mechanics as possible. That said I would not argue that the current official driver needs to be so modified. 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? Less than 6 months...discussion started a few months prior to that. 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? As things stand now - it seems that way. There are no guidelines that I can tell but I'd likely consider pgsql-jdbc the equivalent of -general and GitHub looks like -hackers. Neither is particularly high volume. David J.
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On Tue, May 19, 2015 at 2:34 PM, Bruno Harbulot br...@distributedmatter.net wrote: While I can imagine a Java PostgreSQL driver that would use the libpq syntax, I can't see it being able to have any useful sort of half-compatibility with JDBC, whether it mimics its interfaces or not. I'm not sure it would be very useful at all, considering how much the existing tooling the the Java world relies on JDBC. I won't claim to have studied this in great detail but there is a lot more to the JDBC spec beyond the semantics of PreparedStatement.parse(String). No need to throw out the baby with the bath water and reinvent ResultSet, Connection and various other interfaces that are perfectly usable before and after a suitable query has been fully parsed. When I say setInteger(1, new Integer(1000)) I don't care whether I had to write SELECT ? AS int_val OR SELECT $1 AS int_val; though the later has the nice property of providing corresponding numbers so that I would write something like SELECT $1 AS int_val, $1 AS int_val_2 and not be forced to write setInteger(2, new Integer(1000)) to pass in a value to the second - but identical - parameter. Maybe it violates the semantics defined by the API - which I could be making too lightly of - but having the same mechanics involved to solve the same problem - with only minor semantic nuances to remember seems within the realm of reasonable. David J.
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
Gavin Flower gavinflo...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
-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 g...@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- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On 19 May 2015 at 16:36, Kevin Grittner kgri...@ymail.com wrote: Gavin Flower gavinflo...@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. Actually the issue is what to do about a number of connectors which use a fairly standard '?' as a placeholder. Notably absent from the discussion is ODBC upon which JDBC was modelled and probably predates any use of ? as an operator 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. possibly, however all of the current JDBC maintainers opined and reached an agreement on this. Out of curiosity, how long has the ?? solution been implemented in a driver jar file available as a public download? At least since February of this year 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? Currently pull requests are the easiest to deal with so most discussion is on github. I guess updating the JDBC web page would be in order. Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On Tue, May 19, 2015 at 9:50 PM, David G. Johnston david.g.johns...@gmail.com wrote: On Tue, May 19, 2015 at 1:36 PM, Kevin Grittner kgri...@ymail.com wrote: Gavin Flower gavinflo...@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. I'll repeat my earlier comment that having a mode that allows for libpq syntax while still conforming to the JDBC class API would have value for those users willing to admit their application and code is not portable (and if they are using these operators it is not) and would rather conform as closely to native PostgreSQL language mechanics as possible. I don't think that approach is workable at all. JDBC isn't limited to a number of classes and their methods, the documentation that surrounds it obviously has an impact on how it was implemented internally and what users should and shouldn't be allowed to expect when using these classes. While there are tools that convert various parameter styles to ? (e.g. Groovy SQL or Hibernate's named parameter) and a layer of conversion from $1 to ? could exist, the bottleneck here will still be the JDBC layer itself, since it's what sends the query to the database. 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); Again, this may have to do with the fact that these tools may have a legitimate expectation that ? should be reserved for parameters, partly because it seems to be very common in practice, but more importantly if the SQL specification itself says it's what ? is for. While I can imagine a Java PostgreSQL driver that would use the libpq syntax, I can't see it being able to have any useful sort of half-compatibility with JDBC, whether it mimics its interfaces or not. I'm not sure it would be very useful at all, considering how much the existing tooling the the Java world relies on JDBC. This problem is also broader than JDBC: on top of the languages and libraries already mentioned, it may affect ODBC, as Dave Cramer has just said (I haven't tried). Best wishes, Bruno.
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
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. __ *Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley* 1750 Wallace Ave | St Charles, IL 60174-3401 Office: 630.313.7818 mike.blackw...@rrd.com http://www.rrdonnelley.com http://www.rrdonnelley.com/ * mike.blackw...@rrd.com* On Tue, May 19, 2015 at 12:45 PM, Bruno Harbulot br...@distributedmatter.net wrote: On Tue, May 19, 2015 at 6:15 PM, Mike Blackwell mike.blackw...@rrd.com wrote: A Google search suggests Oracle 9.x supports a unary '?' operator (fuzzy match), so the use of '?' in an operator name is not without precedent. Interesting. Do you have any specific link? I'm probably not using the right Google search, but the nearest reference I've found is for Oracle 10, and it seems to use the tilde (~) operator for fuzzy matching: http://www.oracle.com/technetwork/search/oses/overview/new-query-features-in-10-1-8-2-1-132287.pdf Best wishes, Bruno.
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On Tue, May 19, 2015 at 6:15 PM, Mike Blackwell mike.blackw...@rrd.com wrote: A Google search suggests Oracle 9.x supports a unary '?' operator (fuzzy match), so the use of '?' in an operator name is not without precedent. Interesting. Do you have any specific link? I'm probably not using the right Google search, but the nearest reference I've found is for Oracle 10, and it seems to use the tilde (~) operator for fuzzy matching: http://www.oracle.com/technetwork/search/oses/overview/new-query-features-in-10-1-8-2-1-132287.pdf Best wishes, Bruno.
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On Tue, May 19, 2015 at 7:51 PM, Greg Sabino Mullane g...@turnstep.com wrote: -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. I think deprecation doesn't necessarily imply removal. It seems that the two operators could exist together by creating a second operator with the same characteristics as suggested by Frank Heikens in this post: http://stackoverflow.com/questions/27573778/postgresql-jsonb-and-jdbc/27580137#27580137 This would also make it easier to backport these operators into existing installations (even on 9.4), thereby making the transition easier. I don't know enough about PostgreSQL's implementation, but I presume this is effectively just giving an alias for the same operation, and hopefully, the query engine could benefit from indices created using either notations interchangeably. (This is probably the most important feature when changing one notation for another.) In addition, the argument regarding the time it can take users to upgrade works both ways. If I understood correctly from your message yesterday, you've only implemented the latest workaround using \? in DBD::Pg quite recently, which would equally require users to be able to upgrade to a more recent version of DBD::Pg (or PHP/PDO where the workaround doesn't seem to be implemented at all yet). Admittedly, I guess it might often be easier to upgrade the client side than the database server, but I'm not sure that is always the case (some frontends can potentially be awkward to update, whereas a database upgrade can be smoother... It varies...). Best wishes, Bruno.
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On 19 May 2015 at 15:02, Tom Lane t...@sss.pgh.pa.us wrote: Greg Sabino Mullane g...@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. The current JDBC driver doesn't really support anything beyond 8.4 except for CRUD operations. We are also are no longer supporting JVM's older than 1.6 in the current driver. People who insist on staying on old code get what they get. I don't see a problem with saying after a certain date we just don't support it in the current code. After all I have heard rumblings about deprecating V2 protocol ? FWIW, I was content to leave this alone. JDBC has a workable solution. However I've not seen a good argument for continuing to use the ? operator as it's conflicts with many clients and is apparently not in the SQL standard. Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
-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 g...@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- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On Tue, May 19, 2015 at 8:04 PM, Greg Sabino Mullane g...@turnstep.com wrote: -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... As I was saying in another message on this thread a few hours ago, it appears that ? is reserved for placeholders for Dynamic SQL according to the SQL specifications, and that would be exactly what ECPG is using as far as I understand. 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. ... except if those communities made the assumption that ? was indeed reserved for placeholders according to the SQL specifications. (I might have misinterpreted where that part of the spec is applicable, since I can't claim I've absorbed the entire set of documents.) 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). This model of development also requires the users to be able to upgrade their connectors to a recent release, which may also affect other dependencies (depending on the complexity of the overall system). Best wishes, Bruno.
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
-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 g...@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
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On Sun, May 17, 2015 at 9:15 AM, Greg Sabino Mullane g...@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.
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
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
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
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 br...@distributedmatter.net wrote: 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): - http://www.postgresql.org/message-id/51114165.4070...@abshere.net - http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-February/48.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; }
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On Fri, May 15, 2015 at 4:13 PM, Dave Cramer p...@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
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
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
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On 15 May 2015 at 16:41, Robert Haas robertmh...@gmail.com wrote: On Fri, May 15, 2015 at 4:38 PM, Dave Cramer p...@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
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On 15 May 2015 at 16:21, Robert Haas robertmh...@gmail.com wrote: On Fri, May 15, 2015 at 4:13 PM, Dave Cramer p...@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
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On Fri, May 15, 2015 at 4:23 PM, Dave Cramer p...@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
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On 15 May 2015 at 16:35, Robert Haas robertmh...@gmail.com wrote: On Fri, May 15, 2015 at 4:23 PM, Dave Cramer p...@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. 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
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On Fri, May 15, 2015 at 4:38 PM, Dave Cramer p...@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
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On 15 May 2015 at 16:44, Dave Cramer p...@fastcrypt.com wrote: On 15 May 2015 at 16:41, Robert Haas robertmh...@gmail.com wrote: On Fri, May 15, 2015 at 4:38 PM, Dave Cramer p...@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 dave.cramer(at)credativ(dot)ca http://www.credativ.ca
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On Fri, May 15, 2015 at 9:41 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, May 15, 2015 at 4:38 PM, Dave Cramer p...@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.
[HACKERS] 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): - http://www.postgresql.org/message-id/51114165.4070...@abshere.net - http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-February/48.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; }
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On Fri, May 15, 2015 at 1:45 PM, Dave Cramer p...@fastcrypt.com wrote: On 15 May 2015 at 16:44, Dave Cramer p...@fastcrypt.com wrote: On 15 May 2015 at 16:41, Robert Haas robertmh...@gmail.com wrote: On Fri, May 15, 2015 at 4:38 PM, Dave Cramer p...@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.
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
Bruno Harbulot br...@distributedmatter.net wrote: On Fri, May 15, 2015 at 9:41 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, May 15, 2015 at 4:38 PM, Dave Cramer p...@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
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
Bruno Harbulot br...@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