On 2018-Jul-12, Michael Paquier wrote:
> Changing pg_class.relhastriggers is out of scope because as far as I
> know partitioned tables have no triggers, so the current value is
> correct, and that would be a catalog change at this stage which would
> cause any existing deployments of v11 to complain about the
> inconsistency. I think that this should be fixed client-side as the
> attached does.
Thanks, looks good. I propose to add following pg_dump test to ensure
this stays fixed.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From 05b2d8912688ce6b1c613d7de8a0a3a874e21653 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvhe...@alvh.no-ip.org>
Date: Thu, 12 Jul 2018 14:36:11 -0400
Subject: [PATCH] Dump foreign keys on partitioned tables
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
My patch that ended up as commit 3de241dba86f ("Foreign keys on
partitioned tables") lacked pg_dump tests, so the pg_dump code that was
there to support it inadvertently stopped working when I hacked the
backend code to not emit pg_trigger rows for the partitioned table
itself.
Bug analysis and code fix is by Michaël. I (Álvaro) merely added the
test.
Reported-by: amul sul <sula...@gmail.com>
Co-authored-by: Michaël Paquier <mich...@paquier.xyz>
Co-authored-by: Álvaro Herrera <alvhe...@alvh.no-ip.org>
Discussion:
https://postgr.es/m/CAAJ_b94n=UsNVhgs97vCaWEZAMe-tGDRVuZ73oePQH=eajk...@mail.gmail.com
---
src/bin/pg_dump/pg_dump.c | 7 ++++++-
src/bin/pg_dump/t/002_pg_dump.pl | 18 ++++++++++++++++++
2 files changed, 24 insertions(+), 1 deletion(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 463639208d..74a1270169 100644
--- 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
index 7eee870259..8860928df1 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -631,6 +631,24 @@ my %tests = (
},
},
+ '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);',
+ 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 =>
--
2.11.0
