Bug 1255227

Summary:	SET TRANSACTION failing sometimes on pgpool
Product:	[Community] Bugzilla	Reporter:	Jeff Fearn 🐞 <jfearn>
Component:	Database	Assignee:	Matt Tyson 🤬 <mtyson>
Status:	CLOSED CURRENTRELEASE	QA Contact:	tools-bugs <tools-bugs>
Severity:	unspecified	Docs Contact:
Priority:	unspecified
Version:	4.4	CC:	jmcdonal, mtahir, mtyson, qgong
Target Milestone:	---
Target Release:	---
Hardware:	Unspecified
OS:	Unspecified
Whiteboard:
Fixed In Version:		Doc Type:	Bug Fix
Doc Text:		Story Points:	---
Clone Of:		Environment:
Last Closed:	2015-09-20 23:29:49 UTC	Type:	Bug
Regression:	---	Mount Type:	---
Documentation:	---	CRM:
Verified Versions:		Category:	---
oVirt Team:	---	RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	---	Target Upstream Version:
Embargoed:

Description Jeff Fearn 🐞 2015-08-20 03:56:09 UTC

Description of problem:
Sometimes the call to 'SET TRANSACTION ISOLATION LEVEL READ COMMITTED' fails. This appears to be due to pgpool running queries on a connection and then handing the connection to a client for use.

We need to ROLLBACK and  DISCARD ALL before the begin.

2015-08-19 20:11:57 UTC [7737]: [4475-1] user=bugs,db=bugs LOG:  duration: 0.120 ms  parse pgpool7734: SELECT count(*) FROM pg_class AS c, pg_namespace AS n WHERE c.relname = 'ts_job' AND c.relnamespace = n.oid AND n.nspname = 'pg_catalog'
2015-08-19 20:11:57 UTC [7737]: [4476-1] user=bugs,db=bugs LOG:  duration: 0.207 ms  bind pgpool7734/pgpool7734: SELECT count(*) FROM pg_class AS c, pg_namespace AS n WHERE c.relname = 'ts_job' AND c.relnamespace = n.oid AND n.nspname = 'pg_catalog'
2015-08-19 20:11:57 UTC [7737]: [4477-1] user=bugs,db=bugs LOG:  duration: 0.034 ms  execute pgpool7734/pgpool7734: SELECT count(*) FROM pg_class AS c, pg_namespace AS n WHERE c.relname = 'ts_job' AND c.relnamespace = n.oid AND n.nspname = 'pg_catalog'
2015-08-19 20:11:57 UTC [7737]: [4478-1] user=bugs,db=bugs LOG:  duration: 0.057 ms  parse pgpool7734: SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.relname = 'ts_job' AND c.relpersistence = 'u'
2015-08-19 20:11:57 UTC [7737]: [4479-1] user=bugs,db=bugs LOG:  duration: 0.086 ms  bind pgpool7734/pgpool7734: SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.relname = 'ts_job' AND c.relpersistence = 'u'
2015-08-19 20:11:57 UTC [7737]: [4480-1] user=bugs,db=bugs LOG:  duration: 0.018 ms  execute pgpool7734/pgpool7734: SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.relname = 'ts_job' AND c.relpersistence = 'u'
2015-08-19 20:12:11 UTC [7737]: [4481-1] user=bugs,db=bugs LOG:  duration: 0.024 ms  statement: begin
2015-08-19 20:12:11 UTC [7737]: [4482-1] user=bugs,db=bugs ERROR:  SET TRANSACTION ISOLATION LEVEL must be called before any query
2015-08-19 20:12:11 UTC [7737]: [4483-1] user=bugs,db=bugs STATEMENT:  SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Comment 1 Jason McDonald 2015-08-20 06:15:35 UTC

Do we know what pgpool is trying to achieve with the extra queries and whether the queries add significant performance overhead?

Comment 2 Matt Tyson 🤬 2015-08-21 02:51:53 UTC

(In reply to Jason McDonald from comment #1)
> Do we know what pgpool is trying to achieve with the extra queries and
> whether the queries add significant performance overhead?

I don't know what pgpool is doing here.

Comment 3 Rony Gong 🔥 2015-08-24 09:04:07 UTC

QA environment(bzperfweb01.app.qa) with version(4.4.9039-3, DB: psql )
Result: Pass
Steps:
Can't find this kind of error any more after 2 days test, and will go on paying attention to this error

Comment 4 Matt Tyson 🤬 2015-09-20 23:29:49 UTC

This change is now live. If there are any issues, do not reopen this bug.
Instead, you should create a new bug and reference this bug.