Hi,
On Tue, May 12, 2026 at 2:20 PM Masahiko Sawada <[email protected]> wrote:
>
> I reviewed the patch and here are some comments:
Thank you for reviewing!
> +/* State for pg_get_publication_tables SRF */
> +typedef struct
> +{
> + List *table_infos; /* list of published_rel */
> + int curr_idx; /* current index into table_infos */
> +} publication_tables_state;
>
> I think we can define publication_table_state in
> pg_get_publication_tables() as it's used only in that function.
Done for pre-HEAD versions. For HEAD, I used the SRF approach.
> ---
> + /* Skip if the relation has been concurrently dropped. */
> + if (!OidIsValid(schemaid))
> + continue;
>
> Although this check is done for all relations in table_infos, we also
> check the return value of try_table_open(), and these two checks have
> the same comment. I think we need more comments on why these two
> checks are required.
When get_rel_namespace() returns InvalidOid for the concurrently
dropped tables, it costs additional syscache lookups plus index scans
(cache miss). So, there's no harm from the correctness perspective.
However, I would like to optimize this part of the code by 1/ gating
it with InvalidOid checkup, 2/ moving the schemaid fetch closer to
where it's being used i.e. inside if (!pub->alltables). I would like
to do this only for HEAD, so, I attached it as a separate 0002 patch.
For pre-HEAD versions, I removed this check and retained the
try_table_open() fix.
> In which case is schemaid InvalidOid and do we not call
> try_table_open() (i.e., nulls[2] is false)?
This case is NOT possible, because the drop table ensures dependent
catalog entries (pg_publication_namespace in this case) are deleted
too.
> It might make more sense to get-and-check the schemaid of each
> relation when adding the table information to table_infos.
That won't help fix the table_open() error with concurrent table drops.
> ---
> Looking at the regression tests in the patch, it tests the ALL TABLES
> publication cases and the concurrently-dropped table is handled when
> we call check the return value of try_table_open() but not
> get_rel_namespace(). I think the test case itself is fine but we can
> do the same test without adding a new injection point. For instance,
>
> backend-1: begin;
> backend-2: begin; lock table t_dropme in access exclusive mode;
> backend-1: select * from pg_publication_tables;
> backend-2: drop table t_dropme; commit;
> backend-1: get an error "could not open relation with XXX"
Nice! It works. I used this for the test-case in the new patches.
> ---
> If we use tuplestore instead of SRF, can we simplify the code as we
> would not need publication_tables_state and the above check? It would
> be only for the master, though.
I implemented this idea for HEAD and it simplifies the code a bit.
Please find the attached v7 patches.
--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com
From a3a3ff5c291043abdbb5123ca092e9c778f8b934 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <[email protected]>
Date: Mon, 15 Jun 2026 22:43:14 +0000
Subject: [PATCH v7 1/2] Fix pg_get_publication_tables race with concurrent
DROP TABLE
pg_get_publication_tables() collects table OIDs first, and then
opens all the tables, for which column lists are not specified,
using table_open(). If a table is dropped in between, the
table_open() errors with
"could not open relation with OID". This is common in environments
where many tables are being created and dropped while
pg_publication_tables view is queried, such as with
FOR ALL TABLES publications.
The bug was introduced by b7ae03953690 in PG16.
This commit fixes it by using try_table_open() which returns NULL
insetad of erroring out if the relation does not exist. Tables
created after the list is built are simply not present
in the result set, which is expected point-in-time behavior.
On HEAD, use a tuplestore-based SRF instead of the traditional
SRF per-call mechanism to simplify the code and avoid introducing
additional structures.
On pre-HEAD versions, define a new struct to carry the current
index into the tables list across multiple invocations, to help
correctly skip tables dropped concurrently. This is to keep
code simple in the backpatch branches.
Author: Bharath Rupireddy <[email protected]>
Reviewed-by: Bertrand Drouvot <[email protected]>
Reviewed-by: shveta malik <[email protected]>
Reviewed-by: Ajin Cherian <[email protected]>
Discussion: https://www.postgresql.org/message-id/CALj2ACVYYooWH-5tJ6cPKkU%2BmutVxwb_z4S%2BqAi-zdrFqxXE2Q%40mail.gmail.com
Backpatch-through: 16
---
src/backend/catalog/pg_publication.c | 234 ++++++++++++---------------
src/test/subscription/t/100_bugs.pl | 46 ++++++
2 files changed, 147 insertions(+), 133 deletions(-)
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index 5c457d9aca8..164975cd0cb 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -37,6 +37,7 @@
#include "utils/catcache.h"
#include "utils/fmgroids.h"
#include "utils/lsyscache.h"
+#include "utils/tuplestore.h"
#include "utils/rel.h"
#include "utils/syscache.h"
@@ -1414,160 +1415,123 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames,
bool pub_missing_ok)
{
#define NUM_PUBLICATION_TABLES_ELEM 4
- FuncCallContext *funcctx;
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
List *table_infos = NIL;
+ Datum *elems;
+ int nelems,
+ i;
+ bool viaroot = false;
+ ListCell *lc;
- /* stuff done only on the first call of the function */
- if (SRF_IS_FIRSTCALL())
- {
- TupleDesc tupdesc;
- MemoryContext oldcontext;
- Datum *elems;
- int nelems,
- i;
- bool viaroot = false;
-
- /* create a function context for cross-call persistence */
- funcctx = SRF_FIRSTCALL_INIT();
-
- /*
- * Preliminary check if the specified table can be published in the
- * first place. If not, we can return early without checking the given
- * publications and the table.
- */
- if (filter_by_relid && !is_publishable_table(target_relid))
- SRF_RETURN_DONE(funcctx);
+ InitMaterializedSRF(fcinfo, 0);
- /* switch to memory context appropriate for multiple function calls */
- oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+ /*
+ * Preliminary check if the specified table can be published in the first
+ * place. If not, we can return early without checking the given
+ * publications and the table.
+ */
+ if (filter_by_relid && !is_publishable_table(target_relid))
+ return (Datum) 0;
- /*
- * Deconstruct the parameter into elements where each element is a
- * publication name.
- */
- deconstruct_array_builtin(pubnames, TEXTOID, &elems, NULL, &nelems);
+ /*
+ * Deconstruct the parameter into elements where each element is a
+ * publication name.
+ */
+ deconstruct_array_builtin(pubnames, TEXTOID, &elems, NULL, &nelems);
- /* Get Oids of tables from each publication. */
- for (i = 0; i < nelems; i++)
- {
- Publication *pub_elem;
- List *pub_elem_tables = NIL;
- ListCell *lc;
+ /* Get Oids of tables from each publication. */
+ for (i = 0; i < nelems; i++)
+ {
+ Publication *pub_elem;
+ List *pub_elem_tables = NIL;
+ ListCell *lc2;
- pub_elem = GetPublicationByName(TextDatumGetCString(elems[i]),
- pub_missing_ok);
+ pub_elem = GetPublicationByName(TextDatumGetCString(elems[i]),
+ pub_missing_ok);
- if (pub_elem == NULL)
- continue;
+ if (pub_elem == NULL)
+ continue;
- if (filter_by_relid)
- {
- /* Check if the given table is published for the publication */
- if (is_table_publishable_in_publication(target_relid, pub_elem))
- {
- pub_elem_tables = list_make1_oid(target_relid);
- }
- }
- else
+ if (filter_by_relid)
+ {
+ /* Check if the given table is published for the publication */
+ if (is_table_publishable_in_publication(target_relid, pub_elem))
{
- /*
- * 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);
- }
+ pub_elem_tables = list_make1_oid(target_relid);
}
-
+ }
+ else
+ {
/*
- * 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.
+ * 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.
*/
- foreach(lc, pub_elem_tables)
+ if (pub_elem->alltables)
+ pub_elem_tables = GetAllPublicationRelations(pub_elem->oid,
+ RELKIND_RELATION,
+ pub_elem->pubviaroot);
+ else
{
- 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);
+ 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);
}
-
- /* 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.
+ * 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.
*/
- 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);
-
- TupleDescFinalize(tupdesc);
- funcctx->tuple_desc = BlessTupleDesc(tupdesc);
- funcctx->user_fctx = table_infos;
+ foreach(lc2, pub_elem_tables)
+ {
+ published_rel *table_info = palloc_object(published_rel);
- MemoryContextSwitchTo(oldcontext);
+ table_info->relid = lfirst_oid(lc2);
+ 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;
}
- /* stuff done on every call of the function */
- funcctx = SRF_PERCALL_SETUP();
- table_infos = (List *) funcctx->user_fctx;
+ /*
+ * 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 (funcctx->call_cntr < list_length(table_infos))
+ /* Produce a result row for each published table. */
+ foreach(lc, table_infos)
{
- HeapTuple pubtuple = NULL;
- HeapTuple rettuple;
- Publication *pub;
- published_rel *table_info = (published_rel *) list_nth(table_infos, funcctx->call_cntr);
+ published_rel *table_info = (published_rel *) lfirst(lc);
Oid relid = table_info->relid;
+ Publication *pub;
+ HeapTuple pubtuple = NULL;
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);
values[0] = ObjectIdGetDatum(pub->oid);
@@ -1606,11 +1570,16 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames,
/* Show all columns when the column list is not specified. */
if (nulls[2])
{
- Relation rel = table_open(relid, AccessShareLock);
+ Relation rel = try_table_open(relid, AccessShareLock);
int nattnums = 0;
int16 *attnums;
- TupleDesc desc = RelationGetDescr(rel);
- int i;
+ TupleDesc desc;
+
+ /* Skip if the relation has been concurrently dropped. */
+ if (rel == NULL)
+ continue;
+
+ desc = RelationGetDescr(rel);
attnums = palloc_array(int16, desc->natts);
@@ -1647,12 +1616,11 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames,
table_close(rel, AccessShareLock);
}
- rettuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
-
- SRF_RETURN_NEXT(funcctx, HeapTupleGetDatum(rettuple));
+ tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc,
+ values, nulls);
}
- SRF_RETURN_DONE(funcctx);
+ return (Datum) 0;
}
Datum
diff --git a/src/test/subscription/t/100_bugs.pl b/src/test/subscription/t/100_bugs.pl
index a23035e23fe..5ff4098434d 100644
--- a/src/test/subscription/t/100_bugs.pl
+++ b/src/test/subscription/t/100_bugs.pl
@@ -605,4 +605,50 @@ $node_publisher->safe_psql('postgres', "DROP DATABASE regress_db");
$node_publisher->stop('fast');
+# BUG: pg_get_publication_tables() errors with "could not open relation with
+# OID" when a table is dropped concurrently.
+$node_publisher->start();
+
+$node_publisher->safe_psql(
+ 'postgres', qq{
+ CREATE PUBLICATION pub_all FOR ALL TABLES;
+ CREATE TABLE t_dropme (id int, data text);
+});
+
+# Hold an ACCESS EXCLUSIVE lock on the table in a separate session, so that
+# the pg_publication_tables query will block when it tries to open the table.
+my $holder = $node_publisher->background_psql('postgres');
+$holder->query_safe("BEGIN; LOCK TABLE t_dropme IN ACCESS EXCLUSIVE MODE;");
+
+# Background session queries pg_publication_tables; it will block waiting for
+# the lock on the table.
+my $bgpsql =
+ $node_publisher->background_psql('postgres', on_error_stop => 0);
+$bgpsql->query_until(
+ qr/querying_publication_tables/,
+ qq{\\echo querying_publication_tables
+SELECT count(*) FROM pg_publication_tables WHERE pubname = 'pub_all';
+});
+
+# Wait until the querying session is blocked on the lock.
+$node_publisher->poll_query_until('postgres',
+ "SELECT count(*) > 0 FROM pg_stat_activity WHERE wait_event_type = 'Lock' AND query LIKE '%pg_publication_tables%';"
+);
+
+# Drop the table in the lock-holding session and commit, releasing the lock.
+$holder->query_safe("DROP TABLE t_dropme; COMMIT;");
+$holder->quit;
+
+# Verify the background session completed without error.
+my $bg_result = $bgpsql->query_safe("SELECT 1");
+$bgpsql->quit;
+
+ok(defined($bg_result),
+ "pg_publication_tables handles concurrently dropped tables");
+
+# Cleanup.
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION pub_all");
+
+$node_publisher->stop('fast');
+
done_testing();
--
2.47.3
From 2620a9b0d8b2cd1b0d70313e9cdfb3ef8e4076e5 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <[email protected]>
Date: Mon, 15 Jun 2026 22:45:54 +0000
Subject: [PATCH v7 2/2] Optimize schema lookup in pg_get_publication_tables()
When get_rel_namespace() returns InvalidOid for concurrently
dropped tables, it incurs additional syscache lookups plus index
scans (cache miss). While this is not a correctness issue, it is
unnecessary work.
Optimize this by gating the schema lookup with an InvalidOid
check to skip dropped relations early, and by moving the
schema oid fetch closer to where it is actually used, i.e.,
inside the if (!pub->alltables) block.
Author: Bharath Rupireddy <[email protected]>
Discussion: https://www.postgresql.org/message-id/CALj2ACVYYooWH-5tJ6cPKkU%2BmutVxwb_z4S%2BqAi-zdrFqxXE2Q%40mail.gmail.com
---
src/backend/catalog/pg_publication.c | 21 +++++++++++++--------
1 file changed, 13 insertions(+), 8 deletions(-)
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index 164975cd0cb..52771586010 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -1528,7 +1528,6 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames,
Oid relid = table_info->relid;
Publication *pub;
HeapTuple pubtuple = NULL;
- Oid schemaid = get_rel_namespace(relid);
Datum values[NUM_PUBLICATION_TABLES_ELEM] = {0};
bool nulls[NUM_PUBLICATION_TABLES_ELEM] = {0};
@@ -1541,13 +1540,19 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames,
* 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 (!pub->alltables)
+ {
+ Oid schemaid = get_rel_namespace(relid);
+
+ if (OidIsValid(schemaid) &&
+ !SearchSysCacheExists2(PUBLICATIONNAMESPACEMAP,
+ ObjectIdGetDatum(schemaid),
+ ObjectIdGetDatum(pub->oid)))
+ pubtuple = SearchSysCacheCopy2(PUBLICATIONRELMAP,
+ ObjectIdGetDatum(relid),
+ ObjectIdGetDatum(pub->oid));
+ }
+
if (HeapTupleIsValid(pubtuple))
{
--
2.47.3
From d9a5f540ce4d7c90bfe8fe9259293981f46fc6d7 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <[email protected]>
Date: Sun, 3 May 2026 20:18:19 +0000
Subject: [PATCH v7] PG18 - Fix pg_get_publication_tables race with concurrent
DROP TABLE
pg_get_publication_tables() collects table OIDs first, and then
opens all the tables, for which column lists are not specified,
using table_open(). If a table is dropped in between, the
table_open() errors with
"could not open relation with OID". This is common in environments
where many tables are being created and dropped while
pg_publication_tables view is queried, such as with
FOR ALL TABLES publications.
The bug was introduced by b7ae03953690 in PG16.
This commit fixes it by using try_table_open() which returns NULL
insetad of erroring out if the relation does not exist. Tables
created after the list is built are simply not present
in the result set, which is expected point-in-time behavior.
On HEAD, use a tuplestore-based SRF instead of the traditional
SRF per-call mechanism to simplify the code and avoid introducing
additional structures.
On pre-HEAD versions, define a new struct to carry the current
index into the tables list across multiple invocations, to help
correctly skip tables dropped concurrently. This is to keep
code simple in the backpatch branches.
Author: Bharath Rupireddy <[email protected]>
Reviewed-by: Bertrand Drouvot <[email protected]>
Reviewed-by: shveta malik <[email protected]>
Reviewed-by: Ajin Cherian <[email protected]>
Discussion: https://www.postgresql.org/message-id/CALj2ACVYYooWH-5tJ6cPKkU%2BmutVxwb_z4S%2BqAi-zdrFqxXE2Q%40mail.gmail.com
Backpatch-through: 16
---
src/backend/catalog/pg_publication.c | 35 ++++++++++++++++-----
src/test/subscription/t/100_bugs.pl | 47 ++++++++++++++++++++++++++++
src/tools/pgindent/typedefs.list | 1 +
3 files changed, 76 insertions(+), 7 deletions(-)
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index d6f94db5d99..1211082dca1 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -1117,8 +1117,15 @@ Datum
pg_get_publication_tables(PG_FUNCTION_ARGS)
{
#define NUM_PUBLICATION_TABLES_ELEM 4
+ /* State for carrying the current index across SRF calls. */
+ typedef struct
+ {
+ List *table_infos; /* list of published_rel */
+ int curr_idx; /* current index into table_infos */
+ } publication_tables_state;
+
FuncCallContext *funcctx;
- List *table_infos = NIL;
+ publication_tables_state *ptstate;
/* stuff done only on the first call of the function */
if (SRF_IS_FIRSTCALL())
@@ -1126,6 +1133,7 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
TupleDesc tupdesc;
MemoryContext oldcontext;
ArrayType *arr;
+ List *table_infos = NIL;
Datum *elems;
int nelems,
i;
@@ -1222,26 +1230,33 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
PG_NODE_TREEOID, -1, 0);
funcctx->tuple_desc = BlessTupleDesc(tupdesc);
- funcctx->user_fctx = table_infos;
+
+ /* Store the state to be used across SRF calls. */
+ ptstate = palloc_object(publication_tables_state);
+ ptstate->table_infos = table_infos;
+ ptstate->curr_idx = 0;
+ funcctx->user_fctx = ptstate;
MemoryContextSwitchTo(oldcontext);
}
/* stuff done on every call of the function */
funcctx = SRF_PERCALL_SETUP();
- table_infos = (List *) funcctx->user_fctx;
+ ptstate = (publication_tables_state *) funcctx->user_fctx;
- if (funcctx->call_cntr < list_length(table_infos))
+ while (ptstate->curr_idx < list_length(ptstate->table_infos))
{
HeapTuple pubtuple = NULL;
HeapTuple rettuple;
Publication *pub;
- published_rel *table_info = (published_rel *) list_nth(table_infos, funcctx->call_cntr);
+ published_rel *table_info = (published_rel *) list_nth(ptstate->table_infos, ptstate->curr_idx);
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};
+ ptstate->curr_idx++;
+
/*
* Form tuple with appropriate data.
*/
@@ -1284,12 +1299,18 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
/* Show all columns when the column list is not specified. */
if (nulls[2])
{
- Relation rel = table_open(relid, AccessShareLock);
+ Relation rel = try_table_open(relid, AccessShareLock);
int nattnums = 0;
int16 *attnums;
- TupleDesc desc = RelationGetDescr(rel);
+ TupleDesc desc;
int i;
+ /* Skip if the relation has been concurrently dropped. */
+ if (rel == NULL)
+ continue;
+
+ desc = RelationGetDescr(rel);
+
attnums = (int16 *) palloc(desc->natts * sizeof(int16));
for (i = 0; i < desc->natts; i++)
diff --git a/src/test/subscription/t/100_bugs.pl b/src/test/subscription/t/100_bugs.pl
index 50223054918..31c2fe70050 100644
--- a/src/test/subscription/t/100_bugs.pl
+++ b/src/test/subscription/t/100_bugs.pl
@@ -23,6 +23,7 @@ my $node_publisher = PostgreSQL::Test::Cluster->new('publisher');
$node_publisher->init(allows_streaming => 'logical');
$node_publisher->start;
+
my $node_subscriber = PostgreSQL::Test::Cluster->new('subscriber');
$node_subscriber->init;
$node_subscriber->start;
@@ -605,4 +606,50 @@ $node_publisher->safe_psql('postgres', "DROP DATABASE regress_db");
$node_publisher->stop('fast');
+# BUG: pg_get_publication_tables() errors with "could not open relation with
+# OID" when a table is dropped concurrently.
+$node_publisher->start();
+
+$node_publisher->safe_psql(
+ 'postgres', qq{
+ CREATE PUBLICATION pub_all FOR ALL TABLES;
+ CREATE TABLE t_dropme (id int, data text);
+});
+
+# Hold an ACCESS EXCLUSIVE lock on the table in a separate session, so that
+# the pg_publication_tables query will block when it tries to open the table.
+my $holder = $node_publisher->background_psql('postgres');
+$holder->query_safe("BEGIN; LOCK TABLE t_dropme IN ACCESS EXCLUSIVE MODE;");
+
+# Background session queries pg_publication_tables; it will block waiting for
+# the lock on the table.
+my $bgpsql =
+ $node_publisher->background_psql('postgres', on_error_stop => 0);
+$bgpsql->query_until(
+ qr/querying_publication_tables/,
+ qq{\\echo querying_publication_tables
+SELECT count(*) FROM pg_publication_tables WHERE pubname = 'pub_all';
+});
+
+# Wait until the querying session is blocked on the lock.
+$node_publisher->poll_query_until('postgres',
+ "SELECT count(*) > 0 FROM pg_stat_activity WHERE wait_event_type = 'Lock' AND query LIKE '%pg_publication_tables%';"
+);
+
+# Drop the table in the lock-holding session and commit, releasing the lock.
+$holder->query_safe("DROP TABLE t_dropme; COMMIT;");
+$holder->quit;
+
+# Verify the background session completed without error.
+my $bg_result = $bgpsql->query_safe("SELECT 1");
+$bgpsql->quit;
+
+ok(defined($bg_result),
+ "pg_publication_tables handles concurrently dropped tables");
+
+# Cleanup.
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION pub_all");
+
+$node_publisher->stop('fast');
+
done_testing();
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 8cd74c4e5b6..7c5d28d19f3 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3972,6 +3972,7 @@ pthread_mutex_t
pthread_once_t
pthread_t
ptrdiff_t
+publication_tables_state
published_rel
pull_var_clause_context
pull_varattnos_context
--
2.47.3
From c52f5e28f4d5ef79c11e0c7821d0b08f6c4d7a9a Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <[email protected]>
Date: Sun, 3 May 2026 20:22:47 +0000
Subject: [PATCH v7] PG17 - Fix pg_get_publication_tables race with concurrent
DROP TABLE
pg_get_publication_tables() collects table OIDs first, and then
opens all the tables, for which column lists are not specified,
using table_open(). If a table is dropped in between, the
table_open() errors with
"could not open relation with OID". This is common in environments
where many tables are being created and dropped while
pg_publication_tables view is queried, such as with
FOR ALL TABLES publications.
The bug was introduced by b7ae03953690 in PG16.
This commit fixes it by using try_table_open() which returns NULL
insetad of erroring out if the relation does not exist. Tables
created after the list is built are simply not present
in the result set, which is expected point-in-time behavior.
On HEAD, use a tuplestore-based SRF instead of the traditional
SRF per-call mechanism to simplify the code and avoid introducing
additional structures.
On pre-HEAD versions, define a new struct to carry the current
index into the tables list across multiple invocations, to help
correctly skip tables dropped concurrently. This is to keep
code simple in the backpatch branches.
Author: Bharath Rupireddy <[email protected]>
Reviewed-by: Bertrand Drouvot <[email protected]>
Reviewed-by: shveta malik <[email protected]>
Reviewed-by: Ajin Cherian <[email protected]>
Discussion: https://www.postgresql.org/message-id/CALj2ACVYYooWH-5tJ6cPKkU%2BmutVxwb_z4S%2BqAi-zdrFqxXE2Q%40mail.gmail.com
Backpatch-through: 16
---
src/backend/catalog/pg_publication.c | 34 ++++++++++++++++----
src/test/subscription/t/100_bugs.pl | 46 ++++++++++++++++++++++++++++
src/tools/pgindent/typedefs.list | 1 +
3 files changed, 75 insertions(+), 6 deletions(-)
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index 0602398a545..55fd26370bb 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -1052,8 +1052,15 @@ Datum
pg_get_publication_tables(PG_FUNCTION_ARGS)
{
#define NUM_PUBLICATION_TABLES_ELEM 4
+ /* State for carrying the current index across SRF calls. */
+ typedef struct
+ {
+ List *table_infos; /* list of published_rel */
+ int curr_idx; /* current index into table_infos */
+ } publication_tables_state;
+
FuncCallContext *funcctx;
- List *table_infos = NIL;
+ publication_tables_state *ptstate;
/* stuff done only on the first call of the function */
if (SRF_IS_FIRSTCALL())
@@ -1061,6 +1068,7 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
TupleDesc tupdesc;
MemoryContext oldcontext;
ArrayType *arr;
+ List *table_infos = NIL;
Datum *elems;
int nelems,
i;
@@ -1160,24 +1168,32 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
funcctx->tuple_desc = BlessTupleDesc(tupdesc);
funcctx->user_fctx = (void *) table_infos;
+ /* Store the state to be used across SRF calls. */
+ ptstate = palloc_object(publication_tables_state);
+ ptstate->table_infos = table_infos;
+ ptstate->curr_idx = 0;
+ funcctx->user_fctx = ptstate;
+
MemoryContextSwitchTo(oldcontext);
}
/* stuff done on every call of the function */
funcctx = SRF_PERCALL_SETUP();
- table_infos = (List *) funcctx->user_fctx;
+ ptstate = (publication_tables_state *) funcctx->user_fctx;
- if (funcctx->call_cntr < list_length(table_infos))
+ while (ptstate->curr_idx < list_length(ptstate->table_infos))
{
HeapTuple pubtuple = NULL;
HeapTuple rettuple;
Publication *pub;
- published_rel *table_info = (published_rel *) list_nth(table_infos, funcctx->call_cntr);
+ published_rel *table_info = (published_rel *) list_nth(ptstate->table_infos, ptstate->curr_idx);
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};
+ ptstate->curr_idx++;
+
/*
* Form tuple with appropriate data.
*/
@@ -1220,12 +1236,18 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
/* Show all columns when the column list is not specified. */
if (nulls[2])
{
- Relation rel = table_open(relid, AccessShareLock);
+ Relation rel = try_table_open(relid, AccessShareLock);
int nattnums = 0;
int16 *attnums;
- TupleDesc desc = RelationGetDescr(rel);
+ TupleDesc desc;
int i;
+ /* Skip if the relation has been concurrently dropped. */
+ if (rel == NULL)
+ continue;
+
+ desc = RelationGetDescr(rel);
+
attnums = (int16 *) palloc(desc->natts * sizeof(int16));
for (i = 0; i < desc->natts; i++)
diff --git a/src/test/subscription/t/100_bugs.pl b/src/test/subscription/t/100_bugs.pl
index 17accd11d93..c7c105e324b 100644
--- a/src/test/subscription/t/100_bugs.pl
+++ b/src/test/subscription/t/100_bugs.pl
@@ -597,4 +597,50 @@ $node_publisher->safe_psql('postgres', "DROP DATABASE regress_db");
$node_publisher->stop('fast');
+# BUG: pg_get_publication_tables() errors with "could not open relation with
+# OID" when a table is dropped concurrently.
+$node_publisher->start();
+
+$node_publisher->safe_psql(
+ 'postgres', qq{
+ CREATE PUBLICATION pub_all FOR ALL TABLES;
+ CREATE TABLE t_dropme (id int, data text);
+});
+
+# Hold an ACCESS EXCLUSIVE lock on the table in a separate session, so that
+# the pg_publication_tables query will block when it tries to open the table.
+my $holder = $node_publisher->background_psql('postgres');
+$holder->query_safe("BEGIN; LOCK TABLE t_dropme IN ACCESS EXCLUSIVE MODE;");
+
+# Background session queries pg_publication_tables; it will block waiting for
+# the lock on the table.
+my $bgpsql =
+ $node_publisher->background_psql('postgres', on_error_stop => 0);
+$bgpsql->query_until(
+ qr/querying_publication_tables/,
+ qq{\\echo querying_publication_tables
+SELECT count(*) FROM pg_publication_tables WHERE pubname = 'pub_all';
+});
+
+# Wait until the querying session is blocked on the lock.
+$node_publisher->poll_query_until('postgres',
+ "SELECT count(*) > 0 FROM pg_stat_activity WHERE wait_event_type = 'Lock' AND query LIKE '%pg_publication_tables%';"
+);
+
+# Drop the table in the lock-holding session and commit, releasing the lock.
+$holder->query_safe("DROP TABLE t_dropme; COMMIT;");
+$holder->quit;
+
+# Verify the background session completed without error.
+my $bg_result = $bgpsql->query_safe("SELECT 1");
+$bgpsql->quit;
+
+ok(defined($bg_result),
+ "pg_publication_tables handles concurrently dropped tables");
+
+# Cleanup.
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION pub_all");
+
+$node_publisher->stop('fast');
+
done_testing();
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index e5634ae2969..86b194da8e3 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3805,6 +3805,7 @@ pthread_mutex_t
pthread_once_t
pthread_t
ptrdiff_t
+publication_tables_state
published_rel
pull_var_clause_context
pull_varattnos_context
--
2.47.3
From a5d78aab90a81b23b5ca4b2b3f1ad207ef5a96d8 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <[email protected]>
Date: Sun, 3 May 2026 20:24:10 +0000
Subject: [PATCH v7] PG16 - Fix pg_get_publication_tables race with concurrent
DROP TABLE
pg_get_publication_tables() collects table OIDs first, and then
opens all the tables, for which column lists are not specified,
using table_open(). If a table is dropped in between, the
table_open() errors with
"could not open relation with OID". This is common in environments
where many tables are being created and dropped while
pg_publication_tables view is queried, such as with
FOR ALL TABLES publications.
The bug was introduced by b7ae03953690 in PG16.
This commit fixes it by using try_table_open() which returns NULL
insetad of erroring out if the relation does not exist. Tables
created after the list is built are simply not present
in the result set, which is expected point-in-time behavior.
On HEAD, use a tuplestore-based SRF instead of the traditional
SRF per-call mechanism to simplify the code and avoid introducing
additional structures.
On pre-HEAD versions, define a new struct to carry the current
index into the tables list across multiple invocations, to help
correctly skip tables dropped concurrently. This is to keep
code simple in the backpatch branches.
Author: Bharath Rupireddy <[email protected]>
Reviewed-by: Bertrand Drouvot <[email protected]>
Reviewed-by: shveta malik <[email protected]>
Reviewed-by: Ajin Cherian <[email protected]>
Discussion: https://www.postgresql.org/message-id/CALj2ACVYYooWH-5tJ6cPKkU%2BmutVxwb_z4S%2BqAi-zdrFqxXE2Q%40mail.gmail.com
Backpatch-through: 16
---
src/backend/catalog/pg_publication.c | 34 ++++++++++++++++----
src/test/subscription/t/100_bugs.pl | 46 ++++++++++++++++++++++++++++
src/tools/pgindent/typedefs.list | 1 +
3 files changed, 75 insertions(+), 6 deletions(-)
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index c488b6370b6..f1d18b1a4a2 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -1057,8 +1057,15 @@ Datum
pg_get_publication_tables(PG_FUNCTION_ARGS)
{
#define NUM_PUBLICATION_TABLES_ELEM 4
+ /* State for carrying the current index across SRF calls. */
+ typedef struct
+ {
+ List *table_infos; /* list of published_rel */
+ int curr_idx; /* current index into table_infos */
+ } publication_tables_state;
+
FuncCallContext *funcctx;
- List *table_infos = NIL;
+ publication_tables_state *ptstate;
/* stuff done only on the first call of the function */
if (SRF_IS_FIRSTCALL())
@@ -1066,6 +1073,7 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
TupleDesc tupdesc;
MemoryContext oldcontext;
ArrayType *arr;
+ List *table_infos = NIL;
Datum *elems;
int nelems,
i;
@@ -1165,24 +1173,32 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
funcctx->tuple_desc = BlessTupleDesc(tupdesc);
funcctx->user_fctx = (void *) table_infos;
+ /* Store the state to be used across SRF calls. */
+ ptstate = palloc_object(publication_tables_state);
+ ptstate->table_infos = table_infos;
+ ptstate->curr_idx = 0;
+ funcctx->user_fctx = ptstate;
+
MemoryContextSwitchTo(oldcontext);
}
/* stuff done on every call of the function */
funcctx = SRF_PERCALL_SETUP();
- table_infos = (List *) funcctx->user_fctx;
+ ptstate = (publication_tables_state *) funcctx->user_fctx;
- if (funcctx->call_cntr < list_length(table_infos))
+ while (ptstate->curr_idx < list_length(ptstate->table_infos))
{
HeapTuple pubtuple = NULL;
HeapTuple rettuple;
Publication *pub;
- published_rel *table_info = (published_rel *) list_nth(table_infos, funcctx->call_cntr);
+ published_rel *table_info = (published_rel *) list_nth(ptstate->table_infos, ptstate->curr_idx);
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};
+ ptstate->curr_idx++;
+
/*
* Form tuple with appropriate data.
*/
@@ -1225,12 +1241,18 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
/* Show all columns when the column list is not specified. */
if (nulls[2])
{
- Relation rel = table_open(relid, AccessShareLock);
+ Relation rel = try_table_open(relid, AccessShareLock);
int nattnums = 0;
int16 *attnums;
- TupleDesc desc = RelationGetDescr(rel);
+ TupleDesc desc;
int i;
+ /* Skip if the relation has been concurrently dropped. */
+ if (rel == NULL)
+ continue;
+
+ desc = RelationGetDescr(rel);
+
attnums = (int16 *) palloc(desc->natts * sizeof(int16));
for (i = 0; i < desc->natts; i++)
diff --git a/src/test/subscription/t/100_bugs.pl b/src/test/subscription/t/100_bugs.pl
index 87643b8e620..1e78c11493a 100644
--- a/src/test/subscription/t/100_bugs.pl
+++ b/src/test/subscription/t/100_bugs.pl
@@ -598,4 +598,50 @@ $node_publisher->safe_psql('postgres', "DROP DATABASE regress_db");
$node_publisher->stop('fast');
+# BUG: pg_get_publication_tables() errors with "could not open relation with
+# OID" when a table is dropped concurrently.
+$node_publisher->start();
+
+$node_publisher->safe_psql(
+ 'postgres', qq{
+ CREATE PUBLICATION pub_all FOR ALL TABLES;
+ CREATE TABLE t_dropme (id int, data text);
+});
+
+# Hold an ACCESS EXCLUSIVE lock on the table in a separate session, so that
+# the pg_publication_tables query will block when it tries to open the table.
+my $holder = $node_publisher->background_psql('postgres');
+$holder->query_safe("BEGIN; LOCK TABLE t_dropme IN ACCESS EXCLUSIVE MODE;");
+
+# Background session queries pg_publication_tables; it will block waiting for
+# the lock on the table.
+my $bgpsql =
+ $node_publisher->background_psql('postgres', on_error_stop => 0);
+$bgpsql->query_until(
+ qr/querying_publication_tables/,
+ qq{\\echo querying_publication_tables
+SELECT count(*) FROM pg_publication_tables WHERE pubname = 'pub_all';
+});
+
+# Wait until the querying session is blocked on the lock.
+$node_publisher->poll_query_until('postgres',
+ "SELECT count(*) > 0 FROM pg_stat_activity WHERE wait_event_type = 'Lock' AND query LIKE '%pg_publication_tables%';"
+);
+
+# Drop the table in the lock-holding session and commit, releasing the lock.
+$holder->query_safe("DROP TABLE t_dropme; COMMIT;");
+$holder->quit;
+
+# Verify the background session completed without error.
+my $bg_result = $bgpsql->query_safe("SELECT 1");
+$bgpsql->quit;
+
+ok(defined($bg_result),
+ "pg_publication_tables handles concurrently dropped tables");
+
+# Cleanup.
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION pub_all");
+
+$node_publisher->stop('fast');
+
done_testing();
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 5ee0565a209..f21ddadc4c0 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3654,6 +3654,7 @@ pthread_mutex_t
pthread_once_t
pthread_t
ptrdiff_t
+publication_tables_state
published_rel
pull_var_clause_context
pull_varattnos_context
--
2.47.3
