On Tue, Mar 3, 2026 at 2:22 AM Zhijie Hou (Fujitsu)
<[email protected]> wrote:
>
> On Saturday, February 28, 2026 7:48 AM Masahiko Sawada
> <[email protected]> wrote:
> > To: Marcos Pegoraro <[email protected]>
> > Cc: PostgreSQL Hackers <[email protected]>
> > Subject: Re: Initial COPY of Logical Replication is too slow
> >
> > Another variant of this approach is to extend
> > pg_get_publication_table() so that it can accept a relid to get the
> > publication
> > information of the specific table. I've attached the patch for this idea.
> > I'm
> > going to add regression test cases.
> >
> > pg_get_publication_table() is a VARIACID array function so the patch changes
> > its signature to {text[] [, oid]}, breaking the tool compatibility. Given
> > this
> > function is mostly an internal-use function (we don't have the documentation
> > for it), it would probably be okay with it. I find it's clearer than the
> > other
> > approach of introducing pg_get_publication_table_info(). Feedback is very
> > welcome.
>
> Thanks for updating the patch.
>
> I have few comments for the function change:
>
> 1.
>
> If we change the function signature, will it affect use cases where the
> publisher version is newer and the subscriber version is older ? E.g., when
> publisher is passing text style publication name to
> pg_get_publication_tables().
Good point.
I noticed that changing the function signature of
pg_get_publication_tables() breaks logical replication setups where
the subscriber is 18 or older. In the latest patch, I've switched the
approach back to the pg_get_publication_table_info() idea.
>
> 2.
>
> In the following example, I expected it to output a table with valid row
> filter, but it returns 0 row after applying the patch.
>
> CREATE TABLE measurements (
> city_id int not null,
> logdate date not null,
> peaktemp int,
> unitsales int
> ) PARTITION BY RANGE (logdate);
>
> -- Create partitions
> CREATE TABLE measurements_2023_q1 PARTITION OF measurements
> FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');
>
> CREATE PUBLICATION pub FOR TABLE measurements_2023_q1 WHERE (city_id = 2);
>
> select pg_get_publication_tables(ARRAY['pub2'],
> 'measurements_2023_q1'::regclass);
> pg_get_publication_tables
> ---------------------------
> (0 rows)
Thank you for testing the patch. I've fixed it and added regression
tests in the latest patch.
I've attached the updated patch.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
From 7ffa55e77413743b63092a824c1a70f74dd122f0 Mon Sep 17 00:00:00 2001
From: Masahiko Sawada <[email protected]>
Date: Fri, 27 Feb 2026 15:42:38 -0800
Subject: [PATCH v2] 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+mV8XmA@mail.gmail.com
---
src/backend/catalog/pg_publication.c | 382 +++++++++++++++-----
src/backend/replication/logical/tablesync.c | 68 +++-
src/include/catalog/pg_proc.dat | 9 +
src/test/regress/expected/publication.out | 129 +++++++
src/test/regress/sql/publication.sql | 67 ++++
5 files changed, 543 insertions(+), 112 deletions(-)
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index aadc7c202c6..5213f1d0a23 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -1207,6 +1207,240 @@ GetPublicationByName(const char *pubname, bool missing_ok)
return OidIsValid(oid) ? GetPublication(oid) : NULL;
}
+/*
+ * 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)));
+}
+
+/*
+ * pg_get_publication_tables() and pg_get_publication_table_info() use
+ * the same record type.
+ */
+#define NUM_PUBLICATION_TABLES_ELEM 4
+
+/*
+ * Construct a tuple descriptor for both pg_get_publication_tales() and
+ * pg_get_publication_table_info() functions.
+ */
+static TupleDesc
+create_published_rel_tuple_desc(void)
+{
+ TupleDesc tupdesc;
+
+ tupdesc = CreateTemplateTupleDesc(NUM_PUBLICATION_TABLES_ELEM);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 1, "pubid",
+ OIDOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 2, "relid",
+ OIDOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 3, "attrs",
+ INT2VECTOROID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 4, "qual",
+ PG_NODE_TREEOID, -1, 0);
+
+ return BlessTupleDesc(tupdesc);
+}
+
+/*
+ * Common routine for pg_get_publication_tables() and
+ * pg_get_publication_table_info() to construct the result tuple.
+ * tuple_desc should be the tuple description returned by
+ * create_published_rel_tuple_desc().
+ */
+static HeapTuple
+construct_published_rel_tuple(published_rel *table_info, TupleDesc tuple_desc)
+{
+ Publication *pub;
+ Oid relid = table_info->relid;
+ Oid schemaid = get_rel_namespace(relid);
+ HeapTuple pubtuple = NULL;
+ Datum values[NUM_PUBLICATION_TABLES_ELEM] = {0};
+ bool nulls[NUM_PUBLICATION_TABLES_ELEM] = {0};
+
+ pub = GetPublication(table_info->pubid);
+
+ values[0] = ObjectIdGetDatum(pub->oid);
+ values[1] = ObjectIdGetDatum(relid);
+
+ /*
+ * We don't consider row filters or column lists for FOR ALL TABLES or FOR
+ * TABLES IN SCHEMA publications.
+ */
+ if (!pub->alltables &&
+ !SearchSysCacheExists2(PUBLICATIONNAMESPACEMAP,
+ ObjectIdGetDatum(schemaid),
+ ObjectIdGetDatum(pub->oid)))
+ pubtuple = SearchSysCacheCopy2(PUBLICATIONRELMAP,
+ ObjectIdGetDatum(relid),
+ ObjectIdGetDatum(pub->oid));
+
+ if (HeapTupleIsValid(pubtuple))
+ {
+ /* Lookup the column list attribute. */
+ values[2] = SysCacheGetAttr(PUBLICATIONRELMAP, pubtuple,
+ Anum_pg_publication_rel_prattrs,
+ &(nulls[2]));
+
+ /* Null indicates no filter. */
+ values[3] = SysCacheGetAttr(PUBLICATIONRELMAP, pubtuple,
+ Anum_pg_publication_rel_prqual,
+ &(nulls[3]));
+ }
+ else
+ {
+ nulls[2] = true;
+ nulls[3] = true;
+ }
+
+ /* Show all columns when the column list is not specified. */
+ if (nulls[2])
+ {
+ Relation rel = table_open(relid, AccessShareLock);
+ int nattnums = 0;
+ int16 *attnums;
+ TupleDesc desc = RelationGetDescr(rel);
+
+ attnums = palloc_array(int16, desc->natts);
+
+ for (int i = 0; i < desc->natts; i++)
+ {
+ Form_pg_attribute att = TupleDescAttr(desc, i);
+
+ if (att->attisdropped)
+ continue;
+
+ if (att->attgenerated)
+ {
+ /* We only support replication of STORED generated cols. */
+ if (att->attgenerated != ATTRIBUTE_GENERATED_STORED)
+ continue;
+
+ /*
+ * User hasn't requested to replicate STORED generated cols.
+ */
+ if (pub->pubgencols_type != PUBLISH_GENCOLS_STORED)
+ continue;
+ }
+
+ attnums[nattnums++] = att->attnum;
+ }
+
+ if (nattnums > 0)
+ {
+ values[2] = PointerGetDatum(buildint2vector(attnums, nattnums));
+ nulls[2] = false;
+ }
+
+ table_close(rel, AccessShareLock);
+ }
+
+ return heap_form_tuple(tuple_desc, values, nulls);
+}
+
/*
* Get information of the tables in the given publication array.
*
@@ -1215,14 +1449,12 @@ GetPublicationByName(const char *pubname, bool missing_ok)
Datum
pg_get_publication_tables(PG_FUNCTION_ARGS)
{
-#define NUM_PUBLICATION_TABLES_ELEM 4
FuncCallContext *funcctx;
List *table_infos = NIL;
/* stuff done only on the first call of the function */
if (SRF_IS_FIRSTCALL())
{
- TupleDesc tupdesc;
MemoryContext oldcontext;
ArrayType *arr;
Datum *elems;
@@ -1311,18 +1543,7 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
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",
- OIDOID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) 2, "relid",
- OIDOID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) 3, "attrs",
- INT2VECTOROID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) 4, "qual",
- PG_NODE_TREEOID, -1, 0);
-
- funcctx->tuple_desc = BlessTupleDesc(tupdesc);
+ funcctx->tuple_desc = create_published_rel_tuple_desc();
funcctx->user_fctx = table_infos;
MemoryContextSwitchTo(oldcontext);
@@ -1334,99 +1555,74 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
if (funcctx->call_cntr < list_length(table_infos))
{
- HeapTuple pubtuple = NULL;
HeapTuple rettuple;
- Publication *pub;
published_rel *table_info = (published_rel *) list_nth(table_infos, funcctx->call_cntr);
- Oid relid = table_info->relid;
- Oid schemaid = get_rel_namespace(relid);
- Datum values[NUM_PUBLICATION_TABLES_ELEM] = {0};
- bool nulls[NUM_PUBLICATION_TABLES_ELEM] = {0};
-
- /*
- * Form tuple with appropriate data.
- */
- pub = GetPublication(table_info->pubid);
+ rettuple = construct_published_rel_tuple(table_info, funcctx->tuple_desc);
- values[0] = ObjectIdGetDatum(pub->oid);
- values[1] = ObjectIdGetDatum(relid);
+ SRF_RETURN_NEXT(funcctx, HeapTupleGetDatum(rettuple));
+ }
- /*
- * We don't consider row filters or column lists for FOR ALL TABLES or
- * FOR TABLES IN SCHEMA publications.
- */
- if (!pub->alltables &&
- !SearchSysCacheExists2(PUBLICATIONNAMESPACEMAP,
- ObjectIdGetDatum(schemaid),
- ObjectIdGetDatum(pub->oid)))
- pubtuple = SearchSysCacheCopy2(PUBLICATIONRELMAP,
- ObjectIdGetDatum(relid),
- ObjectIdGetDatum(pub->oid));
-
- if (HeapTupleIsValid(pubtuple))
- {
- /* Lookup the column list attribute. */
- values[2] = SysCacheGetAttr(PUBLICATIONRELMAP, pubtuple,
- Anum_pg_publication_rel_prattrs,
- &(nulls[2]));
-
- /* Null indicates no filter. */
- values[3] = SysCacheGetAttr(PUBLICATIONRELMAP, pubtuple,
- Anum_pg_publication_rel_prqual,
- &(nulls[3]));
- }
- else
- {
- nulls[2] = true;
- nulls[3] = true;
- }
+ SRF_RETURN_DONE(funcctx);
+}
- /* Show all columns when the column list is not specified. */
- if (nulls[2])
- {
- Relation rel = table_open(relid, AccessShareLock);
- int nattnums = 0;
- int16 *attnums;
- TupleDesc desc = RelationGetDescr(rel);
- int i;
+/*
+ * Similar to pg_get_publication_tables(), but retrieves publication
+ * information only for the specified table.
+ */
+Datum
+pg_get_publication_table_info(PG_FUNCTION_ARGS)
+{
+ FuncCallContext *funcctx;
+ published_rel *table_info = NULL;
- attnums = palloc_array(int16, desc->natts);
+ if (SRF_IS_FIRSTCALL())
+ {
+ MemoryContext oldcontext;
+ Oid relid;
+ Name pubname;
+ Relation rel;
+ Publication *pub;
+ published_rel *pubrel = NULL;
- for (i = 0; i < desc->natts; i++)
- {
- Form_pg_attribute att = TupleDescAttr(desc, i);
+ /* create a function context for cross-call persistence */
+ funcctx = SRF_FIRSTCALL_INIT();
- if (att->attisdropped)
- continue;
+ /* switch to memory context appropriate for multiple function calls */
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
- if (att->attgenerated)
- {
- /* We only support replication of STORED generated cols. */
- if (att->attgenerated != ATTRIBUTE_GENERATED_STORED)
- continue;
-
- /*
- * User hasn't requested to replicate STORED generated
- * cols.
- */
- if (pub->pubgencols_type != PUBLISH_GENCOLS_STORED)
- continue;
- }
-
- attnums[nattnums++] = att->attnum;
- }
+ relid = PG_GETARG_OID(0);
+ pubname = PG_GETARG_NAME(1);
- if (nattnums > 0)
- {
- values[2] = PointerGetDatum(buildint2vector(attnums, nattnums));
- nulls[2] = false;
- }
+ rel = table_open(relid, AccessShareLock);
+ pub = GetPublicationByName(NameStr(*pubname), false);
- table_close(rel, AccessShareLock);
+ if (is_table_publishable_in_publication(relid, pub))
+ {
+ pubrel = palloc_object(published_rel);
+ pubrel->relid = relid;
+ pubrel->pubid = pub->oid;
}
- rettuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
+ table_close(rel, AccessShareLock);
+
+ /* Construct a tuple descriptor for the result rows. */
+ funcctx->tuple_desc = create_published_rel_tuple_desc();
+ funcctx->user_fctx = pubrel;
+
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ /* stuff done on every call of the function */
+ funcctx = SRF_PERCALL_SETUP();
+ table_info = (published_rel *) funcctx->user_fctx;
+
+ /* The function returns zero or one tuple */
+ if (table_info && funcctx->call_cntr == 0)
+ {
+ HeapTuple rettuple;
+
+ rettuple = construct_published_rel_tuple(table_info, funcctx->tuple_desc);
SRF_RETURN_NEXT(funcctx, HeapTupleGetDatum(rettuple));
}
diff --git a/src/backend/replication/logical/tablesync.c b/src/backend/replication/logical/tablesync.c
index f49a4852ecb..ce7afd68533 100644
--- a/src/backend/replication/logical/tablesync.c
+++ b/src/backend/replication/logical/tablesync.c
@@ -798,17 +798,34 @@ 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)
+ {
+ /* pg_get_publication_table_info() is available since vesion 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_table_info(%u, p.pubname) 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 +999,27 @@ 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)
+ {
+ /* pg_get_publication_table_info() is available since version 19 */
+ appendStringInfo(&cmd,
+ "SELECT DISTINCT pg_get_expr(gpt.qual, gpt.relid)"
+ " FROM pg_publication p,"
+ " LATERAL pg_get_publication_table_info(%u, p.pubname) 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 361e2cfffeb..b357a67ba7d 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12451,6 +12451,15 @@
proargmodes => '{v,o,o,o,o}',
proargnames => '{pubname,pubid,relid,attrs,qual}',
prosrc => 'pg_get_publication_tables' },
+{ oid => '8060',
+ descr => 'get information of the table that is part of the specified publication',
+ proname => 'pg_get_publication_table_info', prorows => '1',
+ proretset => 't', provolatile => 's',
+ prorettype => 'record', proargtypes => 'oid name',
+ proallargtypes => '{oid,name,oid,oid,int2vector,pg_node_tree}',
+ proargmodes => '{i,i,o,o,o,o}',
+ proargnames => '{relid,pubname,pubid,relid,attrs,qual}',
+ prosrc => 'pg_get_publication_table_info' },
{ 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 681d2564ed5..e9914c147fa 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -2182,6 +2182,135 @@ 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_table_info() 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);
+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);
+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_table_info(relname::regclass::oid, pubname) 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_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[], relname);
+ERROR: type "relname" does not exist
+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 405579dad52..75f1bc2f2fc 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -1378,6 +1378,73 @@ RESET SESSION AUTHORIZATION;
DROP ROLE regress_publication_user, regress_publication_user2;
DROP ROLE regress_publication_user_dummy;
+-- Test pg_get_publication_table_info() 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);
+
+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);
+
+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_table_info(relname::regclass::oid, pubname) 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_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[], relname);
+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
