Subject: System views for versions reporting

From: Dmitry Dolgov <[email-masked]>
Date: Sun, 6 Oct 2024 17:36:49 +0200

Hi,

Based on the feedback in [1], here is my attempt at implementing system
views for versions reporting. It adds pg_system_versions for showing
things like core version, compiler, LLVM, etc, and pg_system_libraries
for showing linked shared objects. I think everyone has ageed that the
first was a good idea, where the second was only suggested -- after some
thinking I find shared obects useful enough to include here as well.

The main idea is to facilitate bug reporting. In particular, in many JIT
related bug reports one of the first questions is often "which LLVM
version is used?". Gathering such information is a manual process,
mistakes could be made when veryfing llvm-config output or anywhere
else. Having a system view for such purposes makes the process a bit
more robust.

The first three patches are essential for this purpose, the fourth one
is somewhat independent and could be concidered in isolation. The output
looks like this :

	=# select * from pg_system_versions;
	   name   |   version    |     type
	----------+--------------+--------------
	 Arch     | x86_64-linux | Compile Time
	 ICU      | 15.1         | Compile Time
	 Core     | 18devel      | Compile Time
	 Compiler | gcc-14.0.1   | Compile Time
	 LLVM     | 18.1.6       | Run Time

	=# select * from pg_system_libraries;
		name
	-----------------------------
	 /lib64/libkrb5.so.3
	 /lib64/libz.so.1
	 linux-vdso.so.1
	 /lib64/libxml2.so.2
	 [...]

Any feedback is appreciated.

0001-Add-infrastructure-for-pg_system_versions-view

Prepares the infrastructure, adds the view without populating it with
the data just yet. The view is backed by a hash table, which contains
callbacks returning a 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. E.g. it allows
to say that the version is not available.

0002-Add-core-versions-to-pg_system_versions.patch

Actually populates the view with some predefined compile time versions.
The versions are registered in PostgresMain, right after
BeginReportingGUCOptions -- there is no strong reasoning for that except
that it looks fine to me, so feel free to suggest a better place.

0003-Add-JIT-provider-version-to-pg_system_versions.patch

Finally adds LLVM version into the view via extending set of JIT
provider callbacks. The registration is happening together with the core
versions from the previous patch, because the JIT provider itself is
initialized only when a first expression is getting compiled.

0004-Add-pg_system_libraries-view.patch

Strictly speaking independent from the above patches. Adds the view to
show linked shared objects by iterating dl_phdr_info with
dl_iterate_phdr. It belongs to the standard C library, so I assume it's
portable enough.

[1]: https://www.postgresql.org/message-id/flat/znc72ymyoelvk5rjk5ub254v3qvcczfrk6autygjdobfvx2e7p%40s3dssvf34twa
>From d903142d178dd8a9c03d07ee4809ac582b9b7818 Mon Sep 17 00:00:00 2001
From: Dmitrii Dolgov <[email-masked]>
Date: Sat, 5 Oct 2024 18:27:57 +0200
Subject: [PATCH v1 1/4] Add infrastructure for pg_system_versions view

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          |  56 ++++++++++++
 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 | 108 ++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat         |   6 ++
 src/include/utils/system_version.h      |  40 +++++++++
 src/test/regress/expected/rules.out     |   8 ++
 8 files changed, 229 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 634a4c0..df0b9d3 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -226,6 +226,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>
@@ -5064,4 +5069,55 @@ 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>
+
+    </tbody>
+   </tgroup>
+  </table>
+ </sect1>
+
 </chapter>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 7fd5d25..091013d 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1377,3 +1377,11 @@ CREATE VIEW pg_stat_subscription_stats AS
 
 CREATE VIEW pg_wait_events AS
     SELECT * FROM pg_get_wait_events();
+
+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 d9f5978..fcb3be7 100644
--- a/src/backend/utils/misc/Makefile
+++ b/src/backend/utils/misc/Makefile
@@ -31,7 +31,8 @@ OBJS = \
 	sampling.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 6669502..ca2abc5 100644
