v57-0001-partition_split.sql-test-refactor-based-on-v57.no-cfbot

From 1c0e3c54d9725fda3498826cccf4ea74d8e9743e Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Wed, 17 Sep 2025 16:20:16 +0800
Subject: [PATCH v57 1/1] partition_split.sql test refactor based on v57

changes in src/test/regress/sql/partition_split.sql:
* make permission related tests together.
* reduce unnecessarily 'CREATE ROLE', 'DROP ROLE', only one 'CREATE ROLE', 'DROP
ROLE' is enough in here.
---
 src/test/regress/expected/partition_split.out | 70 +++++++---------
 src/test/regress/sql/partition_split.sql      | 80 ++++++++-----------
 2 files changed, 65 insertions(+), 85 deletions(-)

diff --git a/src/test/regress/expected/partition_split.out b/src/test/regress/expected/partition_split.out
index 7bafd93e2f6..cb481fa491e 100644
--- a/src/test/regress/expected/partition_split.out
+++ b/src/test/regress/expected/partition_split.out
@@ -1322,39 +1322,6 @@ ORDER BY c.relname COLLATE "C";
 
 DROP TABLE t;
 DROP ACCESS METHOD partition_split_heap;
--- Test permission checks.  The user needs to own the parent table and the
--- the partition to split to do the split.
-CREATE ROLE regress_partition_split_alice;
-CREATE ROLE regress_partition_split_bob;
-GRANT ALL ON SCHEMA partition_split_schema TO regress_partition_split_alice;
-GRANT ALL ON SCHEMA partition_split_schema TO regress_partition_split_bob;
-SET SESSION AUTHORIZATION regress_partition_split_alice;
-CREATE TABLE t (i int) PARTITION BY RANGE (i);
-CREATE TABLE tp_0_2 PARTITION OF t FOR VALUES FROM (0) TO (2);
-SET SESSION AUTHORIZATION regress_partition_split_bob;
-ALTER TABLE t SPLIT PARTITION tp_0_2 INTO
-  (PARTITION tp_0_1 FOR VALUES FROM (0) TO (1),
-   PARTITION tp_1_2 FOR VALUES FROM (1) TO (2));
-ERROR:  must be owner of table t
-RESET SESSION AUTHORIZATION;
-ALTER TABLE t OWNER TO regress_partition_split_bob;
-SET SESSION AUTHORIZATION regress_partition_split_bob;
-ALTER TABLE t SPLIT PARTITION tp_0_2 INTO
-  (PARTITION tp_0_1 FOR VALUES FROM (0) TO (1),
-   PARTITION tp_1_2 FOR VALUES FROM (1) TO (2));
-ERROR:  must be owner of table tp_0_2
-RESET SESSION AUTHORIZATION;
-ALTER TABLE tp_0_2 OWNER TO regress_partition_split_bob;
-SET SESSION AUTHORIZATION regress_partition_split_bob;
-ALTER TABLE t SPLIT PARTITION tp_0_2 INTO
-  (PARTITION tp_0_1 FOR VALUES FROM (0) TO (1),
-   PARTITION tp_1_2 FOR VALUES FROM (1) TO (2));
-RESET SESSION AUTHORIZATION;
-DROP TABLE t;
-REVOKE ALL ON SCHEMA partition_split_schema FROM regress_partition_split_alice;
-REVOKE ALL ON SCHEMA partition_split_schema FROM regress_partition_split_bob;
-DROP ROLE regress_partition_split_alice;
-DROP ROLE regress_partition_split_bob;
 -- Split partition of a temporary table when one of the partitions after
 -- split has the same name as the partition being split
 CREATE TEMP TABLE t (a int) PARTITION BY RANGE (a);
@@ -1422,9 +1389,36 @@ Partition of: t_bigint FOR VALUES FROM ('0') TO ('10')
 Partition constraint: ((b IS NOT NULL) AND (b >= '0'::bigint) AND (b < '10'::bigint))
 
 DROP TABLE t_bigint;
