0002-Filter-COPY-statements-with-differing-colum-20250211.patch

From 6976ebad1e4fa717cd8fa2f70fbe73cd476e7caf Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Date: Tue, 11 Feb 2025 16:31:10 +0530
Subject: [PATCH 2/2] Filter COPY statements with differing column order

---
 src/bin/pg_upgrade/t/002_pg_upgrade.pl      | 10 +---
 src/test/perl/PostgreSQL/Test/AdjustDump.pm | 59 +++++++++++++++------
 2 files changed, 45 insertions(+), 24 deletions(-)

diff --git a/src/bin/pg_upgrade/t/002_pg_upgrade.pl b/src/bin/pg_upgrade/t/002_pg_upgrade.pl
index 0d636529d74..b3e1573ec34 100644
--- a/src/bin/pg_upgrade/t/002_pg_upgrade.pl
+++ b/src/bin/pg_upgrade/t/002_pg_upgrade.pl
@@ -569,9 +569,6 @@ sub test_regression_dump_restore
 		my $dump_file = "$tempdir/regression_dump.$format";
 		my $restored_db = 'regression_' . $format;
 
-		# Even though we compare only schema from the original and the restored
-		# database (See get_dump_for_comparison() for details.), we dump and
-		# restore data as well to catch any errors while doing so.
 		$src_node->command_ok(
 			[
 				'pg_dump', "-F$format", '--no-sync',
@@ -633,13 +630,10 @@ sub get_dump_for_comparison
 	my $dump_adjusted = "${dumpfile}_adjusted";
 
 
-	# The order of columns in COPY statements dumped from the original database
-	# and that from the restored database differs. These differences are hard to
-	# adjust. Hence we compare only schema dumps for now.
 	$node->command_ok(
 		[
-			'pg_dump', '-s', '--no-sync', '-d',
-			$node->connstr($db), '-f', $dumpfile
+			'pg_dump', '--no-sync', '-d', $node->connstr($db), '-f',
+			$dumpfile
 		],
 		'dump for comparison succeeded');
 
diff --git a/src/test/perl/PostgreSQL/Test/AdjustDump.pm b/src/test/perl/PostgreSQL/Test/AdjustDump.pm
index e3e152b88fa..e00a00d1b2c 100644
--- a/src/test/perl/PostgreSQL/Test/AdjustDump.pm
+++ b/src/test/perl/PostgreSQL/Test/AdjustDump.pm
@@ -44,22 +44,36 @@ our @EXPORT = qw(
 
 If we take dump of the regression database left behind after running regression
 tests, restore the dump, and take dump of the restored regression database, the
-outputs of both the dumps differ. Some regression tests purposefully create
-some child tables in such a way that their column orders differ from column
-orders of their respective parents. In the restored database, however, their
-column orders are same as that of their respective parents. Thus the column
+outputs of both the dumps differ in the following cases. This routine adjusts
+the given dump so that dump outputs from the original and restored database,
+respectively, match.
+
+Case 1: Some regression tests purposefully create child tables in such a way
+that the order of their inherited columns differ from column orders of their
+respective parents. In the restored database, however, the order of their
+inherited columns are same as that of their respective parents. Thus the column
 orders of these child tables in the original database and those in the restored
 database differ, causing difference in the dump outputs. See MergeAttributes()
-and dumpTableSchema() for details.
-
-This routine rearranges the column declarations in the relevant
-C<CREATE TABLE... INHERITS> statements in the dump file from original database
-to match those from the restored database. We could instead adjust the
-statements in the dump from the restored database to match those from original
-database or adjust both to a canonical order. But we have chosen to adjust the
-statements in the dump from original database for no particular reason.
-
-Additionally it adjusts blank and new lines to avoid noise.
+and dumpTableSchema() for details.  This routine rearranges the column
+declarations in the relevant C<CREATE TABLE... INHERITS> statements in the dump
+file from original database to match those from the restored database. We could,
+instead, adjust the statements in the dump from the restored database to match
+those from original database or adjust both to a canonical order. But we have
+chosen to adjust the statements in the dump from original database for no
+particular reason.
+
+Case 2: When dumping COPY statements the columns are ordered by their attribute
+number by fmtCopyColumnList(). If a column is added to a parent table after a
+child has inherited the parent and the child has its own columns, the attribute
+number of the column changes after restoring the child table. This is because
+when executing the dumped C<CREATE TABLE... INHERITS> statement all the parent
+attributes are created before any child attributes. Thus the order of columns in
+COPY statements dumped from the original and the restored databases,
+respectively, differs. Such tables in regression tests are listed below. It is
+hard to adjust the column order in the COPY statement along with the data. Hence
+we just remove such COPY statements from the dump output.
+
+Additionally the routine adjusts blank and new lines to avoid noise.
 
 Arguments:
 
@@ -84,8 +98,6 @@ sub adjust_regress_dumpfile
 
 	# use Unix newlines
 	$dump =~ s/\r\n/\n/g;
-	# Suppress blank lines, as some places in pg_dump emit more or fewer.
-	$dump =~ s/\n\n+/\n/g;
 
 	# Adjust the CREATE TABLE ... INHERITS statements.
 	if ($adjust_child_columns)
@@ -122,6 +134,21 @@ sub adjust_regress_dumpfile
 			'applied public.test_type_diff2_c2 adjustments');
 	}
 
+	# Remove COPY statements with differing column order
+	for my $table (
+		'public\.b_star', 'public\.c_star',
+		'public\.cc2', 'public\.d_star',
+		'public\.e_star', 'public\.f_star',
+		'public\.renamecolumnanother', 'public\.renamecolumnchild',
+		'public\.test_type_diff2_c1', 'public\.test_type_diff2_c2',
+		'public\.test_type_diff_c')
+	{
+		$dump =~ s/^COPY\s$table\s\(.+?^\\\.$//sm;
+	}
+
+	# Suppress blank lines, as some places in pg_dump emit more or fewer.
+	$dump =~ s/\n\n+/\n/g;
+
 	return $dump;
 }
 
-- 
2.34.1