--- a/src/backend/utils/misc/meson.build
+++ b/src/backend/utils/misc/meson.build
@@ -15,6 +15,7 @@ backend_sources += files(
   'rls.c',
   'sampling.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 0000000..4d633fc
--- /dev/null
+++ b/src/backend/utils/misc/system_version.c
@@ -0,0 +1,108 @@
+/*------------------------------------------------------------------------
+ *
+ * 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)
+		ereport(ERROR,
+				(errcode(ERRCODE_DUPLICATE_OBJECT),
+				 errmsg("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 43f608d..59587db 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12316,4 +12316,10 @@
   proargtypes => 'int2',
   prosrc => 'gist_stratnum_identity' },
 
+{ 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 0000000..a73f046
--- /dev/null
+++ b/src/include/utils/system_version.h
@@ -0,0 +1,40 @@
+/*-------------------------------------------------------------------------
+ * 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
+
+#include <link.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 a1626f3..b9ad6f5 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2611,6 +2611,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,

base-commit: 6aa44060a3c94ee10273bb8a89e98a5bb2fbbacb
-- 
2.45.1

>From 13d7dfb34dc9286379e55157c847b01cf77b5097 Mon Sep 17 00:00:00 2001
From: Dmitrii Dolgov <[email-masked]>
Date: Sat, 5 Oct 2024 18:30:34 +0200
Subject: [PATCH v1 2/4] Add core versions to pg_system_versions

Populate pg_system_versions with a set of core versions: host system
architecture, ICU version, PostgreSQL itself and compiler which was used
to build everything. All of them are compile time versions. Register the
core versions at the backend startup.

    select * from pg_system_versions;
       name   |   version    |     type
    ----------+--------------+--------------
     Arch     | x86_64-linux | Compile Time
     ICU      | 15.1         | Compile Time
     Core     | 18devel      | Compile Time
     Compiler | gcc-14.0.1   | Compile Time
---
 configure                               | 12 +++++++
 configure.ac                            |  4 +++
 meson.build                             |  4 +++
 src/backend/tcop/postgres.c             | 12 +++++++
 src/backend/utils/misc/system_version.c | 46 +++++++++++++++++++++++++
 src/include/pg_config.h.in              |  4 +++
 src/include/utils/system_version.h      |  7 ++++
 src/test/regress/expected/sysviews.out  |  8 +++++
 src/test/regress/sql/sysviews.sql       |  4 +++
 9 files changed, 101 insertions(+)

diff --git a/configure b/configure
index 53c8a1f..63e6d3e 100755
--- a/configure
+++ b/configure
@@ -19223,6 +19223,18 @@ else
 fi

+cat >>confdefs.h <<_ACEOF
+#define PG_CC_STR "$cc_string"
+_ACEOF
+
+
+
+cat >>confdefs.h <<_ACEOF
+#define PG_ARCH_STR "$host"
+_ACEOF
+
+
+
 cat >>confdefs.h <<_ACEOF
 #define PG_VERSION_STR "PostgreSQL $PG_VERSION on $host, compiled by $cc_string, `expr $ac_cv_sizeof_void_p \* 8`-bit"
 _ACEOF
diff --git a/configure.ac b/configure.ac
index 6a35b28..d063504 100644
--- a/configure.ac
+++ b/configure.ac
@@ -2417,6 +2417,10 @@ else
   cc_string=$CC
 fi
 
+AC_DEFINE_UNQUOTED(PG_CC_STR, ["$cc_string"], [C compiler version])
+
+AC_DEFINE_UNQUOTED(PG_ARCH_STR, ["$host"], [Platform])
+
 AC_DEFINE_UNQUOTED(PG_VERSION_STR,
                    ["PostgreSQL $PG_VERSION on $host, compiled by $cc_string, `expr $ac_cv_sizeof_void_p \* 8`-bit"],
                    [A string containing the version number, platform, and C compiler])
diff --git a/meson.build b/meson.build
index 7150f85..c5dfba6 100644
--- a/meson.build
+++ b/meson.build
@@ -2756,6 +2756,10 @@ cdata.set('USE_@0@_SEMAPHORES'.format(sema_kind.to_upper()), 1)
 cdata.set('MEMSET_LOOP_LIMIT', memset_loop_limit)
 cdata.set_quoted('DLSUFFIX', dlsuffix)
 
+cdata.set_quoted('PG_CC_STR', '@0@-@1@'.format(cc.get_id(), cc.version()))
+
+cdata.set_quoted('PG_ARCH_STR', '@0@-@1@'.format(
+  host_machine.cpu_family(), host_system))
 
 # built later than the rest of the version metadata, we need SIZEOF_VOID_P
 cdata.set_quoted('PG_VERSION_STR',
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 7f5eada..3b45fdc 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -80,6 +80,7 @@
 #include "utils/timeout.h"
 #include "utils/timestamp.h"
 #include "utils/varlena.h"
+#include "utils/system_version.h"
 
 /* ----------------
  *		global variables
@@ -197,6 +198,7 @@ static void drop_unnamed_stmt(void);
 static void log_disconnections(int code, Datum arg);
 static void enable_statement_timeout(void);
 static void disable_statement_timeout(void);
+static void register_system_versions(void);

 /* ----------------------------------------------------------------
@@ -4369,6 +4371,9 @@ PostgresMain(const char *dbname, const char *username)
 	 */
 	BeginReportingGUCOptions();
 
+	/* Prepare information for reporting versions and libraries. */
+	register_system_versions();
+
 	/*
 	 * Also set up handler to log session end; we have to wait till now to be
 	 * sure Log_disconnections has its final value.
@@ -5282,3 +5287,10 @@ disable_statement_timeout(void)
 	if (get_timeout_active(STATEMENT_TIMEOUT))
 		disable_timeout(STATEMENT_TIMEOUT, false);
 }
+
+static void
+register_system_versions()
+{
+	/* Set up reporting of core versions. */
+	register_core_versions();
+}
diff --git a/src/backend/utils/misc/system_version.c b/src/backend/utils/misc/system_version.c
index 4d633fc..8ebd0c3 100644
--- a/src/backend/utils/misc/system_version.c
+++ b/src/backend/utils/misc/system_version.c
@@ -67,6 +67,52 @@ add_system_version(const char* name, SystemVersionCB cb, VersionType type)
 	hentry->type = type;
 }
 
