v2-0001-Fix-race-condition-in-pg_get_publication_tables-w.patch

From 4e56bfbbda4f345d479650dd62a281514c523e05 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Mon, 27 Apr 2026 07:28:50 +0000
Subject: [PATCH v2] Fix race condition in pg_get_publication_tables with
 concurrent DROP TABLE.

pg_get_publication_tables collects table OIDs without locks
on the first call, then opens each table on later calls. If a
table is dropped in between, the function 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 is queried, such
as with FOR ALL TABLES publications.

Fix by skipping concurrently dropped tables instead of erroring
out.

Tables created after the list is built are simply not present in
the result set, which is expected point-in-time behavior with no
error.
---
 src/backend/catalog/pg_publication.c | 49 +++++++++++++++---
 src/test/subscription/t/100_bugs.pl  | 75 ++++++++++++++++++++++++++++
 src/tools/pgindent/typedefs.list     |  1 +
 3 files changed, 118 insertions(+), 7 deletions(-)

diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index a43d385c605..70042daa3ab 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -48,6 +48,13 @@ typedef struct
 								 * table. */
 } published_rel;
 
+/* 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;
+
 /*
  * Check if relation can be in given publication and throws appropriate
  * error if not.
@@ -1408,13 +1415,14 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames,
 {
 #define NUM_PUBLICATION_TABLES_ELEM	4
 	FuncCallContext *funcctx;
-	List	   *table_infos = NIL;
+	publication_tables_state *ptstate;
 
 	/* stuff done only on the first call of the function */
 	if (SRF_IS_FIRSTCALL())
 	{
 		TupleDesc	tupdesc;
 		MemoryContext oldcontext;
+		List	   *table_infos = NIL;
 		Datum	   *elems;
 		int			nelems,
 					i;
@@ -1537,26 +1545,41 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames,
 
 		TupleDescFinalize(tupdesc);
 		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};
 
+		/*
+		 * The relation may have been dropped concurrently since we built the
+		 * table_infos list (without holding locks). If so, silently skip it.
+		 */
+		if (!OidIsValid(schemaid))
+		{
+			ptstate->curr_idx++;
+			continue;
+		}
+
 		/*
 		 * Form tuple with appropriate data.
 		 */
@@ -1599,12 +1622,23 @@ 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);
+			TupleDesc	desc;
 			int			i;
 
+			/*
+			 * If the relation has been concurrently dropped, skip this entry.
+			 */
+			if (rel == NULL)
+			{
+				ptstate->curr_idx++;
+				continue;
+			}
+
+			desc = RelationGetDescr(rel);
+
 			attnums = palloc_array(int16, desc->natts);
 
 			for (i = 0; i < desc->natts; i++)
@@ -1642,6 +1676,7 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames,
 
 		rettuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
 
+		ptstate->curr_idx++;
 		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 a23035e23fe..46414cd2c62 100644
--- a/src/test/subscription/t/100_bugs.pl
+++ b/src/test/subscription/t/100_bugs.pl
@@ -605,4 +605,79 @@ $node_publisher->safe_psql('postgres', "DROP DATABASE regress_db");
 
 $node_publisher->stop('fast');
 
+# pg_publication_tables race with concurrent DROP TABLE
+
+$node_publisher->start();
+
+my $pub_db = 'concurrent_drop_test_db';
+my $num_tables = 100;
+
+$node_publisher->safe_psql('postgres', "CREATE DATABASE $pub_db");
+
+# Setup: FOR ALL TABLES publication, bulk create/drop helper procedure
+$node_publisher->safe_psql(
+	$pub_db, qq{
+	CREATE PUBLICATION pub_all FOR ALL TABLES;
+
+	CREATE OR REPLACE PROCEDURE create_or_drop_tables(
+		do_create boolean, num_tables int)
+	LANGUAGE plpgsql AS \$\$
+	BEGIN
+		FOR i IN 1..num_tables LOOP
+			IF do_create THEN
+				EXECUTE format(
+					'CREATE TABLE IF NOT EXISTS t_%s (id int, data text)', i);
+			ELSE
+				EXECUTE format('DROP TABLE IF EXISTS t_%s', i);
+			END IF;
+			COMMIT;
+		END LOOP;
+	END;
+	\$\$;
+});
+
+# Create tables
+$node_publisher->safe_psql($pub_db,
+	"CALL create_or_drop_tables(true, $num_tables)");
+
+my $pub_count = $node_publisher->safe_psql($pub_db,
+	"SELECT count(*) FROM pg_publication_tables WHERE schemaname = 'public'");
+cmp_ok($pub_count, '>=', $num_tables,
+	"publication lists created tables");
+
+# Continuously query pg_publication_tables in a background session until all
+# tables are dropped, then drop tables from the foreground session.
+my $bgpsql = $node_publisher->background_psql($pub_db, on_error_stop => 0);
+$bgpsql->query_until(
+	qr/polling_started/,
+	qq{\\echo polling_started
+DO \$\$
+DECLARE
+	cnt int;
+BEGIN
+	LOOP
+		SELECT count(*) INTO cnt FROM pg_publication_tables
+		WHERE schemaname = 'public';
+		EXIT WHEN cnt = 0;
+	END LOOP;
+END;
+\$\$;
+});
+
+$node_publisher->safe_psql($pub_db,
+	"CALL create_or_drop_tables(false, $num_tables)");
+
+# Verify that the background session completed without error.
+my (undef, $bg_err) = $bgpsql->query("SELECT 1");
+$bgpsql->quit;
+
+is($bg_err, 0,
+	"pg_publication_tables successfully handles concurrently dropped tables");
+
+# Cleanup
+$node_publisher->safe_psql($pub_db, "DROP PUBLICATION pub_all");
+$node_publisher->safe_psql('postgres', "DROP DATABASE $pub_db");
+
+$node_publisher->stop('fast');
+
 done_testing();
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 9f1dd55213d..f7ebf0339e8 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -4175,6 +4175,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