Hi, hackers!
It seems we have a problem in pg_statio_all_tables view defenition.
According to the documentation and identification fields, this view
must have exact one row per a table.
The view definition contains an x.indexrelid as the last field in its
GROUP BY list:
<...>
GROUP BY c.oid, n.nspname, c.relname, t.oid, x.indexrelid
Which is the oid of a TOAST-index.
However it is possible that the TOAST table will have more than one
index. For example, this happens when REINDEX CONCURRENTLY operation
lefts an index in invalid state (indisvalid = false) due to some kind
of a failure. It's often sufficient to interrupt REINDEX CONCURRENTLY
operation right after start.
Such index will cause the second row to appear in a
pg_statio_all_tables view which obvious is unexpected behaviour.
Now we can have several regular indexes and several TOAST-indexes for
the same table. Statistics for the regular and TOAST indexes is to be
calculated the same way so I've decided to use a CTE here.
The proposed view definition follows:
CREATE VIEW pg_statio_all_tables AS
WITH indstat AS (
SELECT
indrelid,
sum(pg_stat_get_blocks_fetched(indexrelid) -
pg_stat_get_blocks_hit(indexrelid))::bigint
AS idx_blks_read,
sum(pg_stat_get_blocks_hit(indexrelid))::bigint
AS idx_blks_hit
FROM
pg_index
GROUP BY indrelid
)
SELECT
C.oid AS relid,
N.nspname AS schemaname,
C.relname AS relname,
pg_stat_get_blocks_fetched(C.oid) -
pg_stat_get_blocks_hit(C.oid) AS heap_blks_read,
pg_stat_get_blocks_hit(C.oid) AS heap_blks_hit,
I.idx_blks_read AS idx_blks_read,
I.idx_blks_hit 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,
X.idx_blks_read AS tidx_blks_read,
X.idx_blks_read AS tidx_blks_hit
FROM pg_class C LEFT JOIN
indstat I ON C.oid = I.indrelid LEFT JOIN
pg_class T ON C.reltoastrelid = T.oid LEFT JOIN
indstat X ON T.oid = X.indrelid
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE C.relkind IN ('r', 't', 'm');
Reported by Sergey Grinko.
Regards.
--
Andrei Zubkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
From ffde04cf285de32c7b8521c0aa9d0b36c1e8b7f7 Mon Sep 17 00:00:00 2001
From: Andrei Zubkov <[email protected]>
Date: Mon, 29 Nov 2021 16:33:34 +0300
Subject: [PATCH] pg_statio_all_tables: several rows per table due to invalid
TOAST index
This patch changes definition of a pg_statio_all_tables view. More than one
index can exist on a TOAST table due to invalid indexes, which can appear due
to REINDEX CONCURRENTLY failure. Previous view definition in such case caused
several rows to appear in a view for a single table.
Reported by Sergey Grinko.
---
src/backend/catalog/system_views.sql | 29 ++++++++++++++++++----------
src/test/regress/expected/rules.out | 24 ++++++++++++++---------
2 files changed, 34 insertions(+), 19 deletions(-)
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index eb560955cd..84ec8e3989 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -708,6 +708,18 @@ CREATE VIEW pg_stat_xact_user_tables AS
schemaname !~ '^pg_toast';
CREATE VIEW pg_statio_all_tables AS
+ WITH indstat AS (
+ SELECT
+ indrelid,
+ sum(pg_stat_get_blocks_fetched(indexrelid) -
+ pg_stat_get_blocks_hit(indexrelid))::bigint
+ AS idx_blks_read,
+ sum(pg_stat_get_blocks_hit(indexrelid))::bigint
+ AS idx_blks_hit
+ FROM
+ pg_index
+ GROUP BY indrelid
+ )
SELECT
C.oid AS relid,
N.nspname AS schemaname,
@@ -715,22 +727,19 @@ CREATE VIEW pg_statio_all_tables AS
pg_stat_get_blocks_fetched(C.oid) -
pg_stat_get_blocks_hit(C.oid) AS heap_blks_read,
pg_stat_get_blocks_hit(C.oid) AS heap_blks_hit,
- sum(pg_stat_get_blocks_fetched(I.indexrelid) -
- pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_read,
- sum(pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_hit,
+ I.idx_blks_read AS idx_blks_read,
+ I.idx_blks_hit 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,
- 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
+ X.idx_blks_read AS tidx_blks_read,
+ X.idx_blks_read AS tidx_blks_hit
FROM pg_class C LEFT JOIN
- pg_index I ON C.oid = I.indrelid LEFT JOIN
+ indstat 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
+ indstat 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.indexrelid;
+ WHERE C.relkind IN ('r', 't', 'm');
CREATE VIEW pg_statio_sys_tables AS
SELECT * FROM pg_statio_all_tables
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 2fa00a3c29..5a38545436 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2271,24 +2271,30 @@ pg_statio_all_sequences| SELECT c.oid AS relid,
FROM (pg_class c
LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.relkind = 'S'::"char");
-pg_statio_all_tables| SELECT c.oid AS relid,
+pg_statio_all_tables| WITH indstat AS (
+ SELECT pg_index.indrelid,
+ (sum((pg_stat_get_blocks_fetched(pg_index.indexrelid) - pg_stat_get_blocks_hit(pg_index.indexrelid))))::bigint AS idx_blks_read,
+ (sum(pg_stat_get_blocks_hit(pg_index.indexrelid)))::bigint AS idx_blks_hit
+ FROM pg_index
+ GROUP BY pg_index.indrelid
+ )
+ SELECT c.oid AS relid,
n.nspname AS schemaname,
c.relname,
(pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS heap_blks_read,
pg_stat_get_blocks_hit(c.oid) AS heap_blks_hit,
- (sum((pg_stat_get_blocks_fetched(i.indexrelid) - pg_stat_get_blocks_hit(i.indexrelid))))::bigint AS idx_blks_read,
- (sum(pg_stat_get_blocks_hit(i.indexrelid)))::bigint AS idx_blks_hit,
+ i.idx_blks_read,
+ i.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,
- (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
+ x.idx_blks_read AS tidx_blks_read,
+ x.idx_blks_read AS tidx_blks_hit
FROM ((((pg_class c
- LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
+ LEFT JOIN indstat 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 indstat 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.indexrelid;
+ WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]));
pg_statio_sys_indexes| SELECT pg_statio_all_indexes.relid,
pg_statio_all_indexes.indexrelid,
pg_statio_all_indexes.schemaname,
--
2.30.2
