I alluded to this in [0], but it's better discussed in its own thread.
I think the check that makes pgstattuple_approx reject TOAST tables is a
mistake. They have visibility and free space map, and it works just
fine if the check is removed.
Attached is a patch to fix this and add some tests related to how
pgstattuple and pg_visibility accept TOAST tables for inspection.
[0]:
https://www.postgresql.org/message-id/dc35a398-37d0-75ce-07ea-1dd71d98f...@2ndquadrant.com
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From cd2830bfc7159bdbf52541c9a2faef15a48886ec Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Fri, 17 Apr 2020 12:39:39 +0200
Subject: [PATCH] pgstattuple: Have pgstattuple_approx accept TOAST tables
TOAST tables have a visibility map and a free space map, so they can
be supported by pgstattuple_approx just fine.
Add test cases to show how various pgstattuple functions accept TOAST
tables. Also add similar tests to pg_visibility, which already
accepted TOAST tables correctly but had no test coverage for them.
---
.../pg_visibility/expected/pg_visibility.out | 17 +++++++++++--
contrib/pg_visibility/sql/pg_visibility.sql | 7 ++++--
contrib/pgstattuple/expected/pgstattuple.out | 25 ++++++++++++++++---
contrib/pgstattuple/pgstatapprox.c | 10 ++++----
contrib/pgstattuple/sql/pgstattuple.sql | 5 ++++
5 files changed, 52 insertions(+), 12 deletions(-)
diff --git a/contrib/pg_visibility/expected/pg_visibility.out
b/contrib/pg_visibility/expected/pg_visibility.out
index 2abc1b5107..ca4b6e186b 100644
--- a/contrib/pg_visibility/expected/pg_visibility.out
+++ b/contrib/pg_visibility/expected/pg_visibility.out
@@ -102,8 +102,9 @@ ERROR: "test_foreign_table" is not a table, materialized
view, or TOAST table
select pg_truncate_visibility_map('test_foreign_table');
ERROR: "test_foreign_table" is not a table, materialized view, or TOAST table
-- check some of the allowed relkinds
-create table regular_table (a int);
-insert into regular_table values (1), (2);
+create table regular_table (a int, b text);
+alter table regular_table alter column b set storage external;
+insert into regular_table values (1, repeat('one', 1000)), (2, repeat('two',
1000));
vacuum regular_table;
select count(*) > 0 from pg_visibility('regular_table');
?column?
@@ -111,6 +112,12 @@ select count(*) > 0 from pg_visibility('regular_table');
t
(1 row)
+select count(*) > 0 from pg_visibility((select reltoastrelid from pg_class
where relname = 'regular_table'));
+ ?column?
+----------
+ t
+(1 row)
+
truncate regular_table;
select count(*) > 0 from pg_visibility('regular_table');
?column?
@@ -118,6 +125,12 @@ select count(*) > 0 from pg_visibility('regular_table');
f
(1 row)
+select count(*) > 0 from pg_visibility((select reltoastrelid from pg_class
where relname = 'regular_table'));
+ ?column?
+----------
+ f
+(1 row)
+
create materialized view matview_visibility_test as select * from
regular_table;
vacuum matview_visibility_test;
select count(*) > 0 from pg_visibility('matview_visibility_test');
diff --git a/contrib/pg_visibility/sql/pg_visibility.sql
b/contrib/pg_visibility/sql/pg_visibility.sql
index c78b90521b..f79b54480b 100644
--- a/contrib/pg_visibility/sql/pg_visibility.sql
+++ b/contrib/pg_visibility/sql/pg_visibility.sql
@@ -68,12 +68,15 @@ CREATE TABLE droppedtest (c int);
select pg_truncate_visibility_map('test_foreign_table');
-- check some of the allowed relkinds
-create table regular_table (a int);
-insert into regular_table values (1), (2);
+create table regular_table (a int, b text);
+alter table regular_table alter column b set storage external;
+insert into regular_table values (1, repeat('one', 1000)), (2, repeat('two',
1000));
vacuum regular_table;
select count(*) > 0 from pg_visibility('regular_table');
+select count(*) > 0 from pg_visibility((select reltoastrelid from pg_class
where relname = 'regular_table'));
truncate regular_table;
select count(*) > 0 from pg_visibility('regular_table');
+select count(*) > 0 from pg_visibility((select reltoastrelid from pg_class
where relname = 'regular_table'));
create materialized view matview_visibility_test as select * from
regular_table;
vacuum matview_visibility_test;
diff --git a/contrib/pgstattuple/expected/pgstattuple.out
b/contrib/pgstattuple/expected/pgstattuple.out
index 9920dbfd40..40f7825ddb 100644
--- a/contrib/pgstattuple/expected/pgstattuple.out
+++ b/contrib/pgstattuple/expected/pgstattuple.out
@@ -159,7 +159,7 @@ ERROR: "test_partitioned" (partitioned table) is not
supported
select pgstattuple('test_partitioned_index');
ERROR: "test_partitioned_index" (partitioned index) is not supported
select pgstattuple_approx('test_partitioned');
-ERROR: "test_partitioned" is not a table or materialized view
+ERROR: "test_partitioned" is not a table, materialized view, or TOAST table
select pg_relpages('test_partitioned');
ERROR: "test_partitioned" is not a table, index, materialized view, sequence,
or TOAST table
select pgstatindex('test_partitioned');
@@ -173,7 +173,7 @@ create view test_view as select 1;
select pgstattuple('test_view');
ERROR: "test_view" (view) is not supported
select pgstattuple_approx('test_view');
-ERROR: "test_view" is not a table or materialized view
+ERROR: "test_view" is not a table, materialized view, or TOAST table
select pg_relpages('test_view');
ERROR: "test_view" is not a table, index, materialized view, sequence, or
TOAST table
select pgstatindex('test_view');
@@ -189,7 +189,7 @@ create foreign table test_foreign_table () server
dummy_server;
select pgstattuple('test_foreign_table');
ERROR: "test_foreign_table" (foreign table) is not supported
select pgstattuple_approx('test_foreign_table');
-ERROR: "test_foreign_table" is not a table or materialized view
+ERROR: "test_foreign_table" is not a table, materialized view, or TOAST table
select pg_relpages('test_foreign_table');
ERROR: "test_foreign_table" is not a table, index, materialized view,
sequence, or TOAST table
select pgstatindex('test_foreign_table');
@@ -218,6 +218,25 @@ select pg_relpages('test_partition');
0
(1 row)
+-- toast tables should work
+select pgstattuple((select reltoastrelid from pg_class where relname =
'test'));
+ pgstattuple
+---------------------
+ (0,0,0,0,0,0,0,0,0)
+(1 row)
+
+select pgstattuple_approx((select reltoastrelid from pg_class where relname =
'test'));
+ pgstattuple_approx
+-----------------------
+ (0,0,0,0,0,0,0,0,0,0)
+(1 row)
+
+select pg_relpages((select reltoastrelid from pg_class where relname =
'test'));
+ pg_relpages
+-------------
+ 0
+(1 row)
+
-- not for the index calls though, of course
select pgstatindex('test_partition');
ERROR: relation "test_partition" is not a btree index
diff --git a/contrib/pgstattuple/pgstatapprox.c
b/contrib/pgstattuple/pgstatapprox.c
index 96d837485f..dbc0fa11f6 100644
--- a/contrib/pgstattuple/pgstatapprox.c
+++ b/contrib/pgstattuple/pgstatapprox.c
@@ -278,15 +278,15 @@ pgstattuple_approx_internal(Oid relid, FunctionCallInfo
fcinfo)
errmsg("cannot access temporary tables of
other sessions")));
/*
- * We support only ordinary relations and materialised views, because we
- * depend on the visibility map and free space map for our estimates
about
- * unscanned pages.
+ * We support only relation kinds with a visibility map and a free space
+ * map.
*/
if (!(rel->rd_rel->relkind == RELKIND_RELATION ||
- rel->rd_rel->relkind == RELKIND_MATVIEW))
+ rel->rd_rel->relkind == RELKIND_MATVIEW ||
+ rel->rd_rel->relkind == RELKIND_TOASTVALUE))
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("\"%s\" is not a table or materialized
view",
+ errmsg("\"%s\" is not a table, materialized
view, or TOAST table",
RelationGetRelationName(rel))));
if (rel->rd_rel->relam != HEAP_TABLE_AM_OID)
diff --git a/contrib/pgstattuple/sql/pgstattuple.sql
b/contrib/pgstattuple/sql/pgstattuple.sql
index cfa540302d..5111be0e62 100644
--- a/contrib/pgstattuple/sql/pgstattuple.sql
+++ b/contrib/pgstattuple/sql/pgstattuple.sql
@@ -100,6 +100,11 @@ CREATE EXTENSION pgstattuple;
select pgstattuple_approx('test_partition');
select pg_relpages('test_partition');
+-- toast tables should work
+select pgstattuple((select reltoastrelid from pg_class where relname =
'test'));
+select pgstattuple_approx((select reltoastrelid from pg_class where relname =
'test'));
+select pg_relpages((select reltoastrelid from pg_class where relname =
'test'));
+
-- not for the index calls though, of course
select pgstatindex('test_partition');
select pgstatginindex('test_partition');
--
2.26.1
