[jira] [Created] (OPENJPA-2056) Postgres V9.1 issue with LIKE clause and Escape Strings

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

[jira] [Created] (OPENJPA-2056) Postgres V9.1 issue with LIKE clause and Escape Strings

Romain Manni-Bucau (Jira)
Postgres V9.1 issue with LIKE clause and Escape Strings
-------------------------------------------------------

                 Key: OPENJPA-2056
                 URL: https://issues.apache.org/jira/browse/OPENJPA-2056
             Project: OpenJPA
          Issue Type: Bug
          Components: jdbc
    Affects Versions: 2.1.1, 2.0.1, 2.0.0
         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.id, 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:-
  http://www.postgresql.org/docs/9.1/static/release-9-1.html (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: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira

       
Reply | Threaded
Open this post in threaded view
|

[jira] [Commented] (OPENJPA-2056) Postgres V9.1 issue with LIKE clause and Escape Strings

Romain Manni-Bucau (Jira)

    [ https://issues.apache.org/jira/browse/OPENJPA-2056?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13120000#comment-13120000 ]

Andrew Hastie commented on OPENJPA-2056:
----------------------------------------

I think there are several solutions to this problem, but ideally we need something that is backwards compatible with previous Postgres releases. I'm no expect on the internals of OpenJPA, but here are some suggestions:

1. Determine from the current connection the setting of "standard_conforming_strings" (Needs SQL statement "SHOW standard_conforming_strings;"). This can then be used to influence the escape string to being either "\\" or "\\\\" in the Dictionary impl as required. ASFAIK Postgres at V9.1 returns "ON" and earlier releases return "OFF".

Here are some links to the Postgres documentation covering this topic:-
  http://www.postgresql.org/docs/9.1/interactive/runtime-config-compatible.html#GUC-STANDARD-CONFORMING-STRINGS
  http://www.postgresql.org/docs/9.1/interactive/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-ESCAPE

2. Use an explicitly escaped string like E'\\' to force escape interpretation, making the string independent of the standards_conforming_strings param.

Until this is fixed, there are several workarounds for the problem:-

1. Set a property in the persistence.xml as follows to override the default PGDictionary setting:-
    <property name="openjpa.jdbc.DBDictionary" value="postgres(SearchStringEscape=\)"/>
2. In the Postgres configuration file (postgresql.conf) make the setting under "VERSION/PLATFORM COMPATIBILITY" :-
  standard_conforming_strings = off

Option 1 is preferred as not all users may have access to the Postgres configuration files.

               

> Postgres V9.1 issue with LIKE clause and Escape Strings
> -------------------------------------------------------
>
>                 Key: OPENJPA-2056
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-2056
>             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.id, 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:-
>   http://www.postgresql.org/docs/9.1/static/release-9-1.html (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: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira

       
Reply | Threaded
Open this post in threaded view
|

[jira] [Commented] (OPENJPA-2056) Postgres V9.1 issue with LIKE clause and Escape Strings

Romain Manni-Bucau (Jira)
In reply to this post by Romain Manni-Bucau (Jira)

    [ https://issues.apache.org/jira/browse/OPENJPA-2056?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13120590#comment-13120590 ]

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

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: https://issues.apache.org/jira/browse/OPENJPA-2056
>             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.id, 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:-
>   http://www.postgresql.org/docs/9.1/static/release-9-1.html (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: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira

       
Reply | Threaded
Open this post in threaded view
|

[jira] [Assigned] (OPENJPA-2056) Postgres V9.1 issue with LIKE clause and Escape Strings

Romain Manni-Bucau (Jira)
In reply to this post by Romain Manni-Bucau (Jira)

     [ https://issues.apache.org/jira/browse/OPENJPA-2056?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Milosz Tylenda reassigned OPENJPA-2056:
---------------------------------------

    Assignee: Milosz Tylenda
   

> Postgres V9.1 issue with LIKE clause and Escape Strings
> -------------------------------------------------------
>
>                 Key: OPENJPA-2056
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-2056
>             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
>            Assignee: Milosz Tylenda
>
> 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.id, 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:-
>   http://www.postgresql.org/docs/9.1/static/release-9-1.html (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: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira

       
Reply | Threaded
Open this post in threaded view
|

[jira] [Updated] (OPENJPA-2056) Postgres V9.1 issue with LIKE clause and Escape Strings

Romain Manni-Bucau (Jira)
In reply to this post by Romain Manni-Bucau (Jira)

     [ https://issues.apache.org/jira/browse/OPENJPA-2056?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Milosz Tylenda updated OPENJPA-2056:
------------------------------------

    Component/s: sql
   

> Postgres V9.1 issue with LIKE clause and Escape Strings
> -------------------------------------------------------
>
>                 Key: OPENJPA-2056
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-2056
>             Project: OpenJPA
>          Issue Type: Bug
>          Components: jdbc, sql
>    Affects Versions: 2.0.0, 2.0.1, 2.1.1
>         Environment: Running against a Postgres database at version 9.1
>            Reporter: Andrew Hastie
>            Assignee: Milosz Tylenda
>             Fix For: 2.2.0
>
>
> 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.id, 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:-
>   http://www.postgresql.org/docs/9.1/static/release-9-1.html (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: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira

       
Reply | Threaded
Open this post in threaded view
|

[jira] [Resolved] (OPENJPA-2056) Postgres V9.1 issue with LIKE clause and Escape Strings

Romain Manni-Bucau (Jira)
In reply to this post by Romain Manni-Bucau (Jira)

     [ https://issues.apache.org/jira/browse/OPENJPA-2056?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Milosz Tylenda resolved OPENJPA-2056.
-------------------------------------

       Resolution: Fixed
    Fix Version/s: 2.2.0
   

> Postgres V9.1 issue with LIKE clause and Escape Strings
> -------------------------------------------------------
>
>                 Key: OPENJPA-2056
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-2056
>             Project: OpenJPA
>          Issue Type: Bug
>          Components: jdbc, sql
>    Affects Versions: 2.0.0, 2.0.1, 2.1.1
>         Environment: Running against a Postgres database at version 9.1
>            Reporter: Andrew Hastie
>            Assignee: Milosz Tylenda
>             Fix For: 2.2.0
>
>
> 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.id, 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:-
>   http://www.postgresql.org/docs/9.1/static/release-9-1.html (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: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira

       
Reply | Threaded
Open this post in threaded view
|

[jira] [Commented] (OPENJPA-2056) Postgres V9.1 issue with LIKE clause and Escape Strings

Romain Manni-Bucau (Jira)
In reply to this post by Romain Manni-Bucau (Jira)

    [ https://issues.apache.org/jira/browse/OPENJPA-2056?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13123664#comment-13123664 ]

Milosz Tylenda commented on OPENJPA-2056:
-----------------------------------------

Hi Andrew & Craig. Thanks for the extensive information. The escape string now depends on PostgreSQL version:
1. 9.0 and older use '\\'.
2. 9.1 and later use '\'.

I expect this to be a good solution for the vast majority of users. Those with PostgreSQL 9.0 and older who have set standard_conforming_strings to ON can use the SearchStringEscape property workaround proposed by Andrew. Also, setting a DBDictionary property called RequiresSearchStringEscapeForLike to false might help in such cases.
               

> Postgres V9.1 issue with LIKE clause and Escape Strings
> -------------------------------------------------------
>
>                 Key: OPENJPA-2056
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-2056
>             Project: OpenJPA
>          Issue Type: Bug
>          Components: jdbc, sql
>    Affects Versions: 2.0.0, 2.0.1, 2.1.1
>         Environment: Running against a Postgres database at version 9.1
>            Reporter: Andrew Hastie
>            Assignee: Milosz Tylenda
>             Fix For: 2.2.0
>
>
> 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.id, 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:-
>   http://www.postgresql.org/docs/9.1/static/release-9-1.html (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: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira

       
Reply | Threaded
Open this post in threaded view
|

[jira] [Closed] (OPENJPA-2056) Postgres V9.1 issue with LIKE clause and Escape Strings

Romain Manni-Bucau (Jira)
In reply to this post by Romain Manni-Bucau (Jira)

     [ https://issues.apache.org/jira/browse/OPENJPA-2056?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Albert Lee closed OPENJPA-2056.
-------------------------------


Close issue in preparation for 2.2.0 release.
               

> Postgres V9.1 issue with LIKE clause and Escape Strings
> -------------------------------------------------------
>
>                 Key: OPENJPA-2056
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-2056
>             Project: OpenJPA
>          Issue Type: Bug
>          Components: jdbc, sql
>    Affects Versions: 2.0.0, 2.0.1, 2.1.1
>         Environment: Running against a Postgres database at version 9.1
>            Reporter: Andrew Hastie
>            Assignee: Milosz Tylenda
>             Fix For: 2.2.0
>
>
> 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.id, 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:-
>   http://www.postgresql.org/docs/9.1/static/release-9-1.html (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: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira