public inbox for [email protected]
help / color / mirror / Atom feedFrom: Masahiko Sawada <[email protected]>
To: Amit Kapila <[email protected]>
Cc: Jan Wieck <[email protected]>
Cc: [email protected]
Subject: Re: Initial COPY of Logical Replication is too slow
Date: Tue, 24 Mar 2026 22:06:54 -0700
Message-ID: <CAD21AoBJcxRcaWQot302diaxoDcsnezRhnZa7p8UrPh5AGNeHQ@mail.gmail.com> (raw)
In-Reply-To: <CAD21AoB4_n7+s=uM9apX1JVtvGvgM8ismAx_uMxvDmUXfQULsw@mail.gmail.com>
References: <CAB-JLwbBFNuASyEnZWP0Tck9uNkthBZqi6WoXNevUT6+mV8XmA@mail.gmail.com>
<CAD21AoA6i2ui8FMZeuU_KxX4t-fM8G==zTW2Dp6-goujttrpew@mail.gmail.com>
<CAB-JLwZpp=7c9_r0beWWJxRh2BS_2Vvth8UDv7H57DBeaqggVg@mail.gmail.com>
<CAD21AoDT3sL2COprsRumM9zEpL1Bk5VWboK4V2mRnjGua8xfeA@mail.gmail.com>
<CAD21AoDQM62GOtaTzD_CVMSsFhv6o9c0Au1dSM1QuxeKFkWAKw@mail.gmail.com>
<CAD21AoCz7HjEr3oeb=haK31YHxHZLcvD_wx_a-+xLPKywq++3A@mail.gmail.com>
<TY4PR01MB16907733B75A99117F013AFCA947FA@TY4PR01MB16907.jpnprd01.prod.outlook.com>
<CAD21AoA9YgiY1rVKMPZwB00WU_G4UfzoawY=7hyd7hpvBPcK6w@mail.gmail.com>
<CAA4eK1KoSi60dtakJzn0MxNnHF1Yf4indSAffTjJxQG_31jsgQ@mail.gmail.com>
<CAD21AoB4B3MOxJ7-v9YLjV5fTOtaLRUhX3jN3kqhEi7D7-uY4A@mail.gmail.com>
<[email protected]>
<CAD21AoCmHpKrNg9D3mcOA973CZ5N_dBLxb8pERpSxEeRLSQxpA@mail.gmail.com>
<CAD21AoAEVyxwn_bMWHvcU-Gcz3aUVjAtMbdgfoJ8MZNiLLEh0g@mail.gmail.com>
<CAA4eK1Jkouj=w+PHzMB6v890ES3QOLf=cUTvZmGFr-WMQW2OnA@mail.gmail.com>
<CAD21AoB4_n7+s=uM9apX1JVtvGvgM8ismAx_uMxvDmUXfQULsw@mail.gmail.com>
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
Attachments:
[text/x-patch] v4-0002-POC-pass-the-list-of-publications-to-pg_get_publi.patch (21.1K, 2-v4-0002-POC-pass-the-list-of-publications-to-pg_get_publi.patch)
download | inline diff:
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
[text/x-patch] v4-0001-Avoid-full-table-scans-when-getting-publication-t.patch (26.0K, 3-v4-0001-Avoid-full-table-scans-when-getting-publication-t.patch)
download | inline diff:
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+mV8XmA@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
view thread (51+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Initial COPY of Logical Replication is too slow
In-Reply-To: <CAD21AoBJcxRcaWQot302diaxoDcsnezRhnZa7p8UrPh5AGNeHQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox