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: Tue, 18 Feb 2025 15:59:43 +0530
Message-ID: <CANhcyEUt8SfTfWpRdZBBJ=WBxH=x2-VXk5BzdJvXi=UvdwUiSA@mail.gmail.com> (raw)
In-Reply-To: <CALDaNm2+eL22Sbvj74uS37xvt=haQWcOwP15QnDuVeYsjHiffw@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>
<CANhcyEVbFMWnEkShq37SSgW7+X+xOxx9bvc4Jtj6BgAy9jGnqA@mail.gmail.com>
<CALDaNm3bULhtCaaBmdsun0LnHx9Jp0v228kjd=ZujEaaCEOmAA@mail.gmail.com>
<CANhcyEWYJccdE0VP7dnMXGXvM3Lp5em_27hBu3tvi5N3=nWohg@mail.gmail.com>
<CALDaNm2+eL22Sbvj74uS37xvt=haQWcOwP15QnDuVeYsjHiffw@mail.gmail.com>
On Mon, 17 Feb 2025 at 20:13, vignesh C <[email protected]> wrote:
>
> On Fri, 14 Feb 2025 at 12:59, Shlok Kyal <[email protected]> wrote:
> >
> > I have used the changes suggested by you. Also I have updated the
> > comments and the function name.
>
> There is another concurrency issue possible:
> +/* 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);
>
> Create a publication with publish_via_partition_root as true, hold the
> execution after check_partrel_has_foreign_table execution finishes.
> Then parallely execute the following:
> CREATE TABLE t1(id int) PARTITION BY RANGE(id);
> CREATE TABLE part1 PARTITION OF t1 FOR VALUES FROM (0) TO (5);
> CREATE TABLE part2 PARTITION OF t1 FOR VALUES FROM (5) TO (15)
> PARTITION BY RANGE(id);
> CREATE FOREIGN TABLE part2_1 PARTITION OF part2 FOR VALUES FROM (10)
> TO (15) SERVER fdw
>
> Now both the partitioned table having foreign table and a publication
> will be created.
>
Hi Vignesh,
I have addressed the above issue. If we take a ShareLock on the
pg_class, we won't be able to create table concurrently, which may
address the issue. Thoughts?
I have attached the v8 patch here.
Thanks and Regards,
Shlok Kyal
Attachments:
[application/octet-stream] v8-0001-Restrict-publishing-of-partitioned-table-with-for.patch (25.7K, ../CANhcyEUt8SfTfWpRdZBBJ=WBxH=x2-VXk5BzdJvXi=UvdwUiSA@mail.gmail.com/2-v8-0001-Restrict-publishing-of-partitioned-table-with-for.patch)
download | inline diff:
From 56cf18870d442cc3c23173997dbca8a060c65f43 Mon Sep 17 00:00:00 2001
From: Shlok Kyal <[email protected]>
Date: Tue, 11 Feb 2025 15:32:55 +0530
Subject: [PATCH v8] 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.
We have introduced two functions 'check_partrel_has_foreign_table' and
'check_foreign_tables'. In 'check_partrel_has_foreign_table' we go
through the child nodes of a partition and check if it has a foreign
table. While doing so, we take a AccessShareLock on each partition table
to avoid creation of foreign partition concurrently.
In 'check_foreign_tables' if schema id is provided we check for each
partitioned table in that schema if it has a foreign partition, or if
schema id is not provided we check for each partitioned table in the
database if it has a foreign partition. While doing so we take a
ShareLock on pg_class so no partition table is created concurrently after
this check.
---
src/backend/catalog/pg_publication.c | 137 +++++++++++++++++++---
src/backend/commands/foreigncmds.c | 47 ++++++++
src/backend/commands/publicationcmds.c | 47 ++++++++
src/backend/commands/tablecmds.c | 53 +++++++++
src/include/catalog/pg_publication.h | 5 +
src/test/regress/expected/publication.out | 89 ++++++++++++++
src/test/regress/sql/publication.sql | 81 +++++++++++++
7 files changed, 442 insertions(+), 17 deletions(-)
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index d6f94db5d99..d45577679ec 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(schemaid, pub->name);
+
/* Form a tuple */
memset(values, 0, sizeof(values));
memset(nulls, false, sizeof(nulls));
@@ -1332,3 +1348,90 @@ 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 = 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;
+}
+
+/*
+ * If valid schemaid provided, check if the 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.
+ *
+ * If valid schemaid is not provided, we get all partition tables and check if
+ * it has any foreign partition. We take a lock on partition tables so no new
+ * foreign partitions are added concurrently.
+ *
+ * We take a ShareLock on pg_class to restrict addition of new partitioned table
+ * which may contain a foreign partition while publication is being created.
+ */
+void
+check_foreign_tables(Oid schemaid, char *pubname)
+{
+ Relation classRel;
+ ScanKeyData key[3];
+ int keycount = 0;
+ TableScanDesc scan;
+ HeapTuple tuple;
+
+ classRel = table_open(RelationRelationId, ShareLock);
+
+ /* Get the root nodes of partitioned table */
+ ScanKeyInit(&key[keycount++],
+ Anum_pg_class_relkind,
+ BTEqualStrategyNumber, F_CHAREQ,
+ CharGetDatum(RELKIND_PARTITIONED_TABLE));
+
+ ScanKeyInit(&key[keycount++],
+ Anum_pg_class_relispartition,
+ BTEqualStrategyNumber, F_BOOLEQ,
+ BoolGetDatum(false));
+
+ /* If schema id is provided check partitioned table in that schema */
+ if (OidIsValid(schemaid))
+ ScanKeyInit(&key[keycount++],
+ Anum_pg_class_relnamespace,
+ BTEqualStrategyNumber, F_OIDEQ,
+ schemaid);
+
+ scan = table_beginscan_catalog(classRel, keycount, key);
+ while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
+ {
+ Form_pg_class relForm = (Form_pg_class) GETSTRUCT(tuple);
+
+ 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(relForm->oid))));
+ }
+
+ table_endscan(scan);
+ table_close(classRel, NoLock);
+}
diff --git a/src/backend/commands/foreigncmds.c b/src/backend/commands/foreigncmds.c
index c14e038d54f..f41a24aea39 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 150a768d16f..e1121a94f92 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 partitioned table has foreign partition */
+ if (publish_via_partition_root)
+ check_foreign_tables(InvalidOid, 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(InvalidOid, pubname);
+
+ schemaoids = GetPublicationSchemas(pubform->oid);
+
+ foreach_oid(schemaoid, schemaoids)
+ check_foreign_tables(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 72a1b64c2a2..e96e58903ec 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 48c7d1a8615..50705d12c98 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,8 @@ 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(Oid schemaid, char *pubname);
+
#endif /* PG_PUBLICATION_H */
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 4de96c04f9d..6a0c245bd60 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 68001de4000..49c9d98b668 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 (9+ messages) latest in thread
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: <CANhcyEUt8SfTfWpRdZBBJ=WBxH=x2-VXk5BzdJvXi=UvdwUiSA@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