v7-0002-Optimize-schema-lookup-in-pg_get_publication_tabl.patch
application/x-patch
Filename: v7-0002-Optimize-schema-lookup-in-pg_get_publication_tabl.patch
Type: application/x-patch
Part: 1
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch v7-0002
Subject: Optimize schema lookup in pg_get_publication_tables()
| File | + | − |
|---|---|---|
| src/backend/catalog/pg_publication.c | 13 | 8 |
From 2620a9b0d8b2cd1b0d70313e9cdfb3ef8e4076e5 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
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 <bharath.rupireddyforpostgres@gmail.com>
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