v8-0001-PG18-Fix-pg_get_publication_tables-race-with-conc.patch

From 782ca004b00b3896574cb9bce7f2f718c98a585b Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Sat, 27 Jun 2026 00:45:00 +0000
Subject: [PATCH v8] PG18 Fix pg_get_publication_tables race with concurrent
 DROP TABLE

The pg_get_publication_tables() SRF collects table OIDs in its
first call, and then opens the tables using table_open() for
FOR ALL TABLES publications. If a table is dropped in between,
the table_open() errors out with
"could not open relation with OID". This is common in environments
where many tables are being created and dropped while publication
tables are being queried.

The bug was introduced by b7ae03953690 in PG16.

This commit fixes it by using try_table_open(), which returns NULL
instead of erroring out if the relation no longer exists. We now
open all tables this way regardless of whether a column list is
specified, so a concurrently dropped table is consistently
skipped. Such tables are simply absent from the result set, which
is the expected point-in-time behavior.

To skip dropped tables without emitting a row, we track the
current index into the table list ourselves rather than relying
on funcctx->call_cntr.

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
Reviewed-by: Masahiko Sawada sawada.mshk@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 | 52 +++++++++++++++++++++++-----
 src/test/subscription/t/100_bugs.pl  | 46 ++++++++++++++++++++++++
 src/tools/pgindent/typedefs.list     |  1 +
 3 files changed, 90 insertions(+), 9 deletions(-)

diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index d6f94db5d99..259ecab1688 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -1117,14 +1117,27 @@ Datum
 pg_get_publication_tables(PG_FUNCTION_ARGS)
 {
 #define NUM_PUBLICATION_TABLES_ELEM	4
+
+	/*
+	 * State carried across SRF calls. We track the index ourselves instead of
+	 * using funcctx->call_cntr, so that concurrently dropped tables can be
+	 * skipped without emitting a row.
+	 */
+	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 = NULL;
 
 	/* stuff done only on the first call of the function */
 	if (SRF_IS_FIRSTCALL())
 	{
 		TupleDesc	tupdesc;
 		MemoryContext oldcontext;
+		List	   *table_infos = NIL;
 		ArrayType  *arr;
 		Datum	   *elems;
 		int			nelems,
@@ -1222,26 +1235,47 @@ 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);
+		Relation	rel;
+		Oid			schemaid;
 		Datum		values[NUM_PUBLICATION_TABLES_ELEM] = {0};
 		bool		nulls[NUM_PUBLICATION_TABLES_ELEM] = {0};
 
+		/* Advance the index for the next call. */
+		ptstate->curr_idx++;
+
+		/*
+		 * The table OIDs were collected earlier, so a table may have been
+		 * dropped before we get here. try_table_open() returns NULL if it is
+		 * already gone, in which case we skip it; such tables are simply
+		 * absent from the result set, which is the expected point-in-time
+		 * behavior.
+		 */
+		rel = try_table_open(relid, AccessShareLock);
+		if (rel == NULL)
+			continue;
+
 		/*
 		 * Form tuple with appropriate data.
 		 */
@@ -1255,6 +1289,7 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
 		 * We don't consider row filters or column lists for FOR ALL TABLES or
 		 * FOR TABLES IN SCHEMA publications.
 		 */
+		schemaid = RelationGetNamespace(rel);
 		if (!pub->alltables &&
 			!SearchSysCacheExists2(PUBLICATIONNAMESPACEMAP,
 								   ObjectIdGetDatum(schemaid),
@@ -1284,7 +1319,6 @@ 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);
 			int			nattnums = 0;
 			int16	   *attnums;
 			TupleDesc	desc = RelationGetDescr(rel);
@@ -1321,10 +1355,10 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
 				values[2] = PointerGetDatum(buildint2vector(attnums, nattnums));
 				nulls[2] = false;
 			}
-
-			table_close(rel, AccessShareLock);
 		}
 
+		table_close(rel, AccessShareLock);
+
 		rettuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
 
 		SRF_RETURN_NEXT(funcctx, HeapTupleGetDatum(rettuple));
diff --git a/src/test/subscription/t/100_bugs.pl b/src/test/subscription/t/100_bugs.pl
index df6b2e1296d..2a9a4792dfd 100644
--- a/src/test/subscription/t/100_bugs.pl
+++ b/src/test/subscription/t/100_bugs.pl
@@ -650,4 +650,50 @@ $node_publisher->safe_psql('postgres', "DROP TABLE tab_upsert");
 
 $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