Bug 548172

Summary: Bad query in Postgres agent plugin
Product: [Other] RHQ Project Reporter: Mark Wagner <mwagner>
Component: DatabaseAssignee: Heiko W. Rupp <hrupp>
Status: CLOSED CURRENTRELEASE QA Contact: Sunil Kondkar <skondkar>
Severity: medium Docs Contact:
Priority: low    
Version: 1.4CC: cwelton, mwagner
Target Milestone: ---Keywords: SubBug
Target Release: ---   
Hardware: All   
OS: Linux   
Fixed In Version: 2.4 Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2010-08-12 16:51:38 UTC Type: ---
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Bug Depends On:    
Bug Blocks: 565628, 591531    

Description Mark Wagner 2009-12-16 20:14:22 UTC
Description of problem:
There seems to be a bad query in the Postgresql agent. 

SELECT ts.*, pg_relation_size(ts.relname) as table_size, pg_total_relation_size(ts.relname) as total_size, ios.heap_blks_read, ios.heap_blks_hit, ios.idx_blks_read, ios.idx_blks_hit, ios.toast_blks_read, ios.toast_blks_hit, ios.tidx_blks_read, ios.tidx_blks_hit FROM pg_stat_user_tables ts left join pg_statio_user_tables ios on ts.relid=ios.relid WHERE ts.relname=${}

ERROR: function pg_relation_size(name) does not exist at character 15

Version-Release number of selected component (if applicable):
I'm using Postgresql 8.4 

How reproducible:

Steps to Reproduce:
1.run rhq with postgesql database (ver 8.4 in my case)
2. install the postgres agent
3. look at the log files
Actual results:
query fails

Expected results:
query should work

Additional info:


Comment 1 Heiko W. Rupp 2010-01-22 16:35:07 UTC
what rights does the agent user have ?
(or better: what are role name/password in the postgres servers connection properties ? )

When you log into postgres as this user(role) and issue  (via psql)

 select pg_relation_size('rhq_agent') ;

does this work?

Comment 2 Mark Wagner 2010-01-22 18:12:42 UTC
The query does seem to work:

# /opt/PostgreSQL/8.4/bin/psql -U rhqadmin -w -d rhq
psql (8.4.1)
Type "help" for help.

rhq=> select pg_relation_size('rhq_agent') ;
(1 row)

for permissions:
rhq=# select * from pg_authid;
 rolname  | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolconnlimit |             rolpassword             | rolvaliduntil | rolconfig 
 postgres | t        | t          | t             | t           | t            | t           |           -1 | md53175bce1d3201d16594cebf9d7eb3f9d |               | 
 rhqadmin | f        | t          | f             | f           | f            | t           |           -1 | md595e13780358466b08514bca98d7f1386 |               | 

If it matters, there are also permission errors in the log that I believe are coming from the hourly analyze.

Comment 3 wes hayutin 2010-02-16 16:52:14 UTC
Temporarily adding the keyword "SubBug" so we can be sure we have accounted for all the bugs.

new = Tracking + FutureFeature + SubBug

Comment 4 wes hayutin 2010-02-16 16:58:09 UTC
making sure we're not missing any bugs in rhq_triage

Comment 5 Heiko W. Rupp 2010-04-26 12:05:41 UTC
n 8.4, pg_relation_size was changed to use the regclass type, which means that pg_relation_size(data_type_name) no longer works.

For whatever reason, pg_relation_size('table') still works, but pg_relation_size(relation) not.
Switching to OID works in 8.1+ databases.

QE: please also test against 8.3 

Fix is in 7bb4095

Comment 6 Sunil Kondkar 2010-05-21 12:13:35 UTC
Verified on jon build#160 (Revision: 10620) on postgres versions 8.2.4, 8.3.8 and 8.4.2

Postgres plugin is able to get metrics for postgres tables and no exception is observed in agent.log file.

Comment 7 Corey Welton 2010-08-12 16:51:38 UTC
Mass-closure of verified bugs against JON.