[PATCH] pg_statio_all_tables: several rows per table due to invalid TOAST index

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