+/*
+ * Register versions that describe core components and do not correspond to any
+ * individual component.
+ */
+void
+register_core_versions()
+{
+	add_system_version("Core", core_get_version, CompileTime);
+	add_system_version("Arch", core_get_arch, CompileTime);
+	add_system_version("Compiler", core_get_compiler, CompileTime);
+	add_system_version("ICU", icu_get_version, CompileTime);
+}
+
+const char*
+core_get_version(bool *available)
+{
+	*available = true;
+	return (const char*) psprintf("%s", PG_VERSION);
+}
+
+const char*
+core_get_arch(bool *available)
+{
+	*available = true;
+	return (const char*) psprintf("%s", PG_ARCH_STR);
+}
+
+const char*
+core_get_compiler(bool *available)
+{
+	*available = true;
+	return (const char*) psprintf("%s", PG_CC_STR);
+}
+
+const char*
+icu_get_version(bool *available)
+{
+#ifdef USE_ICU
+	*available = true;
+	return (const char*) U_UNICODE_VERSION;
+#else
+	*available = false;
+	return (const char*) "";
+#endif
+}
+
 /*
  * pg_get_system_versions
  *
diff --git a/src/include/pg_config.h.in b/src/include/pg_config.h.in
index 3800636..b0a588b 100644
--- a/src/include/pg_config.h.in
+++ b/src/include/pg_config.h.in
@@ -621,6 +621,10 @@
 /* PostgreSQL version as a number */
 #undef PG_VERSION_NUM
 
+#undef PG_CC_STR
+
+#undef PG_ARCH_STR
+
 /* A string containing the version number, platform, and C compiler */
 #undef PG_VERSION_STR
 
diff --git a/src/include/utils/system_version.h b/src/include/utils/system_version.h
index a73f046..9537f47 100644
--- a/src/include/utils/system_version.h
+++ b/src/include/utils/system_version.h
@@ -36,5 +36,12 @@ typedef struct SystemVersion
 } SystemVersion;
 
 void add_system_version(const char* name, SystemVersionCB cb, VersionType type);
