Thread

[PATCH v4 1/3] Add infrastructure for pg_system_versions view

Dmitry Dolgov <9erthalion6@gmail.com> — 2025-11-16T15:01:21Z

Introduce a unified way of reporting versions (PostgreSQL itself, the
compiler, the host system, compile and runtime dependencies, etc.) via a
new system view pg_system_versions. This is going to be useful for
troubleshooting and should enhance bug reports, replacing manual
bug-prone collecting of the same information.

The view is backed by a hash table, that contains callbacks returning
version string for a particular component. The idea is to allow some
flexibility in reporting, making components responsible for how and when
the information is exposed.
---
 doc/src/sgml/system-views.sgml          |  65 +++++++++++++++
 src/backend/catalog/system_views.sql    |   8 ++
 src/backend/utils/misc/Makefile         |   3 +-
 src/backend/utils/misc/meson.build      |   1 +
 src/backend/utils/misc/system_version.c | 106 ++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat         |   6 ++
 src/include/utils/system_version.h      |  38 +++++++++
 src/test/regress/expected/rules.out     |   8 ++
 src/tools/pgindent/typedefs.list        |   2 +
 9 files changed, 236 insertions(+), 1 deletion(-)
 create mode 100644 src/backend/utils/misc/system_version.c
 create mode 100644 src/include/utils/system_version.h

diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 7971498fe75..cb7b57e0102 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -246,6 +246,11 @@
       <entry>wait events</entry>
      </row>
 
+     <row>
+      <entry><link linkend="view-pg-system-versions"><structname>pg_system_versions</structname></link></entry>
+      <entry>system versions</entry>
+     </row>
+
     </tbody>
    </tgroup>
   </table>
@@ -5611,4 +5616,64 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
   </table>
  </sect1>
 
+ <sect1 id="view-pg-system-versions">
+  <title><structname>pg_system_versions</structname></title>
+
+  <indexterm zone="view-pg-system-version">
+   <primary>pg_system_versions</primary>
+  </indexterm>
+
+  <para>
+   The view <structname>pg_system_versions</structname> provides description
+   about versions of various system components, e.g. PostgreSQL itself,
+   compiler used to build it, dependencies, etc.
+  </para>
+
+  <table>
+   <title><structname>pg_system_versions</structname> Columns</title>
+   <tgroup cols="1">
+    <thead>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       Column Type
+      </para>
+      <para>
+       Description
+      </para></entry>
+     </row>
+    </thead>
+
+    <tbody>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>name</structfield> <type>text</type>
+      </para>
+      <para>
+       Component name
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>version</structfield> <type>text</type>
+      </para>
+      <para>
+       Component version
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>type</structfield> <type>text</type>
+      </para>
+      <para>
+       Component type (compile time or Run time)
+      </para></entry>
+     </row>
+
+    </tbody>
+   </tgroup>
+  </table>
+ </sect1>
+
 </chapter>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 059e8778ca7..a00777e4f8b 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1438,3 +1438,11 @@ REVOKE ALL ON pg_aios FROM PUBLIC;
 GRANT SELECT ON pg_aios TO pg_read_all_stats;
 REVOKE EXECUTE ON FUNCTION pg_get_aios() FROM PUBLIC;
 GRANT EXECUTE ON FUNCTION pg_get_aios() TO pg_read_all_stats;
+
+CREATE VIEW pg_system_versions AS
+    SELECT
+        name, version,
+        CASE type WHEN 0 THEN 'Compile Time'
+                  WHEN 1 THEN 'Run Time'
+                  END AS "type"
+    FROM pg_get_system_versions();
diff --git a/src/backend/utils/misc/Makefile b/src/backend/utils/misc/Makefile
index f142d17178b..0ac5b3c79cd 100644
--- a/src/backend/utils/misc/Makefile
+++ b/src/backend/utils/misc/Makefile
@@ -32,7 +32,8 @@ OBJS = \
 	stack_depth.o \
 	superuser.o \
 	timeout.o \
