Feedback-ID: ia2694551:Fastmail
Date: Fri, 26 Jan 2024 17:36:33 +0100
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: vignesh C <vignesh21@gmail.com>
Cc: Dmitry Koval <d.koval@postgrespro.ru>,
	pgsql-hackers@lists.postgresql.org
Subject: Re: Add SPLIT PARTITION/MERGE PARTITIONS commands
Message-ID: <202401261636.pwqfhwf3hrns@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="eytn77twicdgkfgb"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <202401261401.o4pro4lnfamv@alvherre.pgsql>
Archived-At: 
 <https://www.postgresql.org/message-id/202401261636.pwqfhwf3hrns%40alvherre.pgsql>
Precedence: bulk


--eytn77twicdgkfgb
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

On 2024-Jan-26, Alvaro Herrera wrote:

> On 2024-Jan-26, vignesh C wrote:
> 
> > Please post an updated version for the same.
> 
> Here's a rebase.  I only fixed the conflicts, didn't review.

Hmm, but I got the attached regression.diffs with it.  I didn't
investigate further, but it looks like the recent changes to replication
identity for partitioned tables has broken the regression tests.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"This is what I like so much about PostgreSQL.  Most of the surprises
are of the "oh wow!  That's cool" Not the "oh shit!" kind.  :)"
Scott Marlowe, http://archives.postgresql.org/pgsql-admin/2008-10/msg00152.php

--eytn77twicdgkfgb
Content-Type: text/plain; charset=utf-8
Content-Disposition: attachment; filename="regression.diffs"

diff -U3 /pgsql/source/master/src/test/regress/expected/partition_split.out /home/alvherre/Code/pgsql-build/master/src/test/regress/results/partition_split.out
--- /pgsql/source/master/src/test/regress/expected/partition_split.out	2024-01-26 14:57:39.549730792 +0100
+++ /home/alvherre/Code/pgsql-build/master/src/test/regress/results/partition_split.out	2024-01-26 15:22:15.007059433 +0100
@@ -777,8 +777,12 @@
 -- Create new partition with identity-column:
 CREATE TABLE salesmans2_5(salesman_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, salesman_name VARCHAR(30));
 ALTER TABLE salesmans ATTACH PARTITION salesmans2_5 FOR VALUES FROM (2) TO (5);
+ERROR:  table "salesmans2_5" being attached contains an identity column "salesman_id"
+DETAIL:  The new partition may not contain an identity column.
 INSERT INTO salesmans (salesman_name) VALUES ('Poirot');
 INSERT INTO salesmans (salesman_name) VALUES ('Ivanov');
+ERROR:  no partition of relation "salesmans" found for row
+DETAIL:  Partition key of the failing row contains (salesman_id) = (2).
 SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans'::regclass::oid;
     attname    | attidentity | attgenerated 
 ---------------+-------------+--------------
@@ -789,7 +793,7 @@
 SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans1_2'::regclass::oid;
     attname    | attidentity | attgenerated 
 ---------------+-------------+--------------
- salesman_id   |             | 
+ salesman_id   | a           | 
  salesman_name |             | 
 (2 rows)
 
@@ -805,8 +809,13 @@
   (PARTITION salesmans2_3 FOR VALUES FROM (2) TO (3),
    PARTITION salesmans3_4 FOR VALUES FROM (3) TO (4),
    PARTITION salesmans4_5 FOR VALUES FROM (4) TO (5));
+ERROR:  partition bound for relation "salesmans2_5" is null
 INSERT INTO salesmans (salesman_name) VALUES ('May');
+ERROR:  no partition of relation "salesmans" found for row
+DETAIL:  Partition key of the failing row contains (salesman_id) = (3).
 INSERT INTO salesmans (salesman_name) VALUES ('Ford');
+ERROR:  no partition of relation "salesmans" found for row
+DETAIL:  Partition key of the failing row contains (salesman_id) = (4).
 SELECT * FROM salesmans1_2;
  salesman_id | salesman_name 
 -------------+---------------
@@ -814,23 +823,17 @@
 (1 row)
 
 SELECT * FROM salesmans2_3;
- salesman_id | salesman_name 
--------------+---------------
-           2 | Ivanov
-(1 row)
-
+ERROR:  relation "salesmans2_3" does not exist
+LINE 1: SELECT * FROM salesmans2_3;
+                      ^
 SELECT * FROM salesmans3_4;
- salesman_id | salesman_name 
--------------+---------------
-           3 | May
-(1 row)
-
+ERROR:  relation "salesmans3_4" does not exist
+LINE 1: SELECT * FROM salesmans3_4;
+                      ^
 SELECT * FROM salesmans4_5;
- salesman_id | salesman_name 
--------------+---------------
-           4 | Ford
-(1 row)
-
+ERROR:  relation "salesmans4_5" does not exist
+LINE 1: SELECT * FROM salesmans4_5;
+                      ^
 SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans'::regclass::oid;
     attname    | attidentity | attgenerated 
 ---------------+-------------+--------------
@@ -841,32 +844,23 @@
 SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans1_2'::regclass::oid;
     attname    | attidentity | attgenerated 
 ---------------+-------------+--------------
- salesman_id   |             | 
+ salesman_id   | a           | 
  salesman_name |             | 
 (2 rows)
 
 -- New partitions have identity-columns:
 SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans2_3'::regclass::oid;
-    attname    | attidentity | attgenerated 
----------------+-------------+--------------
- salesman_id   | a           | 
- salesman_name |             | 
-(2 rows)
-
+ERROR:  relation "salesmans2_3" does not exist
+LINE 1: ...FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans...
+                                                             ^
 SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans3_4'::regclass::oid;
