Re: Cannot dump foreign key constraints on partitioned table

On 2018-Jul-13, Michael Paquier wrote:

> On Thu, Jul 12, 2018 at 02:45:37PM -0400, Alvaro Herrera wrote:
> > Thanks, looks good.  I propose to add following pg_dump test to ensure
> > this stays fixed.
> 
> Thanks for adding the test.  I was looking at a good way to add a test
> but could not come up with something which can be summed up with one
> query for create_sql, so what you have here is nice.  Could you add an
> extra test with a partition of dump_test.test_table_fk?  Children should
> have the FK defined as well with relhastriggers set to true, still when
> I tested if the partitioned was not scanned for its FK, then its
> children partition also missed it.  So I think that it is important to
> check that the FK is defined for all members of the partition tree.

Hmm.  The pg_dump tests make it easy to create a partition (in fact I
had already modified the test to add one after submitting):

diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 8860928df1..666760c0d8 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -635,7 +635,10 @@ my %tests = (
         create_order => 4,
         create_sql   => 'CREATE TABLE dump_test.test_table_fk (
                             col1 int references dump_test.test_table)
-                            PARTITION BY RANGE (col1);',
+                            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,

I'm not sure what to *do* with the partition, though :-) I don't think
there's a nice way to verify that the FK actually exists, or that
catalog rows are set in such-and-such way, after restoring this.
The pg_restore tests are marked TODO in the suite.  I think that'll have
to wait.

> I am fine to add the test myself and to push if you need help.  Of
> course feel free to do it yourself if you want.  Either way is fine for
> me.

No worries -- I'll push it tomorrow morning.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services