diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/vacuum.out | 34 | ||||
| -rw-r--r-- | src/test/regress/sql/vacuum.sql | 34 |
2 files changed, 60 insertions, 8 deletions
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out index f944b93fd6f..02c53e30589 100644 --- a/src/test/regress/expected/vacuum.out +++ b/src/test/regress/expected/vacuum.out @@ -93,13 +93,39 @@ SQL function "wrap_do_analyze" statement 1 VACUUM FULL vactst; VACUUM (DISABLE_PAGE_SKIPPING) vaccluster; -- INDEX_CLEANUP option -CREATE TABLE no_index_cleanup (i INT PRIMARY KEY) WITH (vacuum_index_cleanup = false); -VACUUM (INDEX_CLEANUP FALSE) vaccluster; -VACUUM (INDEX_CLEANUP FALSE) vactst; -- index cleanup option is ignored if no indexes -VACUUM (INDEX_CLEANUP FALSE, FREEZE TRUE) vaccluster; +CREATE TABLE no_index_cleanup (i INT PRIMARY KEY, t TEXT); +-- Use uncompressed data stored in toast. +CREATE INDEX no_index_cleanup_idx ON no_index_cleanup(t); +ALTER TABLE no_index_cleanup ALTER COLUMN t SET STORAGE EXTERNAL; +INSERT INTO no_index_cleanup(i, t) VALUES (generate_series(1,30), + repeat('1234567890',300)); -- index cleanup option is ignored if VACUUM FULL VACUUM (INDEX_CLEANUP TRUE, FULL TRUE) no_index_cleanup; VACUUM (FULL TRUE) no_index_cleanup; +-- Toast inherits the value from its parent table. +ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = false); +DELETE FROM no_index_cleanup WHERE i < 15; +-- Nothing is cleaned up. +VACUUM no_index_cleanup; +-- Both parent relation and toast are cleaned up. +ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = true); +VACUUM no_index_cleanup; +-- Parameter is set for both the parent table and its toast relation. +INSERT INTO no_index_cleanup(i, t) VALUES (generate_series(31,60), + repeat('1234567890',300)); +DELETE FROM no_index_cleanup WHERE i < 45; +-- Only toast index is cleaned up. +ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = false, + toast.vacuum_index_cleanup = true); +VACUUM no_index_cleanup; +-- Only parent is cleaned up. +ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = true, + toast.vacuum_index_cleanup = false); +VACUUM no_index_cleanup; +-- Test some extra relations. +VACUUM (INDEX_CLEANUP FALSE) vaccluster; +VACUUM (INDEX_CLEANUP FALSE) vactst; -- index cleanup option is ignored if no indexes +VACUUM (INDEX_CLEANUP FALSE, FREEZE TRUE) vaccluster; -- TRUNCATE option CREATE TABLE vac_truncate_test(i INT NOT NULL, j text) WITH (vacuum_truncate=true, autovacuum_enabled=false); diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql index 16748e1823f..6ffb4955468 100644 --- a/src/test/regress/sql/vacuum.sql +++ b/src/test/regress/sql/vacuum.sql @@ -76,13 +76,39 @@ VACUUM FULL vactst; VACUUM (DISABLE_PAGE_SKIPPING) vaccluster; -- INDEX_CLEANUP option -CREATE TABLE no_index_cleanup (i INT PRIMARY KEY) WITH (vacuum_index_cleanup = false); -VACUUM (INDEX_CLEANUP FALSE) vaccluster; -VACUUM (INDEX_CLEANUP FALSE) vactst; -- index cleanup option is ignored if no indexes -VACUUM (INDEX_CLEANUP FALSE, FREEZE TRUE) vaccluster; +CREATE TABLE no_index_cleanup (i INT PRIMARY KEY, t TEXT); +-- Use uncompressed data stored in toast. +CREATE INDEX no_index_cleanup_idx ON no_index_cleanup(t); +ALTER TABLE no_index_cleanup ALTER COLUMN t SET STORAGE EXTERNAL; +INSERT INTO no_index_cleanup(i, t) VALUES (generate_series(1,30), + repeat('1234567890',300)); -- index cleanup option is ignored if VACUUM FULL VACUUM (INDEX_CLEANUP TRUE, FULL TRUE) no_index_cleanup; VACUUM (FULL TRUE) no_index_cleanup; +-- Toast inherits the value from its parent table. +ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = false); +DELETE FROM no_index_cleanup WHERE i < 15; +-- Nothing is cleaned up. +VACUUM no_index_cleanup; +-- Both parent relation and toast are cleaned up. +ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = true); +VACUUM no_index_cleanup; +-- Parameter is set for both the parent table and its toast relation. +INSERT INTO no_index_cleanup(i, t) VALUES (generate_series(31,60), + repeat('1234567890',300)); +DELETE FROM no_index_cleanup WHERE i < 45; +-- Only toast index is cleaned up. +ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = false, + toast.vacuum_index_cleanup = true); +VACUUM no_index_cleanup; +-- Only parent is cleaned up. +ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = true, + toast.vacuum_index_cleanup = false); +VACUUM no_index_cleanup; +-- Test some extra relations. +VACUUM (INDEX_CLEANUP FALSE) vaccluster; +VACUUM (INDEX_CLEANUP FALSE) vactst; -- index cleanup option is ignored if no indexes +VACUUM (INDEX_CLEANUP FALSE, FREEZE TRUE) vaccluster; -- TRUNCATE option CREATE TABLE vac_truncate_test(i INT NOT NULL, j text) |