public inbox for [email protected]
help / color / mirror / Atom feedFrom: Shlok Kyal <[email protected]>
To: vignesh C <[email protected]>
Cc: Álvaro Herrera <[email protected]>
Cc: Sergey Tatarintsev <[email protected]>
Cc: [email protected]
Subject: Re: Restrict publishing of partitioned table with a foreign table as partition
Date: Thu, 13 Feb 2025 15:50:38 +0530
Message-ID: <CANhcyEVbFMWnEkShq37SSgW7+X+xOxx9bvc4Jtj6BgAy9jGnqA@mail.gmail.com> (raw)
In-Reply-To: <CALDaNm2Q1rNN-L8u95pjVRw-aBJkcGJh2bHeaPc2bO=mj_1QMA@mail.gmail.com>
References: <CALDaNm0EA4zuR+PiNmADGORcADQsPSmYKo=tr4hT=pSrLY1B9A@mail.gmail.com>
<[email protected]>
<CANhcyEXxjq9U7BXxCba3Njz+eHpNzAsSVY2GzbV=8iy5j=UAUA@mail.gmail.com>
<CALDaNm2Q1rNN-L8u95pjVRw-aBJkcGJh2bHeaPc2bO=mj_1QMA@mail.gmail.com>
On Thu, 13 Feb 2025 at 11:25, vignesh C <[email protected]> wrote:
>
> On Tue, 11 Feb 2025 at 16:55, Shlok Kyal <[email protected]> wrote:
> > I have handled the above cases and added tests for the same.
>
> There is a concurrency issue with the patch:
> +check_partrel_has_foreign_table(Form_pg_class relform)
> +{
> + bool has_foreign_tbl = false;
> +
> + if (relform->relkind == RELKIND_PARTITIONED_TABLE)
> + {
> + List *relids = NIL;
> +
> + relids = find_all_inheritors(relform->oid, NoLock, NULL);
> +
> + foreach_oid(relid, relids)
> + {
> + Relation rel = table_open(relid,
> AccessShareLock);
> +
> + if (RelationGetForm(rel)->relkind ==
> RELKIND_FOREIGN_TABLE)
> + has_foreign_tbl = true;
> +
> + table_close(rel, AccessShareLock);
> +
> + if (has_foreign_tbl)
> + break;
> + }
> + }
> +
> + return has_foreign_tbl;
> +}
>
> In an ideal scenario, the creation of a foreign table should fail if
> there is an associated publication, as demonstrated below:
> CREATE TABLE t(id int) PARTITION BY RANGE(id);
> CREATE TABLE part1 PARTITION OF t FOR VALUES FROM (0) TO (5);
> CREATE TABLE part2 PARTITION OF t FOR VALUES FROM (5) TO (15)
> PARTITION BY RANGE(id);
> CREATE PUBLICATION pub1 FOR TABLE t with (publish_via_partition_root = true);
>
> postgres=# CREATE FOREIGN TABLE part22 PARTITION OF part2 FOR VALUES
> FROM (10) TO (15) SERVER fdw;
> ERROR: cannot create table foreign partition "part22"
> DETAIL: partition table "part2" is published with option
> publish_via_partition_root
>
> Consider a scenario where the publication is being created and after
> the check_partrel_has_foreign_table execution is done, concurrently
> creation of foreign table is executed, then the creation will be
> successful.
> postgres=# CREATE FOREIGN TABLE part22 PARTITION OF part2 FOR VALUES
> FROM (10) TO (15) SERVER fdw;
> CREATE FOREIGN TABLE
>
> I felt the problem here is that you have released the lock:
> + if (RelationGetForm(rel)->relkind ==
> RELKIND_FOREIGN_TABLE)
> + has_foreign_tbl = true;
> +
> + table_close(rel, AccessShareLock);
>
> We should retain the lock to fix this issue:
> + if (RelationGetForm(rel)->relkind ==
> RELKIND_FOREIGN_TABLE)
> + has_foreign_tbl = true;
> +
> + table_close(rel, NoLock);
>
Hi Vignesh,
I have fixed the issue. Attached the updated v6 patch.
Thanks and Regards,
Shlok Kyal
Attachments:
[application/octet-stream] v6-0001-Restrict-publishing-of-partitioned-table-with-for.patch (25.9K, ../CANhcyEVbFMWnEkShq37SSgW7+X+xOxx9bvc4Jtj6BgAy9jGnqA@mail.gmail.com/2-v6-0001-Restrict-publishing-of-partitioned-table-with-for.patch)
download | inline diff:
From cd882eb524b192e6260d6459c06200229bdd6a7d Mon Sep 17 00:00:00 2001
From: Shlok Kyal <[email protected]>
Date: Tue, 11 Feb 2025 15:32:55 +0530
Subject: [PATCH v6] Restrict publishing of partitioned table with foreign
partition
Logical replication of foreign table is not supported and we throw an
error in this case. But when create a publication on a partitioned
table that has a foreign partition, the initial sync of such table is
successful and we should avoid such cases.
Current Behaviour, when publication is created:
1. with publish_via_partition_root = true
Root table is published and the initial data of foreign partitions
are replicated.
2. with publish_via_partition_root = false and FOR ALL TABLES
All leaf tables except foreign partitions are published.
3. with publish_via_partition_root = false and
FOR TABLE/ FOR TABLES IN SCHEMA
All leaf tables are published. Initial data of foreign partitions are
replicated.
With this patch we have following behaviour:
1. with publish_via_partition_root = true
We throw an error when we try to publish a foreign partititon. Error
is thrown when we try to create a publication on (or add to existing
publication) a partitioned table with foreign partition, when try to
create a foreign partition and when we try to attach foreign table (or
a table with foreign partition) to existing published tables.
2. with publish_via_partition_root = false
We skip publishing foreign partition which are part of the publication.
This is done by avoid adding foreign partitions in pg_subscription_rel
catalog table.
---
src/backend/catalog/pg_publication.c | 181 ++++++++++++++++++++--
src/backend/commands/foreigncmds.c | 47 ++++++
src/backend/commands/publicationcmds.c | 47 ++++++
src/backend/commands/tablecmds.c | 53 +++++++
src/include/catalog/pg_publication.h | 7 +
src/test/regress/expected/publication.out | 89 +++++++++++
src/test/regress/sql/publication.sql | 81 ++++++++++
7 files changed, 488 insertions(+), 17 deletions(-)
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index d6f94db5d9..520fa2f382 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -53,7 +53,7 @@ typedef struct
* error if not.
*/
static void
-check_publication_add_relation(Relation targetrel)
+check_publication_add_relation(Relation targetrel, Publication *pub)
{
/* Must be a regular or partitioned table */
if (RelationGetForm(targetrel)->relkind != RELKIND_RELATION &&
@@ -64,6 +64,19 @@ check_publication_add_relation(Relation targetrel)
RelationGetRelationName(targetrel)),
errdetail_relkind_not_supported(RelationGetForm(targetrel)->relkind)));
+ /*
+ * publish_via_root_partition cannot be true if it is a partitioned table
+ * and has any foreign partition
+ */
+ if (pub->pubviaroot &&
+ check_partrel_has_foreign_table(RelationGetForm(targetrel)))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("cannot set parameter \"%s\" to true for publication \"%s\"",
+ "publish_via_partition_root", pub->name),
+ errdetail("partition table \"%s\" in publication contains a foreign partition",
+ RelationGetRelationName(targetrel))));
+
/* Can't be system table */
if (IsCatalogRelation(targetrel))
ereport(ERROR,
@@ -304,7 +317,7 @@ check_and_fetch_column_list(Publication *pub, Oid relid, MemoryContext mcxt,
/*
* Gets the relations based on the publication partition option for a specified
- * relation.
+ * relation. Foreign tables are not included.
*/
List *
GetPubPartitionOptionRelations(List *result, PublicationPartOpt pub_partopt,
@@ -313,25 +326,21 @@ GetPubPartitionOptionRelations(List *result, PublicationPartOpt pub_partopt,
if (get_rel_relkind(relid) == RELKIND_PARTITIONED_TABLE &&
pub_partopt != PUBLICATION_PART_ROOT)
{
- List *all_parts = find_all_inheritors(relid, NoLock,
- NULL);
+ List *all_parts = find_all_inheritors(relid, NoLock, NULL);
- if (pub_partopt == PUBLICATION_PART_ALL)
- result = list_concat(result, all_parts);
- else if (pub_partopt == PUBLICATION_PART_LEAF)
+ foreach_oid(partOid, all_parts)
{
- ListCell *lc;
+ char relkind = get_rel_relkind(partOid);
- foreach(lc, all_parts)
- {
- Oid partOid = lfirst_oid(lc);
+ if (relkind == RELKIND_FOREIGN_TABLE)
+ continue;
- if (get_rel_relkind(partOid) != RELKIND_PARTITIONED_TABLE)
- result = lappend_oid(result, partOid);
- }
+ if (pub_partopt == PUBLICATION_PART_LEAF &&
+ relkind == RELKIND_PARTITIONED_TABLE)
+ continue;
+
+ result = lappend_oid(result, partOid);
}
- else
- Assert(false);
}
else
result = lappend_oid(result, relid);
@@ -463,7 +472,7 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri,
RelationGetRelationName(targetrel), pub->name)));
}
- check_publication_add_relation(targetrel);
+ check_publication_add_relation(targetrel, pub);
/* Validate and translate column names into a Bitmapset of attnums. */
attnums = pub_collist_validate(pri->relation, pri->columns);
@@ -703,6 +712,13 @@ publication_add_schema(Oid pubid, Oid schemaid, bool if_not_exists)
check_publication_add_schema(schemaid);
+ /*
+ * If publish_via_partition_root is true, check if schema has any foreign
+ * partition
+ */
+ if (pub->pubviaroot)
+ check_foreign_tables_in_schema(schemaid, pub->name);
+
/* Form a tuple */
memset(values, 0, sizeof(values));
memset(nulls, false, sizeof(nulls));
@@ -1332,3 +1348,134 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
SRF_RETURN_DONE(funcctx);
}
+
+/* Check if a partitioned table has a foreign partition */
+bool
+check_partrel_has_foreign_table(Form_pg_class relform)
+{
+ bool has_foreign_tbl = false;
+
+ if (relform->relkind == RELKIND_PARTITIONED_TABLE)
+ {
+ List *relids = NIL;
+
+ relids = find_all_inheritors(relform->oid, NoLock, NULL);
+
+ foreach_oid(relid, relids)
+ {
+ Relation rel = table_open(relid, AccessShareLock);
+
+ if (RelationGetForm(rel)->relkind == RELKIND_FOREIGN_TABLE)
+ has_foreign_tbl = true;
+
+ table_close(rel, NoLock);
+
+ if (has_foreign_tbl)
+ break;
+ }
+ }
+
+ return has_foreign_tbl;
+}
+
+/*
+ * Check if a schema has a partition table which has a foreign partition.
+ * The partition tables in a schema can have partitions in other schema. We also
+ * need to check if such partitions are foreign partition.
+ */
+void
+check_foreign_tables_in_schema(Oid schemaid, char *pubname)
+{
+ Relation classRel;
+ ScanKeyData key[2];
+ TableScanDesc scan;
+ HeapTuple tuple;
+
+ classRel = table_open(RelationRelationId, AccessShareLock);
+
+ ScanKeyInit(&key[0],
+ Anum_pg_class_relnamespace,
+ BTEqualStrategyNumber, F_OIDEQ,
+ schemaid);
+ ScanKeyInit(&key[1],
+ Anum_pg_class_relkind,
+ BTEqualStrategyNumber, F_CHAREQ,
+ CharGetDatum(RELKIND_PARTITIONED_TABLE));
+
+ scan = table_beginscan_catalog(classRel, 2, key);
+ while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
+ {
+ Form_pg_class relForm = (Form_pg_class) GETSTRUCT(tuple);
+
+ if (check_partrel_has_foreign_table(relForm))
+ {
+ List *ancestors = get_partition_ancestors(relForm->oid);
+ Oid parent_oid = relForm->oid;
+ char *parent_name;
+
+ foreach_oid(ancestor, ancestors)
+ {
+ Oid ancestor_schemaid = get_rel_namespace(ancestor);
+
+ if (ancestor_schemaid == schemaid)
+ parent_oid = ancestor;
+ }
+
+ list_free(ancestors);
+ parent_name = get_rel_name(parent_oid);
+
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("cannot set parameter \"%s\" to true for publication \"%s\"",
+ "publish_via_partition_root", pubname),
+ errdetail("partition table \"%s\" in publication contains a foreign partition",
+ parent_name)));
+ }
+ }
+
+ table_endscan(scan);
+ table_close(classRel, AccessShareLock);
+}
+
+/* Check if any foreign table is a partition table */
+void
+check_foreign_tables(char *pubname)
+{
+ Relation classRel;
+ ScanKeyData key[1];
+ TableScanDesc scan;
+ HeapTuple tuple;
+
+ classRel = table_open(RelationRelationId, AccessShareLock);
+
+ ScanKeyInit(&key[0],
+ Anum_pg_class_relkind,
+ BTEqualStrategyNumber, F_CHAREQ,
+ CharGetDatum(RELKIND_FOREIGN_TABLE));
+
+ scan = table_beginscan_catalog(classRel, 1, key);
+ while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
+ {
+ Form_pg_class relForm = (Form_pg_class) GETSTRUCT(tuple);
+
+ if (relForm->relispartition)
+ {
+ Oid parent_oid;
+ char *parent_name;
+ List *ancestors = get_partition_ancestors(relForm->oid);
+
+ parent_oid = llast_oid(ancestors);
+ parent_name = get_rel_name(parent_oid);
+
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("cannot set parameter \"%s\" to true for publication \"%s\"",
+ "publish_via_partition_root", pubname),
+ errdetail("partition table \"%s\" in publication contains a foreign partition",
+ parent_name)));
+ }
+ }
+
+ table_endscan(scan);
+ table_close(classRel, AccessShareLock);
+}
diff --git a/src/backend/commands/foreigncmds.c b/src/backend/commands/foreigncmds.c
index c14e038d54..f41a24aea3 100644
--- a/src/backend/commands/foreigncmds.c
+++ b/src/backend/commands/foreigncmds.c
@@ -21,6 +21,7 @@
#include "catalog/dependency.h"
#include "catalog/indexing.h"
#include "catalog/objectaccess.h"
+#include "catalog/partition.h"
#include "catalog/pg_foreign_data_wrapper.h"
#include "catalog/pg_foreign_server.h"
#include "catalog/pg_foreign_table.h"
@@ -1423,6 +1424,52 @@ CreateForeignTable(CreateForeignTableStmt *stmt, Oid relid)
ftrel = table_open(ForeignTableRelationId, RowExclusiveLock);
+ /*
+ * Check if it is a foreign partition and the partitioned table is not
+ * published or published with publish_via_partition_root option as false.
+ */
+ if (stmt->base.partbound != NULL)
+ {
+ RangeVar *root = castNode(RangeVar, lfirst(list_head(stmt->base.inhRelations)));
+ Relation rootrel = table_openrv(root, AccessShareLock);
+
+ if (RelationGetForm(rootrel)->relkind == RELKIND_PARTITIONED_TABLE)
+ {
+ Oid schemaid = RelationGetNamespace(rootrel);
+ List *puboids = GetRelationPublications(rootrel->rd_id);
+ List *ancestors;
+
+ puboids = list_concat_unique_oid(puboids, GetAllTablesPublications());
+ puboids = list_concat_unique_oid(puboids, GetSchemaPublications(schemaid));
+ ancestors = get_partition_ancestors(rootrel->rd_id);
+
+ foreach_oid(ancestor, ancestors)
+ {
+ puboids = list_concat_unique_oid(puboids,
+ GetRelationPublications(ancestor));
+ schemaid = get_rel_namespace(ancestor);
+ puboids = list_concat_unique_oid(puboids,
+ GetSchemaPublications(schemaid));
+ }
+ list_free(ancestors);
+
+ foreach_oid(puboid, puboids)
+ {
+ Publication *pub = GetPublication(puboid);
+
+ if (pub->pubviaroot)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot create table foreign partition \"%s\"",
+ get_rel_name(relid)),
+ errdetail("partition table \"%s\" is published with option publish_via_partition_root",
+ RelationGetRelationName(rootrel))));
+ }
+ }
+
+ table_close(rootrel, AccessShareLock);
+ }
+
/*
* For now the owner cannot be specified on create. Use effective user ID.
*/
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index 150a768d16..e71d5408c7 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -876,6 +876,10 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
/* Associate objects with the publication. */
if (stmt->for_all_tables)
{
+ /* Check if any foreign table is a part of partitioned table */
+ if (publish_via_partition_root)
+ check_foreign_tables(stmt->pubname);
+
/* Invalidate relcache so that publication info is rebuilt. */
CacheInvalidateRelcacheAll();
}
@@ -1041,6 +1045,49 @@ AlterPublicationOptions(ParseState *pstate, AlterPublicationStmt *stmt,
}
}
+ /*
+ * If publish_via_partition_root is set to true, check if the publication
+ * already have any foreign partition
+ */
+ if (publish_via_partition_root_given && publish_via_partition_root)
+ {
+ List *schemaoids = NIL;
+ List *relids = NIL;
+
+ char *pubname = stmt->pubname;
+
+ if (pubform->puballtables)
+ check_foreign_tables(pubname);
+
+ schemaoids = GetPublicationSchemas(pubform->oid);
+
+ foreach_oid(schemaoid, schemaoids)
+ check_foreign_tables_in_schema(schemaoid, pubname);
+
+ relids = GetPublicationRelations(pubform->oid, PUBLICATION_PART_ROOT);
+
+ foreach_oid(relid, relids)
+ {
+ HeapTuple reltup;
+ Form_pg_class relform;
+
+ reltup = SearchSysCache1(RELOID, ObjectIdGetDatum(relid));
+ relform = (Form_pg_class) GETSTRUCT(reltup);
+
+ ReleaseSysCache(reltup);
+
+ if (check_partrel_has_foreign_table(relform))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("cannot set parameter \"%s\" to true for publication \"%s\"",
+ "publish_via_partition_root", pubname),
+ errdetail("partition table \"%s\" in publication contains a foreign partition",
+ get_rel_name(relid))));
+ }
+ }
+ }
+
/* Everything ok, form a new tuple. */
memset(values, 0, sizeof(values));
memset(nulls, false, sizeof(nulls));
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 5823fce934..fccc1082f4 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -19440,6 +19440,59 @@ ATExecAttachPartition(List **wqueue, Relation rel, PartitionCmd *cmd,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("cannot attach temporary relation of another session as partition")));
+ /*
+ * Check if attachrel is a foreign table or a partitioned table with
+ * foreign partition and rel is not part of publication with option
+ * publish_via_partition_root as true.
+ */
+ if (attachrel->rd_rel->relkind == RELKIND_FOREIGN_TABLE ||
+ check_partrel_has_foreign_table(RelationGetForm(attachrel)))
+ {
+ Oid schemaid = RelationGetNamespace(rel);
+ List *puboids = GetRelationPublications(rel->rd_id);
+ List *ancestors;
+ char *relname = get_rel_name(rel->rd_id);
+ char *attachrelname = get_rel_name(attachrel->rd_id);
+
+ puboids = list_concat_unique_oid(puboids, GetAllTablesPublications());
+ puboids = list_concat_unique_oid(puboids, GetSchemaPublications(schemaid));
+ ancestors = get_partition_ancestors(rel->rd_id);
+
+ foreach_oid(ancestor, ancestors)
+ {
+ puboids = list_concat_unique_oid(puboids,
+ GetRelationPublications(ancestor));
+ schemaid = get_rel_namespace(ancestor);
+ puboids = list_concat_unique_oid(puboids,
+ GetSchemaPublications(schemaid));
+ }
+
+ list_free(ancestors);
+
+ foreach_oid(puboid, puboids)
+ {
+ Publication *pub = GetPublication(puboid);
+
+ if (pub->pubviaroot)
+ {
+ if (attachrel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot attach foreign table \"%s\" to partition table \"%s\"",
+ attachrelname, relname),
+ errdetail("partition table \"%s\" is published with option publish_via_partition_root",
+ relname)));
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot attach table \"%s\" with foreign partition to partition table \"%s\"",
+ attachrelname, relname),
+ errdetail("partition table \"%s\" is published with option publish_via_partition_root",
+ relname)));
+ }
+ }
+ }
+
/*
* Check if attachrel has any identity columns or any columns that aren't
* in the parent.
diff --git a/src/include/catalog/pg_publication.h b/src/include/catalog/pg_publication.h
index 48c7d1a861..b1bb864d2f 100644
--- a/src/include/catalog/pg_publication.h
+++ b/src/include/catalog/pg_publication.h
@@ -19,6 +19,7 @@
#include "catalog/genbki.h"
#include "catalog/objectaddress.h"
+#include "catalog/pg_class.h"
#include "catalog/pg_publication_d.h" /* IWYU pragma: export */
/* ----------------
@@ -191,4 +192,10 @@ extern Bitmapset *pub_collist_to_bitmapset(Bitmapset *columns, Datum pubcols,
extern Bitmapset *pub_form_cols_map(Relation relation,
PublishGencolsType include_gencols_type);
+extern bool check_partrel_has_foreign_table(Form_pg_class relform);
+
+extern void check_foreign_tables_in_schema(Oid schemaid, char *pubname);
+
+extern void check_foreign_tables(char *pubname);
+
#endif /* PG_PUBLICATION_H */
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 4de96c04f9..6a0c245bd6 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -1924,6 +1924,95 @@ DROP PUBLICATION pub1;
DROP PUBLICATION pub2;
DROP TABLE gencols;
RESET client_min_messages;
+-- ======================================================
+-- Test when foreign table is a partition of a partitioned table on which
+-- publication is created
+SET client_min_messages = 'ERROR';
+CREATE FOREIGN DATA WRAPPER test_fdw;
+CREATE SERVER fdw_server FOREIGN DATA WRAPPER test_fdw;
+CREATE SCHEMA sch3;
+CREATE TABLE sch3.tmain(id int) PARTITION BY RANGE(id);
+CREATE TABLE sch3.part1 PARTITION OF sch3.tmain FOR VALUES FROM (0) TO (5);
+CREATE TABLE sch3.part2(id int) PARTITION BY RANGE(id);
+CREATE FOREIGN TABLE sch3.part2_1 PARTITION OF sch3.part2 FOR VALUES FROM (5) TO (10) SERVER fdw_server;
+ALTER TABLE sch3.tmain ATTACH PARTITION sch3.part2 FOR VALUES FROM (5) TO (10);
+-- Can't create publications with publish_via_partition_root = true, if table
+-- has a foreign partition
+CREATE PUBLICATION pub1 FOR TABLE sch3.tmain WITH (publish_via_partition_root);
+ERROR: cannot set parameter "publish_via_partition_root" to true for publication "pub1"
+DETAIL: partition table "tmain" in publication contains a foreign partition
+CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA sch3 WITH (publish_via_partition_root);
+ERROR: cannot set parameter "publish_via_partition_root" to true for publication "pub1"
+DETAIL: partition table "tmain" in publication contains a foreign partition
+CREATE PUBLICATION pub1 FOR ALL TABLES WITH (publish_via_partition_root);
+ERROR: cannot set parameter "publish_via_partition_root" to true for publication "pub1"
+DETAIL: partition table "tmain" in publication contains a foreign partition
+-- Test when a partitioned table with foreign table as a partition is attached
+-- to partitioned table which is already published
+ALTER TABLE sch3.tmain DETACH PARTITION sch3.part2;
+CREATE PUBLICATION pub1 FOR TABLE sch3.tmain WITH (publish_via_partition_root);
+ALTER TABLE sch3.tmain ATTACH PARTITION sch3.part2 FOR VALUES FROM (5) TO (10);
+ERROR: cannot attach table "part2" with foreign partition to partition table "tmain"
+DETAIL: partition table "tmain" is published with option publish_via_partition_root
+-- Can't create foreign partition of published table with
+-- publish_via_partition_root = true
+CREATE FOREIGN TABLE sch3.part3_1 PARTITION OF sch3.tmain FOR VALUES FROM (10) TO (15) SERVER fdw_server;
+ERROR: cannot create table foreign partition "part3_1"
+DETAIL: partition table "tmain" is published with option publish_via_partition_root
+-- Can't attach foreign partition to published table with
+-- publish_via_partition_root = true
+CREATE FOREIGN TABLE sch3.part3_2(id int) SERVER fdw_server;
+ALTER TABLE sch3.tmain ATTACH PARTITION sch3.part3_2 FOR VALUES FROM (15) TO (20);
+ERROR: cannot attach foreign table "part3_2" to partition table "tmain"
+DETAIL: partition table "tmain" is published with option publish_via_partition_root
+CREATE SCHEMA sch4;
+CREATE TABLE sch4.tmain(id int) PARTITION BY RANGE(id);
+-- publication created with FOR TABLES IN SCHEMA
+DROP PUBLICATION pub1;
+CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA sch4 WITH (publish_via_partition_root);
+-- Can't create foreign partition of published table with
+-- publish_via_partition_root = true
+CREATE FOREIGN TABLE sch3.part3_1 PARTITION OF sch4.tmain FOR VALUES FROM (10) TO (15) SERVER fdw_server;
+ERROR: cannot create table foreign partition "part3_1"
+DETAIL: partition table "tmain" is published with option publish_via_partition_root
+-- Can't attach foreign partition to published table with
+-- publish_via_partition_root = true
+ALTER TABLE sch4.tmain ATTACH PARTITION sch3.part3_2 FOR VALUES FROM (15) TO (20);
+ERROR: cannot attach foreign table "part3_2" to partition table "tmain"
+DETAIL: partition table "tmain" is published with option publish_via_partition_root
+DROP PUBLICATION pub1;
+-- Test with publish_via_partition_root = false
+-- Foreign partitions are skipped by default
+CREATE PUBLICATION pub1 FOR TABLE sch3.tmain;
+CREATE PUBLICATION pub2 FOR TABLES IN SCHEMA sch3;
+CREATE PUBLICATION pub3 FOR ALL TABLES;
+-- Create foreign partition of published table with
+-- publish_via_partition_root = false
+CREATE FOREIGN TABLE sch3.part3_1 PARTITION OF sch3.tmain FOR VALUES FROM (10) TO (15) SERVER fdw_server;
+-- Attach foreign partition to published table
+-- publish_via_partition_root = false
+ALTER TABLE sch3.tmain ATTACH PARTITION sch3.part3_2 FOR VALUES FROM (15) TO (20);
+-- Check the published tables
+SELECT pubname, tablename FROM pg_publication_tables WHERE schemaname in ('sch3', 'sch4') ORDER BY pubname, tablename;
+ pubname | tablename
+---------+-----------
+ pub1 | part1
+ pub2 | part1
+ pub3 | part1
+(3 rows)
+
+-- Can't alter publish_via_partition_root to true, if publication already have
+-- foreign partition
+ALTER PUBLICATION pub1 SET (publish_via_partition_root);
+ERROR: cannot set parameter "publish_via_partition_root" to true for publication "pub1"
+DETAIL: partition table "tmain" in publication contains a foreign partition
+DROP PUBLICATION pub1;
+DROP PUBLICATION pub2;
+DROP PUBLICATION pub3;
+DROP SCHEMA sch3 CASCADE;
+DROP SCHEMA sch4 CASCADE;
+DROP SERVER fdw_server;
+DROP FOREIGN DATA WRAPPER test_fdw;
RESET SESSION AUTHORIZATION;
DROP ROLE regress_publication_user, regress_publication_user2;
DROP ROLE regress_publication_user_dummy;
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 68001de400..49c9d98b66 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -1223,6 +1223,87 @@ DROP PUBLICATION pub2;
DROP TABLE gencols;
RESET client_min_messages;
+-- ======================================================
+
+-- Test when foreign table is a partition of a partitioned table on which
+-- publication is created
+SET client_min_messages = 'ERROR';
+CREATE FOREIGN DATA WRAPPER test_fdw;
+CREATE SERVER fdw_server FOREIGN DATA WRAPPER test_fdw;
+
+CREATE SCHEMA sch3;
+CREATE TABLE sch3.tmain(id int) PARTITION BY RANGE(id);
+CREATE TABLE sch3.part1 PARTITION OF sch3.tmain FOR VALUES FROM (0) TO (5);
+CREATE TABLE sch3.part2(id int) PARTITION BY RANGE(id);
+CREATE FOREIGN TABLE sch3.part2_1 PARTITION OF sch3.part2 FOR VALUES FROM (5) TO (10) SERVER fdw_server;
+ALTER TABLE sch3.tmain ATTACH PARTITION sch3.part2 FOR VALUES FROM (5) TO (10);
+
+-- Can't create publications with publish_via_partition_root = true, if table
+-- has a foreign partition
+CREATE PUBLICATION pub1 FOR TABLE sch3.tmain WITH (publish_via_partition_root);
+CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA sch3 WITH (publish_via_partition_root);
+CREATE PUBLICATION pub1 FOR ALL TABLES WITH (publish_via_partition_root);
+
+-- Test when a partitioned table with foreign table as a partition is attached
+-- to partitioned table which is already published
+ALTER TABLE sch3.tmain DETACH PARTITION sch3.part2;
+CREATE PUBLICATION pub1 FOR TABLE sch3.tmain WITH (publish_via_partition_root);
+ALTER TABLE sch3.tmain ATTACH PARTITION sch3.part2 FOR VALUES FROM (5) TO (10);
+
+-- Can't create foreign partition of published table with
+-- publish_via_partition_root = true
+CREATE FOREIGN TABLE sch3.part3_1 PARTITION OF sch3.tmain FOR VALUES FROM (10) TO (15) SERVER fdw_server;
+
+-- Can't attach foreign partition to published table with
+-- publish_via_partition_root = true
+CREATE FOREIGN TABLE sch3.part3_2(id int) SERVER fdw_server;
+ALTER TABLE sch3.tmain ATTACH PARTITION sch3.part3_2 FOR VALUES FROM (15) TO (20);
+
+CREATE SCHEMA sch4;
+CREATE TABLE sch4.tmain(id int) PARTITION BY RANGE(id);
+
+-- publication created with FOR TABLES IN SCHEMA
+DROP PUBLICATION pub1;
+CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA sch4 WITH (publish_via_partition_root);
+
+-- Can't create foreign partition of published table with
+-- publish_via_partition_root = true
+CREATE FOREIGN TABLE sch3.part3_1 PARTITION OF sch4.tmain FOR VALUES FROM (10) TO (15) SERVER fdw_server;
+
+-- Can't attach foreign partition to published table with
+-- publish_via_partition_root = true
+ALTER TABLE sch4.tmain ATTACH PARTITION sch3.part3_2 FOR VALUES FROM (15) TO (20);
+DROP PUBLICATION pub1;
+
+-- Test with publish_via_partition_root = false
+-- Foreign partitions are skipped by default
+CREATE PUBLICATION pub1 FOR TABLE sch3.tmain;
+CREATE PUBLICATION pub2 FOR TABLES IN SCHEMA sch3;
+CREATE PUBLICATION pub3 FOR ALL TABLES;
+
+-- Create foreign partition of published table with
+-- publish_via_partition_root = false
+CREATE FOREIGN TABLE sch3.part3_1 PARTITION OF sch3.tmain FOR VALUES FROM (10) TO (15) SERVER fdw_server;
+
+-- Attach foreign partition to published table
+-- publish_via_partition_root = false
+ALTER TABLE sch3.tmain ATTACH PARTITION sch3.part3_2 FOR VALUES FROM (15) TO (20);
+
+-- Check the published tables
+SELECT pubname, tablename FROM pg_publication_tables WHERE schemaname in ('sch3', 'sch4') ORDER BY pubname, tablename;
+
+-- Can't alter publish_via_partition_root to true, if publication already have
+-- foreign partition
+ALTER PUBLICATION pub1 SET (publish_via_partition_root);
+
+DROP PUBLICATION pub1;
+DROP PUBLICATION pub2;
+DROP PUBLICATION pub3;
+DROP SCHEMA sch3 CASCADE;
+DROP SCHEMA sch4 CASCADE;
+DROP SERVER fdw_server;
+DROP FOREIGN DATA WRAPPER test_fdw;
+
RESET SESSION AUTHORIZATION;
DROP ROLE regress_publication_user, regress_publication_user2;
DROP ROLE regress_publication_user_dummy;
--
2.34.1
view thread (6+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Restrict publishing of partitioned table with a foreign table as partition
In-Reply-To: <CANhcyEVbFMWnEkShq37SSgW7+X+xOxx9bvc4Jtj6BgAy9jGnqA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox