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