v1-0001-Add-support-for-pg_accept_connections_start_time.patch

From 7521d666ce66347f894acf435d67ea69a6ecc677 Mon Sep 17 00:00:00 2001
From: Robins Tharakan <tharakan@gmail.com>
Date: Fri, 14 Feb 2025 20:51:10 +1030
Subject: [PATCH v1] Add support for pg_accept_connections_start_time()

It is not always reliable to depend on pg_postmaster_start_time()
database uptime calculations, owing to how postmaster catches
child process crashes, startup recovery etc. and continue without
a restart. This could lead to multiple seconds (or minutes/hours)
of difference when although postmaster was up, but the database
was not available for accepting connections.

This function returns the start time when the database was ready
to accept new database connections, allowing better calculation
of database availability.
---
 doc/src/sgml/func.sgml                  | 13 +++++++++++++
 src/backend/postmaster/launch_backend.c |  3 +++
 src/backend/postmaster/postmaster.c     |  5 +++++
 src/backend/tcop/postgres.c             |  5 +++++
 src/backend/utils/adt/timestamp.c       |  9 +++++++++
 src/include/catalog/pg_proc.dat         |  6 ++++++
 src/include/utils/timestamp.h           |  3 +++
 7 files changed, 44 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 7efc81936ab..3bfbc384ea8 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -24935,6 +24935,19 @@ SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_accept_connections_start_time</primary>
+        </indexterm>
+        <function>pg_accept_connections_start_time</function> ()
+        <returnvalue>timestamp with time zone</returnvalue>
+       </para>
+       <para>
+        Returns the time when the server was ready to accept connections.
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/src/backend/postmaster/launch_backend.c b/src/backend/postmaster/launch_backend.c
index a97a1eda6da..985cda2a032 100644
--- a/src/backend/postmaster/launch_backend.c
+++ b/src/backend/postmaster/launch_backend.c
@@ -110,6 +110,7 @@ typedef struct
 	ProcSignalHeader *ProcSignal;
 	pid_t		PostmasterPid;
 	TimestampTz PgStartTime;
+	TimestampTz PgAcceptConnStartTime;
 	TimestampTz PgReloadTime;
 	pg_time_t	first_syslogger_file_time;
 	bool		redirection_done;
@@ -738,6 +739,7 @@ save_backend_variables(BackendParameters *param,
 
 	param->PostmasterPid = PostmasterPid;
 	param->PgStartTime = PgStartTime;
+	param->PgAcceptConnStartTime = PgAcceptConnStartTime;
 	param->PgReloadTime = PgReloadTime;
 	param->first_syslogger_file_time = first_syslogger_file_time;
 
@@ -998,6 +1000,7 @@ restore_backend_variables(BackendParameters *param)
 
 	PostmasterPid = param->PostmasterPid;
 	PgStartTime = param->PgStartTime;
+	PgAcceptConnStartTime = param->PgAcceptConnStartTime;
 	PgReloadTime = param->PgReloadTime;
 	first_syslogger_file_time = param->first_syslogger_file_time;
 
diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c
index bb22b13adef..7bcecdbb830 100644
--- a/src/backend/postmaster/postmaster.c
+++ b/src/backend/postmaster/postmaster.c
@@ -2329,6 +2329,11 @@ process_pm_child_exit(void)
 			 */
 			StartWorkerNeeded = true;
 
+			/*
+			* Remember time when database was ready to accept connections
+			*/
+			PgAcceptConnStartTime = GetCurrentTimestamp();
+
 			/* at this point we are really open for business */
 			ereport(LOG,
 					(errmsg("database system is ready to accept connections")));
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 1149d89d7a1..308930d5d76 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -4117,6 +4117,11 @@ PostgresSingleUserMain(int argc, char *argv[],
 	 */
 	PgStartTime = GetCurrentTimestamp();
 
+	/*
+	 * Remember time when stand-alone came up to accept user commands.
+	 */
+	PgAcceptConnStartTime = GetCurrentTimestamp();
+
 	/*
 	 * Create a per-backend PGPROC struct in shared memory. We must do this
 	 * before we can use LWLocks.
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index ba9bae05069..56cd4bfd3d9 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -52,6 +52,9 @@
 /* Set at postmaster start */
 TimestampTz PgStartTime;
 
+/* Set when database is ready to accept connections */
+TimestampTz PgAcceptConnStartTime;
+
 /* Set at configuration reload */
 TimestampTz PgReloadTime;
 
@@ -1628,6 +1631,12 @@ pg_postmaster_start_time(PG_FUNCTION_ARGS)
 	PG_RETURN_TIMESTAMPTZ(PgStartTime);
 }
 
+Datum
+pg_accept_connections_start_time(PG_FUNCTION_ARGS)
+{
+	PG_RETURN_TIMESTAMPTZ(PgAcceptConnStartTime);
+}
+
 Datum
 pg_conf_load_time(PG_FUNCTION_ARGS)
 {
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 9e803d610d7..2a1f27ea600 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8650,6 +8650,12 @@
   prorettype => 'timestamptz', proargtypes => '',
   prosrc => 'pg_postmaster_start_time' },
 
+# accept connections start time function
+{ oid => '8600', descr => 'accept connections start time',
+  proname => 'pg_accept_connections_start_time', provolatile => 's',
+  prorettype => 'timestamptz', proargtypes => '',
+  prosrc => 'pg_accept_connections_start_time' },
+
 # config reload time function
 { oid => '2034', descr => 'configuration load time',
   proname => 'pg_conf_load_time', provolatile => 's', proparallel => 'r',
diff --git a/src/include/utils/timestamp.h b/src/include/utils/timestamp.h
index d26f023fb87..e1eae7ddfc8 100644
--- a/src/include/utils/timestamp.h
+++ b/src/include/utils/timestamp.h
@@ -89,6 +89,9 @@ IntervalPGetDatum(const Interval *X)
 /* Set at postmaster start */
 extern PGDLLIMPORT TimestampTz PgStartTime;
 
+/* Set when database is ready to accept connections */
+extern PGDLLIMPORT TimestampTz PgAcceptConnStartTime;
+
 /* Set at configuration reload */
 extern PGDLLIMPORT TimestampTz PgReloadTime;
 
-- 
2.43.0