Date: Tue, 21 Jan 2025 12:29:37 +0900
From: Yugo Nagata <nagata@sraoss.co.jp>
To: pgsql-hackers@postgresql.org
Subject: Add a function to get the version of installed extension
Message-Id: <20250121122937.edfdba2b3dfa3dba330a6b66@sraoss.co.jp>
Content-Type: multipart/mixed;
 boundary="Multipart=_Tue__21_Jan_2025_12_29_37_+0900_sBPyJl9O2wESf/lf"
MIME-Version: 1.0
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0:
	=?us-ascii?Q?k177pLfFKcNnF2lnbT84ZbAtDG/t4IyFyyKN/4uiHvOMsPSet/CYV/dHCNj7?=
 =?us-ascii?Q?EanW98YqH8h6oKnS0Srbomd9epDFp7llHxnj4YVgpVTkhN28lLmy59I5S/Yu?=
 =?us-ascii?Q?mtUI1u3i+HY3Fvoq1ymK1l+mELLPHYyD4UYBqPXbEX4e9THjy5uvF2QFAzEO?=
 =?us-ascii?Q?hgybYonzvWCG7Y32ca7qBXHR3SXNm5JgtTaKYZefLss0Essi5kbYsksDzO2z?=
 =?us-ascii?Q?wqr7DqvGNJJwA773R1JQxay7QtPbD+RjC8Wyhlk+FXMNvZ0L7V9v9YGYfjxv?=
 =?us-ascii?Q?DYjDQbh96TmFRLmfGHxNXuXRpjYi894xc9FkoopbVr9wl4uTFAM1RIrEITde?=
 =?us-ascii?Q?5qJGopzCeL3qDNgROVwJiJ5phOmd21wo5+e2mvcCDMJ+TPPkV0q/YmB7Nc+d?=
 =?us-ascii?Q?FtvO/taEr/37BAzHve/RI2e/nY9n9r7VTb9rg6MIva/sZxoH2KZvq5hcv21+?=
 =?us-ascii?Q?wPY/F6rovXvJLOKu8e7LObjslotBhL/jfd8qTYPXvfqaxO/voukxoMeqJ5Rx?=
 =?us-ascii?Q?3vIRM6URN+c4dRm+UaOsWfGG61A9bhe2nFb5xI3ilWoJMu0FDcMiMaC6t9uE?=
 =?us-ascii?Q?/chm/T3cnvz2oMWBtE30BYI82+l8ip3xLI3PTPRmexVQ1KHZvpmm5hWySP6o?=
 =?us-ascii?Q?jSo5jIBXfCDqY1X81PCMvtvzOYuiRlJskjN8Dfz3S53k8BBaUqaNq+sfvZld?=
 =?us-ascii?Q?mvBMZWvZZmFwRN7kmJrYUCiOA8tlREfYuG5rVEFjkQJVIzVyveXoJF4diFZY?=
 =?us-ascii?Q?uQGC8ZiUQDqs/jbzBmKckWGpNwkvsfsGlge/00TWmnvPnYfCl7+i7BpZ2c5E?=
 =?us-ascii?Q?2pKSkEBNl6ExgWO6u0XiH41iJMUwZB2bN35x7ltdMo2UoPl5cvXWMa6HVC2K?=
 =?us-ascii?Q?PGZEwlW7eGMX/h3GRmZGb1yXLh98jwyuRw685WCFFScVsh+YJnuk7sl9fcet?=
 =?us-ascii?Q?SD7gzhQdlqB+izWOJBCV83OqmoAlbqJA9suyTCpVwgultb45T4fW+y5QDnZS?=
 =?us-ascii?Q?NirBNpRKK/v2+WsAFnOBNw/IiFlzV29HPrcB0fAGt/plx083+JTh0WSF+eKv?=
 =?us-ascii?Q?JY8TwMETXRgBBunuFs20yqu3xzybCizhlhdPi4KzawzhFgIu9Dg4BzR/7OPc?=
 =?us-ascii?Q?iSnaLKydzowdutk7FVeyy2R5prGFGkSHHYLvOqwnhCDtrpYMCWV7/RnNcnA3?=
 =?us-ascii?Q?ap/Q/8Jg+dba2WGgEqIa8QZyUZzZLEqPzkSR5H1NbQ9HduV7MW2Nfp3DtXKL?=
 =?us-ascii?Q?2SAEs2IxyLD9naZXYDsVPBmiRIkEdhqPutouUw0OrKpy/Hl0fx+ZunPAMI8a?=
 =?us-ascii?Q?A3X1SMcDXBNcxUOAnSwyDlV7+OZ1ckpmvhYWqo+vZxREcWgVfdxRH6KeuImR?=
 =?us-ascii?Q?3xv7CI5+yKTM9lZ59K0c8lbxlHVX2/dpE+lWsn8Y8ZHPLrecD8upb6JkG8m2?=
 =?us-ascii?Q?xQgRiAo1kgnxca5EBYpdLQ00kOzS2j8GNu1/+uF/TRRpni5ssYeObLHb2eB3?=
 =?us-ascii?Q?iR83RprE+ZZbB+FJb7B9OxoHYWLS7crA/9+Yav9vK1vsAEBDQXiiy5R90iLm?=
 =?us-ascii?Q?/Ndu4fl3AG5tdyz0ez2ji/5AAp9+V+pBxcdSBIMSv9PikOe4GH0NghDkfGz7?=
 =?us-ascii?Q?AbKNXImPPg/oFo2KMPsfiuX028i9KIHTxoKeaBnSMPUK?=