+-- Test permission checks.  The user needs to own the parent table and the
+-- the partition to split to do the split.
+CREATE ROLE regress_partition_split_alice;
+CREATE ROLE regress_partition_split_bob;
+GRANT ALL ON SCHEMA partition_split_schema TO regress_partition_split_alice;
+GRANT ALL ON SCHEMA partition_split_schema TO regress_partition_split_bob;
+SET SESSION AUTHORIZATION regress_partition_split_alice;
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TABLE tp_0_2 PARTITION OF t FOR VALUES FROM (0) TO (2);
+SET SESSION AUTHORIZATION regress_partition_split_bob;
+ALTER TABLE t SPLIT PARTITION tp_0_2 INTO
+  (PARTITION tp_0_1 FOR VALUES FROM (0) TO (1),
+   PARTITION tp_1_2 FOR VALUES FROM (1) TO (2)); --error
+ERROR:  must be owner of table t
+RESET SESSION AUTHORIZATION;
+ALTER TABLE t OWNER TO regress_partition_split_bob;
+SET SESSION AUTHORIZATION regress_partition_split_bob;
+ALTER TABLE t SPLIT PARTITION tp_0_2 INTO
+  (PARTITION tp_0_1 FOR VALUES FROM (0) TO (1),
+   PARTITION tp_1_2 FOR VALUES FROM (1) TO (2)); --error
+ERROR:  must be owner of table tp_0_2
+RESET SESSION AUTHORIZATION;
+ALTER TABLE tp_0_2 OWNER TO regress_partition_split_bob;
+SET SESSION AUTHORIZATION regress_partition_split_bob;
+ALTER TABLE t SPLIT PARTITION tp_0_2 INTO
+  (PARTITION tp_0_1 FOR VALUES FROM (0) TO (1),
+   PARTITION tp_1_2 FOR VALUES FROM (1) TO (2)); --ok
+RESET SESSION AUTHORIZATION;
+DROP TABLE t;
 -- Test: owner of new partitions should be the same as owner of split partition
-CREATE ROLE regress_partition_split_alice;
-GRANT ALL ON SCHEMA partition_split_schema TO regress_partition_split_alice;
 CREATE TABLE t (i int) PARTITION BY RANGE (i);
 SET SESSION AUTHORIZATION regress_partition_split_alice;
 CREATE TABLE tp_0_2(i int);
@@ -1457,12 +1451,8 @@ ALTER TABLE t SPLIT PARTITION tp_0_2 INTO
 (1 row)
 
 DROP TABLE t;
-REVOKE ALL ON SCHEMA partition_split_schema FROM regress_partition_split_alice;
-DROP ROLE regress_partition_split_alice;
 -- Test: index of new partitions should be created with same owner as split
 -- partition
-CREATE ROLE regress_partition_split_alice;
-GRANT ALL ON SCHEMA partition_split_schema TO regress_partition_split_alice;
 SET SESSION AUTHORIZATION regress_partition_split_alice;
 CREATE TABLE t (i int) PARTITION BY RANGE (i);
 CREATE TABLE tp_10_20 PARTITION OF t FOR VALUES FROM (10) TO (20);
@@ -1487,7 +1477,9 @@ NOTICE:  you are running me as regress_partition_split_alice
 DROP TABLE t;
 DROP FUNCTION run_me(integer);
 REVOKE ALL ON SCHEMA partition_split_schema FROM regress_partition_split_alice;
+REVOKE ALL ON SCHEMA partition_split_schema FROM regress_partition_split_bob;
 DROP ROLE regress_partition_split_alice;
+DROP ROLE regress_partition_split_bob;
 -- Test for hash partitioned table
 CREATE TABLE t (i int) PARTITION BY HASH(i);
 CREATE TABLE tp1 PARTITION OF t FOR VALUES WITH (MODULUS 2, REMAINDER 0);
diff --git a/src/test/regress/sql/partition_split.sql b/src/test/regress/sql/partition_split.sql
index 72853e423a7..eefb488253e 100644
--- a/src/test/regress/sql/partition_split.sql
+++ b/src/test/regress/sql/partition_split.sql
@@ -905,43 +905,6 @@ ORDER BY c.relname COLLATE "C";
 DROP TABLE t;
 DROP ACCESS METHOD partition_split_heap;
 
--- Test permission checks.  The user needs to own the parent table and the
--- the partition to split to do the split.
-CREATE ROLE regress_partition_split_alice;
-CREATE ROLE regress_partition_split_bob;
-GRANT ALL ON SCHEMA partition_split_schema TO regress_partition_split_alice;
-GRANT ALL ON SCHEMA partition_split_schema TO regress_partition_split_bob;
-
-SET SESSION AUTHORIZATION regress_partition_split_alice;
-CREATE TABLE t (i int) PARTITION BY RANGE (i);
-CREATE TABLE tp_0_2 PARTITION OF t FOR VALUES FROM (0) TO (2);
-
-SET SESSION AUTHORIZATION regress_partition_split_bob;
-ALTER TABLE t SPLIT PARTITION tp_0_2 INTO
-  (PARTITION tp_0_1 FOR VALUES FROM (0) TO (1),
-   PARTITION tp_1_2 FOR VALUES FROM (1) TO (2));
-RESET SESSION AUTHORIZATION;
-
-ALTER TABLE t OWNER TO regress_partition_split_bob;
-SET SESSION AUTHORIZATION regress_partition_split_bob;
-ALTER TABLE t SPLIT PARTITION tp_0_2 INTO
-  (PARTITION tp_0_1 FOR VALUES FROM (0) TO (1),
-   PARTITION tp_1_2 FOR VALUES FROM (1) TO (2));
-RESET SESSION AUTHORIZATION;
-
-ALTER TABLE tp_0_2 OWNER TO regress_partition_split_bob;
-SET SESSION AUTHORIZATION regress_partition_split_bob;
-ALTER TABLE t SPLIT PARTITION tp_0_2 INTO
-  (PARTITION tp_0_1 FOR VALUES FROM (0) TO (1),
-   PARTITION tp_1_2 FOR VALUES FROM (1) TO (2));
-RESET SESSION AUTHORIZATION;
-
-DROP TABLE t;
-REVOKE ALL ON SCHEMA partition_split_schema FROM regress_partition_split_alice;
-REVOKE ALL ON SCHEMA partition_split_schema FROM regress_partition_split_bob;
-DROP ROLE regress_partition_split_alice;
-DROP ROLE regress_partition_split_bob;
-
 -- Split partition of a temporary table when one of the partitions after
 -- split has the same name as the partition being split
 CREATE TEMP TABLE t (a int) PARTITION BY RANGE (a);
