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

On Wed, May 20, 2015 at 5:46 PM, Robert Haas <robertmhaas(at)gmail(dot)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.