Location via proxy:   [ UP ]  
[Report a bug]   [Manage cookies]                
Fix definition of pg_statio_all_tables view
authorAlexander Korotkov <akorotkov@postgresql.org>
Tue, 28 Apr 2020 08:07:56 +0000 (11:07 +0300)
committerAlexander Korotkov <akorotkov@postgresql.org>
Tue, 28 Apr 2020 08:30:33 +0000 (11:30 +0300)
pg_statio_all_tables view appears to have a wrong grouping.  As the result
numbers of toast index blocks read and hit were multiplied to the number of
table indexes.  This commit fixes the view definition.

Backpatching this appears difficult.  We don't have a mechanism to patch a
system catalog of existing instances in minor upgrade.  We can write a
release notes instruction to do this manually.  But per discussion this is
probably not so critical bug for doing such an intrusive fix.

Reported-by: Andrei Zubkov
Discussion: https://postgr.es/m/CAPpHfdtMYkkNudLMG9G0dxX_B%3Dn5sfKzOyxxrvWYtSicaGW0Lw%40mail.gmail.com

src/backend/catalog/system_views.sql
src/test/regress/expected/rules.out

index d406ea8118c0f0a37408ea32de18d12fc76471fc..2bd5f5ea14a984beb05148944cc63378280f5bb2 100644 (file)
@@ -642,16 +642,16 @@ CREATE VIEW pg_statio_all_tables AS
             pg_stat_get_blocks_fetched(T.oid) -
                     pg_stat_get_blocks_hit(T.oid) AS toast_blks_read,
             pg_stat_get_blocks_hit(T.oid) AS toast_blks_hit,
-            sum(pg_stat_get_blocks_fetched(X.indexrelid) -
-                    pg_stat_get_blocks_hit(X.indexrelid))::bigint AS tidx_blks_read,
-            sum(pg_stat_get_blocks_hit(X.indexrelid))::bigint AS tidx_blks_hit
+            pg_stat_get_blocks_fetched(X.indexrelid) -
+                    pg_stat_get_blocks_hit(X.indexrelid) AS tidx_blks_read,
+            pg_stat_get_blocks_hit(X.indexrelid) AS tidx_blks_hit
     FROM pg_class C LEFT JOIN
             pg_index I ON C.oid = I.indrelid LEFT JOIN
             pg_class T ON C.reltoastrelid = T.oid LEFT JOIN
             pg_index X ON T.oid = X.indrelid
             LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
     WHERE C.relkind IN ('r', 't', 'm')
-    GROUP BY C.oid, N.nspname, C.relname, T.oid, X.indrelid;
+    GROUP BY C.oid, N.nspname, C.relname, T.oid, X.indexrelid;
 
 CREATE VIEW pg_statio_sys_tables AS
     SELECT * FROM pg_statio_all_tables
index ac31840739de92f47d1fb0e067080e8c083debac..8876025aaa7cdda0b2fd3aada0c72731241bd555 100644 (file)
@@ -2216,15 +2216,15 @@ pg_statio_all_tables| SELECT c.oid AS relid,
     (sum(pg_stat_get_blocks_hit(i.indexrelid)))::bigint AS idx_blks_hit,
     (pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid)) AS toast_blks_read,
     pg_stat_get_blocks_hit(t.oid) AS toast_blks_hit,
-    (sum((pg_stat_get_blocks_fetched(x.indexrelid) - pg_stat_get_blocks_hit(x.indexrelid))))::bigint AS tidx_blks_read,
-    (sum(pg_stat_get_blocks_hit(x.indexrelid)))::bigint AS tidx_blks_hit
+    (pg_stat_get_blocks_fetched(x.indexrelid) - pg_stat_get_blocks_hit(x.indexrelid)) AS tidx_blks_read,
+    pg_stat_get_blocks_hit(x.indexrelid) AS tidx_blks_hit
    FROM ((((pg_class c
      LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
      LEFT JOIN pg_class t ON ((c.reltoastrelid = t.oid)))
      LEFT JOIN pg_index x ON ((t.oid = x.indrelid)))
      LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
   WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]))
-  GROUP BY c.oid, n.nspname, c.relname, t.oid, x.indrelid;
+  GROUP BY c.oid, n.nspname, c.relname, t.oid, x.indexrelid;
 pg_statio_sys_indexes| SELECT pg_statio_all_indexes.relid,
     pg_statio_all_indexes.indexrelid,
     pg_statio_all_indexes.schemaname,