1: 761b91b3f1 = 1: 7e68f96af6 pgoutput: refactor publication cache construction 2: 0397a52583 ! 2: a48563919e WIP: introduce pg_set_logical_root for use with pubviaroot @@ Metadata Author: Jacob Champion ## Commit message ## - WIP: introduce pg_set_logical_root for use with pubviaroot + WIP: introduce publish_via_parent for logical replication - Allows regular inherited tables to be published via their root table, - just like partitions. This works by hijacking pg_inherit's inhseqno - column, and replacing a (single) existing entry for the child with the - value zero, indicating that it should be treated as a logical partition - by the publication machinery. + This new relation option allows regular inherited tables to be published + via their root table, just like partitions. This works by hijacking + pg_inherit's inhseqno column, and replacing a (single) existing entry + for the child with the value zero, indicating that it should be treated + as a logical partition by the publication machinery. Initial sync works by asking the publisher for a list of logical descendants of the published table, then COPYing them one-by-one into @@ Commit message roots. Known bugs/TODOs: - - The pg_inherits machinery doesn't prohibit changes to inheritance - after an entry has been marked as a logical root. + - If two publications publish the same leaves of a table hierarchy, but + have different roots, the shared leaf tables will be incorrectly + duplicated when subscribing to both publications simultaneously. - I haven't given any thought to interactions with row filters, or to column lists. - I'm not sure that I'm taking all the necessary locks yet, and those I do take may be taken in the wrong order. - - Dump and upgrade aren't supported yet. + + ## src/backend/access/common/reloptions.c ## +@@ src/backend/access/common/reloptions.c: static relopt_bool boolRelOpts[] = + }, + false + }, ++ { ++ { ++ "publish_via_parent", ++ "Replicate this table's contents via its parent table when publishing with publish_via_partition_root", ++ RELOPT_KIND_HEAP, ++ AccessExclusiveLock ++ }, ++ false ++ }, + { + { + "fastupdate", +@@ src/backend/access/common/reloptions.c: default_reloptions(Datum reloptions, bool validate, relopt_kind kind) + offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, analyze_scale_factor)}, + {"user_catalog_table", RELOPT_TYPE_BOOL, + offsetof(StdRdOptions, user_catalog_table)}, ++ {"publish_via_parent", RELOPT_TYPE_BOOL, ++ offsetof(StdRdOptions, publish_via_parent)}, + {"parallel_workers", RELOPT_TYPE_INT, + offsetof(StdRdOptions, parallel_workers)}, + {"vacuum_index_cleanup", RELOPT_TYPE_ENUM, ## src/backend/catalog/pg_inherits.c ## @@ @@ src/backend/catalog/pg_inherits.c: find_inheritance_children(Oid parentrelId, LO * committed to the active snapshot. In addition, *detached_xmin (if not null) * is set to the xmin of the row of the detached partition. + * -+ * If logical_only is true, only tables marked explicitly via -+ * pg_set_logical_root() are included in the output list. ++ * If logical_only is true, only tables marked explicitly via publish_via_parent ++ * are included in the output list. */ List * find_inheritance_children_extended(Oid parentrelId, bool omit_detached, @@ src/backend/catalog/pg_inherits.c: PartitionHasPendingDetach(Oid partoid) +has_logical_parent(Relation inhRel, Oid relid) +{ + return (get_logical_parent_worker(inhRel, relid) != InvalidOid); -+} -+ -+Datum -+pg_set_logical_root(PG_FUNCTION_ARGS) -+{ -+ Oid tableoid = PG_GETARG_OID(0); -+ Oid rootoid = PG_GETARG_OID(1); -+ char *tablename; -+ char *rootname; -+ Relation inhRel; -+ ScanKeyData key; -+ SysScanDesc scan; -+ Oid parent = InvalidOid; -+ HeapTuple tuple, copyTuple; -+ Form_pg_inherits form; -+ -+ /* -+ * Check that the tables exist. -+ * TODO: check identical schemas too? or does replication handle that? -+ */ -+ tablename = get_rel_name(tableoid); -+ if (tablename == NULL) -+ ereport(ERROR, -+ (errcode(ERRCODE_UNDEFINED_TABLE), -+ errmsg("OID %u does not refer to a table", tableoid))); -+ rootname = get_rel_name(rootoid); -+ if (rootname == NULL) -+ ereport(ERROR, -+ (errcode(ERRCODE_UNDEFINED_TABLE), -+ errmsg("OID %u does not refer to a table", rootoid))); -+ -+ /* Check ownership. */ -+ if (!object_ownercheck(RelationRelationId, tableoid, GetUserId())) -+ aclcheck_error(ACLCHECK_NOT_OWNER, -+ get_relkind_objtype(get_rel_relkind(tableoid)), -+ tablename); -+ if (!object_ownercheck(RelationRelationId, rootoid, GetUserId())) -+ aclcheck_error(ACLCHECK_NOT_OWNER, -+ get_relkind_objtype(get_rel_relkind(rootoid)), -+ rootname); -+ -+ /* Open pg_inherits with RowExclusiveLock so that we can update it. */ -+ inhRel = table_open(InheritsRelationId, RowExclusiveLock); -+ -+ /* -+ * We have to make sure that the inheritance relationship already exists, -+ * and that there is only one existing parent for this table. -+ * -+ * TODO: do we have to lock the tables themselves to avoid races? -+ */ -+ ScanKeyInit(&key, -+ Anum_pg_inherits_inhrelid, -+ BTEqualStrategyNumber, F_OIDEQ, -+ ObjectIdGetDatum(tableoid)); -+ -+ scan = systable_beginscan(inhRel, InheritsRelidSeqnoIndexId, true, -+ NULL, 1, &key); -+ tuple = systable_getnext(scan); -+ if (HeapTupleIsValid(tuple)) -+ { -+ form = (Form_pg_inherits) GETSTRUCT(tuple); -+ parent = form->inhparent; -+ copyTuple = heap_copytuple(tuple); -+ -+ if (HeapTupleIsValid(systable_getnext(scan))) -+ ereport(ERROR, -+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), -+ errmsg("table \"%s\" inherits from multiple tables", -+ tablename))); -+ } -+ -+ if (parent != rootoid) -+ ereport(ERROR, -+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), -+ errmsg("table \"%s\" does not inherit from intended root table \"%s\"", -+ tablename, rootname))); -+ -+ systable_endscan(scan); -+ -+ /* Mark the inheritance as a logical root by setting it to zero. */ -+ form = (Form_pg_inherits) GETSTRUCT(copyTuple); -+ form->inhseqno = 0; -+ -+ CatalogTupleUpdate(inhRel, ©Tuple->t_self, copyTuple); -+ -+ heap_freetuple(copyTuple); -+ table_close(inhRel, RowExclusiveLock); -+ -+ PG_RETURN_VOID(); +} ## src/backend/catalog/pg_publication.c ## @@ src/backend/catalog/pg_publication.c: pg_get_publication_tables(PG_FUNCTION_ARGS + * The only time this list consists of anything more than the table itself is + * when a publication's publish_via_partition_root is set to true and the table + * has inherited child tables in the publications that have been marked with -+ * pg_set_logical_root(). ++ * publish_via_parent. + */ +Datum +pg_get_publication_rels_to_sync(PG_FUNCTION_ARGS) @@ src/backend/catalog/pg_publication.c: process_relation_publications(Oid relid, c - * For a partition, check if any of the ancestors are - * published. If so, note down the topmost ancestor that is + * Check if any of the logical ancestors (that is, partition -+ * parents or tables marked with pg_set_logical_root()) are ++ * parents or tables marked with publish_via_parent) are + * published. If so, note down the topmost ancestor that is * published via this publication, which will be used as the - * relation via which to publish the partition's changes. @@ src/backend/catalog/pg_publication.c: process_relation_publications(Oid relid, c return rel_publications; } +@@ src/backend/catalog/pg_publication.c: pg_get_relation_publishing_info(PG_FUNCTION_ARGS) + + { + List *rel_publications; +- PublicationActions pubactions; ++ PublicationActions pubactions = {0}; + Oid publish_as_relid; + ListCell *lc; + Datum *puboids; ## src/backend/commands/publicationcmds.c ## @@ src/backend/commands/publicationcmds.c: contain_invalid_rfcolumn_walker(Node *node, rf_context *context) @@ src/backend/commands/publicationcmds.c: contain_invalid_rfcolumn_walker(Node *no * information of the child table. So, get the column number of the * child table as parent and child column order could be different. + * -+ * TODO: is this applicable to pg_set_logical_root()? ++ * TODO: is this applicable to publish_via_parent? */ if (context->pubviaroot) { @@ src/backend/commands/publicationcmds.c: pub_rf_contains_invalid_column(Oid pubid * Note that even though the row filter used is for an ancestor, the * REPLICA IDENTITY used will be for the actual child table. + * -+ * TODO: is this applicable to pg_set_logical_root()? ++ * TODO: is this applicable to publish_via_parent? */ if (pubviaroot && relation->rd_rel->relispartition) { @@ src/backend/commands/publicationcmds.c: pub_rf_contains_invalid_column(Oid pubid * * Returns true if any replica identity column is not covered by column list. + * -+ * TODO: pg_set_logical_root()? ++ * TODO: publish_via_parent? */ bool pub_collist_contains_invalid_column(Oid pubid, Relation relation, List *ancestors, @@ src/backend/commands/publicationcmds.c: TransformPubWhereClauses(List *tables, c * table, the partition's row filter will be used. So disallow using * WHERE clause on partitioned table in this case. + * -+ * TODO: decide how this interacts with pg_set_logical_root ++ * TODO: decide how this interacts with publish_via_parent */ if (!pubviaroot && pri->relation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) @@ src/backend/commands/publicationcmds.c: CheckPubRelationColumnList(char *pubname * table, the partition's column list will be used. So disallow using * a column list on the partitioned table in this case. + * -+ * TODO: decide if this interacts with pg_set_logical_root() ++ * TODO: decide if this interacts with publish_via_parent */ if (!pubviaroot && pri->relation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) + ## src/backend/commands/tablecmds.c ## +@@ src/backend/commands/tablecmds.c: static bool MergeCheckConstraint(List *constraints, char *name, Node *expr); + static void MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel); + static void MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel); + static void StoreCatalogInheritance(Oid relationId, List *supers, +- bool child_is_partition); ++ bool child_is_partition, ++ bool publish_via_parent); + static void StoreCatalogInheritance1(Oid relationId, Oid parentOid, + int32 seqNumber, Relation inhRelation, + bool child_is_partition); +@@ src/backend/commands/tablecmds.c: static void ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel, + const char *tablespacename, LOCKMODE lockmode); + static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode); + static void ATExecSetTableSpaceNoStorage(Relation rel, Oid newTableSpace); ++static void change_publish_via_parent(Relation rel, bool set); + static void ATExecSetRelOptions(Relation rel, List *defList, + AlterTableType operation, + LOCKMODE lockmode); +@@ src/backend/commands/tablecmds.c: DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId, + } + + /* Store inheritance information for new rel. */ +- StoreCatalogInheritance(relationId, inheritOids, stmt->partbound != NULL); ++ StoreCatalogInheritance(relationId, inheritOids, stmt->partbound != NULL, ++ RelationIsPublishedViaParent(rel)); + + /* + * Process the partitioning specification (if any) and store the partition +@@ src/backend/commands/tablecmds.c: MergeCheckConstraint(List *constraints, char *name, Node *expr) + */ + static void + StoreCatalogInheritance(Oid relationId, List *supers, +- bool child_is_partition) ++ bool child_is_partition, bool publish_via_parent) + { + Relation relation; + int32 seqNumber; +@@ src/backend/commands/tablecmds.c: StoreCatalogInheritance(Oid relationId, List *supers, + */ + Assert(OidIsValid(relationId)); + ++ /* ++ * publish_via_parent requires exactly one parent. Try to keep this ++ * messaging consistent with ALTER TABLE/change_publish_via_parent(). ++ * ++ * Earlier, in MergeAttributes, we already checked that we had ownership of ++ * the parent table, so we don't need to check that again the way that ++ * change_publish_via_parent() does. ++ */ ++ if (publish_via_parent && list_length(supers) != 1) ++ ereport(ERROR, ++ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), ++ errmsg((list_length(supers) == 0) ++ ? "table \"%s\" does not inherit from any tables" ++ : "table \"%s\" inherits from multiple tables", ++ get_rel_name(relationId)))); ++ + if (supers == NIL) + return; + +@@ src/backend/commands/tablecmds.c: StoreCatalogInheritance(Oid relationId, List *supers, + */ + relation = table_open(InheritsRelationId, RowExclusiveLock); + +- seqNumber = 1; ++ /* ++ * Normally inhseqno starts at one, but for publish_via_parent, it's given a ++ * sentinel value of zero. (In that case, we'll only go through this loop ++ * once, as the list_length() check above enforces.) ++ */ ++ seqNumber = publish_via_parent ? 0 : 1; + foreach(entry, supers) + { + Oid parentOid = lfirst_oid(entry); +@@ src/backend/commands/tablecmds.c: ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel, const char *tablespacen + tab->newTableSpace = tablespaceId; + } + ++static void ++change_publish_via_parent(Relation rel, bool set) ++{ ++ Relation inhRel; ++ ScanKeyData key; ++ SysScanDesc scan; ++ Oid parentOid; ++ Relation parentRel; ++ HeapTuple tuple, copyTuple; ++ Form_pg_inherits form; ++ ++ /* Open pg_inherits with RowExclusiveLock so that we can update it. */ ++ inhRel = table_open(InheritsRelationId, RowExclusiveLock); ++ ++ /* ++ * We have to make sure that an inheritance relationship already exists, and ++ * that there is only one candidate parent for this table. ++ * ++ * TODO: do we have to lock the tables themselves to avoid races? ++ */ ++ ScanKeyInit(&key, ++ Anum_pg_inherits_inhrelid, ++ BTEqualStrategyNumber, F_OIDEQ, ++ ObjectIdGetDatum(RelationGetRelid(rel))); ++ ++ scan = systable_beginscan(inhRel, InheritsRelidSeqnoIndexId, true, ++ NULL, 1, &key); ++ tuple = systable_getnext(scan); ++ ++ if (!HeapTupleIsValid(tuple)) ++ ereport(ERROR, ++ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), ++ errmsg("table \"%s\" does not inherit from any tables", ++ RelationGetRelationName(rel)))); ++ ++ form = (Form_pg_inherits) GETSTRUCT(tuple); ++ parentOid = form->inhparent; ++ copyTuple = heap_copytuple(tuple); ++ ++ if (HeapTupleIsValid(systable_getnext(scan))) ++ ereport(ERROR, ++ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), ++ errmsg("table \"%s\" inherits from multiple tables", ++ RelationGetRelationName(rel)))); ++ ++ systable_endscan(scan); ++ ++ if (set) ++ { ++ /* ++ * Open our parent table and check permissions. Like ATTACH PARTITION, ++ * we require ownership of the parent table in addition to the child ++ * (which has already been checked in ATPrepCmd). ++ * ++ * TODO: what lock strength is needed here? Is nesting it inside the ++ * inhRel lock a risk? ++ */ ++ parentRel = table_open(parentOid, AccessExclusiveLock); ++ ATSimplePermissions(AT_SetRelOptions, parentRel, ATT_TABLE); ++ table_close(parentRel, NoLock); /* TODO: do we need to hang onto the lock? */ ++ } ++ else ++ { ++ /* ++ * Ownership of the parent isn't needed for unsetting the flag, just ++ * like we wouldn't check ownership during NO INHERIT. ++ */ ++ } ++ ++ /* ++ * Mark the inheritance as a logical root by setting inhseqno to zero. ++ * Unmark it by setting inhseqno to one (since there's only one parent). ++ */ ++ form = (Form_pg_inherits) GETSTRUCT(copyTuple); ++ form->inhseqno = set ? 0 : 1; ++ ++ CatalogTupleUpdate(inhRel, ©Tuple->t_self, copyTuple); ++ ++ heap_freetuple(copyTuple); ++ table_close(inhRel, RowExclusiveLock); ++} ++ + /* + * Set, reset, or replace reloptions. + */ +@@ src/backend/commands/tablecmds.c: ATExecSetRelOptions(Relation rel, List *defList, AlterTableType operation, + } + } + ++ /* Extra work for publish_via_parent */ ++ if (rel->rd_rel->relkind == RELKIND_RELATION) ++ { ++ List *heap_options = untransformRelOptions(newOptions); ++ ListCell *cell; ++ bool curSetting = RelationIsPublishedViaParent(rel); ++ DefElem *pubElem = NULL; ++ ++ foreach (cell, heap_options) ++ { ++ DefElem *defel = lfirst_node(DefElem, cell); ++ ++ if (strcmp(defel->defname, "publish_via_parent") == 0) ++ { ++ pubElem = defel; ++ break; ++ } ++ } ++ ++ if (pubElem) ++ { ++ bool newSetting = defGetBoolean(pubElem); ++ ++ if (newSetting != curSetting) ++ change_publish_via_parent(rel, newSetting); ++ } ++ else if (curSetting) ++ { ++ /* publish_via_parent was RESET */ ++ change_publish_via_parent(rel, false); ++ } ++ } ++ + /* + * All we need do here is update the pg_class row; the new options will be + * propagated into relcaches during post-commit cache inval. +@@ src/backend/commands/tablecmds.c: ATExecAddInherit(Relation child_rel, RangeVar *parent, LOCKMODE lockmode) + trigger_name, RelationGetRelationName(child_rel)), + errdetail("ROW triggers with transition tables are not supported in inheritance hierarchies."))); + ++ /* publish_via_parent tables are not allowed to add additional parents. */ ++ if (RelationIsPublishedViaParent(child_rel)) ++ ereport(ERROR, ++ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ++ errmsg("relation option \"%s\" prevents table \"%s\" from changing inheritance", ++ "publish_via_parent", RelationGetRelationName(child_rel)))); ++ + /* OK to create inheritance */ + CreateInheritance(child_rel, parent_rel); + +@@ src/backend/commands/tablecmds.c: ATExecDropInherit(Relation rel, RangeVar *parent, LOCKMODE lockmode) + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("cannot change inheritance of a partition"))); + ++ /* publish_via_parent tables are not allowed to remove their parent. */ ++ if (RelationIsPublishedViaParent(rel)) ++ ereport(ERROR, ++ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ++ errmsg("relation option \"%s\" prevents table \"%s\" from changing inheritance", ++ "publish_via_parent", RelationGetRelationName(rel)))); ++ + /* + * AccessShareLock on the parent is probably enough, seeing that DROP + * TABLE doesn't lock parent tables at all. We need some lock since we'll + ## src/backend/partitioning/partdesc.c ## @@ src/backend/partitioning/partdesc.c: RelationBuildPartitionDesc(Relation rel, bool omit_detached) inhoids = find_inheritance_children_extended(RelationGetRelid(rel), @@ src/backend/replication/logical/tablesync.c: fetch_remote_table_info(char *nspna + /* + * See if there are any other tables to be copied besides the original. This + * happens when a descendant in the inheritance relationship is marked with -+ * pg_set_logical_root() and is part of a publication with ++ * publish_via_parent and is part of a publication with + * publish_via_partition_root = true. + * + * FIXME: if two publications have conflicting settings for @@ src/backend/replication/pgoutput/pgoutput.c: pgoutput_change(LogicalDecodingCont targetrel = ancestor; } + ## src/bin/pg_dump/t/002_pg_dump.pl ## +@@ src/bin/pg_dump/t/002_pg_dump.pl: my %tests = ( + no_table_access_method => 1, + only_dump_measurement => 1, + }, ++ }, ++ ++ 'CREATE TABLE regress_pg_dump_publish_via_parent' => { ++ create_order => 3, ++ create_sql => ' ++ CREATE TABLE dump_test.regress_pg_dump_publish_via_parent (col1 int); ++ CREATE TABLE dump_test.regress_pg_dump_publish_via_parent_1() INHERITS (dump_test.regress_pg_dump_publish_via_parent) WITH (publish_via_parent);', ++ regexp => qr/^ ++ \QCREATE TABLE dump_test.regress_pg_dump_publish_via_parent (\E ++ \n\s+\Qcol1 integer\E ++ \n\); ++ (\n.*)+? ++ \n\QCREATE TABLE dump_test.regress_pg_dump_publish_via_parent_1 (\E ++ \n\) ++ \n\QINHERITS (dump_test.regress_pg_dump_publish_via_parent)\E ++ \n\QWITH (publish_via_parent='true')\E;/xm, ++ like => { ++ %full_runs, %dump_test_schema_runs, section_pre_data => 1, ++ }, ++ unlike => { ++ binary_upgrade => 1, ++ exclude_dump_test_schema => 1, ++ only_dump_measurement => 1, ++ }, + }); + + ######################################### + + ## src/bin/psql/tab-complete.c ## +@@ src/bin/psql/tab-complete.c: static const char *const table_storage_parameters[] = { + "fillfactor", + "log_autovacuum_min_duration", + "parallel_workers", ++ "publish_via_parent", + "toast.autovacuum_enabled", + "toast.autovacuum_freeze_max_age", + "toast.autovacuum_freeze_min_age", + ## src/include/catalog/pg_inherits.h ## +@@ + + #include "nodes/pg_list.h" + #include "storage/lock.h" ++#include "utils/relcache.h" + + /* ---------------- + * pg_inherits definition. cpp turns this into @@ src/include/catalog/pg_inherits.h: typedef FormData_pg_inherits *Form_pg_inherits; DECLARE_UNIQUE_INDEX_PKEY(pg_inherits_relid_seqno_index, 2680, InheritsRelidSeqnoIndexId, on pg_inherits using btree(inhrelid oid_ops, inhseqno int4_ops)); @@ src/include/catalog/pg_proc.dat { oid => '8139', descr => 'get information on how a relation will be published via a list of publications', proname => 'pg_get_relation_publishing_info', provariadic => 'text', -@@ - proname => 'pg_partition_root', prorettype => 'regclass', - proargtypes => 'regclass', prosrc => 'pg_partition_root' }, + + ## src/include/utils/rel.h ## +@@ src/include/utils/rel.h: typedef struct StdRdOptions + int toast_tuple_target; /* target for tuple toasting */ + AutoVacOpts autovacuum; /* autovacuum-related options */ + bool user_catalog_table; /* use as an additional catalog relation */ ++ bool publish_via_parent; /* publish via parent's relid for logrep */ + int parallel_workers; /* max number of parallel workers */ + StdRdOptIndexCleanup vacuum_index_cleanup; /* controls index vacuuming */ + bool vacuum_truncate; /* enables vacuum to truncate a relation */ +@@ src/include/utils/rel.h: typedef struct StdRdOptions + (relation)->rd_rel->relkind == RELKIND_MATVIEW) ? \ + ((StdRdOptions *) (relation)->rd_options)->user_catalog_table : false) -+{ oid => '8136', descr => 'mark a table root for logical replication', -+ proname => 'pg_set_logical_root', provolatile => 'v', proparallel => 'u', -+ prorettype => 'void', proargtypes => 'regclass regclass', -+ prosrc => 'pg_set_logical_root' }, ++/* ++ * RelationIsPublishedViaParent ++ * Returns whether the relation's contents should be logically replicated ++ * via its parent table's relid, when published under the effects of ++ * publish_via_partition_root. Note multiple eval of argument! ++ */ ++#define RelationIsPublishedViaParent(relation) \ ++ ((relation)->rd_options && \ ++ ((relation)->rd_rel->relkind == RELKIND_RELATION) && \ ++ ((StdRdOptions *) (relation)->rd_options)->publish_via_parent) + - { oid => '4350', descr => 'Unicode normalization', - proname => 'normalize', prorettype => 'text', proargtypes => 'text text', - prosrc => 'unicode_normalize_func' }, + /* + * RelationGetParallelWorkers + * Returns the relation's parallel_workers reloption setting. ## src/test/regress/expected/publication.out ## @@ src/test/regress/expected/publication.out: CREATE ROLE regress_publication_user LOGIN SUPERUSER; @@ src/test/regress/expected/publication.out: SELECT * FROM pg_publication_tables; pub | sch1 | tbl1 | {a} | (1 row) -+-- Sanity check cases for pg_set_logical_root(). ++-- Sanity check cases for publish_via_parent. +CREATE TABLE sch1.iroot (a int); +CREATE TABLE sch1.ipart1 (a int); +CREATE TABLE sch1.ipart2 () INHERITS (sch1.iroot); ++-- should do nothing at all ++ALTER TABLE sch1.iroot SET (publish_via_parent = false); ++ALTER TABLE sch1.iroot RESET (publish_via_parent); +-- marking roots between unrelated tables is not allowed -+SELECT pg_set_logical_root('sch1.ipart1', 'sch1.iroot'); -+ERROR: table "ipart1" does not inherit from intended root table "iroot" -+SELECT pg_set_logical_root('sch1.ipart2', 'sch1.tbl1'); -+ERROR: table "ipart2" does not inherit from intended root table "tbl1" ++ALTER TABLE sch1.ipart1 SET (publish_via_parent); ++ERROR: table "ipart1" does not inherit from any tables ++CREATE TABLE fail (a int) WITH (publish_via_parent); ++ERROR: table "fail" does not inherit from any tables +-- establishing an inheritance relationship fixes the problem -+ALTER TABLE sch1.ipart1 INHERIT sch1.iroot; -+SELECT pg_set_logical_root('sch1.ipart1', 'sch1.iroot'); -+ pg_set_logical_root -+--------------------- -+ -+(1 row) -+ ++ALTER TABLE sch1.ipart1 INHERIT sch1.iroot, ++ SET (publish_via_parent); +-- but multiple inheritance is not allowed -+ALTER TABLE sch1.ipart2 INHERIT sch1.ipart1; -+SELECT pg_set_logical_root('sch1.ipart2', 'sch1.iroot'); ++ALTER TABLE sch1.ipart2 INHERIT sch1.ipart1, ++ SET (publish_via_parent); +ERROR: table "ipart2" inherits from multiple tables -+ALTER TABLE sch1.ipart2 NO INHERIT sch1.ipart1; -+SELECT pg_set_logical_root('sch1.ipart2', 'sch1.iroot'); -+ pg_set_logical_root -+--------------------- -+ -+(1 row) -+ ++-- once publish_via_parent is set, inheritance cannot be changed ++ALTER TABLE sch1.ipart2 SET (publish_via_parent); ++ALTER TABLE sch1.ipart2 NO INHERIT sch1.iroot; ++ERROR: relation option "publish_via_parent" prevents table "ipart2" from changing inheritance ++ALTER TABLE sch1.ipart2 INHERIT sch1.ipart1; ++ERROR: relation option "publish_via_parent" prevents table "ipart2" from changing inheritance +-- table ownership must match, like ATTACH PARTITION +CREATE ROLE regress_test_me; +CREATE ROLE regress_test_not_me; @@ src/test/regress/expected/publication.out: SELECT * FROM pg_publication_tables; +ALTER TABLE root OWNER TO regress_test_me; +ALTER TABLE part OWNER TO regress_test_not_me; +SET SESSION AUTHORIZATION regress_test_me; -+SELECT pg_set_logical_root('part', 'root'); -- should fail ++ALTER TABLE part SET (publish_via_parent); -- should fail +ERROR: must be owner of table part +RESET SESSION AUTHORIZATION; +ALTER TABLE root OWNER TO regress_test_not_me; +ALTER TABLE part OWNER TO regress_test_me; +SET SESSION AUTHORIZATION regress_test_me; -+SELECT pg_set_logical_root('part', 'root'); -- should also fail ++ALTER TABLE part SET (publish_via_parent); -- should also fail ++ERROR: must be owner of table root ++CREATE TABLE fail () INHERITS (root) WITH (publish_via_parent); +ERROR: must be owner of table root +RESET SESSION AUTHORIZATION; +DROP TABLE root, part; +DROP ROLE regress_test_not_me; +DROP ROLE regress_test_me; -+-- TODO: make sure existing logical descendant can't be ALTERed [NO] INHERIT +-- Mixed publication settings for publish_via_partition_root, at different +-- levels of the inheritance tree, to pin correct behavior in the worst cases. -+CREATE TABLE sch1.ipart1_a () INHERITS (sch1.ipart1); ++CREATE TABLE sch1.ipart1_a () INHERITS (sch1.ipart1) WITH (publish_via_parent); +CREATE TABLE sch1.ipart1_a1 () INHERITS (sch1.ipart1_a); -+SELECT pg_set_logical_root('sch1.ipart1_a', 'sch1.ipart1'); -+ pg_set_logical_root -+--------------------- -+ -+(1 row) -+ -+SELECT pg_set_logical_root('sch1.ipart1_a1', 'sch1.ipart1_a'); -+ pg_set_logical_root -+--------------------- -+ -+(1 row) -+ ++ALTER TABLE sch1.ipart1_a1 SET (publish_via_parent); +CREATE PUBLICATION ipub_root FOR TABLE sch1.iroot; +CREATE PUBLICATION ipub_part1 FOR TABLE ONLY sch1.ipart1, ONLY sch1.ipart1_a1 + WITH (publish_via_partition_root); @@ src/test/regress/expected/publication.out: SELECT * FROM pg_publication_tables; + ipub_other | sch1.iroot | sch1.ipart1_a +(10 rows) + ++-- "Detaching" partitions should change the subscriptions ++ALTER TABLE sch1.ipart2 SET (publish_via_parent = false); ++SELECT pubname, relid::regclass ++ FROM pg_get_publication_tables('ipub_root', 'ipub_part1', 'ipub_other') t ++ JOIN pg_publication p ON (p.oid = t.pubid); ++ pubname | relid ++------------+------------- ++ ipub_root | sch1.iroot ++ ipub_root | sch1.ipart1 ++ ipub_root | sch1.ipart2 ++ ipub_part1 | sch1.ipart1 ++ ipub_other | sch1.iroot ++ ipub_other | sch1.ipart2 ++(6 rows) ++ ++SELECT * FROM published_sync('ipub_root', 'ipub_part1', 'ipub_other'); ++ published | synced ++-------------+---------------- ++ sch1.iroot | sch1.iroot ++ sch1.iroot | sch1.ipart1_a ++ sch1.ipart1 | sch1.ipart1 ++ sch1.ipart1 | sch1.ipart1_a1 ++ sch1.ipart2 | sch1.ipart2 ++(5 rows) ++ ++SELECT * FROM published_stream('ipub_root', 'ipub_part1', 'ipub_other'); ++ pubname | published | synced ++------------+-------------+---------------- ++ ipub_root | sch1.iroot | sch1.iroot ++ ipub_root | sch1.iroot | sch1.ipart1_a ++ ipub_root | sch1.ipart1 | sch1.ipart1 ++ ipub_root | sch1.ipart1 | sch1.ipart1_a1 ++ ipub_root | sch1.ipart2 | sch1.ipart2 ++ ipub_part1 | sch1.ipart1 | sch1.ipart1 ++ ipub_part1 | sch1.ipart1 | sch1.ipart1_a1 ++ ipub_other | sch1.iroot | sch1.iroot ++ ipub_other | sch1.iroot | sch1.ipart1_a ++ ipub_other | sch1.ipart2 | sch1.ipart2 ++(10 rows) ++ ++ALTER TABLE sch1.ipart1_a1 RESET (publish_via_parent); ++SELECT relid::regclass FROM pg_get_publication_tables('ipub_part1'); ++ relid ++---------------- ++ sch1.ipart1 ++ sch1.ipart1_a1 ++(2 rows) ++ ++SELECT * FROM published_sync('ipub_part1'); ++ published | synced ++----------------+---------------- ++ sch1.ipart1 | sch1.ipart1 ++ sch1.ipart1_a1 | sch1.ipart1_a1 ++(2 rows) ++ ++SELECT * FROM published_stream('ipub_part1'); ++ pubname | published | synced ++------------+----------------+---------------- ++ ipub_part1 | sch1.ipart1 | sch1.ipart1 ++ ipub_part1 | sch1.ipart1_a1 | sch1.ipart1_a1 ++(2 rows) ++ +DROP PUBLICATION ipub_other; +DROP PUBLICATION ipub_part1; +DROP PUBLICATION ipub_root; @@ src/test/regress/sql/publication.sql: ALTER TABLE sch1.tbl1 ATTACH PARTITION sch CREATE PUBLICATION pub FOR TABLES IN SCHEMA sch1 WITH (PUBLISH_VIA_PARTITION_ROOT=1); SELECT * FROM pg_publication_tables; -+-- Sanity check cases for pg_set_logical_root(). ++-- Sanity check cases for publish_via_parent. +CREATE TABLE sch1.iroot (a int); +CREATE TABLE sch1.ipart1 (a int); +CREATE TABLE sch1.ipart2 () INHERITS (sch1.iroot); + ++-- should do nothing at all ++ALTER TABLE sch1.iroot SET (publish_via_parent = false); ++ALTER TABLE sch1.iroot RESET (publish_via_parent); ++ +-- marking roots between unrelated tables is not allowed -+SELECT pg_set_logical_root('sch1.ipart1', 'sch1.iroot'); -+SELECT pg_set_logical_root('sch1.ipart2', 'sch1.tbl1'); ++ALTER TABLE sch1.ipart1 SET (publish_via_parent); ++CREATE TABLE fail (a int) WITH (publish_via_parent); + +-- establishing an inheritance relationship fixes the problem -+ALTER TABLE sch1.ipart1 INHERIT sch1.iroot; -+SELECT pg_set_logical_root('sch1.ipart1', 'sch1.iroot'); ++ALTER TABLE sch1.ipart1 INHERIT sch1.iroot, ++ SET (publish_via_parent); + +-- but multiple inheritance is not allowed -+ALTER TABLE sch1.ipart2 INHERIT sch1.ipart1; -+SELECT pg_set_logical_root('sch1.ipart2', 'sch1.iroot'); ++ALTER TABLE sch1.ipart2 INHERIT sch1.ipart1, ++ SET (publish_via_parent); + -+ALTER TABLE sch1.ipart2 NO INHERIT sch1.ipart1; -+SELECT pg_set_logical_root('sch1.ipart2', 'sch1.iroot'); ++-- once publish_via_parent is set, inheritance cannot be changed ++ALTER TABLE sch1.ipart2 SET (publish_via_parent); ++ALTER TABLE sch1.ipart2 NO INHERIT sch1.iroot; ++ALTER TABLE sch1.ipart2 INHERIT sch1.ipart1; + +-- table ownership must match, like ATTACH PARTITION +CREATE ROLE regress_test_me; +CREATE ROLE regress_test_not_me; +CREATE TABLE root (a int); +CREATE TABLE part () INHERITS (root); ++ +ALTER TABLE root OWNER TO regress_test_me; +ALTER TABLE part OWNER TO regress_test_not_me; +SET SESSION AUTHORIZATION regress_test_me; -+SELECT pg_set_logical_root('part', 'root'); -- should fail ++ALTER TABLE part SET (publish_via_parent); -- should fail +RESET SESSION AUTHORIZATION; ++ +ALTER TABLE root OWNER TO regress_test_not_me; +ALTER TABLE part OWNER TO regress_test_me; +SET SESSION AUTHORIZATION regress_test_me; -+SELECT pg_set_logical_root('part', 'root'); -- should also fail ++ALTER TABLE part SET (publish_via_parent); -- should also fail ++CREATE TABLE fail () INHERITS (root) WITH (publish_via_parent); +RESET SESSION AUTHORIZATION; ++ +DROP TABLE root, part; +DROP ROLE regress_test_not_me; +DROP ROLE regress_test_me; + -+-- TODO: make sure existing logical descendant can't be ALTERed [NO] INHERIT -+ +-- Mixed publication settings for publish_via_partition_root, at different +-- levels of the inheritance tree, to pin correct behavior in the worst cases. -+CREATE TABLE sch1.ipart1_a () INHERITS (sch1.ipart1); ++CREATE TABLE sch1.ipart1_a () INHERITS (sch1.ipart1) WITH (publish_via_parent); +CREATE TABLE sch1.ipart1_a1 () INHERITS (sch1.ipart1_a); -+ -+SELECT pg_set_logical_root('sch1.ipart1_a', 'sch1.ipart1'); -+SELECT pg_set_logical_root('sch1.ipart1_a1', 'sch1.ipart1_a'); ++ALTER TABLE sch1.ipart1_a1 SET (publish_via_parent); + +CREATE PUBLICATION ipub_root FOR TABLE sch1.iroot; +CREATE PUBLICATION ipub_part1 FOR TABLE ONLY sch1.ipart1, ONLY sch1.ipart1_a1 @@ src/test/regress/sql/publication.sql: ALTER TABLE sch1.tbl1 ATTACH PARTITION sch +SELECT * FROM published_sync('ipub_root', 'ipub_part1', 'ipub_other'); +SELECT * FROM published_stream('ipub_root', 'ipub_part1', 'ipub_other'); + ++-- "Detaching" partitions should change the subscriptions ++ALTER TABLE sch1.ipart2 SET (publish_via_parent = false); ++SELECT pubname, relid::regclass ++ FROM pg_get_publication_tables('ipub_root', 'ipub_part1', 'ipub_other') t ++ JOIN pg_publication p ON (p.oid = t.pubid); ++SELECT * FROM published_sync('ipub_root', 'ipub_part1', 'ipub_other'); ++SELECT * FROM published_stream('ipub_root', 'ipub_part1', 'ipub_other'); ++ ++ALTER TABLE sch1.ipart1_a1 RESET (publish_via_parent); ++SELECT relid::regclass FROM pg_get_publication_tables('ipub_part1'); ++SELECT * FROM published_sync('ipub_part1'); ++SELECT * FROM published_stream('ipub_part1'); ++ +DROP PUBLICATION ipub_other; +DROP PUBLICATION ipub_part1; +DROP PUBLICATION ipub_root; @@ src/test/subscription/t/013_partition.pl: $node_subscriber1->append_conf('postgr $node_subscriber1->reload; +# Make sure standard inheritance setups aren't broken by the new -+# pg_set_logical_root() handling. ++# publish_via_parent handling. +$node_subscriber2->safe_psql('postgres', + "CREATE TABLE itab1 (a int, b text)"); +$node_subscriber2->safe_psql('postgres', @@ src/test/subscription/t/013_partition.pl: $node_subscriber1->append_conf('postgr + "CREATE TABLE itab1_2 (CHECK (a = 2)) INHERITS (itab1)"); + +$node_publisher->safe_psql('postgres', -+ "SELECT pg_set_logical_root('itab1_1', 'itab1')"); ++ "ALTER TABLE itab1_1 SET (publish_via_parent = true)"); +$node_publisher->safe_psql('postgres', -+ "SELECT pg_set_logical_root('itab1_2', 'itab1')"); ++ "ALTER TABLE itab1_2 SET (publish_via_parent = true)"); + +$node_publisher->safe_psql('postgres', "INSERT INTO itab1 VALUES (0, 'itab1')"); +$node_publisher->safe_psql('postgres', "INSERT INTO itab1_1 VALUES (1, 'itab1')"); @@ src/test/subscription/t/013_partition.pl: $result = $node_subscriber2->safe_psql + FOR EACH ROW EXECUTE FUNCTION itab1_trigger();"); + +$node_publisher->safe_psql('postgres', -+ "SELECT pg_set_logical_root('itab1_1', 'itab1')"); ++ "ALTER TABLE itab1_1 SET (publish_via_parent = true)"); +$node_publisher->safe_psql('postgres', -+ "SELECT pg_set_logical_root('itab1_2', 'itab1')"); ++ "ALTER TABLE itab1_2 SET (publish_via_parent = true)"); + +$node_publisher->safe_psql('postgres', + "CREATE TABLE itab2 (a int, b text)"); @@ src/test/subscription/t/013_partition.pl: $result = $node_subscriber2->safe_psql + FOR EACH ROW EXECUTE FUNCTION itab2_trigger();"); + +$node_publisher->safe_psql('postgres', -+ "SELECT pg_set_logical_root('itab2_1', 'itab2')"); ++ "ALTER TABLE itab2_1 SET (publish_via_parent = true)"); + +# itab2_1 should be published using its own identity here, since its parent is +# not included. itab1_1 should be published via its parent, itab1, without @@ src/test/subscription/t/013_partition.pl: $result = $node_subscriber2->safe_psql + "CREATE TABLE itab3_1_1 () INHERITS (itab3_1)"); + +$node_publisher->safe_psql('postgres', -+ "SELECT pg_set_logical_root('itab3_1', 'itab3')"); ++ "ALTER TABLE itab3_1 SET (publish_via_parent = true)"); +$node_publisher->safe_psql('postgres', -+ "SELECT pg_set_logical_root('itab3_1_1', 'itab3_1')"); ++ "ALTER TABLE itab3_1_1 SET (publish_via_parent = true)"); + +$node_publisher->safe_psql('postgres', + "INSERT INTO itab3 VALUES (1, 'itab3')"); @@ src/test/subscription/t/013_partition.pl: $result = $node_subscriber2->safe_psql +is($result, qq(), 'initial data routed for itab3_1_1 on subscriber 2'); + +# make sure new data is also correctly routed to the roots -+$node_publisher->safe_psql('postgres', "INSERT INTO itab3 VALUES (4, 'itab3-new')"); -+$node_publisher->safe_psql('postgres', "INSERT INTO itab3_1 VALUES (4, 'itab3_1-new')"); -+$node_publisher->safe_psql('postgres', "INSERT INTO itab3_1_1 VALUES (4, 'itab3_1_1-new')"); ++$node_publisher->safe_psql('postgres', "INSERT INTO itab3 VALUES (4, 'itab3_new')"); ++$node_publisher->safe_psql('postgres', "INSERT INTO itab3_1 VALUES (4, 'itab3_1_new')"); ++$node_publisher->safe_psql('postgres', "INSERT INTO itab3_1_1 VALUES (4, 'itab3_1_1_new')"); + +$node_publisher->wait_for_catchup('mixed'); + +$result = $node_subscriber2->safe_psql('postgres', + "SELECT a, b FROM ONLY itab3 ORDER BY 1, 2"); +is($result, qq(1|itab3 -+4|itab3-new), 'new data routed for itab3 on subscriber 2'); ++4|itab3_new), 'new data routed for itab3 on subscriber 2'); + +$result = $node_subscriber2->safe_psql('postgres', + "SELECT a, b FROM ONLY itab3_1 ORDER BY 1, 2"); +is($result, qq(2|itab3_1 +3|itab3_1_1 -+4|itab3_1-new -+4|itab3_1_1-new), 'new data routed for itab3_1 on subscriber 2'); ++4|itab3_1_1_new ++4|itab3_1_new), 'new data routed for itab3_1 on subscriber 2'); + +$result = $node_subscriber2->safe_psql('postgres', + "SELECT a, b FROM ONLY itab3_1_1");