Posting here for info only as I don't believe this is a "bug" as such,
rather a change in behaviour:-
Latest 2.1.1 stable version of Apache OpenJPA project appears broken
when running against Postgres when upgraded to v9.1. This I think is
down to the changes in escape string handling in SQL LIKE clauses.
Here's a link to the bug I've lodged against OpenJPA which includes
details of how to configure an override at runtime to the default
Postgres DBDictionary setting which will get things working again:-
http://openjpa.208410.n2.nabble.com/Postgres-V9-1-issue-with-LIKE-clause-and-Escape-Strings-td6848069.html
Also copying here for info just in case:-
=======================================================
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.
=======================================================
Andrew