+extern void register_core_versions(void);
+
+const char* core_get_version(bool *available);
+const char* core_get_arch(bool *available);
+const char* core_get_compiler(bool *available);
+
+const char* icu_get_version(bool *available);
 
 #endif							/* SYSTEM_VERSION_H */
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index fad7fc3..0afabc1 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -222,3 +222,11 @@ select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs;
  t
 (1 row)
 
+-- At least 4 core versions should be present, architecture, ICU, core and
+-- compiler
+select count(*) >= 4 as ok FROM pg_system_versions;
+ ok 
+----
+ t
+(1 row)
+
diff --git a/src/test/regress/sql/sysviews.sql b/src/test/regress/sql/sysviews.sql
index b2a7923..7a5a5f6 100644
--- a/src/test/regress/sql/sysviews.sql
+++ b/src/test/regress/sql/sysviews.sql
@@ -98,3 +98,7 @@ set timezone_abbreviations = 'Australia';
 select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs;
 set timezone_abbreviations = 'India';
 select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs;
+
+-- At least 4 core versions should be present, architecture, ICU, core and
+-- compiler
+select count(*) >= 4 as ok FROM pg_system_versions;
-- 
2.45.1

>From b50af6045561104f9021b1da7f3e18aebbb659f1 Mon Sep 17 00:00:00 2001
From: Dmitrii Dolgov <[email-masked]>
Date: Sat, 5 Oct 2024 18:31:36 +0200
Subject: [PATCH v1 3/4] Add JIT provider version to pg_system_versions

Populate pg_system_versions with the JIT provider version. To actually
fetch the version, extend the JIT provider callbacks with the
get_version method. For LLVM provider llvm_version will be used, which
utilizes C-API LLVMGetVersion, available since LLVM 16.

The JIT provider will be initialized, when a first expression will be
compiled. For reporting purposes it's too late, thus register the
version at the backend startup, right after the core versions.
---
 src/backend/jit/jit.c          | 19 +++++++++++++++++++
 src/backend/jit/llvm/llvmjit.c | 17 +++++++++++++++++
 src/backend/tcop/postgres.c    |  7 +++++++
 src/include/jit/jit.h          | 11 +++++++++++
 src/include/jit/llvmjit.h      |  2 ++
 5 files changed, 56 insertions(+)

diff --git a/src/backend/jit/jit.c b/src/backend/jit/jit.c
index 815b58f..8085a43 100644
--- a/src/backend/jit/jit.c
+++ b/src/backend/jit/jit.c
@@ -188,3 +188,22 @@ InstrJitAgg(JitInstrumentation *dst, JitInstrumentation *add)
 	INSTR_TIME_ADD(dst->optimization_counter, add->optimization_counter);
 	INSTR_TIME_ADD(dst->emission_counter, add->emission_counter);
 }
+
+/*
+ * Return JIT provider's version string for troubleshooting purposes.
+ */
+const char *
+jit_get_version(bool *available)
+{
+	if (provider_init())
+		return provider.get_version(available);
+
+	*available = false;
+	return "";
+}
+
+void
+jit_register_version(void)
+{
+	add_system_version("LLVM", jit_get_version, RunTime);
+}
diff --git a/src/backend/jit/llvm/llvmjit.c b/src/backend/jit/llvm/llvmjit.c
index 0f6cec5..2eed882 100644
--- a/src/backend/jit/llvm/llvmjit.c
+++ b/src/backend/jit/llvm/llvmjit.c
@@ -165,6 +165,7 @@ _PG_jit_provider_init(JitProviderCallbacks *cb)
 	cb->reset_after_error = llvm_reset_after_error;
 	cb->release_context = llvm_release_context;
 	cb->compile_expr = llvm_compile_expr;
+	cb->get_version = llvm_version;
 }

@@ -1382,3 +1383,19 @@ ResOwnerReleaseJitContext(Datum res)
 	context->resowner = NULL;
 	jit_release_context(&context->base);
 }
