[Andrei Lepikhov <lepihov@gmail.com>]

On 22/8/2024 03:49, Michael Paquier wrote:
> On Mon, Aug 19, 2024 at 03:21:30PM +0900, Michael Paquier wrote:
>> I won't hide that I've wanted that in the past..
> 
> And I have bumped into a case where this has been helpful today, so
> applied.  Thanks!
It had been my dream, too, for years. But the reason was the too-costly 
call of the get_extension_oid routine (no less than pgbench 2-3% of 
overhead when checked it in the planner hook).
It seems that the get_extension_oid routine was not modified when the 
sys cache was introduced. What is the reason? It may be that this 
routine is redundant now, but if not, and we want to hold the API that 
extensions use, maybe we should rewrite it, too.
See the attachment proposing changes.

-- 
regards, Andrei Lepikhov
From c27241e824de3bf82b1ac7ef263fec13fe8f0ed6 Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <lepihov@gmail.com>
Date: Thu, 5 Sep 2024 15:03:10 +0200
Subject: [PATCH] Use EXTENSIONNAME syscache to find extension oid.

---
 src/backend/commands/extension.c | 28 +++-------------------------
 1 file changed, 3 insertions(+), 25 deletions(-)

diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index 1643c8c69a..e8fbe5aff0 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -64,6 +64,7 @@
 #include "utils/memutils.h"
 #include "utils/rel.h"
 #include "utils/snapmgr.h"
+#include "utils/syscache.h"
 #include "utils/varlena.h"
 
 
@@ -145,32 +146,9 @@ Oid
 get_extension_oid(const char *extname, bool missing_ok)
 {
 	Oid			result;
-	Relation	rel;
-	SysScanDesc scandesc;
-	HeapTuple	tuple;
-	ScanKeyData entry[1];
-
-	rel = table_open(ExtensionRelationId, AccessShareLock);
-
-	ScanKeyInit(&entry[0],
-				Anum_pg_extension_extname,
-				BTEqualStrategyNumber, F_NAMEEQ,
-				CStringGetDatum(extname));
-
-	scandesc = systable_beginscan(rel, ExtensionNameIndexId, true,
-								  NULL, 1, entry);
-
-	tuple = systable_getnext(scandesc);
 
-	/* We assume that there can be at most one matching tuple */
-	if (HeapTupleIsValid(tuple))
-		result = ((Form_pg_extension) GETSTRUCT(tuple))->oid;
-	else
-		result = InvalidOid;
-
-	systable_endscan(scandesc);
-
-	table_close(rel, AccessShareLock);
+	result = GetSysCacheOid1(EXTENSIONNAME, Anum_pg_extension_oid,
+							CStringGetDatum(extname));
 
 	if (!OidIsValid(result) && !missing_ok)
 		ereport(ERROR,
-- 
2.46.0



Attachments:

  [text/plain] 0001-Use-EXTENSIONNAME-syscache-to-find-extension-oid.patch (1.6K, ../529295b2-6ba9-4dae-acd1-20a9c6fb8f9a@gmail.com/2-0001-Use-EXTENSIONNAME-syscache-to-find-extension-oid.patch)
  download | inline diff:
From c27241e824de3bf82b1ac7ef263fec13fe8f0ed6 Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <lepihov@gmail.com>
Date: Thu, 5 Sep 2024 15:03:10 +0200
Subject: [PATCH] Use EXTENSIONNAME syscache to find extension oid.

---
 src/backend/commands/extension.c | 28 +++-------------------------
 1 file changed, 3 insertions(+), 25 deletions(-)

diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index 1643c8c69a..e8fbe5aff0 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -64,6 +64,7 @@
 #include "utils/memutils.h"
 #include "utils/rel.h"
 #include "utils/snapmgr.h"
+#include "utils/syscache.h"
 #include "utils/varlena.h"
 
 
@@ -145,32 +146,9 @@ Oid
 get_extension_oid(const char *extname, bool missing_ok)
 {
 	Oid			result;
-	Relation	rel;
-	SysScanDesc scandesc;
-	HeapTuple	tuple;
-	ScanKeyData entry[1];
-
-	rel = table_open(ExtensionRelationId, AccessShareLock);
-
-	ScanKeyInit(&entry[0],
-				Anum_pg_extension_extname,
-				BTEqualStrategyNumber, F_NAMEEQ,
-				CStringGetDatum(extname));
-
-	scandesc = systable_beginscan(rel, ExtensionNameIndexId, true,
-								  NULL, 1, entry);
-
-	tuple = systable_getnext(scandesc);
 
-	/* We assume that there can be at most one matching tuple */
-	if (HeapTupleIsValid(tuple))
-		result = ((Form_pg_extension) GETSTRUCT(tuple))->oid;
-	else
-		result = InvalidOid;
-
-	systable_endscan(scandesc);
-
-	table_close(rel, AccessShareLock);
+	result = GetSysCacheOid1(EXTENSIONNAME, Anum_pg_extension_oid,
+							CStringGetDatum(extname));
 
 	if (!OidIsValid(result) && !missing_ok)
 		ereport(ERROR,
-- 
2.46.0