Bug 741855

Summary:	Support Postgres 9.1
Product:	[Other] RHQ Project	Reporter:	Heiko W. Rupp <hrupp>
Component:	Core Server	Assignee:	Heiko W. Rupp <hrupp>
Status:	CLOSED NEXTRELEASE	QA Contact:	Mike Foley <mfoley>
Severity:	unspecified	Docs Contact:
Priority:	high
Version:	4.2	CC:	hendy, hrupp
Target Milestone:	---
Target Release:	RHQ 4.3.0
Hardware:	Unspecified
OS:	Unspecified
Whiteboard:
Fixed In Version:		Doc Type:	Bug Fix
Doc Text:		Story Points:	---
Clone Of:		Environment:
Last Closed:	2012-02-06 20:20:28 UTC	Type:	---
Regression:	---	Mount Type:	---
Documentation:	---	CRM:
Verified Versions:		Category:	---
oVirt Team:	---	RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	---	Target Upstream Version:
Embargoed:

Description Heiko W. Rupp 2011-09-28 07:45:06 UTC

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 08:47:11 UTC

cc09ffe on master has the change for standard conforming escape chars.

Comment 2 Charles Crouch 2011-10-04 12:14:01 UTC

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 14:14:12 UTC

Yes.
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 11:32:38 UTC

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 11:37:14 UTC

Hendy,
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 13:30:46 UTC

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 14:28:03 UTC

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 20:20:28 UTC

Done by Ips fixing BUG 787766