+
+const char *
+llvm_version(bool *available)
+{
+#if LLVM_VERSION_MAJOR > 15
+	unsigned int major, minor, patch;
+
+	LLVMGetVersion(&major, &minor, &patch);
+
+	*available = true;
+	return (const char*) psprintf("%d.%d.%d", major, minor, patch);
+#else
+	*available = false;
+	return "";
+#endif
+}
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 3b45fdc..84a2a44 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -5293,4 +5293,11 @@ register_system_versions()
 {
 	/* Set up reporting of core versions. */
 	register_core_versions();
+
+	/*
+	 * Set up reporting for JIT provider version. JIT provider initialization
+	 * happens when the first expression is getting compiled, which is too
+	 * late. Thus register the callback here instead.
+	 */
+	jit_register_version();
 }
diff --git a/src/include/jit/jit.h b/src/include/jit/jit.h
index d9a080c..6b3d453 100644
--- a/src/include/jit/jit.h
+++ b/src/include/jit/jit.h
@@ -13,6 +13,7 @@
 
 #include "executor/instrument.h"
 #include "utils/resowner.h"
+#include "utils/system_version.h"

 /* Flags determining what kind of JIT operations to perform */
@@ -70,12 +71,14 @@ typedef void (*JitProviderResetAfterErrorCB) (void);
 typedef void (*JitProviderReleaseContextCB) (JitContext *context);
 struct ExprState;
 typedef bool (*JitProviderCompileExprCB) (struct ExprState *state);
+typedef const char* (*JitProviderVersion) (bool *available);
 
 struct JitProviderCallbacks
 {
 	JitProviderResetAfterErrorCB reset_after_error;
 	JitProviderReleaseContextCB release_context;
 	JitProviderCompileExprCB compile_expr;
+	JitProviderVersion get_version;
 };

@@ -102,5 +105,13 @@ extern void jit_release_context(JitContext *context);
 extern bool jit_compile_expr(struct ExprState *state);
 extern void InstrJitAgg(JitInstrumentation *dst, JitInstrumentation *add);
 
+/*
+ * Get the provider's version string. The flag indicating availability is
+ * passed as an argument, and will be set accordingly if it's not possible to
+ * get the version.
+ */
+extern const char *jit_get_version(bool *available);
+
+extern void jit_register_version(void);
 
 #endif							/* JIT_H */
diff --git a/src/include/jit/llvmjit.h b/src/include/jit/llvmjit.h
index 420775b..898848a 100644
--- a/src/include/jit/llvmjit.h
+++ b/src/include/jit/llvmjit.h
@@ -136,6 +136,8 @@ extern LLVMValueRef slot_compile_deform(struct LLVMJitContext *context, TupleDes
 extern LLVMTypeRef LLVMGetFunctionReturnType(LLVMValueRef r);
 extern LLVMTypeRef LLVMGetFunctionType(LLVMValueRef r);
 
+extern const char* llvm_version(bool *available);
+
 #ifdef __cplusplus
 } /* extern "C" */
 #endif
-- 
2.45.1

>From caa461373cd14a7cfa41b6a37f0d2c7577c0c935 Mon Sep 17 00:00:00 2001
From: Dmitrii Dolgov <[email-masked]>
Date: Sat, 5 Oct 2024 18:32:04 +0200
Subject: [PATCH v1 4/4] Add pg_system_libraries view

Introduce a way to report shared objects linked with PostgreSQL. Such
information is useful for troubleshooting, and could enhance bug reports. The
reporting is done via pg_system_libraries view, which contains a file path to
the shared object. It's implemented via standard C library dl_iterate_phdr,
which should be portable enough.

    select * from pg_system_libraries;
		name
    -----------------------------
     /lib64/libkrb5.so.3
     /lib64/libz.so.1
     linux-vdso.so.1
     /lib64/libxml2.so.2
    [...]
---
 doc/src/sgml/system-views.sgml          | 46 ++++++++++++++
 src/backend/catalog/system_views.sql    |  3 +
 src/backend/tcop/postgres.c             |  1 +
 src/backend/utils/misc/system_version.c | 81 +++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat         |  6 ++
 src/include/utils/system_version.h      |  9 +++
 src/test/regress/expected/rules.out     |  2 +
 src/test/regress/expected/sysviews.out  |  7 +++
 src/test/regress/sql/sysviews.sql       |  2 +
 9 files changed, 157 insertions(+)

diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index df0b9d3..bf397f5 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -231,6 +231,11 @@
       <entry>system versions</entry>
      </row>
 
+     <row>
+      <entry><link linkend="view-pg-system-libraries"><structname>pg_system_libraries</structname></link></entry>
+      <entry>linked libraries</entry>
+     </row>
+
     </tbody>
    </tgroup>
   </table>
@@ -5120,4 +5125,45 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
   </table>
  </sect1>
 
+ <sect1 id="view-pg-system-libraries">
+  <title><structname>pg_system_libraries</structname></title>
+
+  <indexterm zone="view-pg-system-libraries">
+   <primary>pg_system_libraries</primary>
+  </indexterm>
+
+  <para>
+   The view <structname>pg_system_libraries</structname> provides description
+   about shared objects PostgreSQL is linked with.
+  </para>
+
+  <table>
+   <title><structname>pg_system_libraries</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>
+       Shared object file path
+      </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 091013d..26f087c 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1385,3 +1385,6 @@ CREATE VIEW pg_system_versions AS
                   WHEN 1 THEN 'Run Time'
                   END AS "type"
     FROM pg_get_system_versions();
+
+CREATE VIEW pg_system_libraries AS
+    SELECT * FROM pg_get_system_libraries() as f(name text);
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 84a2a44..80c5372 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -4373,6 +4373,7 @@ PostgresMain(const char *dbname, const char *username)
 
 	/* Prepare information for reporting versions and libraries. */
 	register_system_versions();
+	register_libraries();
 
 	/*
 	 * Also set up handler to log session end; we have to wait till now to be
diff --git a/src/backend/utils/misc/system_version.c b/src/backend/utils/misc/system_version.c
index 8ebd0c3..73e8078 100644
--- a/src/backend/utils/misc/system_version.c
+++ b/src/backend/utils/misc/system_version.c
@@ -14,6 +14,10 @@
  * a version string directly allows for more flexibility about how and when the
  * information could be reported.
  *
+ * Libraries reporting is implemented similarly via a hash table containing
+ * only the library file path. This information is populated directly during
+ * the initialization.
+ *
  * Portions Copyright (c) 1996-2024, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
@@ -32,6 +36,7 @@
 #include "utils/system_version.h"
 
 static HTAB	   *versions = NULL;
+static HTAB	   *libraries = NULL;
 
 void
 add_system_version(const char* name, SystemVersionCB cb, VersionType type)
@@ -152,3 +157,79 @@ pg_get_system_versions(PG_FUNCTION_ARGS)
 
 	return (Datum) 0;
 }
+
+/*
+ * Walk through list of shared objects and populate the libraries hash table.
+ */
+void
+register_libraries()
+{
+	HASHCTL		ctl;
+
+	ctl.keysize = NAMEDATALEN;
+	ctl.entrysize = sizeof(SystemLibrary);
+	ctl.hcxt = CurrentMemoryContext;
+
+	libraries = hash_create("Libraries table",
+								   MAX_SYSTEM_LIBRARIES,
+								   &ctl,
+								   HASH_ELEM | HASH_STRINGS);
+
+	dl_iterate_phdr(add_library, NULL);
+}
+
+int add_library(struct dl_phdr_info *info, size_t size, void *data)
+{
+	const char 		*key;
+	bool 			found;
+
+	if (strcmp(info->dlpi_name, "") == 0)
+	{
+		/* The first visited object is the main program with the empty name,
+		 * which is not so interesting. */
+		return 0;
+	}
+
+	key = pstrdup(info->dlpi_name);
+	hash_search(libraries, key, HASH_ENTER, &found);
+
+	if (found)
+		ereport(ERROR,
+				(errcode(ERRCODE_DUPLICATE_OBJECT),
+				 errmsg("duplicated library")));
+
+	return 0;
+}
+
+/*
+ * pg_get_libraries
+ *
+ * List information about shared objects.
+ */
+Datum
+pg_get_system_libraries(PG_FUNCTION_ARGS)
+{
+#define PG_GET_SYS_LIBRARIES_COLS 1
+	ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+	HASH_SEQ_STATUS status;
+	SystemLibrary *hentry;
+
+	/* Build tuplestore to hold the result rows */
+	InitMaterializedSRF(fcinfo, 0);
+
+	if (!versions)
+		return (Datum) 0;
+
+	hash_seq_init(&status, libraries);
+	while ((hentry = (SystemLibrary *) hash_seq_search(&status)) != NULL)
+	{
+		Datum		values[PG_GET_SYS_LIBRARIES_COLS] = {0};
+		bool		nulls[PG_GET_SYS_LIBRARIES_COLS] = {0};
+
+		values[0] = CStringGetTextDatum(hentry->filepath);
+
+		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 59587db..f00a562 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12322,4 +12322,10 @@
   proargtypes => '', proallargtypes => '{text,text,int8}',
   proargmodes => '{o,o,o}', proargnames => '{name,version,type}',
   prosrc => 'pg_get_system_versions' },
+{ oid => '9433', descr => 'describe system libraries',
+  proname => 'pg_get_system_libraries', procost => '10', prorows => '30',
+  proretset => 't', provolatile => 'v', prorettype => 'record',
+  proargtypes => '', proallargtypes => '{text}',
+  proargmodes => '{o}', proargnames => '{name}',
+  prosrc => 'pg_get_system_libraries' },
 ]
