| 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 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; |
