Hello all!
Currently, according to logical replication restriction sections at [1],
"Attempts to replicate ... foreign tables, will result in an error."
But we don't care about partitioned tables with foreign partitions.
As we discussed here
https://www.postgresql.org/message-id/CAA4eK1Lhh4SgiYQLNiWSNKGdVSzbd53%3Dsr2tQCKooEphDkUtgw%40mail.gmail.com
we decided to discuss this issue separately.
I have attached patch for this issue.
The correction only applies to the case "CREATE PUBLICATION FOR TABLE",
but no "FOR ALL TABLES" and "TABLES IN SCHEMA" because table list built
during subscription.
And I have another patch that filters partitioned tables at subscription
time, but we can't throw an error in that case. I'm not sure if it's
interesting, because it changes the behavior
[1] -
https://www.postgresql.org/docs/devel/logical-replication-restrictions.html
From c69e26ef8519dfbfee9edf586ec8742f79e92269 Mon Sep 17 00:00:00 2001
From: Sergey Tatarintsev <s.tatarint...@postgrespro.ru>
Date: Wed, 29 Jan 2025 12:40:54 +0700
Subject: [PATCH] Attempts to replicate partitioned tables with foreign
partitions, will result in an error
---
src/backend/catalog/pg_publication.c | 36 +++++++++++++++++++++++
src/test/regress/expected/publication.out | 16 ++++++++++
src/test/regress/sql/publication.sql | 13 ++++++++
3 files changed, 65 insertions(+)
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index 09e2dbdd10..a426986c7f 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -48,6 +48,34 @@ typedef struct
* table. */
} published_rel;
+/*
+ * Check is relation has foreign partitions or
+ * it's foreign table itself.
+ */
+static bool IsRelationWithForeignData(Oid relid)
+{
+ List *all_parts;
+ ListCell *lc;
+ char relkind = get_rel_relkind(relid);
+
+ if (relkind == RELKIND_FOREIGN_TABLE)
+ return true;
+ else if (relkind != RELKIND_PARTITIONED_TABLE)
+ return false;
+
+ all_parts = find_all_inheritors(relid, NoLock, NULL);
+
+ foreach(lc, all_parts)
+ {
+ Oid partOid = lfirst_oid(lc);
+
+ if (get_rel_relkind(partOid) == RELKIND_FOREIGN_TABLE)
+ return true;
+ }
+
+ return false;
+}
+
/*
* Check if relation can be in given publication and throws appropriate
* error if not.
@@ -64,6 +92,14 @@ check_publication_add_relation(Relation targetrel)
RelationGetRelationName(targetrel)),
errdetail_relkind_not_supported(RelationGetForm(targetrel)->relkind)));
+ /* Can't be partitioned table with foreign partitions */
+ if (IsRelationWithForeignData(RelationGetForm(targetrel)->oid))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("cannot add relation \"%s\" to publication",
+ RelationGetRelationName(targetrel)),
+ errdetail("This operation is not supported for partitioned tables with foreign partitions")));
+
/* Can't be system table */
if (IsCatalogRelation(targetrel))
ereport(ERROR,
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 5de2d64d01..a9c02a1503 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -1847,6 +1847,22 @@ Tables:
DROP PUBLICATION pub1;
DROP PUBLICATION pub2;
DROP TABLE gencols;
+-- Attempts to replicate foreign tables, will result in an error
+CREATE FOREIGN DATA WRAPPER dummy;
+CREATE SERVER fdw FOREIGN DATA WRAPPER dummy;
+CREATE TABLE ptab(id int) PARTITION BY RANGE(id);
+CREATE TABLE part1 PARTITION OF ptab FOR VALUES FROM (0) TO (5);
+CREATE TABLE part2 PARTITION OF ptab FOR VALUES FROM (5) TO (15) PARTITION BY RANGE(id);
+CREATE TABLE part21 PARTITION OF part2 FOR VALUES FROM (5) TO (10);
+CREATE FOREIGN TABLE part22 PARTITION OF part2 FOR VALUES FROM (10) TO (15) SERVER fdw;
+CREATE PUBLICATION fdwpub FOR TABLE ptab;
+ERROR: cannot add relation "ptab" to publication
+DETAIL: This operation is not supported for partitioned tables with foreign partitions
+CREATE PUBLICATION fdwpub_via_root FOR TABLE ptab WITH (publish_via_partition_root);
+ERROR: cannot add relation "ptab" to publication
+DETAIL: This operation is not supported for partitioned tables with foreign partitions
+DROP TABLE ptab;
+DROP FOREIGN DATA WRAPPER dummy CASCADE;
RESET client_min_messages;
RESET SESSION AUTHORIZATION;
DROP ROLE regress_publication_user, regress_publication_user2;
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 48e68bcca2..c69ad9682b 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -1153,6 +1153,19 @@ DROP PUBLICATION pub1;
DROP PUBLICATION pub2;
DROP TABLE gencols;
+-- Attempts to replicate foreign tables, will result in an error
+CREATE FOREIGN DATA WRAPPER dummy;
+CREATE SERVER fdw FOREIGN DATA WRAPPER dummy;
+CREATE TABLE ptab(id int) PARTITION BY RANGE(id);
+CREATE TABLE part1 PARTITION OF ptab FOR VALUES FROM (0) TO (5);
+CREATE TABLE part2 PARTITION OF ptab FOR VALUES FROM (5) TO (15) PARTITION BY RANGE(id);
+CREATE TABLE part21 PARTITION OF part2 FOR VALUES FROM (5) TO (10);
+CREATE FOREIGN TABLE part22 PARTITION OF part2 FOR VALUES FROM (10) TO (15) SERVER fdw;
+CREATE PUBLICATION fdwpub FOR TABLE ptab;
+CREATE PUBLICATION fdwpub_via_root FOR TABLE ptab WITH (publish_via_partition_root);
+DROP TABLE ptab;
+DROP FOREIGN DATA WRAPPER dummy CASCADE;
+
RESET client_min_messages;
RESET SESSION AUTHORIZATION;
DROP ROLE regress_publication_user, regress_publication_user2;
--
2.43.0
