From 7ffa748bc2fe767c94415350c6424ca0745c3c67 Mon Sep 17 00:00:00 2001 From: Georgios Kokolatos Subject: [PATCH v2] Attempt to make dbsize a bit more consistent --- src/backend/access/table/tableam.c | 8 +- src/backend/utils/adt/dbsize.c | 114 ++++++++++++++--------------- src/bin/psql/describe.c | 16 +++- src/test/regress/expected/psql.out | 12 +-- 4 files changed, 81 insertions(+), 69 deletions(-) diff --git a/src/backend/access/table/tableam.c b/src/backend/access/table/tableam.c index 6438c45716..41ac070426 100644 --- a/src/backend/access/table/tableam.c +++ b/src/backend/access/table/tableam.c @@ -636,10 +636,14 @@ table_block_relation_size(Relation rel, ForkNumber forkNumber) if (forkNumber == InvalidForkNumber) { for (int i = 0; i < MAX_FORKNUM; i++) - nblocks += smgrnblocks(rel->rd_smgr, i); + nblocks += smgrexists(rel->rd_smgr, i) + ? smgrnblocks(rel->rd_smgr, i) + : 0; } else - nblocks = smgrnblocks(rel->rd_smgr, forkNumber); + nblocks = smgrexists(rel->rd_smgr, forkNumber) + ? smgrnblocks(rel->rd_smgr, forkNumber) + : 0; return nblocks * BLCKSZ; } diff --git a/src/backend/utils/adt/dbsize.c b/src/backend/utils/adt/dbsize.c index 3319e9761e..e9ea9b6ad6 100644 --- a/src/backend/utils/adt/dbsize.c +++ b/src/backend/utils/adt/dbsize.c @@ -15,6 +15,7 @@ #include "access/htup_details.h" #include "access/relation.h" +#include "access/tableam.h" #include "catalog/catalog.h" #include "catalog/namespace.h" #include "catalog/pg_authid.h" @@ -34,6 +35,8 @@ /* Divide by two and round towards positive infinity. */ #define half_rounded(x) (((x) + ((x) < 0 ? 0 : 1)) / 2) +static int64 calculate_total_relation_size(Relation rel); + /* Return physical size of directory contents, or 0 if dir doesn't exist */ static int64 db_dir_size(const char *path) @@ -335,76 +338,42 @@ pg_relation_size(PG_FUNCTION_ARGS) PG_RETURN_INT64(size); } -/* - * Calculate total on-disk size of a TOAST relation, including its indexes. - * Must not be applied to non-TOAST relations. - */ -static int64 -calculate_toast_table_size(Oid toastrelid) -{ - int64 size = 0; - Relation toastRel; - ForkNumber forkNum; - ListCell *lc; - List *indexlist; - - toastRel = relation_open(toastrelid, AccessShareLock); - - /* toast heap size, including FSM and VM size */ - for (forkNum = 0; forkNum <= MAX_FORKNUM; forkNum++) - size += calculate_relation_size(&(toastRel->rd_node), - toastRel->rd_backend, forkNum); - - /* toast index size, including FSM and VM size */ - indexlist = RelationGetIndexList(toastRel); - - /* Size is calculated using all the indexes available */ - foreach(lc, indexlist) - { - Relation toastIdxRel; - - toastIdxRel = relation_open(lfirst_oid(lc), - AccessShareLock); - for (forkNum = 0; forkNum <= MAX_FORKNUM; forkNum++) - size += calculate_relation_size(&(toastIdxRel->rd_node), - toastIdxRel->rd_backend, forkNum); - - relation_close(toastIdxRel, AccessShareLock); - } - list_free(indexlist); - relation_close(toastRel, AccessShareLock); - - return size; -} - /* * Calculate total on-disk size of a given table, - * including FSM and VM, plus TOAST table if any. + * plus TOAST table if any. * Indexes other than the TOAST table's index are not included. * - * Note that this also behaves sanely if applied to an index or toast table; - * those won't have attached toast tables, but they can have multiple forks. + * Note that this also behaves sanely if applied to a toast table. */ static int64 calculate_table_size(Relation rel) { - int64 size = 0; + uint64 size = 0; ForkNumber forkNum; /* - * heap size, including FSM and VM + * table size, including FSM and VM */ for (forkNum = 0; forkNum <= MAX_FORKNUM; forkNum++) - size += calculate_relation_size(&(rel->rd_node), rel->rd_backend, - forkNum); + size += table_relation_size(rel, forkNum); /* * Size of toast relation */ if (OidIsValid(rel->rd_rel->reltoastrelid)) - size += calculate_toast_table_size(rel->rd_rel->reltoastrelid); + { + Relation toastRel; + + toastRel = relation_open(rel->rd_rel->reltoastrelid, AccessShareLock); - return size; + size += calculate_total_relation_size(toastRel); + + relation_close(toastRel, AccessShareLock); + } + + Assert(size < PG_INT64_MAX); + + return (int64)size; } /* @@ -415,7 +384,7 @@ calculate_table_size(Relation rel) static int64 calculate_indexes_size(Relation rel) { - int64 size = 0; + uint64 size = 0; /* * Aggregate all indexes on the given relation @@ -444,7 +413,9 @@ calculate_indexes_size(Relation rel) list_free(index_oids); } - return size; + Assert(size < PG_INT64_MAX); + + return (int64)size; } Datum @@ -452,14 +423,22 @@ pg_table_size(PG_FUNCTION_ARGS) { Oid relOid = PG_GETARG_OID(0); Relation rel; - int64 size; + int64 size = 0; rel = try_relation_open(relOid, AccessShareLock); if (rel == NULL) PG_RETURN_NULL(); - size = calculate_table_size(rel); + if (rel->rd_rel->relkind == RELKIND_RELATION || + rel->rd_rel->relkind == RELKIND_TOASTVALUE || + rel->rd_rel->relkind == RELKIND_MATVIEW) + size = calculate_table_size(rel); + else + { + relation_close(rel, AccessShareLock); + PG_RETURN_NULL(); + } relation_close(rel, AccessShareLock); @@ -487,12 +466,12 @@ pg_indexes_size(PG_FUNCTION_ARGS) /* * Compute the on-disk size of all files for the relation, - * including heap data, index data, toast data, FSM, VM. + * including table data, index data, toast data. */ static int64 calculate_total_relation_size(Relation rel) { - int64 size; + uint64 size; /* * Aggregate the table size, this includes size of the heap, toast and @@ -505,7 +484,9 @@ calculate_total_relation_size(Relation rel) */ size += calculate_indexes_size(rel); - return size; + Assert(size < PG_INT64_MAX); + + return (int64)size; } Datum @@ -513,14 +494,27 @@ pg_total_relation_size(PG_FUNCTION_ARGS) { Oid relOid = PG_GETARG_OID(0); Relation rel; - int64 size; + uint64 size = 0; rel = try_relation_open(relOid, AccessShareLock); if (rel == NULL) PG_RETURN_NULL(); - size = calculate_total_relation_size(rel); + if (rel->rd_rel->relkind == RELKIND_RELATION || + rel->rd_rel->relkind == RELKIND_TOASTVALUE || + rel->rd_rel->relkind == RELKIND_MATVIEW) + size = calculate_total_relation_size(rel); + else + { + for (ForkNumber forkNum = 0; forkNum <= MAX_FORKNUM; forkNum++) + { + size += calculate_relation_size(&(rel->rd_node), + rel->rd_backend, + forkNum); + Assert(size < PG_INT64_MAX); + } + } relation_close(rel, AccessShareLock); diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 58de433fd3..a39d67135b 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -3776,10 +3776,24 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys gettext_noop("Access Method")); /* + * As of PostgreSQL 14, do not use pg_table_size() for indexes and + * sequences as it does not behave sanely for those. + * * As of PostgreSQL 9.0, use pg_table_size() to show a more accurate * size of a table, including FSM, VM and TOAST tables. */ - if (pset.sversion >= 90000) + if (pset.sversion >= 140000) + appendPQExpBuffer(&buf, + ",\n CASE" + " WHEN c.relkind in ("CppAsString2(RELKIND_INDEX)", " + CppAsString2(RELKIND_PARTITIONED_INDEX)", " + CppAsString2(RELKIND_SEQUENCE)") THEN" + " pg_catalog.pg_size_pretty(pg_catalog.pg_relation_size(c.oid))" + " ELSE" + " pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid))" + " END as \"%s\"", + gettext_noop("Size")); + else if (pset.sversion >= 90000) appendPQExpBuffer(&buf, ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as \"%s\"", gettext_noop("Size")); diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out index daac0ff49d..bbbd91da98 100644 --- a/src/test/regress/expected/psql.out +++ b/src/test/regress/expected/psql.out @@ -2847,7 +2847,7 @@ Access method: heap tableam_display | mat_view_heap_psql | materialized view | regress_display_role | permanent | heap_psql | 0 bytes | tableam_display | tbl_heap | table | regress_display_role | permanent | heap | 0 bytes | tableam_display | tbl_heap_psql | table | regress_display_role | permanent | heap_psql | 0 bytes | - tableam_display | view_heap_psql | view | regress_display_role | permanent | | 0 bytes | + tableam_display | view_heap_psql | view | regress_display_role | permanent | | | (4 rows) \dt+ @@ -2867,10 +2867,10 @@ Access method: heap -- But not for views and sequences. \dv+ - List of relations - Schema | Name | Type | Owner | Persistence | Size | Description ------------------+----------------+------+----------------------+-------------+---------+------------- - tableam_display | view_heap_psql | view | regress_display_role | permanent | 0 bytes | + List of relations + Schema | Name | Type | Owner | Persistence | Size | Description +-----------------+----------------+------+----------------------+-------------+------+------------- + tableam_display | view_heap_psql | view | regress_display_role | permanent | | (1 row) \set HIDE_TABLEAM on @@ -2881,7 +2881,7 @@ Access method: heap tableam_display | mat_view_heap_psql | materialized view | regress_display_role | permanent | 0 bytes | tableam_display | tbl_heap | table | regress_display_role | permanent | 0 bytes | tableam_display | tbl_heap_psql | table | regress_display_role | permanent | 0 bytes | - tableam_display | view_heap_psql | view | regress_display_role | permanent | 0 bytes | + tableam_display | view_heap_psql | view | regress_display_role | permanent | | (4 rows) RESET ROLE; -- 2.25.1