v7-0001-PG16-Fix-pg_get_publication_tables-race-with-conc.patch

From a5d78aab90a81b23b5ca4b2b3f1ad207ef5a96d8 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
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 <bharath.rupireddyforpostgres@gmail.com>
Reviewed-by: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
Reviewed-by: shveta malik <shveta.malik@gmail.com>
Reviewed-by: Ajin Cherian <itsajin@gmail.com>
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