From 8889b7c6c264e250cc80f3e2ef01f8f147b7c68a Mon Sep 17 00:00:00 2001
From: Robert Haas <rhaas@postgresql.org>
Date: Thu, 3 Jul 2025 12:21:01 -0400
Subject: [PATCH] pg_get_collected_shared_advice

---
 .../pg_plan_advice/pg_plan_advice--1.0.sql    | 14 +++-
 contrib/pg_plan_advice/pgpa_collector.c       | 67 +++++++++++++++++++
 2 files changed, 80 insertions(+), 1 deletion(-)

diff --git a/contrib/pg_plan_advice/pg_plan_advice--1.0.sql b/contrib/pg_plan_advice/pg_plan_advice--1.0.sql
index a4fdf8158a..63a82666df 100644
--- a/contrib/pg_plan_advice/pg_plan_advice--1.0.sql
+++ b/contrib/pg_plan_advice/pg_plan_advice--1.0.sql
@@ -3,7 +3,6 @@
 -- complain if script is sourced in psql, rather than via CREATE EXTENSION
 \echo Use "CREATE EXTENSION pg_plan_advice" to load this file. \quit
 
--- Show visibility map information for each block in a relation.
 CREATE FUNCTION pg_get_collected_local_advice(
 	OUT id bigint,
 	OUT userid oid,
@@ -16,3 +15,16 @@ CREATE FUNCTION pg_get_collected_local_advice(
 RETURNS SETOF record
 AS 'MODULE_PATHNAME', 'pg_get_collected_local_advice'
 LANGUAGE C STRICT;
+
+CREATE FUNCTION pg_get_collected_shared_advice(
+	OUT id bigint,
+	OUT userid oid,
+	OUT dbid oid,
+	OUT queryid bigint,
+	OUT collection_time timestamptz,
+	OUT query text,
+	OUT advice text
+)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'pg_get_collected_shared_advice'
+LANGUAGE C STRICT;
diff --git a/contrib/pg_plan_advice/pgpa_collector.c b/contrib/pg_plan_advice/pgpa_collector.c
index d4b6e00a2f..79cfbcf1f6 100644
--- a/contrib/pg_plan_advice/pgpa_collector.c
+++ b/contrib/pg_plan_advice/pgpa_collector.c
@@ -23,6 +23,7 @@
 #include "utils/timestamp.h"
 
 PG_FUNCTION_INFO_V1(pg_get_collected_local_advice);
+PG_FUNCTION_INFO_V1(pg_get_collected_shared_advice);
 
 #define ADVICE_CHUNK_SIZE		1024
 #define ADVICE_CHUNK_ARRAY_SIZE	64
@@ -446,3 +447,69 @@ pg_get_collected_local_advice(PG_FUNCTION_ARGS)
 
 	return (Datum) 0;
 }
+
+/*
+ * SQL-callable SRF to return locally collected advice
+ */
+Datum
+pg_get_collected_shared_advice(PG_FUNCTION_ARGS)
+{
+	ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+	pgpa_shared_state *state = pg_plan_advice_attach();
+	dsa_area *area = pg_plan_advice_dsa_area();
+	pgpa_shared_advice *sa = shared_collector;
+
+	InitMaterializedSRF(fcinfo, 0);
+
+	/* Lock the shared state. */
+	LWLockAcquire(&state->lock, LW_SHARED);
+
+	/*
+	 * If we're not attached to the shared advice collector yet, fix that now;
+	 * but if the collector doesn't even exist, we can return without doing
+	 * anything else.
+	 */
+	if (sa == NULL)
+	{
+		if (state->shared_collector == InvalidDsaPointer)
+		{
+			LWLockRelease(&state->lock);
+			return (Datum) 0;
+		}
+		shared_collector = sa = dsa_get_address(area, state->shared_collector);
+	}
+
+	for (uint64 id = sa->oldest_id; id < sa->next_id; ++id)
+	{
+		uint64		chunk_number;
+		uint64		chunk_offset;
+		dsa_pointer *chunk_array;
+		pgpa_shared_advice_chunk *chunk;
+		pgpa_collected_advice *ca;
+		Datum		values[PG_GET_ADVICE_COLUMNS];
+		bool		nulls[PG_GET_ADVICE_COLUMNS] = {0};
+
+		chunk_number = (id - sa->base_id) / ADVICE_CHUNK_SIZE;
+		chunk_offset = (id - sa->base_id) % ADVICE_CHUNK_SIZE;
+
+		chunk_array = dsa_get_address(area, sa->chunks);
+		chunk = dsa_get_address(area, chunk_array[chunk_number]);
+		ca = dsa_get_address(area, chunk->entries[chunk_offset]);
+
+		values[0] = UInt64GetDatum(id);
+		values[1] = ObjectIdGetDatum(ca->userid);
+		values[2] = ObjectIdGetDatum(ca->dbid);
+		values[3] = UInt64GetDatum(ca->queryid);
+		values[4] = TimestampGetDatum(ca->timestamp);
+		values[5] = CStringGetTextDatum(query_string(ca));
+		values[6] = CStringGetTextDatum(advice_string(ca));
+
+		tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc,
+							 values, nulls);
+	}
+
+	/* Release lock on shared state. */
+	LWLockRelease(&state->lock);
+
+	return (Datum) 0;
+}
-- 
2.39.5