From 304969e542940240e21698b290cc647a38f066fb Mon Sep 17 00:00:00 2001 From: Ashutosh Bapat Date: Mon, 8 Jan 2024 15:01:06 +0530 Subject: [PATCH 15/27] Test pg_upgrade NOT FOR FINAL COMMIT Old clusters, with versions 10 and above, may have partitioned tables with identity columns. The corresponding columns in partitions of such tables will not be marked as identity columns. Test that such partitioned tables are upgraded correctly. The columns in partitions are also marked as identity columns and use the same underlying sequence as the partitioned table. Test this using following steps 1. On an old version cluster run following commands to create required partitioned tables in "regression" database --- sql CREATE TABLE pitest1 (f1 date NOT NULL, f2 text, f3 bigint generated always as identity) PARTITION BY RANGE (f1); CREATE TABLE pitest1_p1 PARTITION OF pitest1 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); INSERT into pitest1(f1, f2) VALUES ('2016-07-2', 'from pitest1'); INSERT into pitest1_p1 (f1, f2, f3) VALUES ('2016-07-3', 'from pitest1_p1', nextval('pitest1_f3_seq')); CREATE TABLE pitest1_p2 (f1 date NOT NULL, f2 text, f3 bigint); INSERT INTO pitest1_p2 VALUES ('2016-08-2', 'before attaching', 100); ALTER TABLE pitest1_p2 ALTER COLUMN f3 SET NOT NULL; ALTER TABLE pitest1 ATTACH PARTITION pitest1_p2 FOR VALUES FROM ('2016-08-01') TO ('2016-09-01'); INSERT INTO pitest1_p2 (f1, f2, f3) VALUES ('2016-08-3', 'from pitest1_p2', nextval('pitest1_f3_seq')); INSERT INTO pitest1 (f1, f2) VALUES ('2016-08-4', 'from pitest1'); --- sql 2. Take dump using pg_dumpall and save it in a file 3. Set environment variables olddump to the path of this file and oldinstall to the binaries of old version. 4. Run modified 002_pg_upgrade. --- src/bin/pg_upgrade/t/002_pg_upgrade.pl | 19 +++---------------- 1 file changed, 3 insertions(+), 16 deletions(-) diff --git a/src/bin/pg_upgrade/t/002_pg_upgrade.pl b/src/bin/pg_upgrade/t/002_pg_upgrade.pl index 26ee1fb2af..9b63a7b9dd 100644 --- a/src/bin/pg_upgrade/t/002_pg_upgrade.pl +++ b/src/bin/pg_upgrade/t/002_pg_upgrade.pl @@ -241,8 +241,9 @@ if (defined($ENV{oldinstall})) my %dbnames; do { $dbnames{$_} = 1; } foreach split /\s+/s, $dbnames; - my $adjust_cmds = - adjust_database_contents($oldnode->pg_version, %dbnames); + # Testing a custom dump that doesn't have regression objects + my $adjust_cmds = (); + # adjust_database_contents($oldnode->pg_version, %dbnames); foreach my $updb (keys %$adjust_cmds) { @@ -476,19 +477,5 @@ 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