Hi, I came across a 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. Please find the attached patch that fixes this 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. I've also added a TAP test for this issue. Thoughts? -- Bharath Rupireddy Amazon Web Services: https://aws.amazon.com
From 2814bae218aeca57b9553363e3c3dd2420d89de2 Mon Sep 17 00:00:00 2001 From: Bharath Rupireddy <[email protected]> Date: Wed, 22 Apr 2026 04:29:13 +0000 Subject: [PATCH v1] 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 | 27 ++++++++-- src/test/subscription/t/100_bugs.pl | 79 ++++++++++++++++++++++++++++ 2 files changed, 103 insertions(+), 3 deletions(-) diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c index a43d385c605..8c3c9ef0c5b 100644 --- a/src/backend/catalog/pg_publication.c +++ b/src/backend/catalog/pg_publication.c @@ -1546,7 +1546,7 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, funcctx = SRF_PERCALL_SETUP(); table_infos = (List *) funcctx->user_fctx; - if (funcctx->call_cntr < list_length(table_infos)) + while (funcctx->call_cntr < list_length(table_infos)) { HeapTuple pubtuple = NULL; HeapTuple rettuple; @@ -1557,6 +1557,16 @@ pg_get_publication_tables(FunctionCallInfo fcinfo, ArrayType *pubnames, 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)) + { + funcctx->call_cntr++; + continue; + } + /* * Form tuple with appropriate data. */ @@ -1599,12 +1609,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) + { + funcctx->call_cntr++; + continue; + } + + desc = RelationGetDescr(rel); + attnums = palloc_array(int16, desc->natts); 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 a23035e23fe..38fc0329cae 100644 --- a/src/test/subscription/t/100_bugs.pl +++ b/src/test/subscription/t/100_bugs.pl @@ -605,4 +605,83 @@ $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 $test_schema = 'concurrent_drop_test_schema'; +my $num_tables = 1000; + +$node_publisher->safe_psql('postgres', "CREATE DATABASE $pub_db"); + +# Setup: schema, FOR ALL TABLES publication, bulk create/drop procedures +$node_publisher->safe_psql( + $pub_db, qq{ + CREATE SCHEMA $test_schema; + CREATE PUBLICATION pub_all FOR ALL TABLES; + + CREATE OR REPLACE PROCEDURE ${test_schema}.create_tables_range( + prefix text, start_idx int, end_idx int) + LANGUAGE plpgsql AS \$\$ + BEGIN + FOR i IN start_idx..end_idx LOOP + EXECUTE format( + 'CREATE TABLE IF NOT EXISTS ${test_schema}.%I (id int, data text)', + prefix || '_' || i); + COMMIT; + END LOOP; + END; + \$\$; + + CREATE OR REPLACE PROCEDURE ${test_schema}.drop_tables_range( + prefix text, start_idx int, end_idx int) + LANGUAGE plpgsql AS \$\$ + BEGIN + FOR i IN start_idx..end_idx LOOP + EXECUTE format( + 'DROP TABLE IF EXISTS ${test_schema}.%I', + prefix || '_' || i); + COMMIT; + END LOOP; + END; + \$\$; +}); + +# Create tables +$node_publisher->safe_psql($pub_db, + "CALL ${test_schema}.create_tables_range('t', 1, $num_tables)"); + +my $pub_count = $node_publisher->safe_psql($pub_db, + "SELECT count(*) FROM pg_publication_tables WHERE schemaname = '$test_schema'"); +cmp_ok($pub_count, '>=', $num_tables, + "publication lists created tables"); + +# Launch a background session that drops all tables while we concurrently +# query pg_publication_tables from the foreground. +my $dropper = $node_publisher->background_psql($pub_db); +$dropper->query_until( + qr/drop_started/, + qq{\\echo drop_started +CALL ${test_schema}.drop_tables_range('t', 1, $num_tables); +}); + +# Query pg_publication_tables while drops are happening. Capture stderr +# so we can check for the error. +($ret, $stdout, $stderr) = $node_publisher->psql( + $pub_db, + "SELECT count(*) FROM pg_publication_tables WHERE schemaname = '$test_schema'"); + +$dropper->quit; + +# Verify that the query did not fail with error. +unlike($stderr, qr/could not open relation with OID/, + "pg_publication_tables does not error during concurrent DROP"); + +# 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(); -- 2.47.3
