From e18135cc29c074c23113f6cb907cbe83fe22e248 Mon Sep 17 00:00:00 2001 From: Ashutosh Bapat Date: Fri, 5 Jan 2024 19:28:16 +0530 Subject: [PATCH 14/27] Test dump and restore: NOT FOR FINAL COMMIT ... through the changed testcase since it dumps and restores the objects left behind by sql/identity.sql. The additional SQL statements here test the sanity of the restored partitioned tables with identity column in it. I have not seen these kind of tests for checking sanity of other objects so it's quite possible that dump comparison performed by the test is enough. Hence not planning to propose this for final commit. The earlier patches do not change anything in dump restore and yet it works. This is because the identity columns are dumped using ALTER TABLE command, which is constructed only for the table owning the underlying sequence. In case of partitioned tables, only the topmost partitioned table owns the sequence and thus ALTER TABLE command adding identity to the column is run only on the topmost table. The partitions are attached afterwards and inherit the identity column as part of that operation. Ashutosh Bapat --- src/bin/pg_upgrade/t/002_pg_upgrade.pl | 33 ++++++++++++++++++++++++++ 1 file changed, 33 insertions(+) diff --git a/src/bin/pg_upgrade/t/002_pg_upgrade.pl b/src/bin/pg_upgrade/t/002_pg_upgrade.pl index b0470844de..26ee1fb2af 100644 --- a/src/bin/pg_upgrade/t/002_pg_upgrade.pl +++ b/src/bin/pg_upgrade/t/002_pg_upgrade.pl @@ -458,4 +458,37 @@ if ($compare_res != 0) print "=== EOF ===\n"; } +# Test restored partitioned table with identity +$newnode->safe_psql('regression', + "INSERT INTO pitest1 (f1, f2) VALUES ('2016-08-5', 'from pitest1'), ('2016-07-5', 'from pitest1');"); +$newnode->safe_psql('regression', + "INSERT INTO pitest1_p1 (f1, f2) VALUES ('2016-07-6', 'from pitest1_p1');"); +$newnode->safe_psql('regression', + "INSERT INTO pitest1_p2 (f1, f2) VALUES ('2016-08-6', 'from pitest1_p2');"); +$result = $newnode->safe_psql('regression', + "SELECT tableoid::regclass, f1, f2, f3 FROM pitest1"); +is($result,"pitest1_p1|2016-07-02|from pitest1|1 +pitest1_p1|2016-07-03|from pitest1_p1|2 +pitest1_p1|2016-07-05|from pitest1|6 +pitest1_p1|2016-07-06|from pitest1_p1|7 +pitest1_p2|2016-08-02|before attaching|100 +pitest1_p2|2016-08-03|from pitest1_p2|3 +pitest1_p2|2016-08-04|from pitest1|4 +pitest1_p2|2016-08-05|from pitest1|5 +pitest1_p2|2016-08-06|from pitest1_p2|8"); +$newnode->safe_psql('regression', + "INSERT INTO pitest2 (f1, f2) VALUES ('2016-08-8', 'from pitest2');"); +$newnode->safe_psql('regression', + "INSERT INTO pitest2_p2 (f1, f2) VALUES ('2016-08-9', 'from pitest2_p2');"); +$result = $newnode->safe_psql('regression', + "SELECT tableoid::regclass, f1, f2, f3 FROM pitest2;"); +is($result, "pitest2_p2|2016-08-02|from pitest2|2 +pitest2_p2|2016-08-03|from pitest2_p2|4 +pitest2_p2|2016-08-04|from pitest2|6 +pitest2_p2|2016-08-05|from pitest2|1000 +pitest2_p2|2016-08-06|from pitest2_p2|300 +pitest2_p2|2016-08-07|from pitest2|1004 +pitest2_p2|2016-08-08|from pitest2|1006 +pitest2_p2|2016-08-09|from pitest2_p2|1008"); + done_testing(); -- 2.25.1