Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitef11051

Browse files
committed
Fix definition of pg_statio_all_tables view
pg_statio_all_tables view appears to have a wrong grouping. As the resultnumbers of toast index blocks read and hit were multiplied to the number oftable indexes. This commit fixes the view definition.Backpatching this appears difficult. We don't have a mechanism to patch asystem catalog of existing instances in minor upgrade. We can write arelease notes instruction to do this manually. But per discussion this isprobably not so critical bug for doing such an intrusive fix.Reported-by: Andrei ZubkovDiscussion:https://postgr.es/m/CAPpHfdtMYkkNudLMG9G0dxX_B%3Dn5sfKzOyxxrvWYtSicaGW0Lw%40mail.gmail.com
1 parentebf6de8 commitef11051

File tree

2 files changed

+7
-7
lines changed

2 files changed

+7
-7
lines changed

‎src/backend/catalog/system_views.sql

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -642,16 +642,16 @@ CREATE VIEW pg_statio_all_tables AS
642642
pg_stat_get_blocks_fetched(T.oid)-
643643
pg_stat_get_blocks_hit(T.oid)AS toast_blks_read,
644644
pg_stat_get_blocks_hit(T.oid)AS toast_blks_hit,
645-
sum(pg_stat_get_blocks_fetched(X.indexrelid)-
646-
pg_stat_get_blocks_hit(X.indexrelid))::bigintAS tidx_blks_read,
647-
sum(pg_stat_get_blocks_hit(X.indexrelid))::bigintAS tidx_blks_hit
645+
pg_stat_get_blocks_fetched(X.indexrelid)-
646+
pg_stat_get_blocks_hit(X.indexrelid)AS tidx_blks_read,
647+
pg_stat_get_blocks_hit(X.indexrelid)AS tidx_blks_hit
648648
FROM pg_class CLEFT JOIN
649649
pg_index IONC.oid=I.indrelidLEFT JOIN
650650
pg_class TONC.reltoastrelid=T.oidLEFT JOIN
651651
pg_index XONT.oid=X.indrelid
652652
LEFT JOIN pg_namespace NON (N.oid=C.relnamespace)
653653
WHEREC.relkindIN ('r','t','m')
654-
GROUP BYC.oid,N.nspname,C.relname,T.oid,X.indrelid;
654+
GROUP BYC.oid,N.nspname,C.relname,T.oid,X.indexrelid;
655655

656656
CREATEVIEWpg_statio_sys_tablesAS
657657
SELECT*FROM pg_statio_all_tables

‎src/test/regress/expected/rules.out

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -2216,15 +2216,15 @@ pg_statio_all_tables| SELECT c.oid AS relid,
22162216
(sum(pg_stat_get_blocks_hit(i.indexrelid)))::bigint AS idx_blks_hit,
22172217
(pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid)) AS toast_blks_read,
22182218
pg_stat_get_blocks_hit(t.oid) AS toast_blks_hit,
2219-
(sum((pg_stat_get_blocks_fetched(x.indexrelid) - pg_stat_get_blocks_hit(x.indexrelid))))::bigint AS tidx_blks_read,
2220-
(sum(pg_stat_get_blocks_hit(x.indexrelid)))::bigint AS tidx_blks_hit
2219+
(pg_stat_get_blocks_fetched(x.indexrelid) - pg_stat_get_blocks_hit(x.indexrelid)) AS tidx_blks_read,
2220+
pg_stat_get_blocks_hit(x.indexrelid) AS tidx_blks_hit
22212221
FROM ((((pg_class c
22222222
LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
22232223
LEFT JOIN pg_class t ON ((c.reltoastrelid = t.oid)))
22242224
LEFT JOIN pg_index x ON ((t.oid = x.indrelid)))
22252225
LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
22262226
WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]))
2227-
GROUP BY c.oid, n.nspname, c.relname, t.oid, x.indrelid;
2227+
GROUP BY c.oid, n.nspname, c.relname, t.oid, x.indexrelid;
22282228
pg_statio_sys_indexes| SELECT pg_statio_all_indexes.relid,
22292229
pg_statio_all_indexes.indexrelid,
22302230
pg_statio_all_indexes.schemaname,

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp