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

Commitee0202d

Browse files
committed
pgstattuple: Have pgstattuple_approx accept TOAST tables
TOAST tables have a visibility map and a free space map, so they canbe supported by pgstattuple_approx just fine.Add test cases to show how various pgstattuple functions accept TOASTtables. Also add similar tests to pg_visibility, which alreadyaccepted TOAST tables correctly but had no test coverage for them.Reviewed-by: Laurenz Albe <laurenz.albe@cybertec.at>Discussion:https://www.postgresql.org/message-id/flat/27c4496a-02b9-dc87-8f6f-bddbef54e0fe@2ndquadrant.com
1 parentea57e53 commitee0202d

File tree

5 files changed

+52
-12
lines changed

5 files changed

+52
-12
lines changed

‎contrib/pg_visibility/expected/pg_visibility.out

Lines changed: 15 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -102,22 +102,35 @@ ERROR: "test_foreign_table" is not a table, materialized view, or TOAST table
102102
select pg_truncate_visibility_map('test_foreign_table');
103103
ERROR: "test_foreign_table" is not a table, materialized view, or TOAST table
104104
-- check some of the allowed relkinds
105-
create table regular_table (a int);
106-
insert into regular_table values (1), (2);
105+
create table regular_table (a int, b text);
106+
alter table regular_table alter column b set storage external;
107+
insert into regular_table values (1, repeat('one', 1000)), (2, repeat('two', 1000));
107108
vacuum regular_table;
108109
select count(*) > 0 from pg_visibility('regular_table');
109110
?column?
110111
----------
111112
t
112113
(1 row)
113114

115+
select count(*) > 0 from pg_visibility((select reltoastrelid from pg_class where relname = 'regular_table'));
116+
?column?
117+
----------
118+
t
119+
(1 row)
120+
114121
truncate regular_table;
115122
select count(*) > 0 from pg_visibility('regular_table');
116123
?column?
117124
----------
118125
f
119126
(1 row)
120127

128+
select count(*) > 0 from pg_visibility((select reltoastrelid from pg_class where relname = 'regular_table'));
129+
?column?
130+
----------
131+
f
132+
(1 row)
133+
121134
create materialized view matview_visibility_test as select * from regular_table;
122135
vacuum matview_visibility_test;
123136
select count(*) > 0 from pg_visibility('matview_visibility_test');

‎contrib/pg_visibility/sql/pg_visibility.sql

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -68,12 +68,15 @@ select pg_check_frozen('test_foreign_table');
6868
select pg_truncate_visibility_map('test_foreign_table');
6969

7070
-- check some of the allowed relkinds
71-
createtableregular_table (aint);
72-
insert into regular_tablevalues (1), (2);
71+
createtableregular_table (aint, btext);
72+
altertable regular_table alter column bset storage external;
73+
insert into regular_tablevalues (1, repeat('one',1000)), (2, repeat('two',1000));
7374
vacuum regular_table;
7475
selectcount(*)>0from pg_visibility('regular_table');
76+
selectcount(*)>0from pg_visibility((select reltoastrelidfrom pg_classwhere relname='regular_table'));
7577
truncate regular_table;
7678
selectcount(*)>0from pg_visibility('regular_table');
79+
selectcount(*)>0from pg_visibility((select reltoastrelidfrom pg_classwhere relname='regular_table'));
7780

7881
create materialized view matview_visibility_testasselect*from regular_table;
7982
vacuum matview_visibility_test;

‎contrib/pgstattuple/expected/pgstattuple.out