-	tzparser.o
+	tzparser.o \
+	system_version.o
 
 # This location might depend on the installation directories. Therefore
 # we can't substitute it into pg_config.h.
diff --git a/src/backend/utils/misc/meson.build b/src/backend/utils/misc/meson.build
index 9e389a00d05..5268eaa94c7 100644
--- a/src/backend/utils/misc/meson.build
+++ b/src/backend/utils/misc/meson.build
@@ -16,6 +16,7 @@ backend_sources += files(
   'sampling.c',
   'stack_depth.c',
   'superuser.c',
+  'system_version.c',
   'timeout.c',
   'tzparser.c',
 )
diff --git a/src/backend/utils/misc/system_version.c b/src/backend/utils/misc/system_version.c
new file mode 100644
index 00000000000..4d15ce58bf9
--- /dev/null
+++ b/src/backend/utils/misc/system_version.c
@@ -0,0 +1,106 @@
+/*------------------------------------------------------------------------
+ *
+ * system_version.c
+ *	  Functions for reporting version of system components.
+ *
+ * A system component is defined very broadly here, it might be the PostgreSQL
+ * core itself, the compiler, the host system, any dependency that is used at
+ * compile time or run time.
+ *
+ * Version reporting is implemented via a hash table containing the component's
+ * name as a key and the callback to fetch the version string. Every component
+ * can register such a callback during initialization and is responsible for
+ * exposing its own information. The idea is that storing a callback instead of
+ * a version string directly allows for more flexibility about how and when the
+ * information could be reported.
+ *
+ * Portions Copyright (c) 1996-2024, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ *
+ * IDENTIFICATION
+ *	  src/backend/utils/misc/system_version.c
+ *
+ *------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include <unicode/uchar.h>
+
+#include "funcapi.h"
+#include "utils/builtins.h"
+#include "utils/system_version.h"
+
+static HTAB *versions = NULL;
+
+void
+add_system_version(const char *name, SystemVersionCB cb, VersionType type)
+{
+	SystemVersion *hentry;
+	const char *key;
+	bool		found;
+
+	if (!versions)
+	{
+		HASHCTL		ctl;
+
+		ctl.keysize = NAMEDATALEN;
+		ctl.entrysize = sizeof(SystemVersion);
+		ctl.hcxt = CurrentMemoryContext;
+
+		versions = hash_create("System versions table",
+							   MAX_SYSTEM_VERSIONS,
+							   &ctl,
+							   HASH_ELEM | HASH_STRINGS);
+	}
+
+	key = pstrdup(name);
+	hentry = (SystemVersion *) hash_search(versions, key,
+										   HASH_ENTER, &found);
+
+	if (found)
+		elog(ERROR, "duplicated system version");
+
+	hentry->callback = cb;
+	hentry->type = type;
+}
+
+/*
+ * pg_get_system_versions
+ *
+ * List information about system versions.
+ */
+Datum
+pg_get_system_versions(PG_FUNCTION_ARGS)
+{
+#define PG_GET_SYS_VERSIONS_COLS 3
+	ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+	HASH_SEQ_STATUS status;
+	SystemVersion *hentry;
+
+	/* Build tuplestore to hold the result rows */
+	InitMaterializedSRF(fcinfo, 0);
+
+	if (!versions)
+		return (Datum) 0;
+
+	hash_seq_init(&status, versions);
+	while ((hentry = (SystemVersion *) hash_seq_search(&status)) != NULL)
+	{
+		Datum		values[PG_GET_SYS_VERSIONS_COLS] = {0};
+		bool		nulls[PG_GET_SYS_VERSIONS_COLS] = {0};
+		bool		available = false;
+		const char *version = hentry->callback(&available);
+
+		if (!available)
+			continue;
+
+		values[0] = CStringGetTextDatum(hentry->name);
+		values[1] = CStringGetTextDatum(version);
+		values[2] = hentry->type;
+
+		tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
+	}
+
+	return (Datum) 0;
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 5cf9e12fcb9..858e2f9983b 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12604,4 +12604,10 @@
   proargnames => '{pid,io_id,io_generation,state,operation,off,length,target,handle_data_len,raw_result,result,target_desc,f_sync,f_localmem,f_buffered}',
   prosrc => 'pg_get_aios' },
 