-    attname    | attidentity | attgenerated 
----------------+-------------+--------------
- salesman_id   | a           | 
- salesman_name |             | 
-(2 rows)
-
+ERROR:  relation "salesmans3_4" does not exist
+LINE 1: ...FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans...
+                                                             ^
 SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans4_5'::regclass::oid;
-    attname    | attidentity | attgenerated 
----------------+-------------+--------------
- salesman_id   | a           | 
- salesman_name |             | 
-(2 rows)
-
+ERROR:  relation "salesmans4_5" does not exist
+LINE 1: ...FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans...
+                                                             ^
 DROP TABLE salesmans CASCADE;
 --
 -- Test: split partition with deleted columns
@@ -1121,19 +1115,20 @@
   (PARTITION sales_west FOR VALUES IN ('Voronezh', 'Smolensk', 'Bryansk'),
    PARTITION sales_east FOR VALUES IN ('Magadan', 'Khabarovsk', 'Vladivostok'),
    PARTITION sales_central FOR VALUES IN ('Moscow', 'Kazan', 'Volgograd'));
+ERROR:  no owned sequence found
 SELECT * FROM sales_list;
  salesman_id | salesman_name |  sales_state   | sales_amount | sales_date 
 -------------+---------------+----------------+--------------+------------
+           1 | Trump         | Magadan        |         1000 | 03-01-2022
            2 | Smirnoff      | Smolensk       |          500 | 03-03-2022
-           5 | Deev          | Voronezh       |          250 | 03-07-2022
-          11 | Muller        | Bryansk        |          650 | 03-05-2022
-          14 | Plato         | Voronezh       |          950 | 03-05-2022
            4 | Ivanov        | Moscow         |          750 | 03-04-2022
+           5 | Deev          | Voronezh       |          250 | 03-07-2022
            6 | Poirot        | Kazan          |         1000 | 03-01-2022
+           8 | Li            | Vladivostok    |         1150 | 03-09-2022
+          11 | Muller        | Bryansk        |          650 | 03-05-2022
           12 | Smith         | Volgograd      |          350 | 03-10-2022
           13 | Gandi         | Moscow         |          150 | 03-08-2022
-           1 | Trump         | Magadan        |         1000 | 03-01-2022
-           8 | Li            | Vladivostok    |         1150 | 03-09-2022
+          14 | Plato         | Voronezh       |          950 | 03-05-2022
            3 | Ford          | St. Petersburg |         2000 | 03-05-2022
            7 | May           | Ukhta          |         1200 | 03-06-2022
            9 | May           | Ukhta          |         1200 | 03-11-2022
@@ -1141,21 +1136,13 @@
 (14 rows)
 
 SELECT * FROM sales_west;
- salesman_id | salesman_name | sales_state | sales_amount | sales_date 
--------------+---------------+-------------+--------------+------------
-           2 | Smirnoff      | Smolensk    |          500 | 03-03-2022
-           5 | Deev          | Voronezh    |          250 | 03-07-2022
-          11 | Muller        | Bryansk     |          650 | 03-05-2022
-          14 | Plato         | Voronezh    |          950 | 03-05-2022
-(4 rows)
-
+ERROR:  relation "sales_west" does not exist
+LINE 1: SELECT * FROM sales_west;
+                      ^
 SELECT * FROM sales_east;
- salesman_id | salesman_name | sales_state | sales_amount | sales_date 
--------------+---------------+-------------+--------------+------------
-           1 | Trump         | Magadan     |         1000 | 03-01-2022
-           8 | Li            | Vladivostok |         1150 | 03-09-2022
-(2 rows)
-
+ERROR:  relation "sales_east" does not exist
+LINE 1: SELECT * FROM sales_east;
+                      ^
 SELECT * FROM sales_nord;
  salesman_id | salesman_name |  sales_state   | sales_amount | sales_date 
 -------------+---------------+----------------+--------------+------------
@@ -1166,24 +1153,16 @@
 (4 rows)
 
 SELECT * FROM sales_central;
- salesman_id | salesman_name | sales_state | sales_amount | sales_date 
--------------+---------------+-------------+--------------+------------
-           4 | Ivanov        | Moscow      |          750 | 03-04-2022
-           6 | Poirot        | Kazan       |         1000 | 03-01-2022
-          12 | Smith         | Volgograd   |          350 | 03-10-2022
-          13 | Gandi         | Moscow      |          150 | 03-08-2022
-(4 rows)
-
+ERROR:  relation "sales_central" does not exist
+LINE 1: SELECT * FROM sales_central;
+                      ^
 -- Use indexscan for test indexes after split partition
 SET enable_indexscan = ON;
 SET enable_seqscan = OFF;
 SELECT * FROM sales_central WHERE sales_state = 'Moscow';
- salesman_id | salesman_name | sales_state | sales_amount | sales_date 
--------------+---------------+-------------+--------------+------------
-           4 | Ivanov        | Moscow      |          750 | 03-04-2022
-          13 | Gandi         | Moscow      |          150 | 03-08-2022
-(2 rows)
-
+ERROR:  relation "sales_central" does not exist
+LINE 1: SELECT * FROM sales_central WHERE sales_state = 'Moscow';
+                      ^
 SELECT * FROM sales_list WHERE sales_state = 'Moscow';
  salesman_id | salesman_name | sales_state | sales_amount | sales_date 
 -------------+---------------+-------------+--------------+------------
@@ -1722,3 +1701,6 @@
 DROP TABLE test_2colkey CASCADE;
 --
 DROP SCHEMA partition_split_schema;
+ERROR:  cannot drop schema partition_split_schema because other objects depend on it
+DETAIL:  table salesmans2_5 depends on schema partition_split_schema
+HINT:  Use DROP ... CASCADE to drop the dependent objects too.

--eytn77twicdgkfgb--