Lines changed: 22 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -159,7 +159,7 @@ ERROR: "test_partitioned" (partitioned table) is not supported
159159
select pgstattuple('test_partitioned_index');
160160
ERROR: "test_partitioned_index" (partitioned index) is not supported
161161
select pgstattuple_approx('test_partitioned');
162-
ERROR: "test_partitioned" is not a table ormaterialized view
162+
ERROR: "test_partitioned" is not a table,materialized view, or TOAST table
163163
select pg_relpages('test_partitioned');
164164
ERROR: "test_partitioned" is not a table, index, materialized view, sequence, or TOAST table
165165
select pgstatindex('test_partitioned');
@@ -173,7 +173,7 @@ create view test_view as select 1;
173173
select pgstattuple('test_view');
174174
ERROR: "test_view" (view) is not supported
175175
select pgstattuple_approx('test_view');
176-
ERROR: "test_view" is not a table ormaterialized view
176+
ERROR: "test_view" is not a table,materialized view, or TOAST table
177177
select pg_relpages('test_view');
178178
ERROR: "test_view" is not a table, index, materialized view, sequence, or TOAST table
179179
select pgstatindex('test_view');
@@ -189,7 +189,7 @@ create foreign table test_foreign_table () server dummy_server;
189189
select pgstattuple('test_foreign_table');
190190
ERROR: "test_foreign_table" (foreign table) is not supported
191191
select pgstattuple_approx('test_foreign_table');
192-
ERROR: "test_foreign_table" is not a table ormaterialized view
192+
ERROR: "test_foreign_table" is not a table,materialized view, or TOAST table
193193
select pg_relpages('test_foreign_table');
194194
ERROR: "test_foreign_table" is not a table, index, materialized view, sequence, or TOAST table
195195
select pgstatindex('test_foreign_table');
@@ -218,6 +218,25 @@ select pg_relpages('test_partition');
218218
0
219219
(1 row)
220220

221+
-- toast tables should work
222+
select pgstattuple((select reltoastrelid from pg_class where relname = 'test'));
223+
pgstattuple
224+
---------------------
225+
(0,0,0,0,0,0,0,0,0)
226+
(1 row)
227+
228+
select pgstattuple_approx((select reltoastrelid from pg_class where relname = 'test'));
229+
pgstattuple_approx
230+
-----------------------
231+
(0,0,0,0,0,0,0,0,0,0)
232+
(1 row)
233+
234+
select pg_relpages((select reltoastrelid from pg_class where relname = 'test'));
235+
pg_relpages
236+
-------------
237+
0
238+
(1 row)
239+
221240
-- not for the index calls though, of course
222241
select pgstatindex('test_partition');
223242
ERROR: relation "test_partition" is not a btree index

‎contrib/pgstattuple/pgstatapprox.c

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -278,15 +278,15 @@ pgstattuple_approx_internal(Oid relid, FunctionCallInfo fcinfo)
278278
errmsg("cannot access temporary tables of other sessions")));
279279

280280
/*
281-
* We support only ordinary relations and materialised views, because we
282-
* depend on the visibility map and free space map for our estimates about
283-
* unscanned pages.
281+
* We support only relation kinds with a visibility map and a free space
282+
* map.
284283
*/
285284
if (!(rel->rd_rel->relkind==RELKIND_RELATION||
286-
rel->rd_rel->relkind==RELKIND_MATVIEW))
285+
rel->rd_rel->relkind==RELKIND_MATVIEW||
286+
rel->rd_rel->relkind==RELKIND_TOASTVALUE))
287287
ereport(ERROR,
288288
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
289-
errmsg("\"%s\" is not a table ormaterialized view",
289+
errmsg("\"%s\" is not a table,materialized view, or TOAST table",
290290
RelationGetRelationName(rel))));
291291

292292
if (rel->rd_rel->relam!=HEAP_TABLE_AM_OID)

‎contrib/pgstattuple/sql/pgstattuple.sql

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -100,6 +100,11 @@ select pgstattuple('test_partition');
100100
select pgstattuple_approx('test_partition');
101101
select pg_relpages('test_partition');
102102

103+
-- toast tables should work
104+
select pgstattuple((select reltoastrelidfrom pg_classwhere relname='test'));
105+
select pgstattuple_approx((select reltoastrelidfrom pg_classwhere relname='test'));
106+
select pg_relpages((select reltoastrelidfrom pg_classwhere relname='test'));
107+
103108
-- not for the index calls though, of course
104109
select pgstatindex('test_partition');
105110
select pgstatginindex('test_partition');

0 commit comments

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