openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Craig Ringer (Commented) (JIRA)" <>
Subject [jira] [Commented] (OPENJPA-2056) Postgres V9.1 issue with LIKE clause and Escape Strings
Date Tue, 04 Oct 2011 23:51:33 GMT


Craig Ringer commented on OPENJPA-2056:

Note that standard_conforming_strings was introduced in PostgreSQL 8.2. While this is the
oldest release still supported by the PostgreSQL team, and will be exiting support in December
this year, people seem to routinely run old and unsupported releases. Questions about PostgreSQL
7.4 are not uncommon on the mailing list. On prior versions, "SHOW standard_conforming_strings"
will report an error.

It's not particularly likely that people running such ancient versions of PostgreSQL will
upgrade OpenJPA, but in an org where DBA/sysadmin is very separate from dev, or where legacy
and actively maintained apps coexist in the same DBs, it's not impossible. It's worth handling.

I think the escape string syntax E'' was introduced in PostgreSQL 8.1, but haven't tested
to verify that. You should probably not rely on it if people using ancient versions is a realistic

The best answer will probably be to always escape strings. Just "SELECT version()" to decide
whether the explicit escape-string syntax should be used, or if, as older versions that lack
standard_conforming_strings, all strings should be assumed to be subject to escape processing.
That makes OpenJPA independent of the standard_conforming_strings parameter.

It's also important to be aware of the backslash_quote parameter, which is another compatibility
option that can cause pain. Escape single quotes by doubling them, not by using \', to avoid
issues with this. backslash_quote exists as far back as at least PostgreSQL 7.4, as does support
for SQL-standard '' style quote escapes. If OpenJPA currently escapes quotes using \' this
should be changed to avoid another potential bug.

I know all this is ugly; transitioning toward better standards conformance often is in cases
where no 100%-compatible approach can exist. Unfortunately, standard_conforming_strings will
be a wart for quite a long time and it is a setting people DO mess with, so it's important
to handle it.
> Postgres V9.1 issue with LIKE clause and Escape Strings
> -------------------------------------------------------
>                 Key: OPENJPA-2056
>                 URL:
>             Project: OpenJPA
>          Issue Type: Bug
>          Components: jdbc
>    Affects Versions: 2.0.0, 2.0.1, 2.1.1
>         Environment: Running against a Postgres database at version 9.1
>            Reporter: Andrew Hastie
> Noticed an issue with the Postgres DBDictionary definition after updating Postgres from
version 8.4 to 9.1:-
> Here's what you get in the Postgres trace file when executing some JPA driven queries
where an SQL LIKE is involved:-
> 2011-09-30 14:29:41 BST ERROR:  invalid escape string
> 2011-09-30 14:29:41 BST HINT:  Escape string must be empty or one character.
> 2011-09-30 14:29:41 BST STATEMENT:  SELECT, t0.identificationMask, t0.productName
FROM DBTYPE t0 WHERE (t0.identificationMask LIKE $1 ESCAPE '\\')
> 2011-09-30 14:29:41 BST ERROR:  current transaction is aborted, commands ignored until
end of transaction block
> This appears to be down to a change the Postgres project have made to escape string handling:-
> (see section E.2.2.1)
> You appear to be able to override the default DBDictionary setting for this as follows
to get things working again:-
> <property name="openjpa.jdbc.DBDictionary" value="postgres(SearchStringEscape=\)"/>
> So, does this mean OpenJPA needs a version dependent dictionary definition for Postgres
from now on? Anybody got any better solutions or care to confirm what I'm seeing?
> I've also posted this to the Postgres JDBC mailing list in case they have any comments.

This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:!default.jspa
For more information on JIRA, see:


View raw message