[HIVE-3994] Hive metastore is not working on PostgreSQL 9.2 (most likely on anything 9.0+)

• Type: Improvement
• Status: Resolved
• Priority: Major
• Resolution: Not A Problem
• Assignee: Unassigned
• Reporter: Jarek Jarcec Cecho
• Created: 07/Feb/13 00:03
• Updated: 09/Apr/13 07:50
• Resolved: 08/Feb/13 03:00

Description

I'm getting following exception when running metastore on PostgreSQL 9.2:

Caused by: javax.jdo.JDODataStoreException: Error executing JDOQL query "SELECT "THIS"."TBL_NAME" AS NUCORDER0 FROM "TBLS" "THIS" LEFT OUTER JOIN "DBS" "THIS_DATABASE_NAME" ON "THIS"."DB_ID" = "THIS_DATABASE_NAME"."DB_ID" WHERE "THIS_DATABASE_NAME"."NAME" = ? AND (LOWER("THIS"."TBL_NAME") LIKE ? ESCAPE '\\' ) ORDER BY NUCORDER0 " : ERROR: invalid escape string
  Hint: Escape string must be empty or one character..
NestedThrowables:
org.postgresql.util.PSQLException: ERROR: invalid escape string
  Hint: Escape string must be empty or one character.
        at org.datanucleus.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:313)
        at org.datanucleus.jdo.JDOQuery.execute(JDOQuery.java:252)
        at org.apache.hadoop.hive.metastore.ObjectStore.getTables(ObjectStore.java:759)
        ... 28 more
Caused by: org.postgresql.util.PSQLException: ERROR: invalid escape string
  Hint: Escape string must be empty or one character.
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2096)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1829)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:510)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:386)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.datanucleus.store.rdbms.SQLController.executeStatementQuery(SQLController.java:457)
        at org.datanucleus.store.rdbms.query.legacy.SQLEvaluator.evaluate(SQLEvaluator.java:123)
        at org.datanucleus.store.rdbms.query.legacy.JDOQLQuery.performExecute(JDOQLQuery.java:288)
        at org.datanucleus.store.query.Query.executeQuery(Query.java:1657)
        at org.datanucleus.store.rdbms.query.legacy.JDOQLQuery.executeQuery(JDOQLQuery.java:245)
        at org.datanucleus.store.query.Query.executeWithArray(Query.java:1499)
        at org.datanucleus.jdo.JDOQuery.execute(JDOQuery.java:243)
        ... 29 more

I've google a bit about that and I found a lot of similar issues in different projects thus I'm assuming that this might be some backward compatibility issue on PostgreSQL side.