Bug 1095369 - Collection of Postgres table metrics fails when the tables are not in the public schema
Summary: Collection of Postgres table metrics fails when the tables are not in the pub...
Keywords:
Status: CLOSED CURRENTRELEASE
Alias: None
Product: JBoss Operations Network
Classification: JBoss
Component: Plugin -- Other
Version: JON 3.2
Hardware: All
OS: Linux
medium
medium
Target Milestone: DR01
: JON 3.2.2
Assignee: Thomas Segismont
QA Contact: Mike Foley
URL:
Whiteboard:
Depends On: 579810
Blocks: 1079403 1082792
TreeView+ depends on / blocked
 
Reported: 2014-05-07 14:46 UTC by Thomas Segismont
Modified: 2014-07-29 00:17 UTC (History)
2 users (show)

Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Clone Of: 579810
Environment:
Last Closed: 2014-07-29 00:17:13 UTC
Type: ---
Embargoed:


Attachments (Terms of Use)
non-public-table-metric (147.35 KB, image/png)
2014-06-03 14:21 UTC, Armine Hovsepyan
no flags Details

Description Thomas Segismont 2014-05-07 14:46:47 UTC
+++ This bug was initially created as a clone of Bug #579810 +++

The table metrics cannot be collected when the tables do not belong to the public schema.
The problem appears on the following environment :
Monitored Database : Postgresql 8.3
RHQ agent plugin version : rhq-postgres-plugin-1.3.1
Jopr Server version : 2.3.1

To reproduce the problem, create a new schema in a monitored database :
create schema myschema;

Then create a table in the new schema :
create table myschema.mytable (name varchar(20));

In Jopr, no metrics are available for the table mytable.
The role used by the agent to connect to the Postgresql DB is postgres.

The reason for the failure to collect the metrics can be found in the Postgresql logs :

ERROR:  relation "mytable" does not exist
STATEMENT:  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 = $1

This is due to the fact that by default the search_path of a postgresql role is :
"$user",public

By default, the newly created schema is not looked in when performing a query.

To fix the problem, the select statement could me modified to look like this :

SELECT ts.*,
          pg_relation_size(ts.schemaname || '.' || ts.relname) AS table_size,
          pg_total_relation_size(ts.schemaname || '.' || 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 = 'mytable' and ts.schemaname='myschema';

As a temporary workaround, one could alter the search_path for the role postgres :

alter role postgres set search_path="$user",public,myschema;

--- Additional comment from Thomas Segismont on 2014-04-08 11:59:11 CEST ---

Fixed in master

commit 835eca6a7b14bc92db5f98d336470f9b6ece145e
Author: Thomas Segismont <tsegismo>
Date:   Tue Apr 8 11:15:14 2014 +0200

Changed resource key format ("tablename"->"schemaname.tablename")
    
Implemented resource upgrade and updated resource component and discovery component
    
Also changed the availability check: new the resource component checks for table existence instead of looking at the parent database availability

--- Additional comment from Thomas Segismont on 2014-04-09 17:47:26 CEST ---

The fix for this bug is a prerequisite of the fix for Bug 1079403

Comment 1 Thomas Segismont 2014-05-09 15:25:59 UTC
Cherry-picked over to release/jon3.2.x

commit 405a8cd8360e13e4d571b03ef479d513d924587c
Author: Thomas Segismont <tsegismo>
Date:   Tue Apr 8 11:15:14 2014 +0200

Comment 2 Simeon Pinder 2014-05-22 15:15:38 UTC
Via product triage, determined that this bug is to be included for DR01 target milestone.

Comment 3 Simeon Pinder 2014-05-30 02:43:37 UTC
Moving to ON_QA as available for test in latest cumulative patch build(DR01):
http://jon01.mw.lab.eng.bos.redhat.com:8042/dist/release/jon/3.2.2.GA/5-29-2014/

Comment 4 Armine Hovsepyan 2014-06-03 14:21:55 UTC
Created attachment 901808 [details]
non-public-table-metric

Comment 5 Armine Hovsepyan 2014-06-04 08:05:44 UTC
verified

Comment 6 Larry O'Leary 2014-07-29 00:17:13 UTC
This has been verified and released in Red Hat JBoss Operations Network 3.2 Update 02 (3.2.2) available from the Red Hat Customer Portal[1].



[1]: https://access.redhat.com/jbossnetwork/restricted/softwareDetail.html?softwareId=31783


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