Bug 741855 - Support Postgres 9.1
Support Postgres 9.1
Product: RHQ Project
Classification: Other
Component: Core Server (Show other bugs)
Unspecified Unspecified
high Severity unspecified (vote)
: ---
: RHQ 4.3.0
Assigned To: Heiko W. Rupp
Mike Foley
Depends On:
  Show dependency treegraph
Reported: 2011-09-28 03:45 EDT by Heiko W. Rupp
Modified: 2012-02-06 15:20 EST (History)
2 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Last Closed: 2012-02-06 15:20:28 EST
Type: ---
Regression: ---
Mount Type: ---
Documentation: ---
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---

Attachments (Terms of Use)

  None (edit)
Description Heiko W. Rupp 2011-09-28 03:45:06 EDT
Postgres 9.1 has changed the default for string_escapes from postgres-specific to standard conforming, which means that RHQ can not use it right now as the login fails and the console shows

Caused by: org.postgresql.util.PSQLException: ERROR: invalid escape string
 Hinweis: Escape string must be empty or one character.

This comes from the fact that postgres 9.1 by default uses 
"standard conforming strings" - those got introduced in 8.x already,
but were turned off and only warned about.
See http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.1#Backward_compatibility_issues

We can put the following in postgresql.conf and signal Postgres to reload the 
config in order to get it working again:

standard_conforming_strings = off 

But this is only a "duct tape" work around.
Comment 1 Heiko W. Rupp 2011-09-28 04:47:11 EDT
cc09ffe on master has the change for standard conforming escape chars.
Comment 2 Charles Crouch 2011-10-04 08:14:01 EDT
Can you just confirm heiko that this change doesn't impact any db related 
functionality for PG<=9.0?
Comment 3 Heiko W. Rupp 2011-10-04 10:14:12 EDT
This basically meant adding a new class (class was even there already for a long time) to detect 9.1
and to override one method in that class.
Comment 5 Hendy Irawan 2011-11-04 07:32:38 EDT
I have just upgraded from RHQ 4.1.0 PG 8.4 to RHQ 4.2.0 PG 9.1:

1. Stopped old RHQ Server
2. Upgraded the cluster from PG 8.4 to PG 9.1
3. Started the new RHQ Server. Install using the existing PG 9.1 database
4. Install is successful, however...

I can login to RHQ just fine, but opening any Resource yields the following exception:

18:26:08,321 WARN  [JDBCExceptionReporter] SQL Error: 0, SQLState: 42P01
18:26:08,349 ERROR [JDBCExceptionReporter] ERROR: relation "rhq_drift_def_template" does not exist
  Position: 1299
18:26:08,626 WARN  [gwt-log] Sending exception to client: [1320405968566]
javax.ejb.EJBTransactionRolledbackException: org.hibernate.exception.SQLGrammarException: could not execute query
        at org.jboss.ejb3.tx.Ejb3TxPolicy.handleInCallerTx(Ejb3TxPolicy.java:87)
        at org.jboss.aspects.tx.TxPolicy.invokeInCallerTx(TxPolicy.java:130)
        at org.jboss.aspects.tx.TxInterceptor$Required.invoke(TxInterceptor.java:195)
        at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
        at org.jboss.aspects.tx.TxPropagationInterceptor.invoke(TxPropagationInterceptor.java:95

Should I file a new bug ?
Comment 6 Heiko W. Rupp 2011-11-04 07:37:14 EDT
did you run through the installer with upgrade db option when upgrading from RHQ 4.1 to 4.2 ?
Drift templates were only introduced in RHQ 4.2, so require the installer to upgrade the db schema -- independent of any db version.
Comment 7 Hendy Irawan 2011-11-04 09:30:46 EDT
Thank you Heiko.

No I'm not aware of the switch.

Where is this switch documented?

I tried re-reading http://www.rhq-project.org/display/JOPR2/Upgrading+the+Server and http://www.rhq-project.org/display/JOPR2/Running+The+Installer but can't seem to find it (or maybe my eye is getting old...)

P.S. How do I get a RHQ Wiki account? I have a bunch of fixes to edit the Wiki...
Comment 8 Hendy Irawan 2011-11-04 10:28:03 EDT
Hi Heiko,

Yes I did run the installer (upon browsing, it redirects automatically to the installer), and I did choose the "Keep (maintain existing data)" option.

Any suggestions? I can provide logs, etc. if you want..
Comment 9 Heiko W. Rupp 2012-02-06 15:20:28 EST
Done by Ips fixing BUG 787766

Note You need to log in before you can comment on or make changes to this bug.