diff --git a/src/include/utils/system_version.h b/src/include/utils/system_version.h
index 9537f47..01551fd 100644
--- a/src/include/utils/system_version.h
+++ b/src/include/utils/system_version.h
@@ -14,6 +14,7 @@
 #include <link.h>
 
 #define MAX_SYSTEM_VERSIONS 100
+#define MAX_SYSTEM_LIBRARIES 100
 
 typedef enum VersionType
 {
@@ -35,8 +36,14 @@ typedef struct SystemVersion
 	SystemVersionCB	callback; 			/* Callback to fetch the version string */
 } SystemVersion;
 
+typedef struct SystemLibrary
+{
+	char			filepath[NAMEDATALEN];
+} SystemLibrary;
+
 void add_system_version(const char* name, SystemVersionCB cb, VersionType type);
 extern void register_core_versions(void);
+extern void register_libraries(void);
 
 const char* core_get_version(bool *available);
 const char* core_get_arch(bool *available);
@@ -44,4 +51,6 @@ const char* core_get_compiler(bool *available);
 
 const char* icu_get_version(bool *available);
 
+int add_library(struct dl_phdr_info *info, size_t size, void *data);
+
 #endif							/* SYSTEM_VERSION_H */
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index b9ad6f5..3ba2510 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2611,6 +2611,8 @@ 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_libraries| SELECT name
+   FROM pg_get_system_libraries() f(name text);
 pg_system_versions| SELECT name,
     version,
         CASE type
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 0afabc1..c8b14c1 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -230,3 +230,10 @@ select count(*) >= 4 as ok FROM pg_system_versions;
  t
 (1 row)
 
+-- There is always some number of shared objects
+select count(*) > 0 as ok FROM pg_system_libraries;
+ ok 
+----
+ t
+(1 row)
+
diff --git a/src/test/regress/sql/sysviews.sql b/src/test/regress/sql/sysviews.sql
index 7a5a5f6..1334574 100644
--- a/src/test/regress/sql/sysviews.sql
+++ b/src/test/regress/sql/sysviews.sql
@@ -102,3 +102,5 @@ select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs;
 -- At least 4 core versions should be present, architecture, ICU, core and
 -- compiler
 select count(*) >= 4 as ok FROM pg_system_versions;
+-- There is always some number of shared objects
+select count(*) > 0 as ok FROM pg_system_libraries;
-- 
2.45.1

---

From: Joe Conway <[email-masked]>
Date: Sun, 6 Oct 2024 12:01:29 -0400

> 	=# select * from pg_system_versions;

I'm not sure why ICU is "Compile Time" rather than "Run Time" when it is 
not statically linked. Also, if we are going to include ICU here, 
shouldn't we also include libc version?

> 	=# select * from pg_system_libraries;

I think it would be nice to include a sha256 hash (or something similar) 
of the libraries as well, so that they can be checked against known good 
values.

-- 
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com