public inbox for [email protected]
help / color / mirror / Atom feedFrom: Shlok Kyal <[email protected]>
To: Sergey Tatarintsev <[email protected]>
Cc: Álvaro Herrera <[email protected]>
Cc: vignesh C <[email protected]>
Cc: [email protected]
Subject: Re: Restrict publishing of partitioned table with a foreign table as partition
Date: Fri, 4 Apr 2025 14:44:17 +0530
Message-ID: <CANhcyEXSOuv7=ueqU3ZqZCsFGb1+EEpZVQAUkz1uvUpJTUOjqg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CANhcyEU5R4qdkO8SLSswRWgBRDPEaapAQ61rCjjDBg4RNmmsrw@mail.gmail.com>
<[email protected]>
<CANhcyEXVPUAir9G1G1gT9k-xAMzhiV7GRX_fr1rkRhURNKDbxg@mail.gmail.com>
<[email protected]>
On Fri, 4 Apr 2025 at 10:36, Sergey Tatarintsev
<[email protected]> wrote:
>
> 01.04.2025 21:48, Shlok Kyal пишет:
> > On Fri, 28 Mar 2025 at 16:35, Álvaro Herrera <[email protected]> wrote:
> >> On 2025-Mar-28, Shlok Kyal wrote:
> >>
> >>> On Mon, 24 Mar 2025 at 21:17, Álvaro Herrera <[email protected]> wrote:
> >>>> Also, surely we should document this restriction in the SGML docs
> >>>> somewhere.
> >>> I have added comment in create_publication.sgml
> >> Hmm, okay, but "We cannot" is not the style used in the documentation.
> >> In addition, I think this mechanism should be mentioned in
> >> logical-replication.sgml; currently there's a note in the Restrictions
> >> section about foreign tables, which should be expanded to explain this.
> >>
> > I have modified the comment in create_publication.sgml and also added
> > comment in the restrictions section of logical-replication.sgml.
> > I have also added a more detailed explanation in comment of
> > 'check_foreign_tables'
> >
> > I have attached the updated v11 patch.
> >
> >
> > Thanks and Regards,
> > Shlok Kyal
>
> Hi!
>
> I looked at the latest version of the patch, and think that we should
> free puboids list here:
>
> diff --git a/src/backend/commands/tablecmds.c
> b/src/backend/commands/tablecmds.c
> index 6a128f7bd4e..4254654cc24 100644
> --- a/src/backend/commands/tablecmds.c
> +++ b/src/backend/commands/tablecmds.c
> @@ -20122,6 +20122,7 @@ ATExecAttachPartition(List **wqueue, Relation
> rel, PartitionCmd *cmd,
> relname)));
> }
> }
> + list_free(puboids);
> }
>
> /*
>
Hi Sergey,
Thanks for reviewing the patch.
I have fixed the comment. I also found other places where we should
free the relids, schemaoids, and puboids list. I have added changes
for those as well.
I have attached the updated patch.
Thanks and Regards,
Shlok Kyal
Attachments:
[application/octet-stream] v12-0001-Restrict-publishing-of-partitioned-table-with-fo.patch (30.1K, ../CANhcyEXSOuv7=ueqU3ZqZCsFGb1+EEpZVQAUkz1uvUpJTUOjqg@mail.gmail.com/2-v12-0001-Restrict-publishing-of-partitioned-table-with-fo.patch)
download | inline diff:
From 7ef4b3095c9a0b308192898302e8415ba27d60e6 Mon Sep 17 00:00:00 2001
From: Shlok Kyal <[email protected]>
Date: Fri, 28 Mar 2025 11:15:09 +0530
Subject: [PATCH v12] 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 in HEAD, 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_partitioned_table so no partition table is created
concurrently after this check.
---
doc/src/sgml/logical-replication.sgml | 10 +-
doc/src/sgml/ref/create_publication.sgml | 5 +
src/backend/catalog/pg_publication.c | 166 +++++++++++++++++++---
src/backend/commands/foreigncmds.c | 50 +++++++
src/backend/commands/publicationcmds.c | 47 ++++++
src/backend/commands/tablecmds.c | 59 ++++++++
src/include/catalog/pg_publication.h | 5 +
src/test/regress/expected/publication.out | 89 ++++++++++++
src/test/regress/sql/publication.sql | 81 +++++++++++
9 files changed, 494 insertions(+), 18 deletions(-)
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index f288c049a5c..ce60a1b391c 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -2154,7 +2154,15 @@ CONTEXT: processing remote data for replication origin "pg_16395" during "INSER
<listitem>
<para>
- Replication is only supported by tables, including partitioned tables.
+ Replication is supported only for tables, including partitioned tables,
+ except when they contain foreign partitions. If
+ <literal>publish_via_partition_root</literal> is set to
+ <literal>true</literal> for a publication table with foreign partitions, or
+ if an attempt is made to replicate such a table, an error is thrown.
+ Additionally, when replicating a partitioned table where
+ <literal>publish_via_partition_root</literal> is set to
+ <literal>false</literal> and foreign partitions are present, all partitions
+ are replicated except the foreign partitions.
Attempts to replicate other types of relations, such as views, materialized
views, or foreign tables, will result in an error.
</para>
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index 73f0c8d89fb..47216fc4789 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -257,6 +257,11 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
If this is enabled, <literal>TRUNCATE</literal> operations performed
directly on partitions are not replicated.
</para>
+
+ <para>
+ If this is enabled, a foreign table or a partitioned table with a
+ foreign partition is not allowed in the publication.
+ </para>
</listitem>
</varlistentry>
</variablelist></para>
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index d6f94db5d99..77299b75cde 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -26,12 +26,14 @@
#include "catalog/partition.h"
#include "catalog/pg_inherits.h"
#include "catalog/pg_namespace.h"
+#include "catalog/pg_partitioned_table.h"
#include "catalog/pg_publication.h"
#include "catalog/pg_publication_namespace.h"
#include "catalog/pg_publication_rel.h"
#include "catalog/pg_type.h"
#include "commands/publicationcmds.h"
#include "funcapi.h"
+#include "partitioning/partdesc.h"
#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/catcache.h"
@@ -53,7 +55,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 +66,18 @@ 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. See check_foreign_tables for details.
+ */
+ if (pub->pubviaroot && check_partrel_has_foreign_table(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 +318,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 +327,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 +473,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 +713,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 +1349,118 @@ 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(Relation rel)
+{
+ bool has_foreign_tbl = false;
+
+ if (RelationGetForm(rel)->relkind == RELKIND_PARTITIONED_TABLE)
+ {
+ PartitionDesc pd = RelationGetPartitionDesc(rel, true);
+
+ for (int i = 0; i < pd->nparts; i++)
+ {
+ Relation childrel = table_open(pd->oids[i], AccessShareLock);
+
+ if (RelationGetForm(childrel)->relkind == RELKIND_FOREIGN_TABLE)
+ has_foreign_tbl = true;
+ else
+ has_foreign_tbl = check_partrel_has_foreign_table(childrel);
+
+ table_close(childrel, NoLock);
+
+ if (has_foreign_tbl)
+ break;
+ }
+ }
+
+ return has_foreign_tbl;
+}
+
+/*
+ * Protect against including foreign tables that are partitions of partitioned
+ * tables published by the given publication when publish_via_root_partition is
+ * true. This will not work correctly as the initial data from the foreign
+ * table can be replicated by the tablesync worker even though replication of
+ * foreign table is not supported because when publish_via_root_partition is
+ * true, the root table is included in pg_subscription_rel catalog table and
+ * tablesync worker cannot distinguish data from foreign partition. So we
+ * disallow the case here and in all DDL commands that would end up creating
+ * such a case indirectly.
+ *
+ * When publish_via_root_partition is set to false, leaf partitions are included
+ * in pg_subscription_rel catalog table. So, when we include a partition table
+ * with foreign partition in a publication, we skip including foreign partitions
+ * to pg_subscription_rel catalog table. So, the foreign partitions are not
+ * replicated.
+ *
+ *
+ * check_foreign_tables
+ *
+ * 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_partitioned_table 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;
+ Relation partRel;
+ ScanKeyData key[3];
+ int keycount = 0;
+ TableScanDesc scan;
+ HeapTuple tuple;
+
+ classRel = table_open(RelationRelationId, AccessShareLock);
+ partRel = table_open(PartitionedRelationId, 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);
+ Relation pubrel = table_open(relForm->oid, AccessShareLock);
+
+ if (check_partrel_has_foreign_table(pubrel))
+ 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_close(pubrel, NoLock);
+ }
+
+ table_endscan(scan);
+ table_close(classRel, AccessShareLock);
+ table_close(partRel, NoLock);
+}
diff --git a/src/backend/commands/foreigncmds.c b/src/backend/commands/foreigncmds.c
index c14e038d54f..98c1c82db61 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,55 @@ 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.
+ * See check_foreign_tables for details.
+ */
+ if (stmt->base.partbound != NULL)
+ {
+ RangeVar *root = linitial_node(RangeVar, 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))));
+ }
+
+ list_free(puboids);
+ }
+
+ 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 0b23d94c38e..b06960e8e22 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -915,6 +915,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();
}
@@ -1080,6 +1084,49 @@ AlterPublicationOptions(ParseState *pstate, AlterPublicationStmt *stmt,
}
}
+ /*
+ * If publish_via_partition_root is set to true, check if the publication
+ * already have any foreign partition. See check_foreign_tables for details.
+ */
+ 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);
+
+ list_free(schemaoids);
+
+ relids = GetPublicationRelations(pubform->oid, PUBLICATION_PART_ROOT);
+
+ foreach_oid(relid, relids)
+ {
+ Relation pubrel = table_open(relid, AccessShareLock);
+
+ if (check_partrel_has_foreign_table(pubrel))
+ {
+ 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))));
+ }
+
+ table_close(pubrel, NoLock);
+ }
+
+ list_free(relids);
+ }
+
/* 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 11fcb51a165..5c4007dbeee 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -19923,6 +19923,10 @@ QueuePartitionConstraintValidation(List **wqueue, Relation scanrel,
* ALTER TABLE <name> ATTACH PARTITION <partition-name> FOR VALUES
*
* Return the address of the newly attached partition.
+ *
+ * If the would-be partition is a foreign table or a partitioned table with
+ * foreign partition, verify that the partitioned table is not in a publication
+ * with publish_via_partition_root=true. See check_foreign_tables for details.
*/
static ObjectAddress
ATExecAttachPartition(List **wqueue, Relation rel, PartitionCmd *cmd,
@@ -20067,6 +20071,61 @@ 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(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)));
+ }
+ }
+
+ list_free(puboids);
+ }
+
/*
* 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..e2919da2541 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(Relation rel);
+
+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 (17+ 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: <CANhcyEXSOuv7=ueqU3ZqZCsFGb1+EEpZVQAUkz1uvUpJTUOjqg@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