Hello,
I propose new simple sql query, which shows total block numbers in the
relation.
I now reviewing this patch (https://commitfest.postgresql.org/25/2211/)
and I think,
it is usefull for knowing how many blocks there are in the relation to
determine whether we use VACUUM RESUME or not.
Of cource, we can know this value such as
select (pg_relation_size('t') /
current_setting('block_size')::bigint)::int;
but I think it is a litte bit complex.
Comment and feedback are very welcome.
Regards ,
Yu Kimura
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 28eb322f3f..99834e7286 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -21290,6 +21290,9 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
<indexterm>
<primary>pg_relation_size</primary>
</indexterm>
+ <indexterm>
+ <primary>pg_relation_block_number</primary>
+ </indexterm>
<indexterm>
<primary>pg_size_bytes</primary>
</indexterm>
@@ -21363,6 +21366,15 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
Shorthand for <literal>pg_relation_size(..., 'main')</literal>
</entry>
</row>
+ <row>
+ <entry>
+ <literal><function>pg_relation_block_number(<parameter>relation</parameter> <type>regclass</type>)</function></literal>
+ </entry>
+ <entry><type>bigint</type></entry>
+ <entry>
+ Shorthand for <literal>pg_relation_block_number(..., 'main')</literal>
+ </entry>
+ </row>
<row>
<entry>
<literal><function>pg_size_bytes(<type>text</type>)</function></literal>
@@ -21504,6 +21516,11 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
</itemizedlist>
</para>
+ <para>
+ <function>pg_relation_block_number</function> accepts the OID or name of a table
+ and returns the number of blocks of that relation.
+ </para>
+
<para>
<function>pg_size_pretty</function> can be used to format the result of one of
the other functions in a human-readable way, using bytes, kB, MB, GB or TB
diff --git a/src/backend/utils/adt/dbsize.c b/src/backend/utils/adt/dbsize.c
index a87e7214e9..2462d65570 100644
--- a/src/backend/utils/adt/dbsize.c
+++ b/src/backend/utils/adt/dbsize.c
@@ -23,6 +23,7 @@
#include "commands/tablespace.h"
#include "miscadmin.h"
#include "storage/fd.h"
+#include "storage/bufmgr.h"
#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/numeric.h"
@@ -335,6 +336,25 @@ pg_relation_size(PG_FUNCTION_ARGS)
PG_RETURN_INT64(size);
}
+Datum
+pg_relation_block_number(PG_FUNCTION_ARGS)
+{
+ Oid relOid = PG_GETARG_OID(0);
+ Relation rel;
+ int64 size;
+
+ rel = try_relation_open(relOid, AccessShareLock);
+
+ if (rel == NULL)
+ PG_RETURN_NULL();
+
+ size = RelationGetNumberOfBlocks(rel);
+
+ relation_close(rel, AccessShareLock);
+
+ PG_RETURN_INT64(size);
+}
+
/*
* Calculate total on-disk size of a TOAST relation, including its indexes.
* Must not be applied to non-TOAST relations.
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 58ea5b982b..b68a523d3b 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6929,6 +6929,10 @@
descr => 'disk space usage for the specified fork of a table or index',
proname => 'pg_relation_size', provolatile => 'v', prorettype => 'int8',
proargtypes => 'regclass text', prosrc => 'pg_relation_size' },
+{ oid => '2228',
+ descr => 'total block number for the main fork of the specified table or index',
+ proname => 'pg_relation_block_number', provolatile => 'v', prorettype => 'int8',
+ proargtypes => 'regclass', prosrc => 'pg_relation_block_number' },
{ oid => '2286',
descr => 'total disk space usage for the specified table and associated indexes',
proname => 'pg_total_relation_size', provolatile => 'v', prorettype => 'int8',