X-OriginatorOrg: sraoss.co.jp
X-MS-Exchange-CrossTenant-Network-Message-Id: 71276345-db31-4fc3-74aa-08dd39cbd58c
X-MS-Exchange-CrossTenant-AuthSource: TYBP286MB0111.JPNP286.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 21 Jan 2025 03:29:38.1930
 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-CrossTenant-Id: 75db6c32-352e-4063-ae79-cafb8623f6db
X-MS-Exchange-CrossTenant-MailboxType: HOSTED
X-MS-Exchange-CrossTenant-UserPrincipalName: 1jhrDew9+Q4g0ZTea7Kk9peIvxP+bTE2ShLAsj2x5nsnb16pJFfL+4I8/jWPNdjV5NIiqCO8jP0ulDcXxsBpCA==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: TYRP286MB5418
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: <https://www.postgresql.org/message-id/20250121122937.edfdba2b3dfa3dba330a6b66%40sraoss.co.jp>
Precedence: bulk

--Multipart=_Tue__21_Jan_2025_12_29_37_+0900_sBPyJl9O2wESf/lf
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Hi,
When an extension module is updated, first the new binary is installed, then
ALTER EXTENSION UPDATE is executed to create or modify SQL objects. Between
these two steps, users could see an error, or in worst case segfalut, during
a query execution due to the lack or incompatibility of SQL objects.

This might be avoidable if the binary is made carefully to check the existing
of objects, but I think it is useful if an extension module function can check
the current extension version. So, I would like to propose a new function to
return the current extension version, get_extension_version. I've attached a
patch.

Also, as an application of this function, how about allowing to include versions
in the "requires" list in the control file? It seems useful when an extension
requires other extension with a certain version. The proposed
get_extension_version can be used to check the versions of existing extensions.
This is just an idea and a patch is not created yet.

Regards,
Yugo Nagata

-- 
Yugo Nagata <nagata@sraoss.co.jp>

--Multipart=_Tue__21_Jan_2025_12_29_37_+0900_sBPyJl9O2wESf/lf
Content-Type: text/x-diff;
 name="get_extension_version.patch"
Content-Disposition: attachment;
 filename="get_extension_version.patch"
Content-Transfer-Encoding: 7bit

diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index ba540e3de5..48c9109c31 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -215,6 +215,28 @@ get_extension_schema(Oid ext_oid)
 	return result;
 }
 
+/*
+ * get_extension_version - given an extension OID, look up the version
+ *
+ * Returns a palloc'd string, or NULL if no such extension.
+ */
+char *
+get_extension_version(Oid ext_oid)
+{
+	char	   *result;
+	HeapTuple	tuple;
+
+	tuple = SearchSysCache1(EXTENSIONOID, ObjectIdGetDatum(ext_oid));
+
+	if (!HeapTupleIsValid(tuple))
+		return NULL;
+
+	result = pstrdup(NameStr(((Form_pg_extension) GETSTRUCT(tuple))->extversion));
+	ReleaseSysCache(tuple);
+
+	return result;
+}
+
 /*
  * Utility functions to check validity of extension and version names
  */
diff --git a/src/include/commands/extension.h b/src/include/commands/extension.h
index 0b63640512..43a55adea7 100644
--- a/src/include/commands/extension.h
+++ b/src/include/commands/extension.h
@@ -48,6 +48,7 @@ extern ObjectAddress ExecAlterExtensionContentsStmt(AlterExtensionContentsStmt *
 extern Oid	get_extension_oid(const char *extname, bool missing_ok);
 extern char *get_extension_name(Oid ext_oid);
 extern Oid	get_extension_schema(Oid ext_oid);
+extern char *get_extension_version(Oid ext_oid);
 extern bool extension_file_exists(const char *extensionName);
 
 extern ObjectAddress AlterExtensionNamespace(const char *extensionName, const char *newschema,

--Multipart=_Tue__21_Jan_2025_12_29_37_+0900_sBPyJl9O2wESf/lf--