@@ -971,11 +934,40 @@ ALTER TABLE t_bigint SPLIT PARTITION t_bigint_default INTO
 \d+ t_bigint_01_10
 DROP TABLE t_bigint;
 
+-- Test permission checks.  The user needs to own the parent table and the
+-- the partition to split to do the split.
+CREATE ROLE regress_partition_split_alice;
+CREATE ROLE regress_partition_split_bob;
+GRANT ALL ON SCHEMA partition_split_schema TO regress_partition_split_alice;
+GRANT ALL ON SCHEMA partition_split_schema TO regress_partition_split_bob;
+
+SET SESSION AUTHORIZATION regress_partition_split_alice;
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TABLE tp_0_2 PARTITION OF t FOR VALUES FROM (0) TO (2);
+
+SET SESSION AUTHORIZATION regress_partition_split_bob;
+ALTER TABLE t SPLIT PARTITION tp_0_2 INTO
+  (PARTITION tp_0_1 FOR VALUES FROM (0) TO (1),
+   PARTITION tp_1_2 FOR VALUES FROM (1) TO (2)); --error
+RESET SESSION AUTHORIZATION;
+
+ALTER TABLE t OWNER TO regress_partition_split_bob;
+SET SESSION AUTHORIZATION regress_partition_split_bob;
+ALTER TABLE t SPLIT PARTITION tp_0_2 INTO
+  (PARTITION tp_0_1 FOR VALUES FROM (0) TO (1),
+   PARTITION tp_1_2 FOR VALUES FROM (1) TO (2)); --error
+RESET SESSION AUTHORIZATION;
+
+ALTER TABLE tp_0_2 OWNER TO regress_partition_split_bob;
+SET SESSION AUTHORIZATION regress_partition_split_bob;
+ALTER TABLE t SPLIT PARTITION tp_0_2 INTO
+  (PARTITION tp_0_1 FOR VALUES FROM (0) TO (1),
+   PARTITION tp_1_2 FOR VALUES FROM (1) TO (2)); --ok
+RESET SESSION AUTHORIZATION;
+
+DROP TABLE t;
 
 -- Test: owner of new partitions should be the same as owner of split partition
-CREATE ROLE regress_partition_split_alice;
-GRANT ALL ON SCHEMA partition_split_schema TO regress_partition_split_alice;
-
 CREATE TABLE t (i int) PARTITION BY RANGE (i);
 
 SET SESSION AUTHORIZATION regress_partition_split_alice;
@@ -996,15 +988,9 @@ ALTER TABLE t SPLIT PARTITION tp_0_2 INTO
 \dt tp_1_2
 
 DROP TABLE t;
-REVOKE ALL ON SCHEMA partition_split_schema FROM regress_partition_split_alice;
-DROP ROLE regress_partition_split_alice;
-
 
 -- Test: index of new partitions should be created with same owner as split
 -- partition
-CREATE ROLE regress_partition_split_alice;
-GRANT ALL ON SCHEMA partition_split_schema TO regress_partition_split_alice;
-
 SET SESSION AUTHORIZATION regress_partition_split_alice;
 CREATE TABLE t (i int) PARTITION BY RANGE (i);
 CREATE TABLE tp_10_20 PARTITION OF t FOR VALUES FROM (10) TO (20);
@@ -1029,7 +1015,9 @@ DROP TABLE t;
 DROP FUNCTION run_me(integer);
 
 REVOKE ALL ON SCHEMA partition_split_schema FROM regress_partition_split_alice;
+REVOKE ALL ON SCHEMA partition_split_schema FROM regress_partition_split_bob;
 DROP ROLE regress_partition_split_alice;
+DROP ROLE regress_partition_split_bob;
 
 -- Test for hash partitioned table
 CREATE TABLE t (i int) PARTITION BY HASH(i);
-- 
2.34.1