Bug 548172 - Bad query in Postgres agent plugin
Summary: Bad query in Postgres agent plugin
Keywords:
Status: CLOSED CURRENTRELEASE
Alias: None
Product: RHQ Project
Classification: Other
Component: Database
Version: 1.4
Hardware: All
OS: Linux
low
medium vote
Target Milestone: ---
: ---
Assignee: Heiko W. Rupp
QA Contact: Sunil Kondkar
URL:
Whiteboard:
Depends On:
Blocks: rhq_triage jon-sprint10-bugs
TreeView+ depends on / blocked
 
Reported: 2009-12-16 20:14 UTC by Mark Wagner
Modified: 2010-08-12 16:51 UTC (History)
2 users (show)

Fixed In Version: 2.4
Doc Type: Bug Fix
Doc Text:
Clone Of:
Environment:
Last Closed: 2010-08-12 16:51:38 UTC


Attachments (Terms of Use)

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:
Everytime

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:

http://git.fedorahosted.org/git/rhq/rhq.git?p=rhq/rhq.git;a=blob;f=modules/plugins/postgres/src/main/java/org/rhq/plugins/postgres/PostgresTableComponent.java;hb=master

Comment 1 Heiko W. Rupp 2010-01-22 16:35:07 UTC
Mark,
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') ;
 pg_relation_size 
------------------
           974848
(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.

keyword:
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.


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