postgres.git / summary / log / commit / refs
commit a569eea6998a26689409ff6eb5fce2c1eab0f5e1
Author: Michael Paquier <michael@paquier.xyz>
Date: Fri Aug 24 00:15:08 2018 +0000
Add more tests for VACUUM skips with partitioned tables
A VACUUM or ANALYZE command listing directly a partitioned table expands
it to its partitions, causing all elements of a tree to be processed
with individual ownership checks done. This results in different
relation skips depending on the ownership policy of a tree, which may
not be consistent for a partition tree. This commit adds more tests to
ensure that any future refactoring allows to keep a consistent behavior,
or at least that any changes done are easily identified and checked.
The current behavior of VACUUM with partitioned tables is present since
10.
Author: Nathan Bossart
Reviewed-by: Michael Paquier
Discussion: https://postgr.es/m/DC186201-B01F-4A66-9EC4-F855A957C1F9@amazon.com
src/test/regress/expected/vacuum.out | 99 ++++++++++++++++++++++++++++++++++++
src/test/regress/sql/vacuum.sql | 57 +++++++++++++++++++++
2 files changed, 156 insertions(+)
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index c9be71ef60b..5993a902472 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -124,6 +124,9 @@ DROP TABLE vactst;
DROP TABLE vacparted;
-- relation ownership, WARNING logs generated as all are skipped.
CREATE TABLE vacowned (a int);
+CREATE TABLE vacowned_parted (a int) PARTITION BY LIST (a);
+CREATE TABLE vacowned_part1 PARTITION OF vacowned_parted FOR VALUES IN (1);
+CREATE TABLE vacowned_part2 PARTITION OF vacowned_parted FOR VALUES IN (2);
CREATE ROLE regress_vacuum;
SET ROLE regress_vacuum;
-- Simple table
@@ -147,6 +150,102 @@ ANALYZE pg_catalog.pg_authid;
WARNING: skipping "pg_authid" --- only superuser can analyze it
VACUUM (ANALYZE) pg_catalog.pg_authid;
WARNING: skipping "pg_authid" --- only superuser can vacuum it
+-- Partitioned table and its partitions, nothing owned by other user.
+-- Relations are not listed in a single command to test ownership
+-- independently.
+VACUUM vacowned_parted;
+WARNING: skipping "vacowned_parted" --- only table or database owner can vacuum it
+WARNING: skipping "vacowned_part1" --- only table or database owner can vacuum it
+WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+VACUUM vacowned_part1;
+WARNING: skipping "vacowned_part1" --- only table or database owner can vacuum it
+VACUUM vacowned_part2;
+WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+ANALYZE vacowned_parted;
+WARNING: skipping "vacowned_parted" --- only table or database owner can analyze it
+WARNING: skipping "vacowned_part1" --- only table or database owner can analyze it
+WARNING: skipping "vacowned_part2" --- only table or database owner can analyze it
+ANALYZE vacowned_part1;
+WARNING: skipping "vacowned_part1" --- only table or database owner can analyze it
+ANALYZE vacowned_part2;
+WARNING: skipping "vacowned_part2" --- only table or database owner can analyze it
+VACUUM (ANALYZE) vacowned_parted;
+WARNING: skipping "vacowned_parted" --- only table or database owner can vacuum it
+WARNING: skipping "vacowned_part1" --- only table or database owner can vacuum it
+WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+VACUUM (ANALYZE) vacowned_part1;
+WARNING: skipping "vacowned_part1" --- only table or database owner can vacuum it
+VACUUM (ANALYZE) vacowned_part2;
+WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+RESET ROLE;
+-- Partitioned table and one partition owned by other user.
+ALTER TABLE vacowned_parted OWNER TO regress_vacuum;
+ALTER TABLE vacowned_part1 OWNER TO regress_vacuum;
+SET ROLE regress_vacuum;
+VACUUM vacowned_parted;
+WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+VACUUM vacowned_part1;
+VACUUM vacowned_part2;
+WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+ANALYZE vacowned_parted;
+WARNING: skipping "vacowned_part2" --- only table or database owner can analyze it
+ANALYZE vacowned_part1;
+ANALYZE vacowned_part2;
+WARNING: skipping "vacowned_part2" --- only table or database owner can analyze it
+VACUUM (ANALYZE) vacowned_parted;
+WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+VACUUM (ANALYZE) vacowned_part1;
+VACUUM (ANALYZE) vacowned_part2;
+WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+RESET ROLE;
+-- Only one partition owned by other user.
+ALTER TABLE vacowned_parted OWNER TO CURRENT_USER;
+SET ROLE regress_vacuum;
+VACUUM vacowned_parted;
+WARNING: skipping "vacowned_parted" --- only table or database owner can vacuum it
+WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+VACUUM vacowned_part1;
+VACUUM vacowned_part2;
+WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+ANALYZE vacowned_parted;
+WARNING: skipping "vacowned_parted" --- only table or database owner can analyze it
+WARNING: skipping "vacowned_part2" --- only table or database owner can analyze it
+ANALYZE vacowned_part1;
+ANALYZE vacowned_part2;
+WARNING: skipping "vacowned_part2" --- only table or database owner can analyze it
+VACUUM (ANALYZE) vacowned_parted;
+WARNING: skipping "vacowned_parted" --- only table or database owner can vacuum it
+WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+VACUUM (ANALYZE) vacowned_part1;
+VACUUM (ANALYZE) vacowned_part2;
+WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+RESET ROLE;
+-- Only partitioned table owned by other user.
+ALTER TABLE vacowned_parted OWNER TO regress_vacuum;
+ALTER TABLE vacowned_part1 OWNER TO CURRENT_USER;
+SET ROLE regress_vacuum;
+VACUUM vacowned_parted;
+WARNING: skipping "vacowned_part1" --- only table or database owner can vacuum it
+WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+VACUUM vacowned_part1;
+WARNING: skipping "vacowned_part1" --- only table or database owner can vacuum it
+VACUUM vacowned_part2;
+WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+ANALYZE vacowned_parted;
+WARNING: skipping "vacowned_part1" --- only table or database owner can analyze it
+WARNING: skipping "vacowned_part2" --- only table or database owner can analyze it
+ANALYZE vacowned_part1;
+WARNING: skipping "vacowned_part1" --- only table or database owner can analyze it
+ANALYZE vacowned_part2;
+WARNING: skipping "vacowned_part2" --- only table or database owner can analyze it
+VACUUM (ANALYZE) vacowned_parted;
+WARNING: skipping "vacowned_part1" --- only table or database owner can vacuum it
+WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+VACUUM (ANALYZE) vacowned_part1;
+WARNING: skipping "vacowned_part1" --- only table or database owner can vacuum it
+VACUUM (ANALYZE) vacowned_part2;
+WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
RESET ROLE;
DROP TABLE vacowned;
+DROP TABLE vacowned_parted;
DROP ROLE regress_vacuum;
diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql
index 0feff7c4136..7f74da3cbd8 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -99,6 +99,9 @@ DROP TABLE vacparted;
-- relation ownership, WARNING logs generated as all are skipped.
CREATE TABLE vacowned (a int);
+CREATE TABLE vacowned_parted (a int) PARTITION BY LIST (a);
+CREATE TABLE vacowned_part1 PARTITION OF vacowned_parted FOR VALUES IN (1);
+CREATE TABLE vacowned_part2 PARTITION OF vacowned_parted FOR VALUES IN (2);
CREATE ROLE regress_vacuum;
SET ROLE regress_vacuum;
-- Simple table
@@ -113,6 +116,60 @@ VACUUM (ANALYZE) pg_catalog.pg_class;
VACUUM pg_catalog.pg_authid;
ANALYZE pg_catalog.pg_authid;
VACUUM (ANALYZE) pg_catalog.pg_authid;
+-- Partitioned table and its partitions, nothing owned by other user.
+-- Relations are not listed in a single command to test ownership
+-- independently.
+VACUUM vacowned_parted;
+VACUUM vacowned_part1;
+VACUUM vacowned_part2;
+ANALYZE vacowned_parted;
+ANALYZE vacowned_part1;
+ANALYZE vacowned_part2;
+VACUUM (ANALYZE) vacowned_parted;
+VACUUM (ANALYZE) vacowned_part1;
+VACUUM (ANALYZE) vacowned_part2;
+RESET ROLE;
+-- Partitioned table and one partition owned by other user.
+ALTER TABLE vacowned_parted OWNER TO regress_vacuum;
+ALTER TABLE vacowned_part1 OWNER TO regress_vacuum;
+SET ROLE regress_vacuum;
+VACUUM vacowned_parted;
+VACUUM vacowned_part1;
+VACUUM vacowned_part2;
+ANALYZE vacowned_parted;
+ANALYZE vacowned_part1;
+ANALYZE vacowned_part2;
+VACUUM (ANALYZE) vacowned_parted;
+VACUUM (ANALYZE) vacowned_part1;
+VACUUM (ANALYZE) vacowned_part2;
+RESET ROLE;
+-- Only one partition owned by other user.
+ALTER TABLE vacowned_parted OWNER TO CURRENT_USER;
+SET ROLE regress_vacuum;
+VACUUM vacowned_parted;
+VACUUM vacowned_part1;
+VACUUM vacowned_part2;
+ANALYZE vacowned_parted;
+ANALYZE vacowned_part1;
+ANALYZE vacowned_part2;
+VACUUM (ANALYZE) vacowned_parted;
+VACUUM (ANALYZE) vacowned_part1;
+VACUUM (ANALYZE) vacowned_part2;
+RESET ROLE;
+-- Only partitioned table owned by other user.
+ALTER TABLE vacowned_parted OWNER TO regress_vacuum;
+ALTER TABLE vacowned_part1 OWNER TO CURRENT_USER;
+SET ROLE regress_vacuum;
+VACUUM vacowned_parted;
+VACUUM vacowned_part1;
+VACUUM vacowned_part2;
+ANALYZE vacowned_parted;
+ANALYZE vacowned_part1;
+ANALYZE vacowned_part2;
+VACUUM (ANALYZE) vacowned_parted;
+VACUUM (ANALYZE) vacowned_part1;
+VACUUM (ANALYZE) vacowned_part2;
RESET ROLE;
DROP TABLE vacowned;
+DROP TABLE vacowned_parted;
DROP ROLE regress_vacuum;
[parent: 88ebd62fcc2e]