On Tue, Mar 24, 2026 at 11:57 AM Masahiko Sawada <[email protected]> wrote: > > On Tue, Mar 24, 2026 at 3:47 AM Amit Kapila <[email protected]> wrote: > > > > On Thu, Mar 19, 2026 at 4:59 AM Masahiko Sawada <[email protected]> > > wrote: > > > > > > On Wed, Mar 18, 2026 at 3:31 PM Masahiko Sawada <[email protected]> > > > wrote: > > > > > > > > > > I've attached the patch to implement this idea. The patch still > > > introduces a new function but it overloads > > > pg_get_publication_tables(). We might be able to handle different > > > input (array or text) in pg_get_publication_tables() better, but it's > > > enough for discussion at least. > > > > > > > * > > + /* > > + * We can pass relid to pg_get_publication_table_info() since > > + * version 19. > > + */ > > + appendStringInfo(&cmd, > > + "SELECT DISTINCT" > > + " (CASE WHEN (array_length(gpt.attrs, 1) = c.relnatts)" > > + " THEN NULL ELSE gpt.attrs END)" > > + " FROM pg_publication p," > > + " LATERAL pg_get_publication_tables(p.pubname, %u) gpt," > > + " pg_class c" > > + " WHERE c.oid = gpt.relid" > > + " AND p.pubname IN ( %s )", > > + lrel->remoteid, > > + pub_names->data); > > > > Why in the above query we need a join with pg_publication? Can't we > > directly pass 'pub_names' and 'relid' to pg_get_publication_tables() > > to get the required information? > > Since the 'pub_names' is the list of publication names we cannot > directly pass it to the pg_get_publication_tables(). But if we make > pg_get_publication_tables() take {pubname text[], target_relid oid} > instead of {pubname text, target_relid oid}, yes. And it seems to help > somewhat simplify the patch. If having both > pg_get_publication_tables(VARIADIC text[]) and > pg_get_publication_tables(text[], oid) is not odd, it would be worth > trying it. >
I figured out that the join with pg_publication works as a filter; non-existence publication names are not passed to the function. If we pass the list of publication names to the new function signature, while we can simplify the patch and avoid a join, we would change the existing function behavior so that it ignores non-existence publications. I've attached the updated patch. The 0001 patch just incorporated the review comments so far, and the 0002 patch is a draft change for the above idea. Since pg_get_publication_tables(VARIADIC text) is not a documented function, I think we can accept small behavior changes. So I'm going to go with this direction. Feedback is very welcome. -- Masahiko Sawada Amazon Web Services: https://aws.amazon.com
From 2bcf744710589e88bfcdb370ba5c2b098cbdae9c Mon Sep 17 00:00:00 2001 From: Masahiko Sawada <[email protected]> Date: Tue, 24 Mar 2026 20:59:26 -0700 Subject: [PATCH v4 2/2] POC: pass the list of publications to pg_get_publication_tables(). --- src/backend/catalog/pg_publication.c | 140 ++++++++++---------- src/backend/replication/logical/tablesync.c | 26 ++-- src/include/catalog/pg_proc.dat | 6 +- src/test/regress/expected/publication.out | 62 ++++++--- src/test/regress/sql/publication.sql | 49 ++++--- 5 files changed, 154 insertions(+), 129 deletions(-) diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c index f4649dbd8b9..181f999916c 100644 --- a/src/backend/catalog/pg_publication.c +++ b/src/backend/catalog/pg_publication.c @@ -1377,7 +1377,6 @@ is_table_publishable_in_publication(Oid relid, Publication *pub) * Helper function to get information of the tables in the given * publication(s). * - * The parameters pubnames and {pubname, target_relid} are mutually exclusive. * If target_relid is provided, the function returns information only for that * specific table. Otherwise, if returns information for all tables within the * specified publications. @@ -1386,7 +1385,7 @@ is_table_publishable_in_publication(Oid relid, Publication *pub) */ static Datum pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, - text *pubname, Oid target_relid) + Oid target_relid) { #define NUM_PUBLICATION_TABLES_ELEM 4 FuncCallContext *funcctx; @@ -1397,6 +1396,10 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, { TupleDesc tupdesc; MemoryContext oldcontext; + Datum *elems; + int nelems, + i; + bool viaroot = false; /* create a function context for cross-call persistence */ funcctx = SRF_FIRSTCALL_INIT(); @@ -1404,49 +1407,37 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, /* switch to memory context appropriate for multiple function calls */ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); - if (pubname != NULL) - { - /* Try to retrieve the specified table information */ - if (SearchSysCacheExists1(RELOID, target_relid)) - { - Publication *pub; - - pub = GetPublicationByName(text_to_cstring(pubname), false); + Assert(pubnames != NULL); - if (is_table_publishable_in_publication(target_relid, pub)) - { - published_rel *table_info = palloc_object(published_rel); + /* + * Deconstruct the parameter into elements where each element is a + * publication name. + */ + deconstruct_array_builtin(pubnames, TEXTOID, &elems, NULL, &nelems); - table_info->relid = target_relid; - table_info->pubid = pub->oid; - table_infos = lappend(table_infos, table_info); - } - } - } - else + /* Get Oids of tables from each publication. */ + for (i = 0; i < nelems; i++) { - Datum *elems; - int nelems, - i; - bool viaroot = false; + Publication *pub_elem; + List *pub_elem_tables = NIL; + ListCell *lc; - Assert(pubnames != NULL); + pub_elem = GetPublicationByName(TextDatumGetCString(elems[i]), true); - /* - * Deconstruct the parameter into elements where each element is a - * publication name. - */ - deconstruct_array_builtin(pubnames, TEXTOID, &elems, NULL, &nelems); + if (pub_elem == NULL) + continue; - /* Get Oids of tables from each publication. */ - for (i = 0; i < nelems; i++) + if (OidIsValid(target_relid)) + { + /* Try to retrieve the specified table information */ + if (SearchSysCacheExists1(RELOID, target_relid) && + is_table_publishable_in_publication(target_relid, pub_elem)) + { + pub_elem_tables = list_make1_oid(target_relid); + } + } + else { - Publication *pub_elem; - List *pub_elem_tables = NIL; - ListCell *lc; - - pub_elem = GetPublicationByName(TextDatumGetCString(elems[i]), false); - /* * Publications support partitioned tables. If * publish_via_partition_root is false, all changes are @@ -1473,45 +1464,45 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, PUBLICATION_PART_LEAF); pub_elem_tables = list_concat_unique_oid(relids, schemarelids); } + } - /* - * Record the published table and the corresponding - * publication so that we can get row filters and column lists - * later. - * - * When a table is published by multiple publications, to - * obtain all row filters and column lists, the structure - * related to this table will be recorded multiple times. - */ - foreach(lc, pub_elem_tables) - { - published_rel *table_info = palloc_object(published_rel); - - table_info->relid = lfirst_oid(lc); - table_info->pubid = pub_elem->oid; - table_infos = lappend(table_infos, table_info); - } + /* + * Record the published table and the corresponding + * publication so that we can get row filters and column lists + * later. + * + * When a table is published by multiple publications, to + * obtain all row filters and column lists, the structure + * related to this table will be recorded multiple times. + */ + foreach(lc, pub_elem_tables) + { + published_rel *table_info = palloc_object(published_rel); - /* - * At least one publication is using - * publish_via_partition_root. - */ - if (pub_elem->pubviaroot) - viaroot = true; + table_info->relid = lfirst_oid(lc); + table_info->pubid = pub_elem->oid; + table_infos = lappend(table_infos, table_info); } /* - * If the publication publishes partition changes via their - * respective root partitioned tables, we must exclude partitions - * in favor of including the root partitioned tables. Otherwise, - * the function could return both the child and parent tables - * which could cause data of the child table to be - * double-published on the subscriber side. + * At least one publication is using + * publish_via_partition_root. */ - if (viaroot) - filter_partitions(table_infos); + if (pub_elem->pubviaroot) + viaroot = true; } + /* + * If the publication publishes partition changes via their + * respective root partitioned tables, we must exclude partitions + * in favor of including the root partitioned tables. Otherwise, + * the function could return both the child and parent tables + * which could cause data of the child table to be + * double-published on the subscriber side. + */ + if (viaroot) + filter_partitions(table_infos); + /* Construct a tuple descriptor for the result rows. */ tupdesc = CreateTemplateTupleDesc(NUM_PUBLICATION_TABLES_ELEM); TupleDescInitEntry(tupdesc, (AttrNumber) 1, "pubid", @@ -1640,14 +1631,21 @@ Datum pg_get_publication_tables_a(PG_FUNCTION_ARGS) { /* Get the information of the tables in the given publications */ - return pg_get_publication_tables(fcinfo, PG_GETARG_ARRAYTYPE_P(0), NULL, InvalidOid); + return pg_get_publication_tables(fcinfo, PG_GETARG_ARRAYTYPE_P(0), InvalidOid); } Datum pg_get_publication_tables_b(PG_FUNCTION_ARGS) { + Oid relid = PG_GETARG_OID(1); + + if (!OidIsValid(relid)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid relation OID %u", relid))); + /* Get the information of the specified table in the given publication */ - return pg_get_publication_tables(fcinfo, NULL, PG_GETARG_TEXT_P(0), PG_GETARG_OID(1)); + return pg_get_publication_tables(fcinfo, PG_GETARG_ARRAYTYPE_P(0), relid); } /* diff --git a/src/backend/replication/logical/tablesync.c b/src/backend/replication/logical/tablesync.c index ec8840ebf42..d70c172e0f5 100644 --- a/src/backend/replication/logical/tablesync.c +++ b/src/backend/replication/logical/tablesync.c @@ -802,20 +802,18 @@ fetch_remote_table_info(char *nspname, char *relname, LogicalRepRelation *lrel, if (server_version >= 190000) { /* - * We can pass relid to pg_get_publication_table() since version - * 19. + * We can pass both publication names and relid to + * pg_get_publication_table() since version 19. */ appendStringInfo(&cmd, "SELECT DISTINCT" " (CASE WHEN (array_length(gpt.attrs, 1) = c.relnatts)" " THEN NULL ELSE gpt.attrs END)" - " FROM pg_publication p," - " LATERAL pg_get_publication_tables(p.pubname, %u) gpt," + " FROM pg_get_publication_tables(ARRAY[%s], %u) gpt," " pg_class c" - " WHERE c.oid = gpt.relid" - " AND p.pubname IN ( %s )", - lrel->remoteid, - pub_names->data); + " WHERE c.oid = gpt.relid", + pub_names->data, + lrel->remoteid); } else appendStringInfo(&cmd, @@ -1006,16 +1004,14 @@ fetch_remote_table_info(char *nspname, char *relname, LogicalRepRelation *lrel, if (server_version >= 190000) { /* - * We can pass relid to pg_get_publication_table() since version - * 19. + * We can pass both publication names and relid to + * pg_get_publication_table() since version 19. */ appendStringInfo(&cmd, "SELECT DISTINCT pg_get_expr(gpt.qual, gpt.relid)" - " FROM pg_publication p," - " LATERAL pg_get_publication_tables(p.pubname, %u) gpt" - " WHERE p.pubname IN ( %s )", - lrel->remoteid, - pub_names->data); + " FROM pg_get_publication_tables(ARRAY[%s], %u) gpt", + pub_names->data, + lrel->remoteid); } else appendStringInfo(&cmd, diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 6c23f36495f..33729d9573a 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -12473,10 +12473,10 @@ descr => 'get information of the specified table that is part of the specified publication', proname => 'pg_get_publication_tables', prorows => '1', proretset => 't', provolatile => 's', - prorettype => 'record', proargtypes => 'text oid', - proallargtypes => '{text,oid,oid,oid,int2vector,pg_node_tree}', + prorettype => 'record', proargtypes => '_text oid', + proallargtypes => '{_text,oid,oid,oid,int2vector,pg_node_tree}', proargmodes => '{i,i,o,o,o,o}', - proargnames => '{pubname,target_relid,pubid,relid,attrs,qual}', + proargnames => '{pubnames,target_relid,pubid,relid,attrs,qual}', prosrc => 'pg_get_publication_tables_b' }, { oid => '8052', descr => 'get OIDs of sequences in a publication', proname => 'pg_get_publication_sequences', prorows => '1000', proretset => 't', diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out index 2c859de6c5e..c5f8e045307 100644 --- a/src/test/regress/expected/publication.out +++ b/src/test/regress/expected/publication.out @@ -2287,7 +2287,7 @@ CREATE PUBLICATION pub_part_leaf FOR TABLE tbl_part1 WITH (publish_via_partition CREATE PUBLICATION pub_part_parent FOR TABLE tbl_parent (id1, id2) WHERE (id1 = 10) WITH (publish_via_partition_root = true); CREATE PUBLICATION pub_part_parent_novia_root FOR TABLE tbl_parent WITH (publish_via_partition_root = false); RESET client_min_messages; -CREATE FUNCTION test_gpt(pubname text, relname text) +CREATE FUNCTION test_gpt(pubnames text[], relname text) RETURNS TABLE ( pubname text, relname name, @@ -2296,104 +2296,125 @@ RETURNS TABLE ( ) BEGIN ATOMIC SELECT p.pubname, c.relname, gpt.attrs::text, pg_get_expr(gpt.qual, gpt.relid) - FROM pg_get_publication_tables(pubname, relname::regclass::oid) gpt + FROM pg_get_publication_tables(pubnames, relname::regclass::oid) gpt JOIN pg_publication p ON p.oid = gpt.pubid JOIN pg_class c ON c.oid = gpt.relid ORDER BY p.pubname, c.relname; END; -SELECT * FROM test_gpt('pub_normal', 'tbl_normal'); +SELECT * FROM test_gpt(ARRAY['pub_normal'], 'tbl_normal'); pubname | relname | attrs | qual ------------+------------+-------+----------- pub_normal | tbl_normal | 1 | (id < 10) (1 row) -SELECT * FROM test_gpt('pub_normal', 'gpt_test_sch.tbl_sch'); -- no result +SELECT * FROM test_gpt(ARRAY['pub_normal'], 'gpt_test_sch.tbl_sch'); -- no result pubname | relname | attrs | qual ---------+---------+-------+------ (0 rows) -SELECT * FROM test_gpt('pub_schema', 'gpt_test_sch.tbl_sch'); +SELECT * FROM test_gpt(ARRAY['pub_schema'], 'gpt_test_sch.tbl_sch'); pubname | relname | attrs | qual ------------+---------+-------+------ pub_schema | tbl_sch | 1 | (1 row) -SELECT * FROM test_gpt('pub_schema', 'tbl_normal'); -- no result +SELECT * FROM test_gpt(ARRAY['pub_schema'], 'tbl_normal'); -- no result pubname | relname | attrs | qual ---------+---------+-------+------ (0 rows) -SELECT * FROM test_gpt('pub_part_parent', 'tbl_parent'); +SELECT * FROM test_gpt(ARRAY['pub_part_parent'], 'tbl_parent'); pubname | relname | attrs | qual -----------------+------------+-------+------------ pub_part_parent | tbl_parent | 1 2 | (id1 = 10) (1 row) -SELECT * FROM test_gpt('pub_part_parent', 'tbl_part1'); -- no result +SELECT * FROM test_gpt(ARRAY['pub_part_parent'], 'tbl_part1'); -- no result pubname | relname | attrs | qual ---------+---------+-------+------ (0 rows) -SELECT * FROM test_gpt('pub_part_parent_novia_root', 'tbl_parent'); -- no result +SELECT * FROM test_gpt(ARRAY['pub_part_parent_novia_root'], 'tbl_parent'); -- no result pubname | relname | attrs | qual ---------+---------+-------+------ (0 rows) -SELECT * FROM test_gpt('pub_part_parent_novia_root', 'tbl_part1'); +SELECT * FROM test_gpt(ARRAY['pub_part_parent_novia_root'], 'tbl_part1'); pubname | relname | attrs | qual ----------------------------+-----------+-------+------ pub_part_parent_novia_root | tbl_part1 | 1 2 3 | (1 row) -SELECT * FROM test_gpt('pub_part_leaf', 'tbl_parent'); -- no result +SELECT * FROM test_gpt(ARRAY['pub_part_leaf'], 'tbl_parent'); -- no result pubname | relname | attrs | qual ---------+---------+-------+------ (0 rows) -SELECT * FROM test_gpt('pub_part_leaf', 'tbl_part1'); +SELECT * FROM test_gpt(ARRAY['pub_part_leaf'], 'tbl_part1'); pubname | relname | attrs | qual ---------------+-----------+-------+------ pub_part_leaf | tbl_part1 | 1 2 3 | (1 row) -SELECT * FROM test_gpt('pub_all', 'tbl_parent'); +SELECT * FROM test_gpt(ARRAY['pub_all'], 'tbl_parent'); pubname | relname | attrs | qual ---------+------------+-------+------ pub_all | tbl_parent | 1 2 3 | (1 row) -SELECT * FROM test_gpt('pub_all', 'tbl_part1'); -- no result +SELECT * FROM test_gpt(ARRAY['pub_all'], 'tbl_part1'); -- no result pubname | relname | attrs | qual ---------+---------+-------+------ (0 rows) -SELECT * FROM test_gpt('pub_all_except', 'tbl_normal'); +-- two rows with different row filter +SELECT * FROM test_gpt(ARRAY['pub_all', 'pub_normal'], 'tbl_normal'); + pubname | relname | attrs | qual +------------+------------+-------+----------- + pub_all | tbl_normal | 1 | + pub_normal | tbl_normal | 1 | (id < 10) +(2 rows) + +-- one row with 'pub_part_parent' +SELECT * FROM test_gpt(ARRAY['pub_part_parent', 'pub_part_parent_novia_root'], 'tbl_parent'); + pubname | relname | attrs | qual +-----------------+------------+-------+------------ + pub_part_parent | tbl_parent | 1 2 | (id1 = 10) +(1 row) + +-- no result, partitions are excluded +SELECT * FROM test_gpt(ARRAY['pub_part_parent', 'pub_all'], 'tbl_part1'); + pubname | relname | attrs | qual +---------+---------+-------+------ +(0 rows) + +SELECT * FROM test_gpt(ARRAY['pub_all_except'], 'tbl_normal'); pubname | relname | attrs | qual ----------------+------------+-------+------ pub_all_except | tbl_normal | 1 | (1 row) -SELECT * FROM test_gpt('pub_all_except', 'gpt_test_sch.tbl_sch'); -- no result (excluded) +SELECT * FROM test_gpt(ARRAY['pub_all_except'], 'gpt_test_sch.tbl_sch'); -- no result (excluded) pubname | relname | attrs | qual ---------+---------+-------+------ (0 rows) -SELECT * FROM test_gpt('pub_all_except', 'tbl_parent'); -- no result (excluded) +SELECT * FROM test_gpt(ARRAY['pub_all_except'], 'tbl_parent'); -- no result (excluded) pubname | relname | attrs | qual ---------+---------+-------+------ (0 rows) -SELECT * FROM test_gpt('pub_all_except', 'tbl_part1'); -- no result (excluded) +SELECT * FROM test_gpt(ARRAY['pub_all_except'], 'tbl_part1'); -- no result (excluded) pubname | relname | attrs | qual ---------+---------+-------+------ (0 rows) -SELECT * FROM test_gpt('pub_all_novia_root', 'tbl_parent'); -- no result +SELECT * FROM test_gpt(ARRAY['pub_all_novia_root'], 'tbl_parent'); -- no result pubname | relname | attrs | qual ---------+---------+-------+------ (0 rows) -SELECT * FROM test_gpt('pub_all_novia_root', 'tbl_part1'); +SELECT * FROM test_gpt(ARRAY['pub_all_novia_root'], 'tbl_part1'); pubname | relname | attrs | qual --------------------+-----------+-------+------ pub_all_novia_root | tbl_part1 | 1 2 3 | @@ -2401,6 +2422,7 @@ SELECT * FROM test_gpt('pub_all_novia_root', 'tbl_part1'); -- Clean up DROP FUNCTION test_gpt(text, text); +ERROR: function test_gpt(text, text) does not exist DROP PUBLICATION pub_all; DROP PUBLICATION pub_all_novia_root; DROP PUBLICATION pub_all_except; diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql index c1c83f7d701..2016c0aac08 100644 --- a/src/test/regress/sql/publication.sql +++ b/src/test/regress/sql/publication.sql @@ -1447,7 +1447,7 @@ CREATE PUBLICATION pub_part_parent FOR TABLE tbl_parent (id1, id2) WHERE (id1 = CREATE PUBLICATION pub_part_parent_novia_root FOR TABLE tbl_parent WITH (publish_via_partition_root = false); RESET client_min_messages; -CREATE FUNCTION test_gpt(pubname text, relname text) +CREATE FUNCTION test_gpt(pubnames text[], relname text) RETURNS TABLE ( pubname text, relname name, @@ -1456,37 +1456,46 @@ RETURNS TABLE ( ) BEGIN ATOMIC SELECT p.pubname, c.relname, gpt.attrs::text, pg_get_expr(gpt.qual, gpt.relid) - FROM pg_get_publication_tables(pubname, relname::regclass::oid) gpt + FROM pg_get_publication_tables(pubnames, relname::regclass::oid) gpt JOIN pg_publication p ON p.oid = gpt.pubid JOIN pg_class c ON c.oid = gpt.relid ORDER BY p.pubname, c.relname; END; -SELECT * FROM test_gpt('pub_normal', 'tbl_normal'); -SELECT * FROM test_gpt('pub_normal', 'gpt_test_sch.tbl_sch'); -- no result +SELECT * FROM test_gpt(ARRAY['pub_normal'], 'tbl_normal'); +SELECT * FROM test_gpt(ARRAY['pub_normal'], 'gpt_test_sch.tbl_sch'); -- no result -SELECT * FROM test_gpt('pub_schema', 'gpt_test_sch.tbl_sch'); -SELECT * FROM test_gpt('pub_schema', 'tbl_normal'); -- no result +SELECT * FROM test_gpt(ARRAY['pub_schema'], 'gpt_test_sch.tbl_sch'); +SELECT * FROM test_gpt(ARRAY['pub_schema'], 'tbl_normal'); -- no result -SELECT * FROM test_gpt('pub_part_parent', 'tbl_parent'); -SELECT * FROM test_gpt('pub_part_parent', 'tbl_part1'); -- no result +SELECT * FROM test_gpt(ARRAY['pub_part_parent'], 'tbl_parent'); +SELECT * FROM test_gpt(ARRAY['pub_part_parent'], 'tbl_part1'); -- no result -SELECT * FROM test_gpt('pub_part_parent_novia_root', 'tbl_parent'); -- no result -SELECT * FROM test_gpt('pub_part_parent_novia_root', 'tbl_part1'); +SELECT * FROM test_gpt(ARRAY['pub_part_parent_novia_root'], 'tbl_parent'); -- no result +SELECT * FROM test_gpt(ARRAY['pub_part_parent_novia_root'], 'tbl_part1'); -SELECT * FROM test_gpt('pub_part_leaf', 'tbl_parent'); -- no result -SELECT * FROM test_gpt('pub_part_leaf', 'tbl_part1'); +SELECT * FROM test_gpt(ARRAY['pub_part_leaf'], 'tbl_parent'); -- no result +SELECT * FROM test_gpt(ARRAY['pub_part_leaf'], 'tbl_part1'); -SELECT * FROM test_gpt('pub_all', 'tbl_parent'); -SELECT * FROM test_gpt('pub_all', 'tbl_part1'); -- no result +SELECT * FROM test_gpt(ARRAY['pub_all'], 'tbl_parent'); +SELECT * FROM test_gpt(ARRAY['pub_all'], 'tbl_part1'); -- no result -SELECT * FROM test_gpt('pub_all_except', 'tbl_normal'); -SELECT * FROM test_gpt('pub_all_except', 'gpt_test_sch.tbl_sch'); -- no result (excluded) -SELECT * FROM test_gpt('pub_all_except', 'tbl_parent'); -- no result (excluded) -SELECT * FROM test_gpt('pub_all_except', 'tbl_part1'); -- no result (excluded) +-- two rows with different row filter +SELECT * FROM test_gpt(ARRAY['pub_all', 'pub_normal'], 'tbl_normal'); -SELECT * FROM test_gpt('pub_all_novia_root', 'tbl_parent'); -- no result -SELECT * FROM test_gpt('pub_all_novia_root', 'tbl_part1'); +-- one row with 'pub_part_parent' +SELECT * FROM test_gpt(ARRAY['pub_part_parent', 'pub_part_parent_novia_root'], 'tbl_parent'); + +-- no result, partitions are excluded +SELECT * FROM test_gpt(ARRAY['pub_part_parent', 'pub_all'], 'tbl_part1'); + +SELECT * FROM test_gpt(ARRAY['pub_all_except'], 'tbl_normal'); +SELECT * FROM test_gpt(ARRAY['pub_all_except'], 'gpt_test_sch.tbl_sch'); -- no result (excluded) +SELECT * FROM test_gpt(ARRAY['pub_all_except'], 'tbl_parent'); -- no result (excluded) +SELECT * FROM test_gpt(ARRAY['pub_all_except'], 'tbl_part1'); -- no result (excluded) + +SELECT * FROM test_gpt(ARRAY['pub_all_novia_root'], 'tbl_parent'); -- no result +SELECT * FROM test_gpt(ARRAY['pub_all_novia_root'], 'tbl_part1'); -- Clean up DROP FUNCTION test_gpt(text, text); -- 2.53.0
From adb8822ddd5fe1f5f616d31512930d62358a272c Mon Sep 17 00:00:00 2001 From: Masahiko Sawada <[email protected]> Date: Fri, 27 Feb 2026 15:42:38 -0800 Subject: [PATCH v4 1/2] Avoid full table scans when getting publication table information by tablesync workers. Reported-by: Marcos Pegoraro <[email protected]> Reviewed-by: Zhijie Hou (Fujitsu) <[email protected]> Reviewed-by: Matheus Alcantara <[email protected]> Reviewed-by: Chao Li <[email protected]> Discussion: https://postgr.es/m/cab-jlwbbfnuasyenzwp0tck9unkthbzqi6woxnevut6+mv8...@mail.gmail.com --- src/backend/catalog/pg_publication.c | 299 +++++++++++++++----- src/backend/replication/logical/tablesync.c | 74 +++-- src/include/catalog/pg_proc.dat | 11 +- src/test/regress/expected/publication.out | 141 +++++++++ src/test/regress/sql/publication.sql | 72 +++++ 5 files changed, 507 insertions(+), 90 deletions(-) diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c index c92ff3f51c3..f4649dbd8b9 100644 --- a/src/backend/catalog/pg_publication.c +++ b/src/backend/catalog/pg_publication.c @@ -1264,12 +1264,129 @@ GetPublicationByName(const char *pubname, bool missing_ok) } /* - * Get information of the tables in the given publication array. + * Returns true if the table of the given relid is published for the specified + * publication. + * + * This function evaluates the effective published OID based on the + * publish_via_partition_root setting, rather than just checking catalog entries + * (e.g., pg_publication_rel). For instance, when publish_via_partition_root is + * false, it returns false for a parent partitioned table and true for its leaf + * partitions, even if the parent is the one explicitly added to the publication. + * + * For performance reasons, this function avoids the overhead of constructing + * the complete list of published tables during the evaluation. It can execute + * quickly even when the publication contains a large number of relations. + */ +static bool +is_table_publishable_in_publication(Oid relid, Publication *pub) +{ + if (pub->pubviaroot) + { + if (pub->alltables) + { + /* + * ALL TABLE publications with pubviaroot=true include only tables + * that are either regular tables or top-most partitioned tables. + */ + if (get_rel_relispartition(relid)) + return false; + + /* + * Check if the table is specified in the EXCEPT clause in the + * publication. ALL TABLE publications have pg_publication_rel + * entries only for EXCEPT'ed tables, so it's sufficient to check + * the existence of its entry. + */ + return !SearchSysCacheExists2(PUBLICATIONRELMAP, + ObjectIdGetDatum(relid), + ObjectIdGetDatum(pub->oid)); + } + + /* + * Check if its corresponding entry exists either in + * pg_publication_rel or pg_publication_namespace. + */ + return (SearchSysCacheExists2(PUBLICATIONRELMAP, + ObjectIdGetDatum(relid), + ObjectIdGetDatum(pub->oid)) || + SearchSysCacheExists2(PUBLICATIONNAMESPACEMAP, + ObjectIdGetDatum(get_rel_namespace(relid)), + ObjectIdGetDatum(pub->oid))); + } + + /* + * For non-pubviaroot publications, partitioned table's OID can never be a + * published OID. + */ + if (get_rel_relkind(relid) == RELKIND_PARTITIONED_TABLE) + return false; + + if (pub->alltables) + { + Oid target_relid = relid; + + if (get_rel_relispartition(relid)) + { + List *ancestors = get_partition_ancestors(relid); + + /* + * Only the top-most ancestor can appear in the EXCEPT clause. + * Therefore, for a partition, exclusion must be evaluated at the + * top-most ancestor. + */ + target_relid = llast_oid(ancestors); + + list_free(ancestors); + } + + /* + * The table is published unless it's specified in the EXCEPT clause. + * ALL TABLE publications have pg_publication_rel entries only for + * EXCEPT'ed tables, so it's sufficient to check the existence of its + * entry. + */ + return !SearchSysCacheExists2(PUBLICATIONRELMAP, + ObjectIdGetDatum(target_relid), + ObjectIdGetDatum(pub->oid)); + } + + if (get_rel_relispartition(relid)) + { + List *ancestors = get_partition_ancestors(relid); + Oid topmost = GetTopMostAncestorInPublication(pub->oid, ancestors, + NULL); + + list_free(ancestors); + + /* This table is published if its ancestor is published */ + if (OidIsValid(topmost)) + return true; + + /* The partition itself might be published, so check below */ + } + + return (SearchSysCacheExists2(PUBLICATIONRELMAP, + ObjectIdGetDatum(relid), + ObjectIdGetDatum(pub->oid)) || + SearchSysCacheExists2(PUBLICATIONNAMESPACEMAP, + ObjectIdGetDatum(get_rel_namespace(relid)), + ObjectIdGetDatum(pub->oid))); +} + +/* + * Helper function to get information of the tables in the given + * publication(s). + * + * The parameters pubnames and {pubname, target_relid} are mutually exclusive. + * If target_relid is provided, the function returns information only for that + * specific table. Otherwise, if returns information for all tables within the + * specified publications. * * Returns pubid, relid, column list, row filter for each table. */ -Datum -pg_get_publication_tables(PG_FUNCTION_ARGS) +static Datum +pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, + text *pubname, Oid target_relid) { #define NUM_PUBLICATION_TABLES_ELEM 4 FuncCallContext *funcctx; @@ -1280,11 +1397,6 @@ pg_get_publication_tables(PG_FUNCTION_ARGS) { TupleDesc tupdesc; MemoryContext oldcontext; - ArrayType *arr; - Datum *elems; - int nelems, - i; - bool viaroot = false; /* create a function context for cross-call persistence */ funcctx = SRF_FIRSTCALL_INIT(); @@ -1292,81 +1404,114 @@ pg_get_publication_tables(PG_FUNCTION_ARGS) /* switch to memory context appropriate for multiple function calls */ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); - /* - * Deconstruct the parameter into elements where each element is a - * publication name. - */ - arr = PG_GETARG_ARRAYTYPE_P(0); - deconstruct_array_builtin(arr, TEXTOID, &elems, NULL, &nelems); - - /* Get Oids of tables from each publication. */ - for (i = 0; i < nelems; i++) + if (pubname != NULL) { - Publication *pub_elem; - List *pub_elem_tables = NIL; - ListCell *lc; + /* Try to retrieve the specified table information */ + if (SearchSysCacheExists1(RELOID, target_relid)) + { + Publication *pub; - pub_elem = GetPublicationByName(TextDatumGetCString(elems[i]), false); + pub = GetPublicationByName(text_to_cstring(pubname), false); - /* - * Publications support partitioned tables. If - * publish_via_partition_root is false, all changes are replicated - * using leaf partition identity and schema, so we only need - * those. Otherwise, get the partitioned table itself. - */ - if (pub_elem->alltables) - pub_elem_tables = GetAllPublicationRelations(pub_elem->oid, - RELKIND_RELATION, - pub_elem->pubviaroot); - else - { - List *relids, - *schemarelids; - - relids = GetIncludedPublicationRelations(pub_elem->oid, - pub_elem->pubviaroot ? - PUBLICATION_PART_ROOT : - PUBLICATION_PART_LEAF); - schemarelids = GetAllSchemaPublicationRelations(pub_elem->oid, - pub_elem->pubviaroot ? - PUBLICATION_PART_ROOT : - PUBLICATION_PART_LEAF); - pub_elem_tables = list_concat_unique_oid(relids, schemarelids); + if (is_table_publishable_in_publication(target_relid, pub)) + { + published_rel *table_info = palloc_object(published_rel); + + table_info->relid = target_relid; + table_info->pubid = pub->oid; + table_infos = lappend(table_infos, table_info); + } } + } + else + { + Datum *elems; + int nelems, + i; + bool viaroot = false; + + Assert(pubnames != NULL); /* - * Record the published table and the corresponding publication so - * that we can get row filters and column lists later. - * - * When a table is published by multiple publications, to obtain - * all row filters and column lists, the structure related to this - * table will be recorded multiple times. + * Deconstruct the parameter into elements where each element is a + * publication name. */ - foreach(lc, pub_elem_tables) + deconstruct_array_builtin(pubnames, TEXTOID, &elems, NULL, &nelems); + + /* Get Oids of tables from each publication. */ + for (i = 0; i < nelems; i++) { - published_rel *table_info = palloc_object(published_rel); + Publication *pub_elem; + List *pub_elem_tables = NIL; + ListCell *lc; + + pub_elem = GetPublicationByName(TextDatumGetCString(elems[i]), false); + + /* + * Publications support partitioned tables. If + * publish_via_partition_root is false, all changes are + * replicated using leaf partition identity and schema, so we + * only need those. Otherwise, get the partitioned table + * itself. + */ + if (pub_elem->alltables) + pub_elem_tables = GetAllPublicationRelations(pub_elem->oid, + RELKIND_RELATION, + pub_elem->pubviaroot); + else + { + List *relids, + *schemarelids; + + relids = GetIncludedPublicationRelations(pub_elem->oid, + pub_elem->pubviaroot ? + PUBLICATION_PART_ROOT : + PUBLICATION_PART_LEAF); + schemarelids = GetAllSchemaPublicationRelations(pub_elem->oid, + pub_elem->pubviaroot ? + PUBLICATION_PART_ROOT : + PUBLICATION_PART_LEAF); + pub_elem_tables = list_concat_unique_oid(relids, schemarelids); + } + + /* + * Record the published table and the corresponding + * publication so that we can get row filters and column lists + * later. + * + * When a table is published by multiple publications, to + * obtain all row filters and column lists, the structure + * related to this table will be recorded multiple times. + */ + foreach(lc, pub_elem_tables) + { + published_rel *table_info = palloc_object(published_rel); - table_info->relid = lfirst_oid(lc); - table_info->pubid = pub_elem->oid; - table_infos = lappend(table_infos, table_info); + table_info->relid = lfirst_oid(lc); + table_info->pubid = pub_elem->oid; + table_infos = lappend(table_infos, table_info); + } + + /* + * At least one publication is using + * publish_via_partition_root. + */ + if (pub_elem->pubviaroot) + viaroot = true; } - /* At least one publication is using publish_via_partition_root. */ - if (pub_elem->pubviaroot) - viaroot = true; + /* + * If the publication publishes partition changes via their + * respective root partitioned tables, we must exclude partitions + * in favor of including the root partitioned tables. Otherwise, + * the function could return both the child and parent tables + * which could cause data of the child table to be + * double-published on the subscriber side. + */ + if (viaroot) + filter_partitions(table_infos); } - /* - * If the publication publishes partition changes via their respective - * root partitioned tables, we must exclude partitions in favor of - * including the root partitioned tables. Otherwise, the function - * could return both the child and parent tables which could cause - * data of the child table to be double-published on the subscriber - * side. - */ - if (viaroot) - filter_partitions(table_infos); - /* Construct a tuple descriptor for the result rows. */ tupdesc = CreateTemplateTupleDesc(NUM_PUBLICATION_TABLES_ELEM); TupleDescInitEntry(tupdesc, (AttrNumber) 1, "pubid", @@ -1491,6 +1636,20 @@ pg_get_publication_tables(PG_FUNCTION_ARGS) SRF_RETURN_DONE(funcctx); } +Datum +pg_get_publication_tables_a(PG_FUNCTION_ARGS) +{ + /* Get the information of the tables in the given publications */ + return pg_get_publication_tables(fcinfo, PG_GETARG_ARRAYTYPE_P(0), NULL, InvalidOid); +} + +Datum +pg_get_publication_tables_b(PG_FUNCTION_ARGS) +{ + /* Get the information of the specified table in the given publication */ + return pg_get_publication_tables(fcinfo, NULL, PG_GETARG_TEXT_P(0), PG_GETARG_OID(1)); +} + /* * Returns Oids of sequences in a publication. */ diff --git a/src/backend/replication/logical/tablesync.c b/src/backend/replication/logical/tablesync.c index f49a4852ecb..ec8840ebf42 100644 --- a/src/backend/replication/logical/tablesync.c +++ b/src/backend/replication/logical/tablesync.c @@ -798,17 +798,37 @@ fetch_remote_table_info(char *nspname, char *relname, LogicalRepRelation *lrel, * publications). */ resetStringInfo(&cmd); - appendStringInfo(&cmd, - "SELECT DISTINCT" - " (CASE WHEN (array_length(gpt.attrs, 1) = c.relnatts)" - " THEN NULL ELSE gpt.attrs END)" - " FROM pg_publication p," - " LATERAL pg_get_publication_tables(p.pubname) gpt," - " pg_class c" - " WHERE gpt.relid = %u AND c.oid = gpt.relid" - " AND p.pubname IN ( %s )", - lrel->remoteid, - pub_names->data); + + if (server_version >= 190000) + { + /* + * We can pass relid to pg_get_publication_table() since version + * 19. + */ + appendStringInfo(&cmd, + "SELECT DISTINCT" + " (CASE WHEN (array_length(gpt.attrs, 1) = c.relnatts)" + " THEN NULL ELSE gpt.attrs END)" + " FROM pg_publication p," + " LATERAL pg_get_publication_tables(p.pubname, %u) gpt," + " pg_class c" + " WHERE c.oid = gpt.relid" + " AND p.pubname IN ( %s )", + lrel->remoteid, + pub_names->data); + } + else + appendStringInfo(&cmd, + "SELECT DISTINCT" + " (CASE WHEN (array_length(gpt.attrs, 1) = c.relnatts)" + " THEN NULL ELSE gpt.attrs END)" + " FROM pg_publication p," + " LATERAL pg_get_publication_tables(p.pubname) gpt," + " pg_class c" + " WHERE gpt.relid = %u AND c.oid = gpt.relid" + " AND p.pubname IN ( %s )", + lrel->remoteid, + pub_names->data); pubres = walrcv_exec(LogRepWorkerWalRcvConn, cmd.data, lengthof(attrsRow), attrsRow); @@ -982,14 +1002,30 @@ fetch_remote_table_info(char *nspname, char *relname, LogicalRepRelation *lrel, /* Check for row filters. */ resetStringInfo(&cmd); - appendStringInfo(&cmd, - "SELECT DISTINCT pg_get_expr(gpt.qual, gpt.relid)" - " FROM pg_publication p," - " LATERAL pg_get_publication_tables(p.pubname) gpt" - " WHERE gpt.relid = %u" - " AND p.pubname IN ( %s )", - lrel->remoteid, - pub_names->data); + + if (server_version >= 190000) + { + /* + * We can pass relid to pg_get_publication_table() since version + * 19. + */ + appendStringInfo(&cmd, + "SELECT DISTINCT pg_get_expr(gpt.qual, gpt.relid)" + " FROM pg_publication p," + " LATERAL pg_get_publication_tables(p.pubname, %u) gpt" + " WHERE p.pubname IN ( %s )", + lrel->remoteid, + pub_names->data); + } + else + appendStringInfo(&cmd, + "SELECT DISTINCT pg_get_expr(gpt.qual, gpt.relid)" + " FROM pg_publication p," + " LATERAL pg_get_publication_tables(p.pubname) gpt" + " WHERE gpt.relid = %u" + " AND p.pubname IN ( %s )", + lrel->remoteid, + pub_names->data); res = walrcv_exec(LogRepWorkerWalRcvConn, cmd.data, 1, qualRow); diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 0118e970dda..6c23f36495f 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -12468,7 +12468,16 @@ proallargtypes => '{_text,oid,oid,int2vector,pg_node_tree}', proargmodes => '{v,o,o,o,o}', proargnames => '{pubname,pubid,relid,attrs,qual}', - prosrc => 'pg_get_publication_tables' }, + prosrc => 'pg_get_publication_tables_a' }, +{ oid => '8060', + descr => 'get information of the specified table that is part of the specified publication', + proname => 'pg_get_publication_tables', prorows => '1', + proretset => 't', provolatile => 's', + prorettype => 'record', proargtypes => 'text oid', + proallargtypes => '{text,oid,oid,oid,int2vector,pg_node_tree}', + proargmodes => '{i,i,o,o,o,o}', + proargnames => '{pubname,target_relid,pubid,relid,attrs,qual}', + prosrc => 'pg_get_publication_tables_b' }, { oid => '8052', descr => 'get OIDs of sequences in a publication', proname => 'pg_get_publication_sequences', prorows => '1000', proretset => 't', provolatile => 's', prorettype => 'oid', proargtypes => 'text', diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out index a220f48b285..2c859de6c5e 100644 --- a/src/test/regress/expected/publication.out +++ b/src/test/regress/expected/publication.out @@ -2271,6 +2271,147 @@ DROP TABLE testpub_merge_pk; RESET SESSION AUTHORIZATION; DROP ROLE regress_publication_user, regress_publication_user2; DROP ROLE regress_publication_user_dummy; +-- Test pg_get_publication_tables(text, oid) function +CREATE SCHEMA gpt_test_sch; +CREATE TABLE gpt_test_sch.tbl_sch (id int); +CREATE TABLE tbl_normal (id int); +CREATE TABLE tbl_parent (id1 int, id2 int, id3 int) PARTITION BY RANGE (id1); +CREATE TABLE tbl_part1 PARTITION OF tbl_parent FOR VALUES FROM (1) TO (10); +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION pub_all FOR ALL TABLES WITH (publish_via_partition_root = true); +CREATE PUBLICATION pub_all_novia_root FOR ALL TABLES WITH (publish_via_partition_root = false); +CREATE PUBLICATION pub_all_except FOR ALL TABLES EXCEPT TABLE (tbl_parent, gpt_test_sch.tbl_sch); +CREATE PUBLICATION pub_schema FOR TABLES IN SCHEMA gpt_test_sch; +CREATE PUBLICATION pub_normal FOR TABLE tbl_normal WHERE (id < 10); +CREATE PUBLICATION pub_part_leaf FOR TABLE tbl_part1 WITH (publish_via_partition_root = false); +CREATE PUBLICATION pub_part_parent FOR TABLE tbl_parent (id1, id2) WHERE (id1 = 10) WITH (publish_via_partition_root = true); +CREATE PUBLICATION pub_part_parent_novia_root FOR TABLE tbl_parent WITH (publish_via_partition_root = false); +RESET client_min_messages; +CREATE FUNCTION test_gpt(pubname text, relname text) +RETURNS TABLE ( + pubname text, + relname name, + attrs text, + qual text +) +BEGIN ATOMIC + SELECT p.pubname, c.relname, gpt.attrs::text, pg_get_expr(gpt.qual, gpt.relid) + FROM pg_get_publication_tables(pubname, relname::regclass::oid) gpt + JOIN pg_publication p ON p.oid = gpt.pubid + JOIN pg_class c ON c.oid = gpt.relid + ORDER BY p.pubname, c.relname; +END; +SELECT * FROM test_gpt('pub_normal', 'tbl_normal'); + pubname | relname | attrs | qual +------------+------------+-------+----------- + pub_normal | tbl_normal | 1 | (id < 10) +(1 row) + +SELECT * FROM test_gpt('pub_normal', 'gpt_test_sch.tbl_sch'); -- no result + pubname | relname | attrs | qual +---------+---------+-------+------ +(0 rows) + +SELECT * FROM test_gpt('pub_schema', 'gpt_test_sch.tbl_sch'); + pubname | relname | attrs | qual +------------+---------+-------+------ + pub_schema | tbl_sch | 1 | +(1 row) + +SELECT * FROM test_gpt('pub_schema', 'tbl_normal'); -- no result + pubname | relname | attrs | qual +---------+---------+-------+------ +(0 rows) + +SELECT * FROM test_gpt('pub_part_parent', 'tbl_parent'); + pubname | relname | attrs | qual +-----------------+------------+-------+------------ + pub_part_parent | tbl_parent | 1 2 | (id1 = 10) +(1 row) + +SELECT * FROM test_gpt('pub_part_parent', 'tbl_part1'); -- no result + pubname | relname | attrs | qual +---------+---------+-------+------ +(0 rows) + +SELECT * FROM test_gpt('pub_part_parent_novia_root', 'tbl_parent'); -- no result + pubname | relname | attrs | qual +---------+---------+-------+------ +(0 rows) + +SELECT * FROM test_gpt('pub_part_parent_novia_root', 'tbl_part1'); + pubname | relname | attrs | qual +----------------------------+-----------+-------+------ + pub_part_parent_novia_root | tbl_part1 | 1 2 3 | +(1 row) + +SELECT * FROM test_gpt('pub_part_leaf', 'tbl_parent'); -- no result + pubname | relname | attrs | qual +---------+---------+-------+------ +(0 rows) + +SELECT * FROM test_gpt('pub_part_leaf', 'tbl_part1'); + pubname | relname | attrs | qual +---------------+-----------+-------+------ + pub_part_leaf | tbl_part1 | 1 2 3 | +(1 row) + +SELECT * FROM test_gpt('pub_all', 'tbl_parent'); + pubname | relname | attrs | qual +---------+------------+-------+------ + pub_all | tbl_parent | 1 2 3 | +(1 row) + +SELECT * FROM test_gpt('pub_all', 'tbl_part1'); -- no result + pubname | relname | attrs | qual +---------+---------+-------+------ +(0 rows) + +SELECT * FROM test_gpt('pub_all_except', 'tbl_normal'); + pubname | relname | attrs | qual +----------------+------------+-------+------ + pub_all_except | tbl_normal | 1 | +(1 row) + +SELECT * FROM test_gpt('pub_all_except', 'gpt_test_sch.tbl_sch'); -- no result (excluded) + pubname | relname | attrs | qual +---------+---------+-------+------ +(0 rows) + +SELECT * FROM test_gpt('pub_all_except', 'tbl_parent'); -- no result (excluded) + pubname | relname | attrs | qual +---------+---------+-------+------ +(0 rows) + +SELECT * FROM test_gpt('pub_all_except', 'tbl_part1'); -- no result (excluded) + pubname | relname | attrs | qual +---------+---------+-------+------ +(0 rows) + +SELECT * FROM test_gpt('pub_all_novia_root', 'tbl_parent'); -- no result + pubname | relname | attrs | qual +---------+---------+-------+------ +(0 rows) + +SELECT * FROM test_gpt('pub_all_novia_root', 'tbl_part1'); + pubname | relname | attrs | qual +--------------------+-----------+-------+------ + pub_all_novia_root | tbl_part1 | 1 2 3 | +(1 row) + +-- Clean up +DROP FUNCTION test_gpt(text, text); +DROP PUBLICATION pub_all; +DROP PUBLICATION pub_all_novia_root; +DROP PUBLICATION pub_all_except; +DROP PUBLICATION pub_schema; +DROP PUBLICATION pub_normal; +DROP PUBLICATION pub_part_leaf; +DROP PUBLICATION pub_part_parent; +DROP PUBLICATION pub_part_parent_novia_root; +DROP TABLE tbl_normal, tbl_parent, tbl_part1; +DROP SCHEMA gpt_test_sch CASCADE; +NOTICE: drop cascades to table gpt_test_sch.tbl_sch -- stage objects for pg_dump tests CREATE SCHEMA pubme CREATE TABLE t0 (c int, d int) CREATE TABLE t1 (c int); CREATE SCHEMA pubme2 CREATE TABLE t0 (c int, d int); diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql index 22e0a30b5c7..c1c83f7d701 100644 --- a/src/test/regress/sql/publication.sql +++ b/src/test/regress/sql/publication.sql @@ -1429,6 +1429,78 @@ RESET SESSION AUTHORIZATION; DROP ROLE regress_publication_user, regress_publication_user2; DROP ROLE regress_publication_user_dummy; +-- Test pg_get_publication_tables(text, oid) function +CREATE SCHEMA gpt_test_sch; +CREATE TABLE gpt_test_sch.tbl_sch (id int); +CREATE TABLE tbl_normal (id int); +CREATE TABLE tbl_parent (id1 int, id2 int, id3 int) PARTITION BY RANGE (id1); +CREATE TABLE tbl_part1 PARTITION OF tbl_parent FOR VALUES FROM (1) TO (10); + +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION pub_all FOR ALL TABLES WITH (publish_via_partition_root = true); +CREATE PUBLICATION pub_all_novia_root FOR ALL TABLES WITH (publish_via_partition_root = false); +CREATE PUBLICATION pub_all_except FOR ALL TABLES EXCEPT TABLE (tbl_parent, gpt_test_sch.tbl_sch); +CREATE PUBLICATION pub_schema FOR TABLES IN SCHEMA gpt_test_sch; +CREATE PUBLICATION pub_normal FOR TABLE tbl_normal WHERE (id < 10); +CREATE PUBLICATION pub_part_leaf FOR TABLE tbl_part1 WITH (publish_via_partition_root = false); +CREATE PUBLICATION pub_part_parent FOR TABLE tbl_parent (id1, id2) WHERE (id1 = 10) WITH (publish_via_partition_root = true); +CREATE PUBLICATION pub_part_parent_novia_root FOR TABLE tbl_parent WITH (publish_via_partition_root = false); +RESET client_min_messages; + +CREATE FUNCTION test_gpt(pubname text, relname text) +RETURNS TABLE ( + pubname text, + relname name, + attrs text, + qual text +) +BEGIN ATOMIC + SELECT p.pubname, c.relname, gpt.attrs::text, pg_get_expr(gpt.qual, gpt.relid) + FROM pg_get_publication_tables(pubname, relname::regclass::oid) gpt + JOIN pg_publication p ON p.oid = gpt.pubid + JOIN pg_class c ON c.oid = gpt.relid + ORDER BY p.pubname, c.relname; +END; + +SELECT * FROM test_gpt('pub_normal', 'tbl_normal'); +SELECT * FROM test_gpt('pub_normal', 'gpt_test_sch.tbl_sch'); -- no result + +SELECT * FROM test_gpt('pub_schema', 'gpt_test_sch.tbl_sch'); +SELECT * FROM test_gpt('pub_schema', 'tbl_normal'); -- no result + +SELECT * FROM test_gpt('pub_part_parent', 'tbl_parent'); +SELECT * FROM test_gpt('pub_part_parent', 'tbl_part1'); -- no result + +SELECT * FROM test_gpt('pub_part_parent_novia_root', 'tbl_parent'); -- no result +SELECT * FROM test_gpt('pub_part_parent_novia_root', 'tbl_part1'); + +SELECT * FROM test_gpt('pub_part_leaf', 'tbl_parent'); -- no result +SELECT * FROM test_gpt('pub_part_leaf', 'tbl_part1'); + +SELECT * FROM test_gpt('pub_all', 'tbl_parent'); +SELECT * FROM test_gpt('pub_all', 'tbl_part1'); -- no result + +SELECT * FROM test_gpt('pub_all_except', 'tbl_normal'); +SELECT * FROM test_gpt('pub_all_except', 'gpt_test_sch.tbl_sch'); -- no result (excluded) +SELECT * FROM test_gpt('pub_all_except', 'tbl_parent'); -- no result (excluded) +SELECT * FROM test_gpt('pub_all_except', 'tbl_part1'); -- no result (excluded) + +SELECT * FROM test_gpt('pub_all_novia_root', 'tbl_parent'); -- no result +SELECT * FROM test_gpt('pub_all_novia_root', 'tbl_part1'); + +-- Clean up +DROP FUNCTION test_gpt(text, text); +DROP PUBLICATION pub_all; +DROP PUBLICATION pub_all_novia_root; +DROP PUBLICATION pub_all_except; +DROP PUBLICATION pub_schema; +DROP PUBLICATION pub_normal; +DROP PUBLICATION pub_part_leaf; +DROP PUBLICATION pub_part_parent; +DROP PUBLICATION pub_part_parent_novia_root; +DROP TABLE tbl_normal, tbl_parent, tbl_part1; +DROP SCHEMA gpt_test_sch CASCADE; + -- stage objects for pg_dump tests CREATE SCHEMA pubme CREATE TABLE t0 (c int, d int) CREATE TABLE t1 (c int); CREATE SCHEMA pubme2 CREATE TABLE t0 (c int, d int); -- 2.53.0
