1: 7e68f96af6 = 1: 173e74279c pgoutput: refactor publication cache construction 2: a48563919e ! 2: dc2425ceaa WIP: introduce publish_via_parent for logical replication @@ Commit message roots. Known bugs/TODOs: - - 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 @@ src/backend/catalog/pg_publication.c: pg_get_publication_tables(PG_FUNCTION_ARGS + { + Oid rootid = PG_GETARG_OID(0); + MemoryContext oldcontext; -+ ArrayType *arr; -+ Datum *elems; -+ int nelems, -+ i; ++ List *publications = NIL; ++ List *inheritors; ++ List *candidates = NIL; ++ ListCell *lc; + + /* create a function context for cross-call persistence */ + funcctx = SRF_FIRSTCALL_INIT(); @@ src/backend/catalog/pg_publication.c: pg_get_publication_tables(PG_FUNCTION_ARGS + /* switch to memory context appropriate for multiple function calls */ + oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + ++ /* Construct the list of publications to consider. */ ++ { ++ ArrayType *arr; ++ Datum *elems; ++ int nelems, ++ i; ++ ++ /* ++ * Deconstruct the parameter into elements where each element is a ++ * publication name. ++ */ ++ arr = PG_GETARG_ARRAYTYPE_P(1); ++ deconstruct_array(arr, TEXTOID, -1, false, TYPALIGN_INT, ++ &elems, NULL, &nelems); ++ ++ for (i = 0; i < nelems; i++) ++ { ++ char *pubname = TextDatumGetCString(elems[i]); ++ Publication *pub = GetPublicationByName(pubname, false); ++ ++ publications = lappend(publications, pub); ++ } ++ } ++ ++ /* TODO: do the tables in this list need to be locked? */ ++ inheritors = find_all_logical_inheritors(rootid); ++ + /* -+ * Deconstruct the parameter into elements where each element is a -+ * publication name. ++ * For a logical descendant to be in the list to sync, both it and the ++ * root table need to be part of a pubviaroot publication. (But they ++ * don't have to be part of the *same* pubviaroot publication. This is ++ * somewhat counterintuitive, but it prevents a descendant from being ++ * double-published through two separate roots.) + */ -+ arr = PG_GETARG_ARRAYTYPE_P(1); -+ deconstruct_array(arr, TEXTOID, -1, false, TYPALIGN_INT, -+ &elems, NULL, &nelems); -+ -+ /* Get Oids of tables from each publication. */ -+ for (i = 0; i < nelems; i++) ++ foreach(lc, publications) + { -+ char *pubname = TextDatumGetCString(elems[i]); -+ Publication *publication = GetPublicationByName(pubname, false); -+ List *pub_tables = NIL; ++ Publication *publication = lfirst(lc); ++ List *relids; ++ List *schemarelids; ++ List *published; ++ ListCell *cell; + -+ /* TODO: do the tables in this list need to be locked? */ -+ if (publication->pubviaroot) -+ pub_tables = find_all_logical_inheritors(rootid); -+ else -+ pub_tables = list_make1_oid(rootid); ++ if (!publication->pubviaroot) ++ continue; + -+ if (!publication->alltables) ++ if (publication->alltables) + { -+ List *relids; -+ List *schemarelids; -+ List *published; -+ ListCell *cell; -+ -+ relids = GetPublicationRelations(publication->oid, -+ publication->pubviaroot ? -+ PUBLICATION_PART_ROOT : -+ PUBLICATION_PART_ALL); -+ schemarelids = GetAllSchemaPublicationRelations(publication->oid, -+ publication->pubviaroot ? -+ PUBLICATION_PART_ROOT : -+ PUBLICATION_PART_LEAF); -+ published = list_concat_unique_oid(relids, schemarelids); -+ -+ /* -+ * First we have to check to make sure the root table is -+ * actually part of the publication; if not, none of its -+ * descendants' contents belong to it. -+ */ -+ if (!list_member_oid(published, rootid)) -+ continue; -+ -+ /* -+ * Now filter out any descendants that aren't part of this -+ * particular publication. -+ */ -+ foreach(cell, pub_tables) -+ { -+ Oid current = lfirst_oid(cell); -+ -+ if (!list_member_oid(published, current)) -+ pub_tables = foreach_delete_current(pub_tables, cell); -+ } ++ /* Easy case: every inheritor is published through the root. */ ++ candidates = inheritors; ++ break; + } + -+ tables = list_concat_unique_oid(tables, pub_tables); ++ /* ++ * Figure out which of the inheritors is included in this ++ * publication. They get added to our candidates list. ++ */ ++ relids = GetPublicationRelations(publication->oid, ++ publication->pubviaroot ? ++ PUBLICATION_PART_ROOT : ++ PUBLICATION_PART_ALL); ++ schemarelids = GetAllSchemaPublicationRelations(publication->oid, ++ publication->pubviaroot ? ++ PUBLICATION_PART_ROOT : ++ PUBLICATION_PART_LEAF); ++ published = list_concat_unique_oid(relids, schemarelids); ++ ++ foreach(cell, inheritors) ++ { ++ Oid current = lfirst_oid(cell); ++ ++ if (list_member_oid(published, current)) ++ candidates = list_append_unique_oid(candidates, current); ++ } + } + ++ /* ++ * Finally, if we didn't find the root table itself in a pubviaroot ++ * publication, none of the logical descendants will be published ++ * through it. ++ * ++ * XXX: this assumes that the root table was part of at least one of the ++ * publications in the list. It doesn't make any sense to call this ++ * function otherwise, but should we ERROR out if that assumption is ++ * violated? ++ */ ++ if (list_member_oid(candidates, rootid)) ++ tables = candidates; ++ else ++ tables = list_make1_oid(rootid); ++ + funcctx->user_fctx = (void *) tables; + + MemoryContextSwitchTo(oldcontext); @@ src/backend/catalog/pg_publication.c: pg_get_publication_tables(PG_FUNCTION_ARGS process_relation_publications(Oid relid, const List *publications, PublicationActions *pubactions, @@ src/backend/catalog/pg_publication.c: process_relation_publications(Oid relid, const List *publications, + ListCell *lc; + int publish_ancestor_level = 0; bool am_partition = get_rel_relispartition(relid); ++ bool am_pubviaroot = false; char relkind = get_rel_relkind(relid); List *rel_publications = NIL; -- -- *publish_as_relid = relid; + Oid publish_candidate = relid; ++ ++ /* ++ * For publish_via_parent handling later, it's useful to know whether the ++ * current table is explicitly part of a publish_via_partition_root ++ * publication up front. ++ */ ++ foreach(lc, publications) ++ { ++ Publication *pub = lfirst(lc); + +- *publish_as_relid = relid; ++ if (!pub->pubviaroot) ++ continue; ++ ++ if (pub->alltables || ++ list_member_oid(pubids, pub->oid) || ++ list_member_oid(schemaPubids, pub->oid)) ++ { ++ am_pubviaroot = true; ++ break; ++ } ++ } foreach(lc, publications) { @@ src/backend/catalog/pg_publication.c: process_relation_publications(Oid relid, c + } } } - - if (!publish) +- +- if (!publish) ++ else { bool ancestor_published = false; + Oid ancestor; @@ src/backend/catalog/pg_publication.c: process_relation_publications(Oid relid, c - Oid ancestor; - int level; - List *ancestors = get_partition_ancestors(relid); -- -- ancestor = GetTopMostAncestorInPublication(pub->oid, -- ancestors, -- &level); + ancestors = get_logical_ancestors(relid, am_partition); + ancestor = GetTopMostAncestorInPublication(pub->oid, + ancestors, + &level); +- ancestor = GetTopMostAncestorInPublication(pub->oid, +- ancestors, +- &level); +- - if (ancestor != InvalidOid) + if (ancestor != InvalidOid) + { @@ src/backend/catalog/pg_publication.c: process_relation_publications(Oid relid, c if (list_member_oid(pubids, pub->oid) || list_member_oid(schemaPubids, pub->oid) || - ancestor_published) -+ (am_partition && ancestor_published)) ++ (ancestor_published && am_partition) || ++ (ancestor_published && am_pubviaroot && pub->pubviaroot)) publish = true; } @@ 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 publish_via_parent ++ * TODO: decide how this interacts with publish_via_parent. Probably not ++ * in the same way, since logical roots can contain data of their own. */ 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 publish_via_parent ++ * TODO: decide if this interacts with publish_via_parent. Probably not ++ * (see above). */ if (!pubviaroot && pri->relation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) @@ src/test/regress/expected/publication.out: SELECT * FROM pg_publication_tables; +CREATE TABLE sch1.ipart1_a () INHERITS (sch1.ipart1) WITH (publish_via_parent); +CREATE TABLE sch1.ipart1_a1 () INHERITS (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 ++CREATE PUBLICATION ipub_all FOR TABLE sch1.iroot; ++CREATE PUBLICATION ipub_one FOR TABLE ONLY sch1.ipart1_a + WITH (publish_via_partition_root); -+CREATE PUBLICATION ipub_other FOR TABLE ONLY sch1.iroot, ONLY sch1.ipart1_a, -+ ONLY sch1.ipart2 ++CREATE PUBLICATION ipub_two FOR TABLE ONLY sch1.ipart1_a + WITH (publish_via_partition_root); -+-- At this point, the published trees look like this: -+-- -+-- ipub_root ipub_part1 (pubviaroot) ipub_other (pubviaroot) -+-- ------------------ ----------------------- ----------------------- -+-- iroot iroot -+-- +- ipart1 ipart1 | -+-- | +- ipart1_a | +--- ipart1_a -+-- | +- ipart1_a1 +--- ipart1_a1 | -+-- +- ipart2 +- ipart2 -+-- What a subscription to only ipub_root should see -+SELECT relid::regclass FROM pg_get_publication_tables('ipub_root'); ++-- ipub_all ipub_one (pubviaroot) ipub_two (pubviaroot) ++-- ------------------ --------------------- --------------------- ++-- iroot ++-- +- ipart1 ++-- | +- ipart1_a - ipart1_a - ipart1_a ++-- | +- ipart1_a1 ++-- +- ipart2 ++-- A subscription to only ipub_all should see every individual table. ++SELECT relid::regclass FROM pg_get_publication_tables('ipub_all'); + relid +---------------- + sch1.iroot @@ src/test/regress/expected/publication.out: SELECT * FROM pg_publication_tables; + sch1.ipart1_a1 +(5 rows) + -+SELECT * FROM published_sync('ipub_root'); ++SELECT * FROM published_sync('ipub_all'); + published | synced +----------------+---------------- + sch1.iroot | sch1.iroot @@ src/test/regress/expected/publication.out: SELECT * FROM pg_publication_tables; + sch1.ipart1_a1 | sch1.ipart1_a1 +(5 rows) + -+SELECT * FROM published_stream('ipub_root'); -+ pubname | published | synced -+-----------+----------------+---------------- -+ ipub_root | sch1.iroot | sch1.iroot -+ ipub_root | sch1.ipart1 | sch1.ipart1 -+ ipub_root | sch1.ipart2 | sch1.ipart2 -+ ipub_root | sch1.ipart1_a | sch1.ipart1_a -+ ipub_root | sch1.ipart1_a1 | sch1.ipart1_a1 ++SELECT * FROM published_stream('ipub_all'); ++ pubname | published | synced ++----------+----------------+---------------- ++ ipub_all | sch1.iroot | sch1.iroot ++ ipub_all | sch1.ipart1 | sch1.ipart1 ++ ipub_all | sch1.ipart2 | sch1.ipart2 ++ ipub_all | sch1.ipart1_a | sch1.ipart1_a ++ ipub_all | sch1.ipart1_a1 | sch1.ipart1_a1 +(5 rows) + -+-- What a subscription to only ipub_part1 should see -+SELECT relid::regclass FROM pg_get_publication_tables('ipub_part1'); -+ relid -+------------- -+ sch1.ipart1 -+(1 row) ++-- A subscription to both ipub_all and ipub_one shouldn't change the initial ++-- sync, since there is no alternative root being published for ipart1_a. It ++-- will be duplicated in the stream list, since TODO ++SELECT pubname, relid::regclass ++ FROM pg_get_publication_tables('ipub_all', 'ipub_one') t ++ JOIN pg_publication p ON (p.oid = t.pubid); ++ pubname | relid ++----------+---------------- ++ ipub_all | sch1.iroot ++ ipub_all | sch1.ipart1 ++ ipub_all | sch1.ipart2 ++ ipub_all | sch1.ipart1_a ++ ipub_all | sch1.ipart1_a1 ++ ipub_one | sch1.ipart1_a ++(6 rows) ++ ++SELECT * FROM published_sync('ipub_all', 'ipub_one'); ++ published | synced ++----------------+---------------- ++ sch1.iroot | sch1.iroot ++ sch1.ipart1 | sch1.ipart1 ++ sch1.ipart2 | sch1.ipart2 ++ sch1.ipart1_a | sch1.ipart1_a ++ sch1.ipart1_a1 | sch1.ipart1_a1 ++(5 rows) ++ ++SELECT * FROM published_stream('ipub_all', 'ipub_one'); ++ pubname | published | synced ++----------+----------------+---------------- ++ ipub_all | sch1.iroot | sch1.iroot ++ ipub_all | sch1.ipart1 | sch1.ipart1 ++ ipub_all | sch1.ipart2 | sch1.ipart2 ++ ipub_all | sch1.ipart1_a | sch1.ipart1_a ++ ipub_all | sch1.ipart1_a1 | sch1.ipart1_a1 ++ ipub_one | sch1.ipart1_a | sch1.ipart1_a ++(6 rows) + -+SELECT * FROM published_sync('ipub_part1'); -+ published | synced -+-------------+---------------- -+ sch1.ipart1 | sch1.ipart1 -+ sch1.ipart1 | sch1.ipart1_a1 -+(2 rows) -+ -+SELECT * FROM published_stream('ipub_part1'); -+ pubname | published | synced -+------------+-------------+---------------- -+ ipub_part1 | sch1.ipart1 | sch1.ipart1 -+ ipub_part1 | sch1.ipart1 | sch1.ipart1_a1 -+(2 rows) -+ -+-- What a subscription to both ipub_root and ipub_part1 should see ++ALTER PUBLICATION ipub_one ADD TABLE ONLY sch1.ipart1; ++-- ipub_all ipub_one (pubviaroot) ipub_two (pubviaroot) ++-- ------------------ --------------------- --------------------- ++-- iroot ++-- +- ipart1 - ipart1 ++-- | +- ipart1_a +- ipart1_a - ipart1_a ++-- | +- ipart1_a1 ++-- +- ipart2 ++-- Adding ipart1_a's parent table to ipub_one results in ipart1_a1 being ++-- "stolen" from the ipub_all stream, to prevent its data from being duplicated. +SELECT pubname, relid::regclass -+ FROM pg_get_publication_tables('ipub_root', 'ipub_part1') t ++ FROM pg_get_publication_tables('ipub_all', 'ipub_one') 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_root | sch1.ipart1_a -+ ipub_part1 | sch1.ipart1 ++ pubname | relid ++----------+---------------- ++ ipub_all | sch1.iroot ++ ipub_all | sch1.ipart1 ++ ipub_all | sch1.ipart2 ++ ipub_all | sch1.ipart1_a1 ++ ipub_one | sch1.ipart1 +(5 rows) + -+SELECT * FROM published_sync('ipub_root', 'ipub_part1'); -+ published | synced -+---------------+---------------- -+ sch1.iroot | sch1.iroot -+ sch1.ipart1 | sch1.ipart1 -+ sch1.ipart1 | sch1.ipart1_a1 -+ sch1.ipart2 | sch1.ipart2 -+ sch1.ipart1_a | sch1.ipart1_a ++SELECT * FROM published_sync('ipub_all', 'ipub_one'); ++ published | synced ++----------------+---------------- ++ sch1.iroot | sch1.iroot ++ sch1.ipart1 | sch1.ipart1 ++ sch1.ipart1 | sch1.ipart1_a ++ sch1.ipart2 | sch1.ipart2 ++ sch1.ipart1_a1 | sch1.ipart1_a1 +(5 rows) + -+SELECT * FROM published_stream('ipub_root', 'ipub_part1'); -+ pubname | published | synced -+------------+---------------+---------------- -+ ipub_root | sch1.iroot | sch1.iroot -+ ipub_root | sch1.ipart1 | sch1.ipart1 -+ ipub_root | sch1.ipart1 | sch1.ipart1_a1 -+ ipub_root | sch1.ipart2 | sch1.ipart2 -+ ipub_root | sch1.ipart1_a | sch1.ipart1_a -+ ipub_part1 | sch1.ipart1 | sch1.ipart1 -+ ipub_part1 | sch1.ipart1 | sch1.ipart1_a1 ++SELECT * FROM published_stream('ipub_all', 'ipub_one'); ++ pubname | published | synced ++----------+----------------+---------------- ++ ipub_all | sch1.iroot | sch1.iroot ++ ipub_all | sch1.ipart1 | sch1.ipart1 ++ ipub_all | sch1.ipart1 | sch1.ipart1_a ++ ipub_all | sch1.ipart2 | sch1.ipart2 ++ ipub_all | sch1.ipart1_a1 | sch1.ipart1_a1 ++ ipub_one | sch1.ipart1 | sch1.ipart1 ++ ipub_one | sch1.ipart1 | sch1.ipart1_a +(7 rows) + -+-- What a subscription to both ipub_part1 and ipub_other should see ++-- Including ipub_two in the subscription list should change nothing about the ++-- sync. ipub_two will gain an entry for ipart1_a in the streaming list. +SELECT pubname, relid::regclass -+ FROM pg_get_publication_tables('ipub_part1', 'ipub_other') t ++ FROM pg_get_publication_tables('ipub_all', 'ipub_one', 'ipub_two') t + JOIN pg_publication p ON (p.oid = t.pubid); -+ pubname | relid -+------------+------------- -+ ipub_part1 | sch1.ipart1 -+ ipub_other | sch1.iroot -+(2 rows) -+ -+SELECT * FROM published_sync('ipub_part1', 'ipub_other'); -+ published | synced -+-------------+---------------- -+ sch1.iroot | sch1.iroot -+ sch1.iroot | sch1.ipart2 -+ sch1.iroot | sch1.ipart1_a -+ sch1.ipart1 | sch1.ipart1 -+ sch1.ipart1 | sch1.ipart1_a1 ++ pubname | relid ++----------+---------------- ++ ipub_all | sch1.iroot ++ ipub_all | sch1.ipart1 ++ ipub_all | sch1.ipart2 ++ ipub_all | sch1.ipart1_a1 ++ ipub_one | sch1.ipart1 +(5 rows) + -+SELECT * FROM published_stream('ipub_part1', 'ipub_other'); -+ pubname | published | synced -+------------+-------------+---------------- -+ ipub_part1 | sch1.ipart1 | sch1.ipart1 -+ ipub_part1 | sch1.ipart1 | sch1.ipart1_a1 -+ ipub_other | sch1.iroot | sch1.iroot -+ ipub_other | sch1.iroot | sch1.ipart2 -+ ipub_other | sch1.iroot | sch1.ipart1_a ++SELECT * FROM published_sync('ipub_all', 'ipub_one', 'ipub_two'); ++ published | synced ++----------------+---------------- ++ sch1.iroot | sch1.iroot ++ sch1.ipart1 | sch1.ipart1 ++ sch1.ipart1 | sch1.ipart1_a ++ sch1.ipart2 | sch1.ipart2 ++ sch1.ipart1_a1 | sch1.ipart1_a1 +(5 rows) + -+-- What a subscription to all three should see ++SELECT * FROM published_stream('ipub_all', 'ipub_one', 'ipub_two'); ++ pubname | published | synced ++----------+----------------+---------------- ++ ipub_all | sch1.iroot | sch1.iroot ++ ipub_all | sch1.ipart1 | sch1.ipart1 ++ ipub_all | sch1.ipart1 | sch1.ipart1_a ++ ipub_all | sch1.ipart2 | sch1.ipart2 ++ ipub_all | sch1.ipart1_a1 | sch1.ipart1_a1 ++ ipub_one | sch1.ipart1 | sch1.ipart1 ++ ipub_one | sch1.ipart1 | sch1.ipart1_a ++ ipub_two | sch1.ipart1 | sch1.ipart1_a ++(8 rows) ++ ++ALTER PUBLICATION ipub_two ADD TABLE ONLY sch1.iroot; ++-- ipub_all ipub_one (pubviaroot) ipub_two (pubviaroot) ++-- ------------------ --------------------- --------------------- ++-- iroot iroot ++-- +- ipart1 - ipart1 | ++-- | +- ipart1_a +- ipart1_a +---- ipart1_a ++-- | +- ipart1_a1 ++-- +- ipart2 ++-- Adding iroot to ipub_two ends up stealing both ipart1 and ipart1_a from the ++-- other publications, again so that no data is double-published. +SELECT pubname, relid::regclass -+ FROM pg_get_publication_tables('ipub_root', 'ipub_part1', 'ipub_other') t ++ FROM pg_get_publication_tables('ipub_all', 'ipub_one', 'ipub_two') t + JOIN pg_publication p ON (p.oid = t.pubid); -+ pubname | relid -+------------+------------- -+ ipub_root | sch1.iroot -+ ipub_root | sch1.ipart1 -+ ipub_part1 | sch1.ipart1 -+ ipub_other | sch1.iroot ++ pubname | relid ++----------+---------------- ++ ipub_all | sch1.iroot ++ ipub_all | sch1.ipart2 ++ ipub_all | sch1.ipart1_a1 ++ ipub_two | sch1.iroot +(4 rows) + -+SELECT * FROM published_sync('ipub_root', 'ipub_part1', 'ipub_other'); -+ published | synced -+-------------+---------------- -+ sch1.iroot | sch1.iroot -+ sch1.iroot | sch1.ipart2 -+ sch1.iroot | sch1.ipart1_a -+ sch1.ipart1 | sch1.ipart1 -+ sch1.ipart1 | sch1.ipart1_a1 ++SELECT * FROM published_sync('ipub_all', 'ipub_one', 'ipub_two'); ++ published | synced ++----------------+---------------- ++ sch1.iroot | sch1.iroot ++ sch1.iroot | sch1.ipart1 ++ sch1.iroot | sch1.ipart1_a ++ sch1.ipart2 | sch1.ipart2 ++ sch1.ipart1_a1 | sch1.ipart1_a1 +(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.ipart2 -+ ipub_root | sch1.iroot | sch1.ipart1_a -+ ipub_root | sch1.ipart1 | sch1.ipart1 -+ ipub_root | sch1.ipart1 | sch1.ipart1_a1 -+ ipub_part1 | sch1.ipart1 | sch1.ipart1 -+ ipub_part1 | sch1.ipart1 | sch1.ipart1_a1 -+ ipub_other | sch1.iroot | sch1.iroot -+ ipub_other | sch1.iroot | sch1.ipart2 -+ ipub_other | sch1.iroot | sch1.ipart1_a -+(10 rows) ++SELECT * FROM published_stream('ipub_all', 'ipub_one', 'ipub_two'); ++ pubname | published | synced ++----------+----------------+---------------- ++ ipub_all | sch1.iroot | sch1.iroot ++ ipub_all | sch1.iroot | sch1.ipart1 ++ ipub_all | sch1.iroot | sch1.ipart1_a ++ ipub_all | sch1.ipart2 | sch1.ipart2 ++ ipub_all | sch1.ipart1_a1 | sch1.ipart1_a1 ++ ipub_one | sch1.iroot | sch1.ipart1 ++ ipub_one | sch1.iroot | sch1.ipart1_a ++ ipub_two | sch1.iroot | sch1.iroot ++ ipub_two | sch1.iroot | sch1.ipart1_a ++(9 rows) + +-- "Detaching" partitions should change the subscriptions -+ALTER TABLE sch1.ipart2 SET (publish_via_parent = false); ++ALTER TABLE sch1.ipart1_a SET (publish_via_parent = false); +SELECT pubname, relid::regclass -+ FROM pg_get_publication_tables('ipub_root', 'ipub_part1', 'ipub_other') t ++ FROM pg_get_publication_tables('ipub_all', 'ipub_one', 'ipub_two') 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) ++ pubname | relid ++----------+---------------- ++ ipub_all | sch1.iroot ++ ipub_all | sch1.ipart2 ++ ipub_all | sch1.ipart1_a ++ ipub_all | sch1.ipart1_a1 ++ ipub_one | sch1.ipart1_a ++ ipub_two | sch1.iroot ++ ipub_two | sch1.ipart1_a ++(7 rows) + -+SELECT * FROM published_sync('ipub_part1'); ++SELECT * FROM published_sync('ipub_all', 'ipub_one', 'ipub_two'); + published | synced +----------------+---------------- -+ sch1.ipart1 | sch1.ipart1 ++ sch1.iroot | sch1.iroot ++ sch1.iroot | sch1.ipart1 ++ sch1.ipart2 | sch1.ipart2 ++ sch1.ipart1_a | sch1.ipart1_a + 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; ++(5 rows) ++ ++SELECT * FROM published_stream('ipub_all', 'ipub_one', 'ipub_two'); ++ pubname | published | synced ++----------+----------------+---------------- ++ ipub_all | sch1.iroot | sch1.iroot ++ ipub_all | sch1.iroot | sch1.ipart1 ++ ipub_all | sch1.ipart2 | sch1.ipart2 ++ ipub_all | sch1.ipart1_a | sch1.ipart1_a ++ ipub_all | sch1.ipart1_a1 | sch1.ipart1_a1 ++ ipub_one | sch1.iroot | sch1.ipart1 ++ ipub_one | sch1.ipart1_a | sch1.ipart1_a ++ ipub_two | sch1.iroot | sch1.iroot ++ ipub_two | sch1.ipart1_a | sch1.ipart1_a ++(9 rows) ++ ++ALTER TABLE sch1.ipart1 RESET (publish_via_parent); ++SELECT pubname, relid::regclass ++ FROM pg_get_publication_tables('ipub_one', 'ipub_two') t ++ JOIN pg_publication p ON (p.oid = t.pubid); ++ pubname | relid ++----------+--------------- ++ ipub_one | sch1.ipart1 ++ ipub_one | sch1.ipart1_a ++ ipub_two | sch1.iroot ++ ipub_two | sch1.ipart1_a ++(4 rows) ++ ++SELECT * FROM published_sync('ipub_one', 'ipub_two'); ++ published | synced ++---------------+--------------- ++ sch1.iroot | sch1.iroot ++ sch1.ipart1 | sch1.ipart1 ++ sch1.ipart1_a | sch1.ipart1_a ++(3 rows) ++ ++SELECT * FROM published_stream('ipub_one', 'ipub_two'); ++ pubname | published | synced ++----------+---------------+--------------- ++ ipub_one | sch1.ipart1 | sch1.ipart1 ++ ipub_one | sch1.ipart1_a | sch1.ipart1_a ++ ipub_two | sch1.iroot | sch1.iroot ++ ipub_two | sch1.ipart1_a | sch1.ipart1_a ++(4 rows) ++ ++DROP PUBLICATION ipub_all; ++DROP PUBLICATION ipub_one; ++DROP PUBLICATION ipub_two; RESET client_min_messages; DROP PUBLICATION pub; DROP TABLE sch1.tbl1; @@ src/test/regress/sql/publication.sql: ALTER TABLE sch1.tbl1 ATTACH PARTITION sch +CREATE TABLE sch1.ipart1_a1 () INHERITS (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 ++CREATE PUBLICATION ipub_all FOR TABLE sch1.iroot; ++CREATE PUBLICATION ipub_one FOR TABLE ONLY sch1.ipart1_a + WITH (publish_via_partition_root); -+CREATE PUBLICATION ipub_other FOR TABLE ONLY sch1.iroot, ONLY sch1.ipart1_a, -+ ONLY sch1.ipart2 ++CREATE PUBLICATION ipub_two FOR TABLE ONLY sch1.ipart1_a + WITH (publish_via_partition_root); + -+-- At this point, the published trees look like this: -+-- -+-- ipub_root ipub_part1 (pubviaroot) ipub_other (pubviaroot) -+-- ------------------ ----------------------- ----------------------- -+-- iroot iroot -+-- +- ipart1 ipart1 | -+-- | +- ipart1_a | +--- ipart1_a -+-- | +- ipart1_a1 +--- ipart1_a1 | -+-- +- ipart2 +- ipart2 -+ -+-- What a subscription to only ipub_root should see -+SELECT relid::regclass FROM pg_get_publication_tables('ipub_root'); -+SELECT * FROM published_sync('ipub_root'); -+SELECT * FROM published_stream('ipub_root'); -+ -+-- What a subscription to only ipub_part1 should see -+SELECT relid::regclass FROM pg_get_publication_tables('ipub_part1'); -+SELECT * FROM published_sync('ipub_part1'); -+SELECT * FROM published_stream('ipub_part1'); -+ -+-- What a subscription to both ipub_root and ipub_part1 should see ++-- ipub_all ipub_one (pubviaroot) ipub_two (pubviaroot) ++-- ------------------ --------------------- --------------------- ++-- iroot ++-- +- ipart1 ++-- | +- ipart1_a - ipart1_a - ipart1_a ++-- | +- ipart1_a1 ++-- +- ipart2 ++ ++-- A subscription to only ipub_all should see every individual table. ++SELECT relid::regclass FROM pg_get_publication_tables('ipub_all'); ++SELECT * FROM published_sync('ipub_all'); ++SELECT * FROM published_stream('ipub_all'); ++ ++-- A subscription to both ipub_all and ipub_one shouldn't change the initial ++-- sync, since there is no alternative root being published for ipart1_a. It ++-- will be duplicated in the stream list, since TODO +SELECT pubname, relid::regclass -+ FROM pg_get_publication_tables('ipub_root', 'ipub_part1') t ++ FROM pg_get_publication_tables('ipub_all', 'ipub_one') t + JOIN pg_publication p ON (p.oid = t.pubid); -+SELECT * FROM published_sync('ipub_root', 'ipub_part1'); -+SELECT * FROM published_stream('ipub_root', 'ipub_part1'); ++SELECT * FROM published_sync('ipub_all', 'ipub_one'); ++SELECT * FROM published_stream('ipub_all', 'ipub_one'); ++ ++ALTER PUBLICATION ipub_one ADD TABLE ONLY sch1.ipart1; ++ ++-- ipub_all ipub_one (pubviaroot) ipub_two (pubviaroot) ++-- ------------------ --------------------- --------------------- ++-- iroot ++-- +- ipart1 - ipart1 ++-- | +- ipart1_a +- ipart1_a - ipart1_a ++-- | +- ipart1_a1 ++-- +- ipart2 + -+-- What a subscription to both ipub_part1 and ipub_other should see ++-- Adding ipart1_a's parent table to ipub_one results in ipart1_a1 being ++-- "stolen" from the ipub_all stream, to prevent its data from being duplicated. +SELECT pubname, relid::regclass -+ FROM pg_get_publication_tables('ipub_part1', 'ipub_other') t ++ FROM pg_get_publication_tables('ipub_all', 'ipub_one') t + JOIN pg_publication p ON (p.oid = t.pubid); -+SELECT * FROM published_sync('ipub_part1', 'ipub_other'); -+SELECT * FROM published_stream('ipub_part1', 'ipub_other'); ++SELECT * FROM published_sync('ipub_all', 'ipub_one'); ++SELECT * FROM published_stream('ipub_all', 'ipub_one'); + -+-- What a subscription to all three should see ++-- Including ipub_two in the subscription list should change nothing about the ++-- sync. ipub_two will gain an entry for ipart1_a in the streaming list. +SELECT pubname, relid::regclass -+ FROM pg_get_publication_tables('ipub_root', 'ipub_part1', 'ipub_other') t ++ FROM pg_get_publication_tables('ipub_all', 'ipub_one', 'ipub_two') 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'); ++SELECT * FROM published_sync('ipub_all', 'ipub_one', 'ipub_two'); ++SELECT * FROM published_stream('ipub_all', 'ipub_one', 'ipub_two'); ++ ++ALTER PUBLICATION ipub_two ADD TABLE ONLY sch1.iroot; ++ ++-- ipub_all ipub_one (pubviaroot) ipub_two (pubviaroot) ++-- ------------------ --------------------- --------------------- ++-- iroot iroot ++-- +- ipart1 - ipart1 | ++-- | +- ipart1_a +- ipart1_a +---- ipart1_a ++-- | +- ipart1_a1 ++-- +- ipart2 ++ ++-- Adding iroot to ipub_two ends up stealing both ipart1 and ipart1_a from the ++-- other publications, again so that no data is double-published. ++SELECT pubname, relid::regclass ++ FROM pg_get_publication_tables('ipub_all', 'ipub_one', 'ipub_two') t ++ JOIN pg_publication p ON (p.oid = t.pubid); ++SELECT * FROM published_sync('ipub_all', 'ipub_one', 'ipub_two'); ++SELECT * FROM published_stream('ipub_all', 'ipub_one', 'ipub_two'); + +-- "Detaching" partitions should change the subscriptions -+ALTER TABLE sch1.ipart2 SET (publish_via_parent = false); ++ALTER TABLE sch1.ipart1_a SET (publish_via_parent = false); +SELECT pubname, relid::regclass -+ FROM pg_get_publication_tables('ipub_root', 'ipub_part1', 'ipub_other') t ++ FROM pg_get_publication_tables('ipub_all', 'ipub_one', 'ipub_two') 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'); ++SELECT * FROM published_sync('ipub_all', 'ipub_one', 'ipub_two'); ++SELECT * FROM published_stream('ipub_all', 'ipub_one', 'ipub_two'); + -+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'); ++ALTER TABLE sch1.ipart1 RESET (publish_via_parent); ++SELECT pubname, relid::regclass ++ FROM pg_get_publication_tables('ipub_one', 'ipub_two') t ++ JOIN pg_publication p ON (p.oid = t.pubid); ++SELECT * FROM published_sync('ipub_one', 'ipub_two'); ++SELECT * FROM published_stream('ipub_one', 'ipub_two'); + -+DROP PUBLICATION ipub_other; -+DROP PUBLICATION ipub_part1; -+DROP PUBLICATION ipub_root; ++DROP PUBLICATION ipub_all; ++DROP PUBLICATION ipub_one; ++DROP PUBLICATION ipub_two; + RESET client_min_messages; DROP PUBLICATION pub;