+{ oid => '9432', descr => 'describe system verions',
+  proname => 'pg_get_system_versions', procost => '10', prorows => '10',
+  proretset => 't', provolatile => 'v', prorettype => 'record',
+  proargtypes => '', proallargtypes => '{text,text,int8}',
+  proargmodes => '{o,o,o}', proargnames => '{name,version,type}',
+  prosrc => 'pg_get_system_versions' },
 ]
diff --git a/src/include/utils/system_version.h b/src/include/utils/system_version.h
new file mode 100644
index 00000000000..18cb673d4ca
--- /dev/null
+++ b/src/include/utils/system_version.h
@@ -0,0 +1,38 @@
+/*-------------------------------------------------------------------------
+ * system_version.h
+ *	  Definitions related to system versions reporting
+ *
+ * Copyright (c) 2001-2024, PostgreSQL Global Development Group
+ *
+ * src/include/utils/system_version.h
+ * ----------
+ */
+
+#ifndef SYSTEM_VERSION_H
+#define SYSTEM_VERSION_H
+
+#define MAX_SYSTEM_VERSIONS 100
+
+typedef enum VersionType
+{
+	CompileTime,
+	RunTime,
+} VersionType;
+
+/*
+ * Callback to return version string of a system component.
+ * The version might be not available, what is indicated via the argument.
+ */
+typedef const char *(*SystemVersionCB) (bool *available);
+
+typedef struct SystemVersion
+{
+	char		name[NAMEDATALEN];	/* Unique component name, used as a key
+									 * for versions HTAB */
+	VersionType type;
+	SystemVersionCB callback;	/* Callback to fetch the version string */
+} SystemVersion;
+
+void		add_system_version(const char *name, SystemVersionCB cb, VersionType type);
+
+#endif							/* SYSTEM_VERSION_H */
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 7c52181cbcb..7db23198ff3 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2683,6 +2683,14 @@ pg_stats_ext_exprs| SELECT cn.nspname AS schemaname,
      JOIN LATERAL ( SELECT unnest(pg_get_statisticsobjdef_expressions(s.oid)) AS expr,
             unnest(sd.stxdexpr) AS a) stat ON ((stat.expr IS NOT NULL)))
   WHERE (pg_has_role(c.relowner, 'USAGE'::text) AND ((c.relrowsecurity = false) OR (NOT row_security_active(c.oid))));
+pg_system_versions| SELECT name,
+    version,
+        CASE type
+            WHEN 0 THEN 'Compile Time'::text
+            WHEN 1 THEN 'Run Time'::text
+            ELSE NULL::text
+        END AS type
+   FROM pg_get_system_versions() pg_get_system_versions(name, version, type);
 pg_tables| SELECT n.nspname AS schemaname,
     c.relname AS tablename,
     pg_get_userbyid(c.relowner) AS tableowner,
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 23bce72ae64..5ebb83d9899 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2934,6 +2934,7 @@ SysloggerStartupData
 SystemRowsSamplerData
 SystemSamplerData
 SystemTimeSamplerData
+SystemVersion
 TAPtype
 TAR_MEMBER
 TBMIterateResult
@@ -4215,6 +4216,7 @@ varattrib_1b_e
 varattrib_4b
 vbits
 verifier_context
+VersionType
 walrcv_alter_slot_fn
 walrcv_check_conninfo_fn
 walrcv_connect_fn
-- 
2.49.0


--x4jls7tsrg7h7x5s
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment;
	filename="v4-0002-Add-core-versions-to-pg_system_versions.patch"