Dump foreign keys on partitioned tables

alvherre · michaelpq · alvherre · commit 2af39c39df94 · 2018-07-13T13:15:29.000-04:00
The patch that ended up as commit 3de241d ("Foreign keys on partitioned tables") lacked pg_dump tests, so the pg_dump code that was there to support it inadvertently stopped working when in later development I modified the backend code not to emit pg_trigger rows for the partitioned table itself. Bug analysis and code fix is by Michaël. I (Álvaro) added the test. Reported-by: amul sul <sulamul@gmail.com> Co-authored-by: Michaël Paquier <michael@paquier.xyz> Co-authored-by: Álvaro Herrera <alvherre@alvh.no-ip.org> Discussion: https://postgr.es/m/CAAJ_b94n=UsNVhgs97vCaWEZAMe-tGDRVuZ73oePQH=eaJKGSA@mail.gmail.com
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
@@ -7131,7 +7131,12 @@ getConstraints(Archive *fout, TableInfo tblinfo[], int numTables)
 	{
 		TableInfo  *tbinfo = &tblinfo[i];
 
-		if (!tbinfo->hastriggers ||
+		/*
+		 * For partitioned tables, foreign keys have no triggers so they
+		 * must be included anyway in case some foreign keys are defined.
+		 */
+		if ((!tbinfo->hastriggers &&
+			 tbinfo->relkind != RELKIND_PARTITIONED_TABLE) ||
 			!(tbinfo->dobj.dump & DUMP_COMPONENT_DEFINITION))
 			continue;
 
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -631,6 +631,25 @@
 		},
 	},
 
+	'ALTER TABLE (partitioned) ADD CONSTRAINT ... FOREIGN KEY' => {
+		create_order => 4,
+		create_sql   => 'CREATE TABLE dump_test.test_table_fk (
+							col1 int references dump_test.test_table)
+							PARTITION BY RANGE (col1);
+							CREATE TABLE dump_test.test_table_fk_1
+							PARTITION OF dump_test.test_table_fk
+							FOR VALUES FROM (0) TO (10);',
+		regexp => qr/
+			\QADD CONSTRAINT test_table_fk_col1_fkey FOREIGN KEY (col1) REFERENCES dump_test.test_table\E
+			/xm,
+		like => {
+			%full_runs, %dump_test_schema_runs, section_post_data => 1,
+		},
+		unlike => {
+			exclude_dump_test_schema => 1,
+		},
+	},
+
 	'ALTER TABLE ONLY test_table ALTER COLUMN col1 SET STATISTICS 90' => {
 		create_order => 93,
 		create_sql =>
