diff options
Diffstat (limited to '')
| -rw-r--r-- | contrib/pg_visibility/expected/pg_visibility.out | 279 |
1 files changed, 279 insertions, 0 deletions
diff --git a/contrib/pg_visibility/expected/pg_visibility.out b/contrib/pg_visibility/expected/pg_visibility.out new file mode 100644 index 0000000..9de54db --- /dev/null +++ b/contrib/pg_visibility/expected/pg_visibility.out @@ -0,0 +1,279 @@ +CREATE EXTENSION pg_visibility; +-- +-- recently-dropped table +-- +\set VERBOSITY sqlstate +BEGIN; +CREATE TABLE droppedtest (c int); +SELECT 'droppedtest'::regclass::oid AS oid \gset +SAVEPOINT q; DROP TABLE droppedtest; RELEASE q; +SAVEPOINT q; SELECT * FROM pg_visibility_map(:oid); ROLLBACK TO q; +ERROR: XX000 +-- ERROR: could not open relation with OID 16xxx +SAVEPOINT q; SELECT 1; ROLLBACK TO q; + ?column? +---------- + 1 +(1 row) + +SAVEPOINT q; SELECT 1; ROLLBACK TO q; + ?column? +---------- + 1 +(1 row) + +SELECT pg_relation_size(:oid), pg_relation_filepath(:oid), + has_table_privilege(:oid, 'SELECT'); + pg_relation_size | pg_relation_filepath | has_table_privilege +------------------+----------------------+--------------------- + | | +(1 row) + +SELECT * FROM pg_visibility_map(:oid); +ERROR: XX000 +-- ERROR: could not open relation with OID 16xxx +ROLLBACK; +\set VERBOSITY default +-- +-- check that using the module's functions with unsupported relations will fail +-- +-- partitioned tables (the parent ones) don't have visibility maps +create table test_partitioned (a int) partition by list (a); +-- these should all fail +select pg_visibility('test_partitioned', 0); +ERROR: relation "test_partitioned" is of wrong relation kind +DETAIL: This operation is not supported for partitioned tables. +select pg_visibility_map('test_partitioned'); +ERROR: relation "test_partitioned" is of wrong relation kind +DETAIL: This operation is not supported for partitioned tables. +select pg_visibility_map_summary('test_partitioned'); +ERROR: relation "test_partitioned" is of wrong relation kind +DETAIL: This operation is not supported for partitioned tables. +select pg_check_frozen('test_partitioned'); +ERROR: relation "test_partitioned" is of wrong relation kind +DETAIL: This operation is not supported for partitioned tables. +select pg_truncate_visibility_map('test_partitioned'); +ERROR: relation "test_partitioned" is of wrong relation kind +DETAIL: This operation is not supported for partitioned tables. +create table test_partition partition of test_partitioned for values in (1); +create index test_index on test_partition (a); +-- indexes do not, so these all fail +select pg_visibility('test_index', 0); +ERROR: relation "test_index" is of wrong relation kind +DETAIL: This operation is not supported for indexes. +select pg_visibility_map('test_index'); +ERROR: relation "test_index" is of wrong relation kind +DETAIL: This operation is not supported for indexes. +select pg_visibility_map_summary('test_index'); +ERROR: relation "test_index" is of wrong relation kind +DETAIL: This operation is not supported for indexes. +select pg_check_frozen('test_index'); +ERROR: relation "test_index" is of wrong relation kind +DETAIL: This operation is not supported for indexes. +select pg_truncate_visibility_map('test_index'); +ERROR: relation "test_index" is of wrong relation kind +DETAIL: This operation is not supported for indexes. +create view test_view as select 1; +-- views do not have VMs, so these all fail +select pg_visibility('test_view', 0); +ERROR: relation "test_view" is of wrong relation kind +DETAIL: This operation is not supported for views. +select pg_visibility_map('test_view'); +ERROR: relation "test_view" is of wrong relation kind +DETAIL: This operation is not supported for views. +select pg_visibility_map_summary('test_view'); +ERROR: relation "test_view" is of wrong relation kind +DETAIL: This operation is not supported for views. +select pg_check_frozen('test_view'); +ERROR: relation "test_view" is of wrong relation kind +DETAIL: This operation is not supported for views. +select pg_truncate_visibility_map('test_view'); +ERROR: relation "test_view" is of wrong relation kind +DETAIL: This operation is not supported for views. +create sequence test_sequence; +-- sequences do not have VMs, so these all fail +select pg_visibility('test_sequence', 0); +ERROR: relation "test_sequence" is of wrong relation kind +DETAIL: This operation is not supported for sequences. +select pg_visibility_map('test_sequence'); +ERROR: relation "test_sequence" is of wrong relation kind +DETAIL: This operation is not supported for sequences. +select pg_visibility_map_summary('test_sequence'); +ERROR: relation "test_sequence" is of wrong relation kind +DETAIL: This operation is not supported for sequences. +select pg_check_frozen('test_sequence'); +ERROR: relation "test_sequence" is of wrong relation kind +DETAIL: This operation is not supported for sequences. +select pg_truncate_visibility_map('test_sequence'); +ERROR: relation "test_sequence" is of wrong relation kind +DETAIL: This operation is not supported for sequences. +create foreign data wrapper dummy; +create server dummy_server foreign data wrapper dummy; +create foreign table test_foreign_table () server dummy_server; +-- foreign tables do not have VMs, so these all fail +select pg_visibility('test_foreign_table', 0); +ERROR: relation "test_foreign_table" is of wrong relation kind +DETAIL: This operation is not supported for foreign tables. +select pg_visibility_map('test_foreign_table'); +ERROR: relation "test_foreign_table" is of wrong relation kind +DETAIL: This operation is not supported for foreign tables. +select pg_visibility_map_summary('test_foreign_table'); +ERROR: relation "test_foreign_table" is of wrong relation kind +DETAIL: This operation is not supported for foreign tables. +select pg_check_frozen('test_foreign_table'); +ERROR: relation "test_foreign_table" is of wrong relation kind +DETAIL: This operation is not supported for foreign tables. +select pg_truncate_visibility_map('test_foreign_table'); +ERROR: relation "test_foreign_table" is of wrong relation kind +DETAIL: This operation is not supported for foreign tables. +-- check some of the allowed relkinds +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 (disable_page_skipping) regular_table; +select count(*) > 0 from pg_visibility('regular_table'); + ?column? +---------- + 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? +---------- + 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 (disable_page_skipping) matview_visibility_test; +select count(*) > 0 from pg_visibility('matview_visibility_test'); + ?column? +---------- + f +(1 row) + +insert into regular_table values (1), (2); +refresh materialized view matview_visibility_test; +select count(*) > 0 from pg_visibility('matview_visibility_test'); + ?column? +---------- + t +(1 row) + +-- regular tables which are part of a partition *do* have visibility maps +insert into test_partition values (1); +vacuum (disable_page_skipping) test_partition; +select count(*) > 0 from pg_visibility('test_partition', 0); + ?column? +---------- + t +(1 row) + +select count(*) > 0 from pg_visibility_map('test_partition'); + ?column? +---------- + t +(1 row) + +select count(*) > 0 from pg_visibility_map_summary('test_partition'); + ?column? +---------- + t +(1 row) + +select * from pg_check_frozen('test_partition'); -- hopefully none + t_ctid +-------- +(0 rows) + +select pg_truncate_visibility_map('test_partition'); + pg_truncate_visibility_map +---------------------------- + +(1 row) + +-- test copy freeze +create table copyfreeze (a int, b char(1500)); +-- load all rows via COPY FREEZE and ensure that all pages are set all-visible +-- and all-frozen. +begin; +truncate copyfreeze; +copy copyfreeze from stdin freeze; +commit; +select * from pg_visibility_map('copyfreeze'); + blkno | all_visible | all_frozen +-------+-------------+------------ + 0 | t | t + 1 | t | t + 2 | t | t +(3 rows) + +select * from pg_check_frozen('copyfreeze'); + t_ctid +-------- +(0 rows) + +-- load half the rows via regular COPY and rest via COPY FREEZE. The pages +-- which are touched by regular COPY must not be set all-visible/all-frozen. On +-- the other hand, pages allocated by COPY FREEZE should be marked +-- all-frozen/all-visible. +begin; +truncate copyfreeze; +copy copyfreeze from stdin; +copy copyfreeze from stdin freeze; +commit; +select * from pg_visibility_map('copyfreeze'); + blkno | all_visible | all_frozen +-------+-------------+------------ + 0 | f | f + 1 | f | f + 2 | t | t +(3 rows) + +select * from pg_check_frozen('copyfreeze'); + t_ctid +-------- +(0 rows) + +-- Try a mix of regular COPY and COPY FREEZE. +begin; +truncate copyfreeze; +copy copyfreeze from stdin freeze; +copy copyfreeze from stdin; +copy copyfreeze from stdin freeze; +commit; +select * from pg_visibility_map('copyfreeze'); + blkno | all_visible | all_frozen +-------+-------------+------------ + 0 | t | t + 1 | f | f + 2 | t | t +(3 rows) + +select * from pg_check_frozen('copyfreeze'); + t_ctid +-------- +(0 rows) + +-- cleanup +drop table test_partitioned; +drop view test_view; +drop sequence test_sequence; +drop foreign table test_foreign_table; +drop server dummy_server; +drop foreign data wrapper dummy; +drop materialized view matview_visibility_test; +drop table regular_table; +drop table copyfreeze; |