public inbox for [email protected]
help / color / mirror / Atom feedFrom: Robert Haas <[email protected]>
To: Lukas Fittl <[email protected]>
Cc: Jakub Wartak <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: pg_plan_advice
Date: Thu, 2 Apr 2026 22:15:36 -0400
Message-ID: <CA+TgmoaeNuHXQ60P3ZZqJLrSjP3L1KYokW9kPfGbWDyt+1t=Ng@mail.gmail.com> (raw)
In-Reply-To: <CA+Tgmob87qsWa-VugofU6epuV0H5XjWZGMbQas4Q-ADKmvSyBg@mail.gmail.com>
References: <CA+TgmoZ-Jh1T6QyWoCODMVQdhTUPYkaZjWztzP1En4=ZHoKPzw@mail.gmail.com>
<CAKZiRmxtJAFG7e1+Vs9B8ngON=AOzJbuws+1ZeH4LsbJh5AzoQ@mail.gmail.com>
<CA+TgmoY9Ne_Sh10u6LSPc3wvOQPLp3kF9nBp3nqJEG2JGF2QiA@mail.gmail.com>
<CA+Tgmoa57S6mP=aTOXH2-gDAL4TMO1WbGgrHSg0s6J4zUH=04g@mail.gmail.com>
<[email protected]>
<CA+Tgmoaf__2B0BUL+vrg28P+3buX=Ti-kybqkHiLTtFrrCfzuA@mail.gmail.com>
<CA+TgmoYpcLNOuypOTdgCSLW7FuA=t6BtB3meTARHX2-Dj_81xQ@mail.gmail.com>
<[email protected]>
<CA+TgmoZjv9OyFu1Gkt78w0vWEti8S33w8trYHmErf-GMmGSi=w@mail.gmail.com>
<[email protected]>
<CA+TgmoaOSBQD9Ux4eG40w723ZN=c0J7p-+oX4+J8urUeyLMo5w@mail.gmail.com>
<CAOYmi+=g+MMoOpWkk2weXWKJcKH0eKey8gKHHdH0dF4Tiawrhw@mail.gmail.com>
<CA+TgmobwaT=PXPDDrgDup+jA8KHBbkxigtziD-zNzAKKkQYVgQ@mail.gmail.com>
<CAOYmi+mOmEW=amDRQMfw6-Fb3ZmDEQFaJzwk8Bc8W8DzaP85XQ@mail.gmail.com>
<CA+TgmoaX2AMW4cdFM3OngBJxmxpkdmzF33R7-CWhvRLfucbFMg@mail.gmail.com>
<CA+TgmoZ0x3ym_oueXRWzbM_=6ucKoPZVGj3rRMLBDC_FnetXDw@mail.gmail.com>
<CAP53Pkycc=7N2bLzVT3x+qE1JamvRZWev5tFjdLJ1+-AV3Di+Q@mail.gmail.com>
<CA+TgmoaKhuD91RnazbRyGkmP7--JdNq8oNDC3UcgTZSWbMxC7w@mail.gmail.com>
<CAP53Pkw5-wMEeDJXFmqo_RTyL_spzCXb7HHKrbSnQqokVoZcNQ@mail.gmail.com>
<CA+Tgmob-69bzbdi3U_QtebqAf6u1y8js=5=oNK639csVe1VbhA@mail.gmail.com>
<CA+TgmoaZMOikxK=LqS+Jn+835h9S139JLGk-3LyETVXw5W5j=w@mail.gmail.com>
<[email protected]>
<CAP53PkwZ1ZTMARKg6iEfAw9qzBhkjBitj-9gr_Jvy7k2AwGgWA@mail.gmail.com>
<CAMbWs4--NuEUFE_xTo991TRXaZryE29jarJPDnVxoaQOYdt7tA@mail.gmail.com>
<CA+TgmobzR+XMGbRosVPbjHbSo4+cgJn=qZK6w05aF1sbj=C+9Q@mail.gmail.com>
<CA+TgmoawzvCoZAwFS85tE5+c8vBkqgcS8ZstQ_ohjXQ9wGT9sw@mail.gmail.com>
<CA+TgmoYS4ZCVAF2jTce=bMP0Oq_db_srocR4cZyO0OBp9oUoGg@mail.gmail.com>
<CAK98qZ2RzbgCHrSg4zLkvpzyBam_X6te-KF8w1+_vON9BAVMEw@mail.gmail.com>
<CA+TgmoaCdsuvNn6T6SfQ_0YD2Hh2+hgTXh9fTGHQhPg1zvy2rQ@mail.gmail.com>
<CA+Tgmob7ozJAs5SU6bD2RfAt4w_AmsMGz-aaVA6WeLXHkBypOg@mail.gmail.com>
<CAK98qZ1J42RoAsHnYWGPPmHziZmzmqE=Lp_O6WJ-9aKK2qjikA@mail.gmail.com>
<CA+TgmoYjcBA6dw3nwiyfDzPXTCrxTZPXDMrc2TrDJcL1cPK6iA@mail.gmail.com>
<CA+TgmoYru-vxoTKfwjQby30r2OkTXfb18Km_=VLs6qk8Akr0-g@mail.gmail.com>
<CA+Tgmoau7yJtvbeH-0kPt1Q=Gt_ezRdgM35Q1=LT665U_86Etg@mail.gmail.com>
<[email protected]>
<CA+TgmobOLrMn5jEinWNPL5SrDH1DPpo3a4j+S6-4yhsZwWgzLg@mail.gmail.com>
<CA+TgmoZUN8FT1Ah=m6Uis5bHa4FUa+_hMDWtcABG17toEfpiUg@mail.gmail.com>
<CA+TgmoYh2-kM+tscOz=jVYq9Tf4SRPVqzPojs3KLZcW6E9m1BQ@mail.gmail.com>
<CA+TgmoaK=4w7-qknUo3QhUJ53pXZq=c=KgZmRyD+k7ytqfmgSg@mail.gmail.com>
<CAP53Pkz3DSFaaowYvbO5LULf3NhydD_UhHkighfWf6_pwxiqUw@mail.gmail.com>
<CA+TgmoZ45n5jaNKKgbbj4-kYV8WsPvUn=Z8HnoZ7tUb_p9WKXg@mail.gmail.com>
<CA+TgmoYuWmN-00Ec5pY7zAcpSFQUQLbgAdVWGR9kOR-HM-fHrA@mail.gmail.com>
<CAKZiRmwFKhVz-HWvQmgPY7nZES9mCqdXHD++m9b7F4digcJpRQ@mail.gmail.com>
<CAP53Pkxak127z_Xp7xW5G0+X4FELCBzNyoWCqTQ-xGfLog3KyQ@mail.gmail.com>
<CA+Tgmoandcm1p=BCwLZPGRG_cno3pKSj4Ejt8AQZ4Hu-yhP1Mw@mail.gmail.com>
<CAP53PkxJp=9PMzD8mKB3=4fK3Qp1iFZnA4pQSavP0nkpiNyoaA@mail.gmail.com>
<CA+TgmobnwuyTgYXweQrvOz_kFuBuCbKhsLz-mUPasFw_DQ8f6w@mail.gmail.com>
<CAP53PkxvjckT=1CokN7-PMcthr0OvWNMrAXTH7dStaXrKi6q2A@mail.gmail.com>
<CA+Tgmob87qsWa-VugofU6epuV0H5XjWZGMbQas4Q-ADKmvSyBg@mail.gmail.com>
On Thu, Apr 2, 2026 at 12:15 PM Robert Haas <[email protected]> wrote:
> So here's v24, also dropping pg_collect_advice.
That version didn't actually pass CI. Here's v25.
--
Robert Haas
EDB: http://www.enterprisedb.com
Attachments:
[application/octet-stream] v25-0001-Add-pg_stash_advice-contrib-module.patch (65.0K, 2-v25-0001-Add-pg_stash_advice-contrib-module.patch)
download | inline diff:
From 7451adce350626e64755abeecde757b3dfbd6de2 Mon Sep 17 00:00:00 2001
From: Robert Haas <[email protected]>
Date: Fri, 27 Feb 2026 16:58:14 -0500
Subject: [PATCH v25 1/2] Add pg_stash_advice contrib module.
This module allows plan advice strings to be provided automatically
from an in-memory advice stash. Advice stashes are stored in dynamic
shared memory and must be recreated and repopulated after a server
restart. If pg_stash_advice.stash_name is set to the name of an advice
stash, and if query identifiers are enabled, the query identifier
for each query will be looked up in the advice stash and the
associated advice string, if any, will be used each time that query
is planned.
Reviewed-by: Lukas Fittl <[email protected]>
Reviewed-by: Alexandra Wang <[email protected]>
Reviewed-by: David G. Johnston <[email protected]>
Reviewed-by: Jakub Wartak <[email protected]>
---
contrib/Makefile | 1 +
contrib/meson.build | 1 +
contrib/pg_stash_advice/Makefile | 27 +
.../expected/pg_stash_advice.out | 331 ++++++++++
.../expected/pg_stash_advice_utf8.out | 16 +
.../expected/pg_stash_advice_utf8_1.out | 8 +
contrib/pg_stash_advice/meson.build | 37 ++
.../pg_stash_advice/pg_stash_advice--1.0.sql | 43 ++
contrib/pg_stash_advice/pg_stash_advice.c | 605 ++++++++++++++++++
.../pg_stash_advice/pg_stash_advice.control | 5 +
contrib/pg_stash_advice/pg_stash_advice.h | 99 +++
.../pg_stash_advice/sql/pg_stash_advice.sql | 150 +++++
.../sql/pg_stash_advice_utf8.sql | 16 +
contrib/pg_stash_advice/stashfuncs.c | 307 +++++++++
doc/src/sgml/contrib.sgml | 1 +
doc/src/sgml/filelist.sgml | 1 +
doc/src/sgml/pgstashadvice.sgml | 216 +++++++
src/tools/pgindent/typedefs.list | 6 +
18 files changed, 1870 insertions(+)
create mode 100644 contrib/pg_stash_advice/Makefile
create mode 100644 contrib/pg_stash_advice/expected/pg_stash_advice.out
create mode 100644 contrib/pg_stash_advice/expected/pg_stash_advice_utf8.out
create mode 100644 contrib/pg_stash_advice/expected/pg_stash_advice_utf8_1.out
create mode 100644 contrib/pg_stash_advice/meson.build
create mode 100644 contrib/pg_stash_advice/pg_stash_advice--1.0.sql
create mode 100644 contrib/pg_stash_advice/pg_stash_advice.c
create mode 100644 contrib/pg_stash_advice/pg_stash_advice.control
create mode 100644 contrib/pg_stash_advice/pg_stash_advice.h
create mode 100644 contrib/pg_stash_advice/sql/pg_stash_advice.sql
create mode 100644 contrib/pg_stash_advice/sql/pg_stash_advice_utf8.sql
create mode 100644 contrib/pg_stash_advice/stashfuncs.c
create mode 100644 doc/src/sgml/pgstashadvice.sgml
diff --git a/contrib/Makefile b/contrib/Makefile
index dd04c20acd2..7d91fe77db3 100644
--- a/contrib/Makefile
+++ b/contrib/Makefile
@@ -36,6 +36,7 @@ SUBDIRS = \
pg_overexplain \
pg_plan_advice \
pg_prewarm \
+ pg_stash_advice \
pg_stat_statements \
pg_surgery \
pg_trgm \
diff --git a/contrib/meson.build b/contrib/meson.build
index 5a752eac347..ebb7f83d8c5 100644
--- a/contrib/meson.build
+++ b/contrib/meson.build
@@ -51,6 +51,7 @@ subdir('pg_overexplain')
subdir('pg_plan_advice')
subdir('pg_prewarm')
subdir('pgrowlocks')
+subdir('pg_stash_advice')
subdir('pg_stat_statements')
subdir('pgstattuple')
subdir('pg_surgery')
diff --git a/contrib/pg_stash_advice/Makefile b/contrib/pg_stash_advice/Makefile
new file mode 100644
index 00000000000..f7670c2d4b6
--- /dev/null
+++ b/contrib/pg_stash_advice/Makefile
@@ -0,0 +1,27 @@
+# contrib/pg_stash_advice/Makefile
+
+MODULE_big = pg_stash_advice
+OBJS = \
+ $(WIN32RES) \
+ pg_stash_advice.o \
+ stashfuncs.o
+
+EXTENSION = pg_stash_advice
+DATA = pg_stash_advice--1.0.sql
+PGFILEDESC = "pg_stash_advice - store and automatically apply plan advice"
+
+REGRESS = pg_stash_advice pg_stash_advice_utf8
+EXTRA_INSTALL = contrib/pg_plan_advice
+
+ifdef USE_PGXS
+PG_CPPFLAGS = -I$(includedir_server)/extension
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+PG_CPPFLAGS = -I$(top_srcdir)/contrib/pg_plan_advice
+subdir = contrib/pg_stash_advice
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/pg_stash_advice/expected/pg_stash_advice.out b/contrib/pg_stash_advice/expected/pg_stash_advice.out
new file mode 100644
index 00000000000..788da854aa7
--- /dev/null
+++ b/contrib/pg_stash_advice/expected/pg_stash_advice.out
@@ -0,0 +1,331 @@
+CREATE EXTENSION pg_stash_advice;
+SET compute_query_id = on;
+SET max_parallel_workers_per_gather = 0;
+-- Helper: extract query identifier from EXPLAIN VERBOSE output.
+CREATE OR REPLACE FUNCTION get_query_id(query_text text) RETURNS bigint
+LANGUAGE plpgsql AS $$
+DECLARE
+ line text;
+ qid bigint;
+BEGIN
+ FOR line IN EXECUTE 'EXPLAIN (VERBOSE, FORMAT TEXT) ' || query_text
+ LOOP
+ IF line ~ 'Query Identifier:' THEN
+ qid := regexp_replace(line, '.*Query Identifier:\s*(-?\d+).*', '\1')::bigint;
+ RETURN qid;
+ END IF;
+ END LOOP;
+ RAISE EXCEPTION 'Query Identifier not found in EXPLAIN output';
+END;
+$$;
+CREATE TABLE aa_dim1 (id integer primary key, dim1 text, val1 int)
+ WITH (autovacuum_enabled = false);
+INSERT INTO aa_dim1 (id, dim1, val1)
+ SELECT g, 'some filler text ' || g, (g % 3) + 1
+ FROM generate_series(1,100) g;
+VACUUM ANALYZE aa_dim1;
+CREATE TABLE aa_dim2 (id integer primary key, dim2 text, val2 int)
+ WITH (autovacuum_enabled = false);
+INSERT INTO aa_dim2 (id, dim2, val2)
+ SELECT g, 'some filler text ' || g, (g % 7) + 1
+ FROM generate_series(1,1000) g;
+VACUUM ANALYZE aa_dim2;
+CREATE TABLE aa_fact (
+ id int primary key,
+ dim1_id integer not null references aa_dim1 (id),
+ dim2_id integer not null references aa_dim2 (id)
+) WITH (autovacuum_enabled = false);
+INSERT INTO aa_fact
+ SELECT g, (g%100)+1, (g%100)+1 FROM generate_series(1,100000) g;
+VACUUM ANALYZE aa_fact;
+-- Get the query identifier.
+SELECT get_query_id($$
+SELECT * FROM aa_fact f LEFT JOIN aa_dim1 d1 ON f.dim1_id = d1.id
+ LEFT JOIN aa_dim2 d2 ON f.dim2_id = d2.id
+ WHERE val1 = 1 AND val2 = 1;
+$$) AS qid \gset
+-- Create an advice stash and point pg_stash_advice at it.
+SELECT pg_create_advice_stash('regress_stash');
+ pg_create_advice_stash
+------------------------
+
+(1 row)
+
+SET pg_stash_advice.stash_name = 'regress_stash';
+-- Run our test query for the first time with no stashed advice.
+EXPLAIN (COSTS OFF)
+SELECT * FROM aa_fact f LEFT JOIN aa_dim1 d1 ON f.dim1_id = d1.id
+ LEFT JOIN aa_dim2 d2 ON f.dim2_id = d2.id
+ WHERE val1 = 1 AND val2 = 1;
+ QUERY PLAN
+------------------------------------------
+ Hash Join
+ Hash Cond: (f.dim1_id = d1.id)
+ -> Hash Join
+ Hash Cond: (f.dim2_id = d2.id)
+ -> Seq Scan on aa_fact f
+ -> Hash
+ -> Seq Scan on aa_dim2 d2
+ Filter: (val2 = 1)
+ -> Hash
+ -> Seq Scan on aa_dim1 d1
+ Filter: (val1 = 1)
+(11 rows)
+
+-- Force an index scan on dim1
+SELECT pg_set_stashed_advice('regress_stash', :'qid',
+ 'INDEX_SCAN(d1 aa_dim1_pkey)');
+ pg_set_stashed_advice
+-----------------------
+
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM aa_fact f
+ LEFT JOIN aa_dim1 d1 ON f.dim1_id = d1.id
+ LEFT JOIN aa_dim2 d2 ON f.dim2_id = d2.id
+ WHERE val1 = 1 AND val2 = 1;
+ QUERY PLAN
+---------------------------------------------------------
+ Hash Join
+ Hash Cond: (f.dim1_id = d1.id)
+ -> Hash Join
+ Hash Cond: (f.dim2_id = d2.id)
+ -> Seq Scan on aa_fact f
+ -> Hash
+ -> Seq Scan on aa_dim2 d2
+ Filter: (val2 = 1)
+ -> Hash
+ -> Index Scan using aa_dim1_pkey on aa_dim1 d1
+ Filter: (val1 = 1)
+ Supplied Plan Advice:
+ INDEX_SCAN(d1 aa_dim1_pkey) /* matched */
+(13 rows)
+
+-- Force an alternative join order
+SELECT pg_set_stashed_advice('regress_stash', :'qid',
+ 'join_order(f d1 d2)');
+ pg_set_stashed_advice
+-----------------------
+
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM aa_fact f
+ LEFT JOIN aa_dim1 d1 ON f.dim1_id = d1.id
+ LEFT JOIN aa_dim2 d2 ON f.dim2_id = d2.id
+ WHERE val1 = 1 AND val2 = 1;
+ QUERY PLAN
+------------------------------------------
+ Hash Join
+ Hash Cond: (f.dim2_id = d2.id)
+ -> Hash Join
+ Hash Cond: (f.dim1_id = d1.id)
+ -> Seq Scan on aa_fact f
+ -> Hash
+ -> Seq Scan on aa_dim1 d1
+ Filter: (val1 = 1)
+ -> Hash
+ -> Seq Scan on aa_dim2 d2
+ Filter: (val2 = 1)
+ Supplied Plan Advice:
+ JOIN_ORDER(f d1 d2) /* matched */
+(13 rows)
+
+-- Force an alternative join strategy
+SELECT pg_set_stashed_advice('regress_stash', :'qid',
+ 'NESTED_LOOP_PLAIN(d1)');
+ pg_set_stashed_advice
+-----------------------
+
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM aa_fact f
+ LEFT JOIN aa_dim1 d1 ON f.dim1_id = d1.id
+ LEFT JOIN aa_dim2 d2 ON f.dim2_id = d2.id
+ WHERE val1 = 1 AND val2 = 1;
+ QUERY PLAN
+---------------------------------------------------
+ Nested Loop
+ -> Hash Join
+ Hash Cond: (f.dim2_id = d2.id)
+ -> Seq Scan on aa_fact f
+ -> Hash
+ -> Seq Scan on aa_dim2 d2
+ Filter: (val2 = 1)
+ -> Index Scan using aa_dim1_pkey on aa_dim1 d1
+ Index Cond: (id = f.dim1_id)
+ Filter: (val1 = 1)
+ Supplied Plan Advice:
+ NESTED_LOOP_PLAIN(d1) /* matched */
+(12 rows)
+
+-- Add a useless extra entry to our test stash. Shouldn't change the result
+-- from the previous test.
+-- (If we're unlucky enough that this ever fails due to query ID actually
+-- being 1, then just put some other constant here. Seems unlikely.)
+SELECT pg_set_stashed_advice('regress_stash', 1, 'SEQ_SCAN(d1)');
+ pg_set_stashed_advice
+-----------------------
+
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM aa_fact f
+ LEFT JOIN aa_dim1 d1 ON f.dim1_id = d1.id
+ LEFT JOIN aa_dim2 d2 ON f.dim2_id = d2.id
+ WHERE val1 = 1 AND val2 = 1;
+ QUERY PLAN
+---------------------------------------------------
+ Nested Loop
+ -> Hash Join
+ Hash Cond: (f.dim2_id = d2.id)
+ -> Seq Scan on aa_fact f
+ -> Hash
+ -> Seq Scan on aa_dim2 d2
+ Filter: (val2 = 1)
+ -> Index Scan using aa_dim1_pkey on aa_dim1 d1
+ Index Cond: (id = f.dim1_id)
+ Filter: (val1 = 1)
+ Supplied Plan Advice:
+ NESTED_LOOP_PLAIN(d1) /* matched */
+(12 rows)
+
+-- Try an empty stash to be sure it does nothing
+SELECT pg_create_advice_stash('regress_empty_stash');
+ pg_create_advice_stash
+------------------------
+
+(1 row)
+
+SET pg_stash_advice.stash_name = 'regress_empty_stash';
+EXPLAIN (COSTS OFF) SELECT * FROM aa_fact f
+ LEFT JOIN aa_dim1 d1 ON f.dim1_id = d1.id
+ LEFT JOIN aa_dim2 d2 ON f.dim2_id = d2.id
+ WHERE val1 = 1 AND val2 = 1;
+ QUERY PLAN
+------------------------------------------
+ Hash Join
+ Hash Cond: (f.dim1_id = d1.id)
+ -> Hash Join
+ Hash Cond: (f.dim2_id = d2.id)
+ -> Seq Scan on aa_fact f
+ -> Hash
+ -> Seq Scan on aa_dim2 d2
+ Filter: (val2 = 1)
+ -> Hash
+ -> Seq Scan on aa_dim1 d1
+ Filter: (val1 = 1)
+(11 rows)
+
+-- Test that we can list each stash individually and all of them together,
+-- but not a nonexistent stash.
+SELECT * FROM pg_get_advice_stashes() ORDER BY stash_name;
+ stash_name | num_entries
+---------------------+-------------
+ regress_empty_stash | 0
+ regress_stash | 2
+(2 rows)
+
+SELECT stash_name, advice_string
+ FROM pg_get_advice_stash_contents('regress_stash') ORDER BY advice_string;
+ stash_name | advice_string
+---------------+-----------------------
+ regress_stash | NESTED_LOOP_PLAIN(d1)
+ regress_stash | SEQ_SCAN(d1)
+(2 rows)
+
+SELECT stash_name, advice_string
+ FROM pg_get_advice_stash_contents('regress_empty_stash')
+ ORDER BY advice_string;
+ stash_name | advice_string
+------------+---------------
+(0 rows)
+
+SELECT stash_name, advice_string
+ FROM pg_get_advice_stash_contents(NULL) ORDER BY advice_string;
+ stash_name | advice_string
+---------------+-----------------------
+ regress_stash | NESTED_LOOP_PLAIN(d1)
+ regress_stash | SEQ_SCAN(d1)
+(2 rows)
+
+SELECT stash_name, advice_string
+ FROM pg_get_advice_stash_contents('no_such_stash')
+ ORDER BY advice_string;
+ERROR: advice stash "no_such_stash" does not exist
+-- Test that we can remove advice.
+SELECT pg_set_stashed_advice('regress_stash', :'qid', null);
+ pg_set_stashed_advice
+-----------------------
+
+(1 row)
+
+SET pg_stash_advice.stash_name = 'regress_stash';
+EXPLAIN (COSTS OFF) SELECT * FROM aa_fact f
+ LEFT JOIN aa_dim1 d1 ON f.dim1_id = d1.id
+ LEFT JOIN aa_dim2 d2 ON f.dim2_id = d2.id
+ WHERE val1 = 1 AND val2 = 1;
+ QUERY PLAN
+------------------------------------------
+ Hash Join
+ Hash Cond: (f.dim1_id = d1.id)
+ -> Hash Join
+ Hash Cond: (f.dim2_id = d2.id)
+ -> Seq Scan on aa_fact f
+ -> Hash
+ -> Seq Scan on aa_dim2 d2
+ Filter: (val2 = 1)
+ -> Hash
+ -> Seq Scan on aa_dim1 d1
+ Filter: (val1 = 1)
+(11 rows)
+
+SELECT * FROM pg_get_advice_stashes() ORDER BY stash_name;
+ stash_name | num_entries
+---------------------+-------------
+ regress_empty_stash | 0
+ regress_stash | 1
+(2 rows)
+
+SELECT stash_name, advice_string
+ FROM pg_get_advice_stash_contents('regress_stash') ORDER BY advice_string;
+ stash_name | advice_string
+---------------+---------------
+ regress_stash | SEQ_SCAN(d1)
+(1 row)
+
+-- Can't create a stash that already exists, or drop one that doesn't.
+SELECT pg_create_advice_stash('regress_stash');
+ERROR: advice stash "regress_stash" already exists
+SELECT pg_drop_advice_stash('no_such_stash');
+ERROR: advice stash "no_such_stash" does not exist
+-- Can't add to or remove from a stash that does not exist.
+SELECT pg_set_stashed_advice('no_such_stash', 1, 'SEQ_SCAN(t)');
+ERROR: advice stash "no_such_stash" does not exist
+SELECT pg_set_stashed_advice('no_such_stash', 1, null);
+ERROR: advice stash "no_such_stash" does not exist
+-- Can't use query ID 0.
+SELECT pg_set_stashed_advice('regress_stash', 0, 'SEQ_SCAN(t)');
+ERROR: cannot set advice string for query ID 0
+-- Stash names must be non-empty, ASCII, and not too long, and must look
+-- like identifiers.
+SELECT pg_create_advice_stash('');
+ERROR: advice stash name may not be zero length
+SELECT pg_create_advice_stash('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
+ERROR: advice stash names may not be longer than 63 bytes
+SELECT pg_create_advice_stash(' ');
+ERROR: advice stash name must begin with a letter or underscore and contain only letters, digits, and underscores
+SET pg_stash_advice.stash_name = '99bottles';
+ERROR: invalid value for parameter "pg_stash_advice.stash_name": "99bottles"
+DETAIL: advice stash name must begin with a letter or underscore and contain only letters, digits, and underscores
+-- Clean up state in dynamic shared memory.
+SELECT pg_drop_advice_stash('regress_stash');
+ pg_drop_advice_stash
+----------------------
+
+(1 row)
+
+SELECT pg_drop_advice_stash('regress_empty_stash');
+ pg_drop_advice_stash
+----------------------
+
+(1 row)
+
diff --git a/contrib/pg_stash_advice/expected/pg_stash_advice_utf8.out b/contrib/pg_stash_advice/expected/pg_stash_advice_utf8.out
new file mode 100644
index 00000000000..7c532571ed5
--- /dev/null
+++ b/contrib/pg_stash_advice/expected/pg_stash_advice_utf8.out
@@ -0,0 +1,16 @@
+/*
+ * This test must be run in a database with UTF-8 encoding,
+ * because other encodings don't support all the characters used.
+ */
+SELECT getdatabaseencoding() <> 'UTF8'
+ AS skip_test \gset
+\if :skip_test
+\quit
+\endif
+SET client_encoding = utf8;
+-- Non-ASCII stash names should be rejected.
+SELECT pg_create_advice_stash('café');
+ERROR: advice stash name must not contain non-ASCII characters
+SET pg_stash_advice.stash_name = 'café';
+ERROR: invalid value for parameter "pg_stash_advice.stash_name": "café"
+DETAIL: advice stash name must not contain non-ASCII characters
diff --git a/contrib/pg_stash_advice/expected/pg_stash_advice_utf8_1.out b/contrib/pg_stash_advice/expected/pg_stash_advice_utf8_1.out
new file mode 100644
index 00000000000..37aead89c0c
--- /dev/null
+++ b/contrib/pg_stash_advice/expected/pg_stash_advice_utf8_1.out
@@ -0,0 +1,8 @@
+/*
+ * This test must be run in a database with UTF-8 encoding,
+ * because other encodings don't support all the characters used.
+ */
+SELECT getdatabaseencoding() <> 'UTF8'
+ AS skip_test \gset
+\if :skip_test
+\quit
diff --git a/contrib/pg_stash_advice/meson.build b/contrib/pg_stash_advice/meson.build
new file mode 100644
index 00000000000..8fbcfcf8693
--- /dev/null
+++ b/contrib/pg_stash_advice/meson.build
@@ -0,0 +1,37 @@
+# Copyright (c) 2022-2026, PostgreSQL Global Development Group
+
+pg_stash_advice_sources = files(
+ 'pg_stash_advice.c',
+ 'stashfuncs.c'
+)
+
+if host_system == 'windows'
+ pg_stash_advice_sources += rc_lib_gen.process(win32ver_rc, extra_args: [
+ '--NAME', 'pg_stash_advice',
+ '--FILEDESC', 'pg_stash_advice - store and automatically apply plan advice',])
+endif
+
+pg_stash_advice = shared_module('pg_stash_advice',
+ pg_stash_advice_sources,
+ include_directories: [pg_plan_advice_inc, include_directories('.')],
+ kwargs: contrib_mod_args,
+)
+contrib_targets += pg_stash_advice
+
+install_data(
+ 'pg_stash_advice--1.0.sql',
+ 'pg_stash_advice.control',
+ kwargs: contrib_data_args,
+)
+
+tests += {
+ 'name': 'pg_stash_advice',
+ 'sd': meson.current_source_dir(),
+ 'bd': meson.current_build_dir(),
+ 'regress': {
+ 'sql': [
+ 'pg_stash_advice',
+ 'pg_stash_advice_utf8',
+ ],
+ },
+}
diff --git a/contrib/pg_stash_advice/pg_stash_advice--1.0.sql b/contrib/pg_stash_advice/pg_stash_advice--1.0.sql
new file mode 100644
index 00000000000..88dedd8ef1b
--- /dev/null
+++ b/contrib/pg_stash_advice/pg_stash_advice--1.0.sql
@@ -0,0 +1,43 @@
+/* contrib/pg_stash_advice/pg_stash_advice--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION pg_stash_advice" to load this file. \quit
+
+CREATE FUNCTION pg_create_advice_stash(stash_name text)
+RETURNS void
+AS 'MODULE_PATHNAME', 'pg_create_advice_stash'
+LANGUAGE C STRICT;
+
+CREATE FUNCTION pg_drop_advice_stash(stash_name text)
+RETURNS void
+AS 'MODULE_PATHNAME', 'pg_drop_advice_stash'
+LANGUAGE C STRICT;
+
+CREATE FUNCTION pg_set_stashed_advice(stash_name text, query_id bigint,
+ advice_string text)
+RETURNS void
+AS 'MODULE_PATHNAME', 'pg_set_stashed_advice'
+LANGUAGE C;
+
+CREATE FUNCTION pg_get_advice_stashes(
+ OUT stash_name text,
+ OUT num_entries bigint
+)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'pg_get_advice_stashes'
+LANGUAGE C STRICT;
+
+CREATE FUNCTION pg_get_advice_stash_contents(
+ INOUT stash_name text,
+ OUT query_id bigint,
+ OUT advice_string text
+)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'pg_get_advice_stash_contents'
+LANGUAGE C;
+
+REVOKE ALL ON FUNCTION pg_create_advice_stash(text) FROM PUBLIC;
+REVOKE ALL ON FUNCTION pg_drop_advice_stash(text) FROM PUBLIC;
+REVOKE ALL ON FUNCTION pg_get_advice_stash_contents(text) FROM PUBLIC;
+REVOKE ALL ON FUNCTION pg_get_advice_stashes() FROM PUBLIC;
+REVOKE ALL ON FUNCTION pg_set_stashed_advice(text, bigint, text) FROM PUBLIC;
diff --git a/contrib/pg_stash_advice/pg_stash_advice.c b/contrib/pg_stash_advice/pg_stash_advice.c
new file mode 100644
index 00000000000..15e7adf849b
--- /dev/null
+++ b/contrib/pg_stash_advice/pg_stash_advice.c
@@ -0,0 +1,605 @@
+/*-------------------------------------------------------------------------
+ *
+ * pg_stash_advice.c
+ * core infrastructure for pg_stash_advice contrib module
+ *
+ * Copyright (c) 2016-2026, PostgreSQL Global Development Group
+ *
+ * contrib/pg_stash_advice/pg_stash_advice.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "common/hashfn.h"
+#include "common/string.h"
+#include "nodes/queryjumble.h"
+#include "pg_plan_advice.h"
+#include "pg_stash_advice.h"
+#include "storage/dsm_registry.h"
+#include "utils/guc.h"
+#include "utils/memutils.h"
+
+PG_MODULE_MAGIC;
+
+/* Shared memory hash table parameters */
+static dshash_parameters pgsa_stash_dshash_parameters = {
+ NAMEDATALEN,
+ sizeof(pgsa_stash),
+ dshash_strcmp,
+ dshash_strhash,
+ dshash_strcpy,
+ LWTRANCHE_INVALID /* gets set at runtime */
+};
+
+static dshash_parameters pgsa_entry_dshash_parameters = {
+ sizeof(pgsa_entry_key),
+ sizeof(pgsa_entry),
+ dshash_memcmp,
+ dshash_memhash,
+ dshash_memcpy,
+ LWTRANCHE_INVALID /* gets set at runtime */
+};
+
+/* GUC variable */
+static char *pg_stash_advice_stash_name = "";
+
+/* Shared memory pointers */
+pgsa_shared_state *pgsa_state;
+dsa_area *pgsa_dsa_area;
+dshash_table *pgsa_stash_dshash;
+dshash_table *pgsa_entry_dshash;
+
+/* Other global variables */
+static MemoryContext pg_stash_advice_mcxt;
+
+/* Function prototypes */
+static char *pgsa_advisor(PlannerGlobal *glob,
+ Query *parse,
+ const char *query_string,
+ int cursorOptions,
+ ExplainState *es);
+static bool pgsa_check_stash_name_guc(char **newval, void **extra,
+ GucSource source);
+static void pgsa_init_shared_state(void *ptr, void *arg);
+static bool pgsa_is_identifier(char *str);
+
+/* Stash name -> stash ID hash table */
+#define SH_PREFIX pgsa_stash_name_table
+#define SH_ELEMENT_TYPE pgsa_stash_name
+#define SH_KEY_TYPE uint64
+#define SH_KEY pgsa_stash_id
+#define SH_HASH_KEY(tb, key) hash_bytes((const unsigned char *) &(key), sizeof(uint64))
+#define SH_EQUAL(tb, a, b) (a == b)
+#define SH_SCOPE extern
+#define SH_DEFINE
+#include "lib/simplehash.h"
+
+/*
+ * Initialize this module.
+ */
+void
+_PG_init(void)
+{
+ void (*add_advisor_fn) (pg_plan_advice_advisor_hook hook);
+
+ /* If compute_query_id = 'auto', we would like query IDs. */
+ EnableQueryId();
+
+ /* Define our GUCs. */
+ DefineCustomStringVariable("pg_stash_advice.stash_name",
+ "Name of the advice stash to be used in this session.",
+ NULL,
+ &pg_stash_advice_stash_name,
+ "",
+ PGC_USERSET,
+ 0,
+ pgsa_check_stash_name_guc,
+ NULL,
+ NULL);
+
+ MarkGUCPrefixReserved("pg_stash_advice");
+
+ /* Tell pg_plan_advice that we want to provide advice strings. */
+ add_advisor_fn =
+ load_external_function("pg_plan_advice", "pg_plan_advice_add_advisor",
+ true, NULL);
+ (*add_advisor_fn) (pgsa_advisor);
+}
+
+/*
+ * Get the advice string that has been configured for this query, if any,
+ * and return it. Otherwise, return NULL.
+ */
+static char *
+pgsa_advisor(PlannerGlobal *glob, Query *parse,
+ const char *query_string, int cursorOptions,
+ ExplainState *es)
+{
+ pgsa_entry_key key;
+ pgsa_entry *entry;
+ char *advice_string;
+ uint64 stash_id;
+
+ /*
+ * Exit quickly if the stash name is empty or there's no query ID.
+ */
+ if (pg_stash_advice_stash_name[0] == '\0' || parse->queryId == 0)
+ return NULL;
+
+ /* Attach to dynamic shared memory if not already done. */
+ if (unlikely(pgsa_entry_dshash == NULL))
+ pgsa_attach();
+
+ /*
+ * Translate pg_stash_advice.stash_name to an integer ID.
+ *
+ * pgsa_check_stash_name_guc() has already validated the advice stash
+ * name, so we don't need to call pgsa_check_stash_name() here.
+ */
+ stash_id = pgsa_lookup_stash_id(pg_stash_advice_stash_name);
+ if (stash_id == 0)
+ return NULL;
+
+ /*
+ * Look up the advice string for the given stash ID + query ID.
+ *
+ * If we find an advice string, we copy it into the current memory
+ * context, presumably short-lived, so that we can release the lock on the
+ * dshash entry. pg_plan_advice only needs the value to remain allocated
+ * long enough for it to be parsed, so this should be good enough.
+ */
+ memset(&key, 0, sizeof(pgsa_entry_key));
+ key.pgsa_stash_id = stash_id;
+ key.queryId = parse->queryId;
+ entry = dshash_find(pgsa_entry_dshash, &key, false);
+ if (entry == NULL)
+ return NULL;
+ if (entry->advice_string == InvalidDsaPointer)
+ advice_string = NULL;
+ else
+ advice_string = pstrdup(dsa_get_address(pgsa_dsa_area,
+ entry->advice_string));
+ dshash_release_lock(pgsa_entry_dshash, entry);
+
+ /* If we found an advice string, emit a debug message. */
+ if (advice_string != NULL)
+ elog(DEBUG2, "supplying automatic advice for stash \"%s\", query ID %" PRId64 ": %s",
+ pg_stash_advice_stash_name, key.queryId, advice_string);
+
+ return advice_string;
+}
+
+/*
+ * Attach to various structures in dynamic shared memory.
+ *
+ * This function is designed to be resilient against errors. That is, if it
+ * fails partway through, it should be possible to call it again, repeat no
+ * work already completed, and potentially succeed or at least get further if
+ * whatever caused the previous failure has been corrected.
+ */
+void
+pgsa_attach(void)
+{
+ bool found;
+ MemoryContext oldcontext;
+
+ /*
+ * Create a memory context to make sure that any control structures
+ * allocated in local memory are sufficiently persistent.
+ */
+ if (pg_stash_advice_mcxt == NULL)
+ pg_stash_advice_mcxt = AllocSetContextCreate(TopMemoryContext,
+ "pg_stash_advice",
+ ALLOCSET_DEFAULT_SIZES);
+ oldcontext = MemoryContextSwitchTo(pg_stash_advice_mcxt);
+
+ /* Attach to the fixed-size state object if not already done. */
+ if (pgsa_state == NULL)
+ pgsa_state = GetNamedDSMSegment("pg_stash_advice",
+ sizeof(pgsa_shared_state),
+ pgsa_init_shared_state,
+ &found, NULL);
+
+ /* Attach to the DSA area if not already done. */
+ if (pgsa_dsa_area == NULL)
+ {
+ dsa_handle area_handle;
+
+ LWLockAcquire(&pgsa_state->lock, LW_EXCLUSIVE);
+ area_handle = pgsa_state->area;
+ if (area_handle == DSA_HANDLE_INVALID)
+ {
+ pgsa_dsa_area = dsa_create(pgsa_state->dsa_tranche);
+ dsa_pin(pgsa_dsa_area);
+ pgsa_state->area = dsa_get_handle(pgsa_dsa_area);
+ LWLockRelease(&pgsa_state->lock);
+ }
+ else
+ {
+ LWLockRelease(&pgsa_state->lock);
+ pgsa_dsa_area = dsa_attach(area_handle);
+ }
+ dsa_pin_mapping(pgsa_dsa_area);
+ }
+
+ /* Attach to the stash_name->stash_id hash table if not already done. */
+ if (pgsa_stash_dshash == NULL)
+ {
+ dshash_table_handle stash_handle;
+
+ LWLockAcquire(&pgsa_state->lock, LW_EXCLUSIVE);
+ pgsa_stash_dshash_parameters.tranche_id = pgsa_state->stash_tranche;
+ stash_handle = pgsa_state->stash_hash;
+ if (stash_handle == DSHASH_HANDLE_INVALID)
+ {
+ pgsa_stash_dshash = dshash_create(pgsa_dsa_area,
+ &pgsa_stash_dshash_parameters,
+ NULL);
+ pgsa_state->stash_hash =
+ dshash_get_hash_table_handle(pgsa_stash_dshash);
+ LWLockRelease(&pgsa_state->lock);
+ }
+ else
+ {
+ LWLockRelease(&pgsa_state->lock);
+ pgsa_stash_dshash = dshash_attach(pgsa_dsa_area,
+ &pgsa_stash_dshash_parameters,
+ stash_handle, NULL);
+ }
+ }
+
+ /* Attach to the entry hash table if not already done. */
+ if (pgsa_entry_dshash == NULL)
+ {
+ dshash_table_handle entry_handle;
+
+ LWLockAcquire(&pgsa_state->lock, LW_EXCLUSIVE);
+ pgsa_entry_dshash_parameters.tranche_id = pgsa_state->entry_tranche;
+ entry_handle = pgsa_state->entry_hash;
+ if (entry_handle == DSHASH_HANDLE_INVALID)
+ {
+ pgsa_entry_dshash = dshash_create(pgsa_dsa_area,
+ &pgsa_entry_dshash_parameters,
+ NULL);
+ pgsa_state->entry_hash =
+ dshash_get_hash_table_handle(pgsa_entry_dshash);
+ LWLockRelease(&pgsa_state->lock);
+ }
+ else
+ {
+ LWLockRelease(&pgsa_state->lock);
+ pgsa_entry_dshash = dshash_attach(pgsa_dsa_area,
+ &pgsa_entry_dshash_parameters,
+ entry_handle, NULL);
+ }
+ }
+
+ /* Restore previous memory context. */
+ MemoryContextSwitchTo(oldcontext);
+}
+
+/*
+ * Check whether an advice stash name is legal, and signal an error if not.
+ *
+ * Keep this in sync with pgsa_check_stash_name_guc, below.
+ */
+void
+pgsa_check_stash_name(char *stash_name)
+{
+ /* Reject empty advice stash name. */
+ if (stash_name[0] == '\0')
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("advice stash name may not be zero length"));
+
+ /* Reject overlong advice stash names. */
+ if (strlen(stash_name) + 1 > NAMEDATALEN)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("advice stash names may not be longer than %d bytes",
+ NAMEDATALEN - 1));
+
+ /*
+ * Reject non-ASCII advice stash names, since advice stashes are visible
+ * across all databases and the encodings of those databases might differ.
+ */
+ if (!pg_is_ascii(stash_name))
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("advice stash name must not contain non-ASCII characters"));
+
+ /*
+ * Reject things that do not look like identifiers, since the ability to
+ * create an advice stash with non-printable characters or weird symbols
+ * in the name is not likely to be useful to anyone.
+ */
+ if (!pgsa_is_identifier(stash_name))
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("advice stash name must begin with a letter or underscore and contain only letters, digits, and underscores"));
+}
+
+/*
+ * As above, but for the GUC check_hook. We allow the empty string here,
+ * though, as equivalent to disabling the feature.
+ */
+static bool
+pgsa_check_stash_name_guc(char **newval, void **extra, GucSource source)
+{
+ char *stash_name = *newval;
+
+ /* Reject overlong advice stash names. */
+ if (strlen(stash_name) + 1 > NAMEDATALEN)
+ {
+ GUC_check_errcode(ERRCODE_INVALID_PARAMETER_VALUE);
+ GUC_check_errdetail("advice stash names may not be longer than %d bytes",
+ NAMEDATALEN - 1);
+ return false;
+ }
+
+ /*
+ * Reject non-ASCII advice stash names, since advice stashes are visible
+ * across all databases and the encodings of those databases might differ.
+ */
+ if (!pg_is_ascii(stash_name))
+ {
+ GUC_check_errcode(ERRCODE_INVALID_PARAMETER_VALUE);
+ GUC_check_errdetail("advice stash name must not contain non-ASCII characters");
+ return false;
+ }
+
+ /*
+ * Reject things that do not look like identifiers, since the ability to
+ * create an advice stash with non-printable characters or weird symbols
+ * in the name is not likely to be useful to anyone.
+ */
+ if (!pgsa_is_identifier(stash_name))
+ {
+ GUC_check_errcode(ERRCODE_INVALID_PARAMETER_VALUE);
+ GUC_check_errdetail("advice stash name must begin with a letter or underscore and contain only letters, digits, and underscores");
+ return false;
+ }
+
+ return true;
+}
+
+/*
+ * Create an advice stash.
+ */
+void
+pgsa_create_stash(char *stash_name)
+{
+ pgsa_stash *stash;
+ bool found;
+
+ Assert(LWLockHeldByMeInMode(&pgsa_state->lock, LW_EXCLUSIVE));
+
+ /* Create a stash with this name, unless one already exists. */
+ stash = dshash_find_or_insert(pgsa_stash_dshash, stash_name, &found);
+ if (found)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("advice stash \"%s\" already exists", stash_name));
+ stash->pgsa_stash_id = pgsa_state->next_stash_id++;
+ dshash_release_lock(pgsa_stash_dshash, stash);
+}
+
+/*
+ * Remove any stored advice string for the given advice stash and query ID.
+ */
+void
+pgsa_clear_advice_string(char *stash_name, int64 queryId)
+{
+ pgsa_entry *entry;
+ pgsa_entry_key key;
+ uint64 stash_id;
+ dsa_pointer old_dp;
+
+ Assert(LWLockHeldByMe(&pgsa_state->lock));
+
+ /* Translate the stash name to an integer ID. */
+ if ((stash_id = pgsa_lookup_stash_id(stash_name)) == 0)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("advice stash \"%s\" does not exist", stash_name));
+
+ /*
+ * Look for an existing entry, and free it. But, be sure to save the
+ * pointer to the associated advice string, if any.
+ */
+ memset(&key, 0, sizeof(pgsa_entry_key));
+ key.pgsa_stash_id = stash_id;
+ key.queryId = queryId;
+ entry = dshash_find(pgsa_entry_dshash, &key, true);
+ if (entry == NULL)
+ old_dp = InvalidDsaPointer;
+ else
+ {
+ old_dp = entry->advice_string;
+ dshash_delete_entry(pgsa_entry_dshash, entry);
+ }
+
+ /* Now we free the advice string as well, if there was one. */
+ if (old_dp != InvalidDsaPointer)
+ dsa_free(pgsa_dsa_area, old_dp);
+}
+
+/*
+ * Drop an advice stash.
+ */
+void
+pgsa_drop_stash(char *stash_name)
+{
+ pgsa_entry *entry;
+ pgsa_stash *stash;
+ dshash_seq_status iterator;
+ uint64 stash_id;
+
+ Assert(LWLockHeldByMeInMode(&pgsa_state->lock, LW_EXCLUSIVE));
+
+ /* Remove the entry for this advice stash. */
+ stash = dshash_find(pgsa_stash_dshash, stash_name, true);
+ if (stash == NULL)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("advice stash \"%s\" does not exist", stash_name));
+ stash_id = stash->pgsa_stash_id;
+ dshash_delete_entry(pgsa_stash_dshash, stash);
+
+ /*
+ * Now remove all the entries. Since pgsa_state->lock must be held at
+ * least in shared mode to insert entries into pgsa_entry_dshash, it
+ * doesn't matter whether we do this before or after deleting the entry
+ * from pgsa_stash_dshash.
+ */
+ dshash_seq_init(&iterator, pgsa_entry_dshash, true);
+ while ((entry = dshash_seq_next(&iterator)) != NULL)
+ {
+ if (stash_id == entry->key.pgsa_stash_id)
+ {
+ if (entry->advice_string != InvalidDsaPointer)
+ dsa_free(pgsa_dsa_area, entry->advice_string);
+ dshash_delete_current(&iterator);
+ }
+ }
+ dshash_seq_term(&iterator);
+}
+
+/*
+ * Initialize shared state when first created.
+ */
+static void
+pgsa_init_shared_state(void *ptr, void *arg)
+{
+ pgsa_shared_state *state = (pgsa_shared_state *) ptr;
+
+ LWLockInitialize(&state->lock,
+ LWLockNewTrancheId("pg_stash_advice_lock"));
+ state->dsa_tranche = LWLockNewTrancheId("pg_stash_advice_dsa");
+ state->stash_tranche = LWLockNewTrancheId("pg_stash_advice_stash");
+ state->entry_tranche = LWLockNewTrancheId("pg_stash_advice_entry");
+ state->next_stash_id = UINT64CONST(1);
+ state->area = DSA_HANDLE_INVALID;
+ state->stash_hash = DSHASH_HANDLE_INVALID;
+ state->entry_hash = DSHASH_HANDLE_INVALID;
+}
+
+/*
+ * Check whether a string looks like a valid identifier. It must contain only
+ * ASCII identifier characters, and must not begin with a digit.
+ */
+static bool
+pgsa_is_identifier(char *str)
+{
+ if (*str >= '0' && *str <= '9')
+ return false;
+
+ while (*str != '\0')
+ {
+ char c = *str++;
+
+ if ((c < '0' || c > '9') && (c < 'a' || c > 'z') &&
+ (c < 'A' || c > 'Z') && c != '_')
+ return false;
+ }
+
+ return true;
+}
+
+/*
+ * Look up the integer ID that corresponds to the given stash name.
+ *
+ * Returns 0 if no such stash exists.
+ */
+uint64
+pgsa_lookup_stash_id(char *stash_name)
+{
+ pgsa_stash *stash;
+ uint64 stash_id;
+
+ /* Search the shared hash table. */
+ stash = dshash_find(pgsa_stash_dshash, stash_name, false);
+ if (stash == NULL)
+ return 0;
+ stash_id = stash->pgsa_stash_id;
+ dshash_release_lock(pgsa_stash_dshash, stash);
+
+ return stash_id;
+}
+
+/*
+ * Store a new or updated advice string for the given advice stash and query ID.
+ */
+void
+pgsa_set_advice_string(char *stash_name, int64 queryId, char *advice_string)
+{
+ pgsa_entry *entry;
+ bool found;
+ pgsa_entry_key key;
+ uint64 stash_id;
+ dsa_pointer new_dp;
+ dsa_pointer old_dp;
+
+ /*
+ * The caller must hold our lock, at least in shared mode. This is
+ * important for two reasons.
+ *
+ * First, it holds off interrupts, so that we can't bail out of this code
+ * after allocating DSA memory for the advice string and before storing
+ * the resulting pointer somewhere that others can find it.
+ *
+ * Second, we need to avoid a race against pgsa_drop_stash(). That
+ * function removes a stash_name->stash_id mapping and all the entries for
+ * that stash_id. Without the lock, there's a race condition no matter
+ * which of those things it does first, because as soon as we've looked up
+ * the stash ID, that whole function can execute before we do the rest of
+ * our work, which would result in us adding an entry for a stash that no
+ * longer exists.
+ */
+ Assert(LWLockHeldByMe(&pgsa_state->lock));
+
+ /* Look up the stash ID. */
+ if ((stash_id = pgsa_lookup_stash_id(stash_name)) == 0)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("advice stash \"%s\" does not exist", stash_name));
+
+ /* Allocate space for the advice string. */
+ new_dp = dsa_allocate(pgsa_dsa_area, strlen(advice_string) + 1);
+ strcpy(dsa_get_address(pgsa_dsa_area, new_dp), advice_string);
+
+ /* Attempt to insert an entry into the hash table. */
+ memset(&key, 0, sizeof(pgsa_entry_key));
+ key.pgsa_stash_id = stash_id;
+ key.queryId = queryId;
+ entry = dshash_find_or_insert_extended(pgsa_entry_dshash, &key, &found,
+ DSHASH_INSERT_NO_OOM);
+
+ /*
+ * If it didn't work, bail out, being careful to free the shared memory
+ * we've already allocated before, since error cleanup will not do so.
+ */
+ if (entry == NULL)
+ {
+ dsa_free(pgsa_dsa_area, new_dp);
+ ereport(ERROR,
+ errcode(ERRCODE_OUT_OF_MEMORY),
+ errmsg("out of memory"),
+ errdetail("could not insert advice string into shared hash table"));
+ }
+
+ /* Update the entry and release the lock. */
+ old_dp = found ? entry->advice_string : InvalidDsaPointer;
+ entry->advice_string = new_dp;
+ dshash_release_lock(pgsa_entry_dshash, entry);
+
+ /*
+ * We're not safe from leaks yet!
+ *
+ * There's now a pointer to new_dp in the entry that we just updated, but
+ * that means that there's no longer anything pointing to old_dp.
+ */
+ if (DsaPointerIsValid(old_dp))
+ dsa_free(pgsa_dsa_area, old_dp);
+}
diff --git a/contrib/pg_stash_advice/pg_stash_advice.control b/contrib/pg_stash_advice/pg_stash_advice.control
new file mode 100644
index 00000000000..4a0fff5c866
--- /dev/null
+++ b/contrib/pg_stash_advice/pg_stash_advice.control
@@ -0,0 +1,5 @@
+# pg_stash_advice extension
+comment = 'store and automatically apply plan advice'
+default_version = '1.0'
+module_pathname = '$libdir/pg_stash_advice'
+relocatable = true
diff --git a/contrib/pg_stash_advice/pg_stash_advice.h b/contrib/pg_stash_advice/pg_stash_advice.h
new file mode 100644
index 00000000000..eeaa61e0f37
--- /dev/null
+++ b/contrib/pg_stash_advice/pg_stash_advice.h
@@ -0,0 +1,99 @@
+/*-------------------------------------------------------------------------
+ *
+ * pg_stash_advice.h
+ * main header for pg_stash_advice contrib module
+ *
+ * This module allows plan advice strings (as used and generated by
+ * pg_plan_advice) to be "stashed" in dynamic shared memory and, from
+ * there, automatically be applied to queries as they are planned.
+ * You can create any number of advice stashes, each of which is
+ * identified by a human-readable, ASCII identifier, and each of them is
+ * essentially a query ID -> advice_string mapping.
+ *
+ * Copyright (c) 2016-2026, PostgreSQL Global Development Group
+ *
+ * contrib/pg_stash_advice/pg_stash_advice.h
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef PG_STASH_ADVICE_H
+#define PG_STASH_ADVICE_H
+
+#include "lib/dshash.h"
+#include "storage/lwlock.h"
+
+/*
+ * The key that we use to find a particular stash entry.
+ */
+typedef struct pgsa_entry_key
+{
+ uint64 pgsa_stash_id;
+ int64 queryId;
+} pgsa_entry_key;
+
+/*
+ * A single stash entry.
+ */
+typedef struct pgsa_entry
+{
+ pgsa_entry_key key;
+ dsa_pointer advice_string;
+} pgsa_entry;
+
+/*
+ * The stash itself is just a mapping from a name to a stash ID.
+ */
+typedef struct pgsa_stash
+{
+ char name[NAMEDATALEN];
+ uint64 pgsa_stash_id;
+} pgsa_stash;
+
+/*
+ * Top-level shared state object for pg_stash_advice.
+ */
+typedef struct pgsa_shared_state
+{
+ LWLock lock;
+ int dsa_tranche;
+ int stash_tranche;
+ int entry_tranche;
+ uint64 next_stash_id;
+ dsa_handle area;
+ dshash_table_handle stash_hash;
+ dshash_table_handle entry_hash;
+} pgsa_shared_state;
+
+/* For stash ID -> stash name hash table */
+typedef struct pgsa_stash_name
+{
+ uint32 status;
+ uint64 pgsa_stash_id;
+ char *name;
+} pgsa_stash_name;
+
+/* Declare stash ID -> stash name hash table */
+#define SH_PREFIX pgsa_stash_name_table
+#define SH_ELEMENT_TYPE pgsa_stash_name
+#define SH_KEY_TYPE uint64
+#define SH_SCOPE extern
+#define SH_DECLARE
+#include "lib/simplehash.h"
+
+/* Shared memory pointers */
+extern pgsa_shared_state *pgsa_state;
+extern dsa_area *pgsa_dsa_area;
+extern dshash_table *pgsa_stash_dshash;
+extern dshash_table *pgsa_entry_dshash;
+
+/* Function prototypes */
+extern void pgsa_attach(void);
+extern void pgsa_check_stash_name(char *stash_name);
+extern void pgsa_clear_advice_string(char *stash_name, int64 queryId);
+extern void pgsa_create_stash(char *stash_name);
+extern void pgsa_drop_stash(char *stash_name);
+extern uint64 pgsa_lookup_stash_id(char *stash_name);
+extern void pgsa_set_advice_string(char *stash_name, int64 queryId,
+ char *advice_string);
+
+#endif
diff --git a/contrib/pg_stash_advice/sql/pg_stash_advice.sql b/contrib/pg_stash_advice/sql/pg_stash_advice.sql
new file mode 100644
index 00000000000..f047a2d1a09
--- /dev/null
+++ b/contrib/pg_stash_advice/sql/pg_stash_advice.sql
@@ -0,0 +1,150 @@
+CREATE EXTENSION pg_stash_advice;
+SET compute_query_id = on;
+SET max_parallel_workers_per_gather = 0;
+
+-- Helper: extract query identifier from EXPLAIN VERBOSE output.
+CREATE OR REPLACE FUNCTION get_query_id(query_text text) RETURNS bigint
+LANGUAGE plpgsql AS $$
+DECLARE
+ line text;
+ qid bigint;
+BEGIN
+ FOR line IN EXECUTE 'EXPLAIN (VERBOSE, FORMAT TEXT) ' || query_text
+ LOOP
+ IF line ~ 'Query Identifier:' THEN
+ qid := regexp_replace(line, '.*Query Identifier:\s*(-?\d+).*', '\1')::bigint;
+ RETURN qid;
+ END IF;
+ END LOOP;
+ RAISE EXCEPTION 'Query Identifier not found in EXPLAIN output';
+END;
+$$;
+
+CREATE TABLE aa_dim1 (id integer primary key, dim1 text, val1 int)
+ WITH (autovacuum_enabled = false);
+INSERT INTO aa_dim1 (id, dim1, val1)
+ SELECT g, 'some filler text ' || g, (g % 3) + 1
+ FROM generate_series(1,100) g;
+VACUUM ANALYZE aa_dim1;
+
+CREATE TABLE aa_dim2 (id integer primary key, dim2 text, val2 int)
+ WITH (autovacuum_enabled = false);
+INSERT INTO aa_dim2 (id, dim2, val2)
+ SELECT g, 'some filler text ' || g, (g % 7) + 1
+ FROM generate_series(1,1000) g;
+VACUUM ANALYZE aa_dim2;
+
+CREATE TABLE aa_fact (
+ id int primary key,
+ dim1_id integer not null references aa_dim1 (id),
+ dim2_id integer not null references aa_dim2 (id)
+) WITH (autovacuum_enabled = false);
+INSERT INTO aa_fact
+ SELECT g, (g%100)+1, (g%100)+1 FROM generate_series(1,100000) g;
+VACUUM ANALYZE aa_fact;
+
+-- Get the query identifier.
+SELECT get_query_id($$
+SELECT * FROM aa_fact f LEFT JOIN aa_dim1 d1 ON f.dim1_id = d1.id
+ LEFT JOIN aa_dim2 d2 ON f.dim2_id = d2.id
+ WHERE val1 = 1 AND val2 = 1;
+$$) AS qid \gset
+
+-- Create an advice stash and point pg_stash_advice at it.
+SELECT pg_create_advice_stash('regress_stash');
+SET pg_stash_advice.stash_name = 'regress_stash';
+
+-- Run our test query for the first time with no stashed advice.
+EXPLAIN (COSTS OFF)
+SELECT * FROM aa_fact f LEFT JOIN aa_dim1 d1 ON f.dim1_id = d1.id
+ LEFT JOIN aa_dim2 d2 ON f.dim2_id = d2.id
+ WHERE val1 = 1 AND val2 = 1;
+
+-- Force an index scan on dim1
+SELECT pg_set_stashed_advice('regress_stash', :'qid',
+ 'INDEX_SCAN(d1 aa_dim1_pkey)');
+EXPLAIN (COSTS OFF) SELECT * FROM aa_fact f
+ LEFT JOIN aa_dim1 d1 ON f.dim1_id = d1.id
+ LEFT JOIN aa_dim2 d2 ON f.dim2_id = d2.id
+ WHERE val1 = 1 AND val2 = 1;
+
+-- Force an alternative join order
+SELECT pg_set_stashed_advice('regress_stash', :'qid',
+ 'join_order(f d1 d2)');
+EXPLAIN (COSTS OFF) SELECT * FROM aa_fact f
+ LEFT JOIN aa_dim1 d1 ON f.dim1_id = d1.id
+ LEFT JOIN aa_dim2 d2 ON f.dim2_id = d2.id
+ WHERE val1 = 1 AND val2 = 1;
+
+-- Force an alternative join strategy
+SELECT pg_set_stashed_advice('regress_stash', :'qid',
+ 'NESTED_LOOP_PLAIN(d1)');
+EXPLAIN (COSTS OFF) SELECT * FROM aa_fact f
+ LEFT JOIN aa_dim1 d1 ON f.dim1_id = d1.id
+ LEFT JOIN aa_dim2 d2 ON f.dim2_id = d2.id
+ WHERE val1 = 1 AND val2 = 1;
+
+-- Add a useless extra entry to our test stash. Shouldn't change the result
+-- from the previous test.
+-- (If we're unlucky enough that this ever fails due to query ID actually
+-- being 1, then just put some other constant here. Seems unlikely.)
+SELECT pg_set_stashed_advice('regress_stash', 1, 'SEQ_SCAN(d1)');
+EXPLAIN (COSTS OFF) SELECT * FROM aa_fact f
+ LEFT JOIN aa_dim1 d1 ON f.dim1_id = d1.id
+ LEFT JOIN aa_dim2 d2 ON f.dim2_id = d2.id
+ WHERE val1 = 1 AND val2 = 1;
+
+-- Try an empty stash to be sure it does nothing
+SELECT pg_create_advice_stash('regress_empty_stash');
+SET pg_stash_advice.stash_name = 'regress_empty_stash';
+EXPLAIN (COSTS OFF) SELECT * FROM aa_fact f
+ LEFT JOIN aa_dim1 d1 ON f.dim1_id = d1.id
+ LEFT JOIN aa_dim2 d2 ON f.dim2_id = d2.id
+ WHERE val1 = 1 AND val2 = 1;
+
+-- Test that we can list each stash individually and all of them together,
+-- but not a nonexistent stash.
+SELECT * FROM pg_get_advice_stashes() ORDER BY stash_name;
+SELECT stash_name, advice_string
+ FROM pg_get_advice_stash_contents('regress_stash') ORDER BY advice_string;
+SELECT stash_name, advice_string
+ FROM pg_get_advice_stash_contents('regress_empty_stash')
+ ORDER BY advice_string;
+SELECT stash_name, advice_string
+ FROM pg_get_advice_stash_contents(NULL) ORDER BY advice_string;
+SELECT stash_name, advice_string
+ FROM pg_get_advice_stash_contents('no_such_stash')
+ ORDER BY advice_string;
+
+-- Test that we can remove advice.
+SELECT pg_set_stashed_advice('regress_stash', :'qid', null);
+SET pg_stash_advice.stash_name = 'regress_stash';
+EXPLAIN (COSTS OFF) SELECT * FROM aa_fact f
+ LEFT JOIN aa_dim1 d1 ON f.dim1_id = d1.id
+ LEFT JOIN aa_dim2 d2 ON f.dim2_id = d2.id
+ WHERE val1 = 1 AND val2 = 1;
+SELECT * FROM pg_get_advice_stashes() ORDER BY stash_name;
+SELECT stash_name, advice_string
+ FROM pg_get_advice_stash_contents('regress_stash') ORDER BY advice_string;
+
+-- Can't create a stash that already exists, or drop one that doesn't.
+SELECT pg_create_advice_stash('regress_stash');
+SELECT pg_drop_advice_stash('no_such_stash');
+
+-- Can't add to or remove from a stash that does not exist.
+SELECT pg_set_stashed_advice('no_such_stash', 1, 'SEQ_SCAN(t)');
+SELECT pg_set_stashed_advice('no_such_stash', 1, null);
+
+-- Can't use query ID 0.
+SELECT pg_set_stashed_advice('regress_stash', 0, 'SEQ_SCAN(t)');
+
+-- Stash names must be non-empty, ASCII, and not too long, and must look
+-- like identifiers.
+SELECT pg_create_advice_stash('');
+SELECT pg_create_advice_stash('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
+SELECT pg_create_advice_stash(' ');
+SET pg_stash_advice.stash_name = '99bottles';
+
+-- Clean up state in dynamic shared memory.
+SELECT pg_drop_advice_stash('regress_stash');
+SELECT pg_drop_advice_stash('regress_empty_stash');
diff --git a/contrib/pg_stash_advice/sql/pg_stash_advice_utf8.sql b/contrib/pg_stash_advice/sql/pg_stash_advice_utf8.sql
new file mode 100644
index 00000000000..13ba635267f
--- /dev/null
+++ b/contrib/pg_stash_advice/sql/pg_stash_advice_utf8.sql
@@ -0,0 +1,16 @@
+/*
+ * This test must be run in a database with UTF-8 encoding,
+ * because other encodings don't support all the characters used.
+ */
+
+SELECT getdatabaseencoding() <> 'UTF8'
+ AS skip_test \gset
+\if :skip_test
+\quit
+\endif
+
+SET client_encoding = utf8;
+
+-- Non-ASCII stash names should be rejected.
+SELECT pg_create_advice_stash('café');
+SET pg_stash_advice.stash_name = 'café';
diff --git a/contrib/pg_stash_advice/stashfuncs.c b/contrib/pg_stash_advice/stashfuncs.c
new file mode 100644
index 00000000000..d8c669d6ab7
--- /dev/null
+++ b/contrib/pg_stash_advice/stashfuncs.c
@@ -0,0 +1,307 @@
+/*-------------------------------------------------------------------------
+ *
+ * stashfuncs.c
+ * SQL interface to pg_stash_advice
+ *
+ * Copyright (c) 2016-2026, PostgreSQL Global Development Group
+ *
+ * contrib/pg_stash_advice/stashfuncs.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "common/hashfn.h"
+#include "fmgr.h"
+#include "funcapi.h"
+#include "pg_stash_advice.h"
+#include "utils/builtins.h"
+#include "utils/tuplestore.h"
+
+PG_FUNCTION_INFO_V1(pg_create_advice_stash);
+PG_FUNCTION_INFO_V1(pg_drop_advice_stash);
+PG_FUNCTION_INFO_V1(pg_get_advice_stash_contents);
+PG_FUNCTION_INFO_V1(pg_get_advice_stashes);
+PG_FUNCTION_INFO_V1(pg_set_stashed_advice);
+
+typedef struct pgsa_stash_count
+{
+ uint32 status;
+ uint64 pgsa_stash_id;
+ int64 num_entries;
+} pgsa_stash_count;
+
+#define SH_PREFIX pgsa_stash_count_table
+#define SH_ELEMENT_TYPE pgsa_stash_count
+#define SH_KEY_TYPE uint64
+#define SH_KEY pgsa_stash_id
+#define SH_HASH_KEY(tb, key) hash_bytes((const unsigned char *) &(key), sizeof(uint64))
+#define SH_EQUAL(tb, a, b) (a == b)
+#define SH_SCOPE static inline
+#define SH_DEFINE
+#define SH_DECLARE
+#include "lib/simplehash.h"
+
+/*
+ * SQL-callable function to create an advice stash
+ */
+Datum
+pg_create_advice_stash(PG_FUNCTION_ARGS)
+{
+ char *stash_name = text_to_cstring(PG_GETARG_TEXT_PP(0));
+
+ pgsa_check_stash_name(stash_name);
+ if (unlikely(pgsa_entry_dshash == NULL))
+ pgsa_attach();
+ LWLockAcquire(&pgsa_state->lock, LW_EXCLUSIVE);
+ pgsa_create_stash(stash_name);
+ LWLockRelease(&pgsa_state->lock);
+ PG_RETURN_VOID();
+}
+
+/*
+ * SQL-callable function to drop an advice stash
+ */
+Datum
+pg_drop_advice_stash(PG_FUNCTION_ARGS)
+{
+ char *stash_name = text_to_cstring(PG_GETARG_TEXT_PP(0));
+
+ pgsa_check_stash_name(stash_name);
+ if (unlikely(pgsa_entry_dshash == NULL))
+ pgsa_attach();
+ LWLockAcquire(&pgsa_state->lock, LW_EXCLUSIVE);
+ pgsa_drop_stash(stash_name);
+ LWLockRelease(&pgsa_state->lock);
+ PG_RETURN_VOID();
+}
+
+/*
+ * SQL-callable function to provide a list of advice stashes
+ */
+Datum
+pg_get_advice_stashes(PG_FUNCTION_ARGS)
+{
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ dshash_seq_status iterator;
+ pgsa_entry *entry;
+ pgsa_stash *stash;
+ pgsa_stash_count_table_hash *chash;
+
+ InitMaterializedSRF(fcinfo, 0);
+
+ /* Attach to dynamic shared memory if not already done. */
+ if (unlikely(pgsa_entry_dshash == NULL))
+ pgsa_attach();
+
+ /* Tally up the number of entries per stash. */
+ chash = pgsa_stash_count_table_create(CurrentMemoryContext, 64, NULL);
+ dshash_seq_init(&iterator, pgsa_entry_dshash, true);
+ while ((entry = dshash_seq_next(&iterator)) != NULL)
+ {
+ pgsa_stash_count *c;
+ bool found;
+
+ c = pgsa_stash_count_table_insert(chash,
+ entry->key.pgsa_stash_id,
+ &found);
+ if (!found)
+ c->num_entries = 1;
+ else
+ c->num_entries++;
+ }
+ dshash_seq_term(&iterator);
+
+ /* Emit results. */
+ dshash_seq_init(&iterator, pgsa_stash_dshash, true);
+ while ((stash = dshash_seq_next(&iterator)) != NULL)
+ {
+ Datum values[2];
+ bool nulls[2];
+ pgsa_stash_count *c;
+
+ values[0] = CStringGetTextDatum(stash->name);
+ nulls[0] = false;
+
+ c = pgsa_stash_count_table_lookup(chash, stash->pgsa_stash_id);
+ values[1] = Int64GetDatum(c == NULL ? 0 : c->num_entries);
+ nulls[1] = false;
+
+ tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values,
+ nulls);
+ }
+ dshash_seq_term(&iterator);
+
+ return (Datum) 0;
+}
+
+/*
+ * SQL-callable function to provide advice stash contents
+ */
+Datum
+pg_get_advice_stash_contents(PG_FUNCTION_ARGS)
+{
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ dshash_seq_status iterator;
+ char *stash_name = NULL;
+ pgsa_stash_name_table_hash *nhash = NULL;
+ uint64 stash_id = 0;
+ pgsa_entry *entry;
+
+ InitMaterializedSRF(fcinfo, 0);
+
+ /* Attach to dynamic shared memory if not already done. */
+ if (unlikely(pgsa_entry_dshash == NULL))
+ pgsa_attach();
+
+ /* User can pass NULL for all stashes, or the name of a specific stash. */
+ if (!PG_ARGISNULL(0))
+ {
+ stash_name = text_to_cstring(PG_GETARG_TEXT_PP(0));
+ pgsa_check_stash_name(stash_name);
+ stash_id = pgsa_lookup_stash_id(stash_name);
+
+ /* If the user specified a stash name, it should exist. */
+ if (stash_id == 0)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("advice stash \"%s\" does not exist", stash_name));
+ }
+ else
+ {
+ pgsa_stash *stash;
+
+ /*
+ * If we're dumping data about all stashes, we need an ID->name lookup
+ * table.
+ */
+ nhash = pgsa_stash_name_table_create(CurrentMemoryContext, 64, NULL);
+ dshash_seq_init(&iterator, pgsa_stash_dshash, true);
+ while ((stash = dshash_seq_next(&iterator)) != NULL)
+ {
+ pgsa_stash_name *n;
+ bool found;
+
+ n = pgsa_stash_name_table_insert(nhash,
+ stash->pgsa_stash_id,
+ &found);
+ Assert(!found);
+ n->name = pstrdup(stash->name);
+ }
+ dshash_seq_term(&iterator);
+ }
+
+ /* Now iterate over all the entries. */
+ dshash_seq_init(&iterator, pgsa_entry_dshash, false);
+ while ((entry = dshash_seq_next(&iterator)) != NULL)
+ {
+ Datum values[3];
+ bool nulls[3];
+ char *this_stash_name;
+ char *advice_string;
+
+ /* Skip incomplete entries where the advice string was never set. */
+ if (entry->advice_string == InvalidDsaPointer)
+ continue;
+
+ if (stash_id != 0)
+ {
+ /*
+ * We're only dumping data for one particular stash, so skip
+ * entries for any other stash and use the stash name specified by
+ * the user.
+ */
+ if (stash_id != entry->key.pgsa_stash_id)
+ continue;
+ this_stash_name = stash_name;
+ }
+ else
+ {
+ pgsa_stash_name *n;
+
+ /*
+ * We're dumping data for all stashes, so look up the correct name
+ * to use in the hash table. If nothing is found, which is
+ * possible due to race conditions, make up a string to use.
+ */
+ n = pgsa_stash_name_table_lookup(nhash, entry->key.pgsa_stash_id);
+ if (n != NULL)
+ this_stash_name = n->name;
+ else
+ this_stash_name = psprintf("<stash %" PRIu64 ">",
+ entry->key.pgsa_stash_id);
+ }
+
+ /* Work out tuple values. */
+ values[0] = CStringGetTextDatum(this_stash_name);
+ nulls[0] = false;
+ values[1] = Int64GetDatum(entry->key.queryId);
+ nulls[1] = false;
+ advice_string = dsa_get_address(pgsa_dsa_area, entry->advice_string);
+ values[2] = CStringGetTextDatum(advice_string);
+ nulls[2] = false;
+
+ /* Emit the tuple. */
+ tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values,
+ nulls);
+ }
+ dshash_seq_term(&iterator);
+
+ return (Datum) 0;
+}
+
+/*
+ * SQL-callable function to update an advice stash entry for a particular
+ * query ID
+ *
+ * If the second argument is NULL, we delete any existing advice stash
+ * entry; otherwise, we either create an entry or update it with the new
+ * advice string.
+ */
+Datum
+pg_set_stashed_advice(PG_FUNCTION_ARGS)
+{
+ char *stash_name;
+ int64 queryId;
+
+ if (PG_ARGISNULL(0) || PG_ARGISNULL(1))
+ PG_RETURN_NULL();
+
+ /* Get and check advice stash name. */
+ stash_name = text_to_cstring(PG_GETARG_TEXT_PP(0));
+ pgsa_check_stash_name(stash_name);
+
+ /*
+ * Get and check query ID.
+ *
+ * queryID 0 means no query ID was computed, so reject that.
+ */
+ queryId = PG_GETARG_INT64(1);
+ if (queryId == 0)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("cannot set advice string for query ID 0"));
+
+ /* Attach to dynamic shared memory if not already done. */
+ if (unlikely(pgsa_entry_dshash == NULL))
+ pgsa_attach();
+
+ /* Now call the appropriate function to do the real work. */
+ if (PG_ARGISNULL(2))
+ {
+ LWLockAcquire(&pgsa_state->lock, LW_SHARED);
+ pgsa_clear_advice_string(stash_name, queryId);
+ LWLockRelease(&pgsa_state->lock);
+ }
+ else
+ {
+ char *advice_string = text_to_cstring(PG_GETARG_TEXT_PP(2));
+
+ LWLockAcquire(&pgsa_state->lock, LW_SHARED);
+ pgsa_set_advice_string(stash_name, queryId, advice_string);
+ LWLockRelease(&pgsa_state->lock);
+ }
+
+ PG_RETURN_VOID();
+}
+
diff --git a/doc/src/sgml/contrib.sgml b/doc/src/sgml/contrib.sgml
index bdd4865f53f..b9b03654aad 100644
--- a/doc/src/sgml/contrib.sgml
+++ b/doc/src/sgml/contrib.sgml
@@ -159,6 +159,7 @@ CREATE EXTENSION <replaceable>extension_name</replaceable>;
&pgplanadvice;
&pgprewarm;
&pgrowlocks;
+ &pgstashadvice;
&pgstatstatements;
&pgstattuple;
&pgsurgery;
diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml
index d90b4338d2a..e8f758fc24b 100644
--- a/doc/src/sgml/filelist.sgml
+++ b/doc/src/sgml/filelist.sgml
@@ -144,6 +144,7 @@
<!ENTITY oid2name SYSTEM "oid2name.sgml">
<!ENTITY pageinspect SYSTEM "pageinspect.sgml">
<!ENTITY passwordcheck SYSTEM "passwordcheck.sgml">
+<!ENTITY pgstashadvice SYSTEM "pgstashadvice.sgml">
<!ENTITY pgbuffercache SYSTEM "pgbuffercache.sgml">
<!ENTITY pgcrypto SYSTEM "pgcrypto.sgml">
<!ENTITY pgfreespacemap SYSTEM "pgfreespacemap.sgml">
diff --git a/doc/src/sgml/pgstashadvice.sgml b/doc/src/sgml/pgstashadvice.sgml
new file mode 100644
index 00000000000..ec60552a447
--- /dev/null
+++ b/doc/src/sgml/pgstashadvice.sgml
@@ -0,0 +1,216 @@
+<!-- doc/src/sgml/pgstashadvice.sgml -->
+
+<sect1 id="pgstashadvice" xreflabel="pg_stash_advice">
+ <title>pg_stash_advice — store and automatically apply plan advice</title>
+
+ <indexterm zone="pgstashadvice">
+ <primary>pg_stash_advice</primary>
+ </indexterm>
+
+ <para>
+ The <filename>pg_stash_advice</filename> extension allows you to stash
+ <link linkend="pgplanadvice">plan advice</link> strings in dynamic
+ shared memory where they can be automatically applied. An
+ <literal>advice stash</literal> is a mapping from
+ <link linkend="guc-compute-query-id">query identifiers</link> to plan advice
+ strings. Whenever a session is asked to plan a query whose query ID appears
+ in the relevant advice stash, the plan advice string is automatically applied
+ to guide planning. Note that advice stashes exist purely in memory. This
+ means both that it is important to be mindful of memory consumption when
+ deciding how much plan advice to stash, and also that advice stashes must
+ be recreated and repopulated whenever the server is restarted.
+ </para>
+
+ <para>
+ In order to use this module, you will need to execute
+ <literal>CREATE EXTENSION pg_stash_advice</literal> in at least
+ one database, so that you have access to the SQL functions to manage
+ advice stashes. You will also need the <literal>pg_stash_advice</literal>
+ module to be loaded in all sessions where you want this module to
+ automatically apply advice. It will usually be best to do this by adding
+ <literal>pg_stash_advice</literal> to
+ <xref linkend="guc-shared-preload-libraries"/> and restarting the server.
+ </para>
+
+ <para>
+ Once you have met the above criteria, you can create advice stashes
+ using the <literal>pg_create_advice_stash</literal> function described
+ below and set the plan advice for a given query ID in a given stash using
+ the <literal>pg_set_stashed_advice</literal> function. Then, you need
+ only configure <literal>pg_stash_advice.stash_name</literal> to point
+ to the chosen advice stash name. For some use cases, rather than setting
+ this on a system-wide basis, you may find it helpful to use
+ <literal>ALTER DATABASE ... SET</literal> or
+ <literal>ALTER ROLE ... SET</literal> to configure values that will apply
+ only to a database or only to a certain role. Likewise, it may sometimes
+ be better to set the stash name in a particular session using
+ <literal>SET</literal>.
+ </para>
+
+ <para>
+ Because <literal>pg_stash_advice</literal> works on the basis of query
+ identifiers, you will need to determine the query identifier for each query
+ whose plan you wish to control. You will also need to determine the advice
+ string that you wish to store for each query. One way to do this is to use
+ <literal>EXPLAIN</literal>: the <literal>VERBOSE</literal> option will
+ show the query ID, and the <literal>PLAN_ADVICE</literal> option will
+ show plan advice. Query identifiers can also be obtained through tools
+ such as <xref linkend="pgstatstatements" /> or
+ <xref linkend="monitoring-pg-stat-activity-view" />, but these tools
+ will not provide plan advice strings. Note that
+ <xref linkend="guc-compute-query-id" /> must be enabled for query
+ identifiers to be computed; if set to <literal>auto</literal>, loading
+ <literal>pg_stash_advice</literal> will enable it automatically.
+ </para>
+
+ <para>
+ Generally, the fact that the planner is able to change query plans as
+ the underlying distribution of data changes is a feature, not a bug.
+ Moreover, applying plan advice can have a noticeable performance cost even
+ when it does not result in a change to the query plan. Therefore, it is
+ a good idea to use this feature only when and to the extent needed.
+ Plan advice strings can be trimmed down to mention only those aspects
+ of the plan that need to be controlled, and used only for queries where
+ there is believed to be a significant risk of planner error.
+ </para>
+
+ <para>
+ Note that <literal>pg_stash_advice</literal> currently lacks a sophisticated
+ security model. Only the superuser, or a user to whom the superuser has
+ granted <literal>EXECUTE</literal> permission on the relevant functions,
+ may create advice stashes or alter their contents, but any user may set
+ <literal>pg_stash_advice.stash_name</literal> for their session, and this
+ may reveal the contents of any advice stash with that name. Users should
+ assume that information embedded in stashed advice strings may become visible
+ to nonprivileged users.
+ </para>
+
+ <sect2 id="pgstashadvice-functions">
+ <title>Functions</title>
+
+ <variablelist>
+
+ <varlistentry>
+ <term>
+ <function>pg_create_advice_stash(stash_name text) returns void</function>
+ <indexterm>
+ <primary>pg_create_advice_stash</primary>
+ </indexterm>
+ </term>
+
+ <listitem>
+ <para>
+ Creates a new, empty advice stash with the given name.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <function>pg_drop_advice_stash(stash_name text) returns void</function>
+ <indexterm>
+ <primary>pg_drop_advice_stash</primary>
+ </indexterm>
+ </term>
+
+ <listitem>
+ <para>
+ Drops the named advice stash and all of its entries.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <function>pg_set_stashed_advice(stash_name text, query_id bigint,
+ advice_string text) returns void</function>
+ <indexterm>
+ <primary>pg_set_stashed_advice</primary>
+ </indexterm>
+ </term>
+
+ <listitem>
+ <para>
+ Stores an advice string in the named advice stash, associated with
+ the given query identifier. If an entry for that query identifier
+ already exists in the stash, it is replaced. If
+ <parameter>advice_string</parameter> is <literal>NULL</literal>,
+ any existing entry for that query identifier is removed.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <function>pg_get_advice_stashes() returns setof (stash_name text,
+ num_entries bigint)</function>
+ <indexterm>
+ <primary>pg_get_advice_stashes</primary>
+ </indexterm>
+ </term>
+
+ <listitem>
+ <para>
+ Returns one row for each advice stash, showing the stash name and
+ the number of entries it contains.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <function>pg_get_advice_stash_contents(stash_name text) returns setof
+ (stash_name text, query_id bigint, advice_string text)</function>
+ <indexterm>
+ <primary>pg_get_advice_stash_contents</primary>
+ </indexterm>
+ </term>
+
+ <listitem>
+ <para>
+ Returns one row for each entry in the named advice stash. If
+ <parameter>stash_name</parameter> is <literal>NULL</literal>, returns
+ entries from all stashes.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+
+ </sect2>
+
+ <sect2 id="pgstashadvice-config-params">
+ <title>Configuration Parameters</title>
+
+ <variablelist>
+
+ <varlistentry>
+ <term>
+ <varname>pg_stash_advice.stash_name</varname> (<type>string</type>)
+ <indexterm>
+ <primary><varname>pg_stash_advice.stash_name</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+
+ <listitem>
+ <para>
+ Specifies the name of the advice stash to consult during query
+ planning. The default value is the empty string, which disables
+ this module.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+
+ </sect2>
+
+ <sect2 id="pgstashadvice-author">
+ <title>Author</title>
+
+ <para>
+ Robert Haas <email>[email protected]</email>
+ </para>
+ </sect2>
+
+</sect1>
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 5bc517602b1..7f6f79875ed 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -4058,6 +4058,12 @@ pgpa_trove_lookup_type
pgpa_trove_result
pgpa_trove_slice
pgpa_unrolled_join
+pgsa_entry
+pgsa_entry_key
+pgsa_shared_state
+pgsa_stash
+pgsa_stash_count
+pgsa_stash_name
pgsocket
pgsql_thing_t
pgssEntry
--
2.51.0
[application/octet-stream] v25-0002-pg_stash_advice-Allow-stashed-advice-to-be-persi.patch (46.2K, 3-v25-0002-pg_stash_advice-Allow-stashed-advice-to-be-persi.patch)
download | inline diff:
From 5c023afac8a5aef5f751bc5bf385f6f2e4401e73 Mon Sep 17 00:00:00 2001
From: Robert Haas <[email protected]>
Date: Thu, 2 Apr 2026 21:49:02 -0400
Subject: [PATCH v25 2/2] pg_stash_advice: Allow stashed advice to be persisted
to disk.
If pg_stash_advice.persist = true, stashed advice will be written to
pg_stash_advice.tsv in the data directory, periodically and at
shutdown. On restart, stash modifications are locked out until this
file has been reloaded, but queries will not be, so there may be a
short window after startup during which previously-stashed advice is
not automatically applied.
Author: Robert Haas <[email protected]>
Co-authored-by: <[email protected]>
---
contrib/pg_stash_advice/Makefile | 4 +-
contrib/pg_stash_advice/meson.build | 8 +-
.../pg_stash_advice/pg_stash_advice--1.0.sql | 6 +
contrib/pg_stash_advice/pg_stash_advice.c | 172 +++-
contrib/pg_stash_advice/pg_stash_advice.h | 12 +
contrib/pg_stash_advice/stashfuncs.c | 40 +
contrib/pg_stash_advice/stashpersist.c | 799 ++++++++++++++++++
contrib/pg_stash_advice/t/001_persist.pl | 84 ++
doc/src/sgml/pgstashadvice.sgml | 70 +-
src/tools/pgindent/typedefs.list | 4 +
10 files changed, 1191 insertions(+), 8 deletions(-)
create mode 100644 contrib/pg_stash_advice/stashpersist.c
create mode 100644 contrib/pg_stash_advice/t/001_persist.pl
diff --git a/contrib/pg_stash_advice/Makefile b/contrib/pg_stash_advice/Makefile
index f7670c2d4b6..470c07b9dd7 100644
--- a/contrib/pg_stash_advice/Makefile
+++ b/contrib/pg_stash_advice/Makefile
@@ -4,13 +4,15 @@ MODULE_big = pg_stash_advice
OBJS = \
$(WIN32RES) \
pg_stash_advice.o \
- stashfuncs.o
+ stashfuncs.o \
+ stashpersist.o
EXTENSION = pg_stash_advice
DATA = pg_stash_advice--1.0.sql
PGFILEDESC = "pg_stash_advice - store and automatically apply plan advice"
REGRESS = pg_stash_advice pg_stash_advice_utf8
+TAP_TESTS = 1
EXTRA_INSTALL = contrib/pg_plan_advice
ifdef USE_PGXS
diff --git a/contrib/pg_stash_advice/meson.build b/contrib/pg_stash_advice/meson.build
index 8fbcfcf8693..96f485b7729 100644
--- a/contrib/pg_stash_advice/meson.build
+++ b/contrib/pg_stash_advice/meson.build
@@ -2,7 +2,8 @@
pg_stash_advice_sources = files(
'pg_stash_advice.c',
- 'stashfuncs.c'
+ 'stashfuncs.c',
+ 'stashpersist.c'
)
if host_system == 'windows'
@@ -34,4 +35,9 @@ tests += {
'pg_stash_advice_utf8',
],
},
+ 'tap': {
+ 'tests': [
+ 't/001_persist.pl',
+ ],
+ },
}
diff --git a/contrib/pg_stash_advice/pg_stash_advice--1.0.sql b/contrib/pg_stash_advice/pg_stash_advice--1.0.sql
index 88dedd8ef1b..50f12dac313 100644
--- a/contrib/pg_stash_advice/pg_stash_advice--1.0.sql
+++ b/contrib/pg_stash_advice/pg_stash_advice--1.0.sql
@@ -36,8 +36,14 @@ RETURNS SETOF record
AS 'MODULE_PATHNAME', 'pg_get_advice_stash_contents'
LANGUAGE C;
+CREATE FUNCTION pg_start_stash_advice_worker()
+RETURNS void
+AS 'MODULE_PATHNAME', 'pg_start_stash_advice_worker'
+LANGUAGE C STRICT;
+
REVOKE ALL ON FUNCTION pg_create_advice_stash(text) FROM PUBLIC;
REVOKE ALL ON FUNCTION pg_drop_advice_stash(text) FROM PUBLIC;
REVOKE ALL ON FUNCTION pg_get_advice_stash_contents(text) FROM PUBLIC;
REVOKE ALL ON FUNCTION pg_get_advice_stashes() FROM PUBLIC;
REVOKE ALL ON FUNCTION pg_set_stashed_advice(text, bigint, text) FROM PUBLIC;
+REVOKE ALL ON FUNCTION pg_start_stash_advice_worker() FROM PUBLIC;
diff --git a/contrib/pg_stash_advice/pg_stash_advice.c b/contrib/pg_stash_advice/pg_stash_advice.c
index 15e7adf849b..1858c6a135a 100644
--- a/contrib/pg_stash_advice/pg_stash_advice.c
+++ b/contrib/pg_stash_advice/pg_stash_advice.c
@@ -13,9 +13,11 @@
#include "common/hashfn.h"
#include "common/string.h"
+#include "miscadmin.h"
#include "nodes/queryjumble.h"
#include "pg_plan_advice.h"
#include "pg_stash_advice.h"
+#include "postmaster/bgworker.h"
#include "storage/dsm_registry.h"
#include "utils/guc.h"
#include "utils/memutils.h"
@@ -41,12 +43,14 @@ static dshash_parameters pgsa_entry_dshash_parameters = {
LWTRANCHE_INVALID /* gets set at runtime */
};
-/* GUC variable */
+/* GUC variables */
static char *pg_stash_advice_stash_name = "";
+bool pg_stash_advice_persist = true;
+int pg_stash_advice_persist_interval = 30;
/* Shared memory pointers */
pgsa_shared_state *pgsa_state;
-dsa_area *pgsa_dsa_area;
+dsa_area *pgsa_dsa_area;
dshash_table *pgsa_stash_dshash;
dshash_table *pgsa_entry_dshash;
@@ -87,6 +91,33 @@ _PG_init(void)
EnableQueryId();
/* Define our GUCs. */
+ if (process_shared_preload_libraries_in_progress)
+ DefineCustomBoolVariable("pg_stash_advice.persist",
+ "Save and restore advice stash contents across restarts.",
+ NULL,
+ &pg_stash_advice_persist,
+ true,
+ PGC_POSTMASTER,
+ 0,
+ NULL,
+ NULL,
+ NULL);
+ else
+ pg_stash_advice_persist = false;
+
+ DefineCustomIntVariable("pg_stash_advice.persist_interval",
+ "Interval between advice stash saves, in seconds.",
+ NULL,
+ &pg_stash_advice_persist_interval,
+ 30,
+ 0,
+ 3600,
+ PGC_SIGHUP,
+ GUC_UNIT_S,
+ NULL,
+ NULL,
+ NULL);
+
DefineCustomStringVariable("pg_stash_advice.stash_name",
"Name of the advice stash to be used in this session.",
NULL,
@@ -100,6 +131,10 @@ _PG_init(void)
MarkGUCPrefixReserved("pg_stash_advice");
+ /* Start the background worker for persistence, if enabled. */
+ if (pg_stash_advice_persist)
+ pgsa_start_worker();
+
/* Tell pg_plan_advice that we want to provide advice strings. */
add_advisor_fn =
load_external_function("pg_plan_advice", "pg_plan_advice_add_advisor",
@@ -131,6 +166,10 @@ pgsa_advisor(PlannerGlobal *glob, Query *parse,
if (unlikely(pgsa_entry_dshash == NULL))
pgsa_attach();
+ /* If stash data is still being restored from disk, ignore. */
+ if (pg_atomic_unlocked_test_flag(&pgsa_state->stashes_ready))
+ return NULL;
+
/*
* Translate pg_stash_advice.stash_name to an integer ID.
*
@@ -279,6 +318,19 @@ pgsa_attach(void)
MemoryContextSwitchTo(oldcontext);
}
+/*
+ * Error out if the stashes have not been loaded from disk yet.
+ */
+void
+pgsa_check_lockout(void)
+{
+ if (pg_atomic_unlocked_test_flag(&pgsa_state->stashes_ready))
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("stash modifications are not allowed because \"%s\" has not been loaded yet",
+ PGSA_DUMP_FILE)));
+}
+
/*
* Check whether an advice stash name is legal, and signal an error if not.
*
@@ -383,6 +435,9 @@ pgsa_create_stash(char *stash_name)
errmsg("advice stash \"%s\" already exists", stash_name));
stash->pgsa_stash_id = pgsa_state->next_stash_id++;
dshash_release_lock(pgsa_stash_dshash, stash);
+
+ /* Bump change count. */
+ pg_atomic_add_fetch_u64(&pgsa_state->change_count, 1);
}
/*
@@ -423,6 +478,9 @@ pgsa_clear_advice_string(char *stash_name, int64 queryId)
/* Now we free the advice string as well, if there was one. */
if (old_dp != InvalidDsaPointer)
dsa_free(pgsa_dsa_area, old_dp);
+
+ /* Bump change count. */
+ pg_atomic_add_fetch_u64(&pgsa_state->change_count, 1);
}
/*
@@ -464,6 +522,43 @@ pgsa_drop_stash(char *stash_name)
}
}
dshash_seq_term(&iterator);
+
+ /* Bump change count. */
+ pg_atomic_add_fetch_u64(&pgsa_state->change_count, 1);
+}
+
+/*
+ * Remove all stashes and entries from shared memory.
+ *
+ * This is intended to be called before reloading from a dump file, so that
+ * a failed previous attempt doesn't leave stale data behind.
+ */
+void
+pgsa_reset_all_stashes(void)
+{
+ dshash_seq_status iter;
+ pgsa_entry *entry;
+
+ Assert(LWLockHeldByMeInMode(&pgsa_state->lock, LW_EXCLUSIVE));
+
+ /* Remove all stashes. */
+ dshash_seq_init(&iter, pgsa_stash_dshash, true);
+ while (dshash_seq_next(&iter) != NULL)
+ dshash_delete_current(&iter);
+ dshash_seq_term(&iter);
+
+ /* Remove all entries. */
+ dshash_seq_init(&iter, pgsa_entry_dshash, true);
+ while ((entry = dshash_seq_next(&iter)) != NULL)
+ {
+ if (entry->advice_string != InvalidDsaPointer)
+ dsa_free(pgsa_dsa_area, entry->advice_string);
+ dshash_delete_current(&iter);
+ }
+ dshash_seq_term(&iter);
+
+ /* Reset the stash ID counter. */
+ pgsa_state->next_stash_id = UINT64CONST(1);
}
/*
@@ -483,6 +578,23 @@ pgsa_init_shared_state(void *ptr, void *arg)
state->area = DSA_HANDLE_INVALID;
state->stash_hash = DSHASH_HANDLE_INVALID;
state->entry_hash = DSHASH_HANDLE_INVALID;
+ state->bgworker_pid = InvalidPid;
+ pg_atomic_init_flag(&state->stashes_ready);
+ pg_atomic_init_u64(&state->change_count, 0);
+
+ /*
+ * If this module was loaded via shared_preload_libraries, then
+ * pg_stash_advice_persist is a GUC variable. If it's true, that means
+ * that we should lock out manual stash modifications until the dump file
+ * has been successfully loaded. If it's false, there's nothing to load,
+ * so we set stashes_ready immediately.
+ *
+ * If this module was not loaded via shared_preload_libraries, then
+ * pg_stash_advice_persist is not a GUC variable, but it will be false,
+ * which leads to the correct behavior.
+ */
+ if (!pg_stash_advice_persist)
+ pg_atomic_test_set_flag(&state->stashes_ready);
}
/*
@@ -602,4 +714,60 @@ pgsa_set_advice_string(char *stash_name, int64 queryId, char *advice_string)
*/
if (DsaPointerIsValid(old_dp))
dsa_free(pgsa_dsa_area, old_dp);
+
+ /* Bump change count. */
+ pg_atomic_add_fetch_u64(&pgsa_state->change_count, 1);
+}
+
+/*
+ * Start our worker process.
+ */
+void
+pgsa_start_worker(void)
+{
+ BackgroundWorker worker = {0};
+ BackgroundWorkerHandle *handle;
+ BgwHandleStatus status;
+ pid_t pid;
+
+ worker.bgw_flags = BGWORKER_SHMEM_ACCESS;
+ worker.bgw_start_time = BgWorkerStart_ConsistentState;
+ worker.bgw_restart_time = BGW_DEFAULT_RESTART_INTERVAL;
+ strcpy(worker.bgw_library_name, "pg_stash_advice");
+ strcpy(worker.bgw_function_name, "pg_stash_advice_worker_main");
+ strcpy(worker.bgw_name, "pg_stash_advice worker");
+ strcpy(worker.bgw_type, "pg_stash_advice worker");
+
+ /*
+ * If process_shared_preload_libraries_in_progress = true, we may be in
+ * the postmaster, in which case this will really register the worker, or
+ * we may be in a child process in an EXEC_BACKEND build, in which case it
+ * will silently do nothing (which is the correct behavior).
+ */
+ if (process_shared_preload_libraries_in_progress)
+ {
+ RegisterBackgroundWorker(&worker);
+ return;
+ }
+
+ /*
+ * If process_shared_preload_libraries_in_progress = false, we're being
+ * asked to start the worker after system startup time. In other words,
+ * unless this is single-user mode, we're not in the postmaster, so we
+ * should use RegisterDynamicBackgroundWorker and then wait for startup to
+ * complete. (If we do happen to be in single-user mode, this will error
+ * out, which is fine.)
+ */
+ worker.bgw_notify_pid = MyProcPid;
+ if (!RegisterDynamicBackgroundWorker(&worker, &handle))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_RESOURCES),
+ errmsg("could not register background process"),
+ errhint("You may need to increase \"max_worker_processes\".")));
+ status = WaitForBackgroundWorkerStartup(handle, &pid);
+ if (status != BGWH_STARTED)
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_RESOURCES),
+ errmsg("could not start background process"),
+ errhint("More details may be available in the server log.")));
}
diff --git a/contrib/pg_stash_advice/pg_stash_advice.h b/contrib/pg_stash_advice/pg_stash_advice.h
index eeaa61e0f37..01aded472f3 100644
--- a/contrib/pg_stash_advice/pg_stash_advice.h
+++ b/contrib/pg_stash_advice/pg_stash_advice.h
@@ -22,6 +22,8 @@
#include "lib/dshash.h"
#include "storage/lwlock.h"
+#define PGSA_DUMP_FILE "pg_stash_advice.tsv"
+
/*
* The key that we use to find a particular stash entry.
*/
@@ -62,6 +64,9 @@ typedef struct pgsa_shared_state
dsa_handle area;
dshash_table_handle stash_hash;
dshash_table_handle entry_hash;
+ pid_t bgworker_pid;
+ pg_atomic_flag stashes_ready;
+ pg_atomic_uint64 change_count;
} pgsa_shared_state;
/* For stash ID -> stash name hash table */
@@ -86,14 +91,21 @@ extern dsa_area *pgsa_dsa_area;
extern dshash_table *pgsa_stash_dshash;
extern dshash_table *pgsa_entry_dshash;
+/* GUC variables */
+extern bool pg_stash_advice_persist;
+extern int pg_stash_advice_persist_interval;
+
/* Function prototypes */
extern void pgsa_attach(void);
+extern void pgsa_check_lockout(void);
extern void pgsa_check_stash_name(char *stash_name);
extern void pgsa_clear_advice_string(char *stash_name, int64 queryId);
extern void pgsa_create_stash(char *stash_name);
extern void pgsa_drop_stash(char *stash_name);
extern uint64 pgsa_lookup_stash_id(char *stash_name);
+extern void pgsa_reset_all_stashes(void);
extern void pgsa_set_advice_string(char *stash_name, int64 queryId,
char *advice_string);
+extern void pgsa_start_worker(void);
#endif
diff --git a/contrib/pg_stash_advice/stashfuncs.c b/contrib/pg_stash_advice/stashfuncs.c
index d8c669d6ab7..77f8e19e867 100644
--- a/contrib/pg_stash_advice/stashfuncs.c
+++ b/contrib/pg_stash_advice/stashfuncs.c
@@ -14,6 +14,7 @@
#include "common/hashfn.h"
#include "fmgr.h"
#include "funcapi.h"
+#include "miscadmin.h"
#include "pg_stash_advice.h"
#include "utils/builtins.h"
#include "utils/tuplestore.h"
@@ -23,6 +24,7 @@ PG_FUNCTION_INFO_V1(pg_drop_advice_stash);
PG_FUNCTION_INFO_V1(pg_get_advice_stash_contents);
PG_FUNCTION_INFO_V1(pg_get_advice_stashes);
PG_FUNCTION_INFO_V1(pg_set_stashed_advice);
+PG_FUNCTION_INFO_V1(pg_start_stash_advice_worker);
typedef struct pgsa_stash_count
{
@@ -53,6 +55,7 @@ pg_create_advice_stash(PG_FUNCTION_ARGS)
pgsa_check_stash_name(stash_name);
if (unlikely(pgsa_entry_dshash == NULL))
pgsa_attach();
+ pgsa_check_lockout();
LWLockAcquire(&pgsa_state->lock, LW_EXCLUSIVE);
pgsa_create_stash(stash_name);
LWLockRelease(&pgsa_state->lock);
@@ -70,6 +73,7 @@ pg_drop_advice_stash(PG_FUNCTION_ARGS)
pgsa_check_stash_name(stash_name);
if (unlikely(pgsa_entry_dshash == NULL))
pgsa_attach();
+ pgsa_check_lockout();
LWLockAcquire(&pgsa_state->lock, LW_EXCLUSIVE);
pgsa_drop_stash(stash_name);
LWLockRelease(&pgsa_state->lock);
@@ -94,6 +98,10 @@ pg_get_advice_stashes(PG_FUNCTION_ARGS)
if (unlikely(pgsa_entry_dshash == NULL))
pgsa_attach();
+ /* If stash data is still being restored from disk, ignore. */
+ if (pg_atomic_unlocked_test_flag(&pgsa_state->stashes_ready))
+ return (Datum) 0;
+
/* Tally up the number of entries per stash. */
chash = pgsa_stash_count_table_create(CurrentMemoryContext, 64, NULL);
dshash_seq_init(&iterator, pgsa_entry_dshash, true);
@@ -154,6 +162,10 @@ pg_get_advice_stash_contents(PG_FUNCTION_ARGS)
if (unlikely(pgsa_entry_dshash == NULL))
pgsa_attach();
+ /* If stash data is still being restored from disk, ignore. */
+ if (pg_atomic_unlocked_test_flag(&pgsa_state->stashes_ready))
+ return (Datum) 0;
+
/* User can pass NULL for all stashes, or the name of a specific stash. */
if (!PG_ARGISNULL(0))
{
@@ -286,6 +298,9 @@ pg_set_stashed_advice(PG_FUNCTION_ARGS)
if (unlikely(pgsa_entry_dshash == NULL))
pgsa_attach();
+ /* Don't allow writes if stash data is still being restored from disk. */
+ pgsa_check_lockout();
+
/* Now call the appropriate function to do the real work. */
if (PG_ARGISNULL(2))
{
@@ -305,3 +320,28 @@ pg_set_stashed_advice(PG_FUNCTION_ARGS)
PG_RETURN_VOID();
}
+/*
+ * SQL-callable function to start the persistence background worker.
+ */
+Datum
+pg_start_stash_advice_worker(PG_FUNCTION_ARGS)
+{
+ pid_t pid;
+
+ if (unlikely(pgsa_entry_dshash == NULL))
+ pgsa_attach();
+
+ LWLockAcquire(&pgsa_state->lock, LW_SHARED);
+ pid = pgsa_state->bgworker_pid;
+ LWLockRelease(&pgsa_state->lock);
+
+ if (pid != InvalidPid)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("pg_stash_advice worker is already running under PID %d",
+ (int) pid)));
+
+ pgsa_start_worker();
+
+ PG_RETURN_VOID();
+}
diff --git a/contrib/pg_stash_advice/stashpersist.c b/contrib/pg_stash_advice/stashpersist.c
new file mode 100644
index 00000000000..da96ee0d803
--- /dev/null
+++ b/contrib/pg_stash_advice/stashpersist.c
@@ -0,0 +1,799 @@
+/*-------------------------------------------------------------------------
+ *
+ * stashpersist.c
+ * Persistence support for pg_stash_advice.
+ *
+ * Copyright (c) 2016-2026, PostgreSQL Global Development Group
+ *
+ * contrib/pg_stash_advice/stashpersist.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include <sys/stat.h>
+
+#include "common/hashfn.h"
+#include "miscadmin.h"
+#include "pg_stash_advice.h"
+#include "postmaster/bgworker.h"
+#include "postmaster/interrupt.h"
+#include "storage/fd.h"
+#include "storage/ipc.h"
+#include "storage/latch.h"
+#include "storage/proc.h"
+#include "storage/procsignal.h"
+#include "utils/backend_status.h"
+#include "utils/guc.h"
+#include "utils/memutils.h"
+#include "utils/timestamp.h"
+
+typedef struct pgsa_writer_context
+{
+ char pathname[MAXPGPATH];
+ FILE *file;
+ pgsa_stash_name_table_hash *nhash;
+ StringInfoData buf;
+ int entries_written;
+} pgsa_writer_context;
+
+/*
+ * A parsed entry line, with pointers into the slurp buffer.
+ */
+typedef struct pgsa_saved_entry
+{
+ char *stash_name;
+ int64 queryId;
+ char *advice_string;
+} pgsa_saved_entry;
+
+/*
+ * simplehash for detecting duplicate stash names during parsing.
+ * Keyed by stash name (char *), pointing into the slurp buffer.
+ */
+typedef struct pgsa_saved_stash
+{
+ uint32 status;
+ char *name;
+} pgsa_saved_stash;
+
+#define SH_PREFIX pgsa_saved_stash_table
+#define SH_ELEMENT_TYPE pgsa_saved_stash
+#define SH_KEY_TYPE char *
+#define SH_KEY name
+#define SH_HASH_KEY(tb, key) hash_bytes((const unsigned char *) (key), strlen(key))
+#define SH_EQUAL(tb, a, b) (strcmp(a, b) == 0)
+#define SH_SCOPE static inline
+#define SH_DEFINE
+#define SH_DECLARE
+#include "lib/simplehash.h"
+
+extern PGDLLEXPORT void pg_stash_advice_worker_main(Datum main_arg);
+static void pgsa_append_tsv_escaped_string(StringInfo buf, const char *str);
+static void pgsa_detach_shmem(int code, Datum arg);
+static char *pgsa_next_tsv_field(char **cursor);
+static void pgsa_read_from_disk(void);
+static void pgsa_restore_entries(pgsa_saved_entry *entries, int num_entries);
+static void pgsa_restore_stashes(pgsa_saved_stash_table_hash *saved_stashes);
+static void pgsa_unescape_tsv_field(char *str, const char *filename,
+ unsigned lineno);
+static void pgsa_write_entries(pgsa_writer_context *wctx);
+pg_noreturn static void pgsa_write_error(pgsa_writer_context *wctx);
+static void pgsa_write_stashes(pgsa_writer_context *wctx);
+static void pgsa_write_to_disk(void);
+
+/*
+ * Background worker entry point for pg_stash_advice persistence.
+ *
+ * On startup, if load_from_disk_pending is set, we load previously saved
+ * stash data from disk. Then we enter a loop, periodically checking whether
+ * any changes have been made (via the change_count atomic counter) and
+ * writing them to disk. On shutdown, we perform a final write.
+ */
+PGDLLEXPORT void
+pg_stash_advice_worker_main(Datum main_arg)
+{
+ uint64 last_change_count;
+ TimestampTz last_write_time = 0;
+
+ /* Establish signal handlers; once that's done, unblock signals. */
+ pqsignal(SIGTERM, SignalHandlerForShutdownRequest);
+ pqsignal(SIGHUP, SignalHandlerForConfigReload);
+ pqsignal(SIGUSR1, procsignal_sigusr1_handler);
+ BackgroundWorkerUnblockSignals();
+
+ /* Log a debug message */
+ ereport(DEBUG1,
+ errmsg("pg_stash_advice worker started"));
+
+ /* Set up session user so pgstat can report it. */
+ InitializeSessionUserIdStandalone();
+
+ /* Report this worker in pg_stat_activity. */
+ pgstat_beinit();
+ pgstat_bestart_initial();
+ pgstat_bestart_final();
+
+ /* Attach to shared memory structures. */
+ pgsa_attach();
+
+ /* Set on-detach hook so that our PID will be cleared on exit. */
+ before_shmem_exit(pgsa_detach_shmem, 0);
+
+ /*
+ * Store our PID in shared memory, unless there's already another worker
+ * running, in which case just exit.
+ */
+ LWLockAcquire(&pgsa_state->lock, LW_EXCLUSIVE);
+ if (pgsa_state->bgworker_pid != InvalidPid)
+ {
+ LWLockRelease(&pgsa_state->lock);
+ ereport(LOG,
+ (errmsg("pg_stash_advice worker is already running under PID %d",
+ (int) pgsa_state->bgworker_pid)));
+ return;
+ }
+ pgsa_state->bgworker_pid = MyProcPid;
+ LWLockRelease(&pgsa_state->lock);
+
+ /*
+ * If pg_stash_advice.persist was set to true during
+ * process_shared_preload_libraries() and the data has not yet been
+ * successfully loaded, load it now.
+ */
+ if (pg_atomic_unlocked_test_flag(&pgsa_state->stashes_ready))
+ {
+ pgsa_read_from_disk();
+ pg_atomic_test_set_flag(&pgsa_state->stashes_ready);
+ }
+
+ /* Note the current change count so we can detect future changes. */
+ last_change_count = pg_atomic_read_u64(&pgsa_state->change_count);
+
+ /* Periodically write to disk until terminated. */
+ while (!ShutdownRequestPending)
+ {
+ /* In case of a SIGHUP, just reload the configuration. */
+ if (ConfigReloadPending)
+ {
+ ConfigReloadPending = false;
+ ProcessConfigFile(PGC_SIGHUP);
+ }
+
+ if (pg_stash_advice_persist_interval <= 0)
+ {
+ /* Only writing at shutdown, so just wait forever. */
+ (void) WaitLatch(MyLatch,
+ WL_LATCH_SET | WL_EXIT_ON_PM_DEATH,
+ -1L,
+ PG_WAIT_EXTENSION);
+ }
+ else
+ {
+ TimestampTz next_write_time;
+ long delay_in_ms;
+ uint64 current_change_count;
+
+ /* Compute when the next write should happen. */
+ next_write_time =
+ TimestampTzPlusMilliseconds(last_write_time,
+ pg_stash_advice_persist_interval * 1000);
+ delay_in_ms =
+ TimestampDifferenceMilliseconds(GetCurrentTimestamp(),
+ next_write_time);
+
+ /*
+ * When we reach next_write_time, we always update last_write_time
+ * (which is really the time at which we last considered writing),
+ * but we only actually write to disk if something has changed.
+ */
+ if (delay_in_ms <= 0)
+ {
+ current_change_count =
+ pg_atomic_read_u64(&pgsa_state->change_count);
+ if (current_change_count != last_change_count)
+ {
+ pgsa_write_to_disk();
+ last_change_count = current_change_count;
+ }
+ last_write_time = GetCurrentTimestamp();
+ continue;
+ }
+
+ /* Sleep until the next write time. */
+ (void) WaitLatch(MyLatch,
+ WL_LATCH_SET | WL_TIMEOUT | WL_EXIT_ON_PM_DEATH,
+ delay_in_ms,
+ PG_WAIT_EXTENSION);
+ }
+
+ ResetLatch(MyLatch);
+ }
+
+ /* Write one last time before exiting. */
+ pgsa_write_to_disk();
+}
+
+/*
+ * Clear our PID from shared memory on exit.
+ */
+static void
+pgsa_detach_shmem(int code, Datum arg)
+{
+ LWLockAcquire(&pgsa_state->lock, LW_EXCLUSIVE);
+ if (pgsa_state->bgworker_pid == MyProcPid)
+ pgsa_state->bgworker_pid = InvalidPid;
+ LWLockRelease(&pgsa_state->lock);
+}
+
+/*
+ * Load advice stash data from a dump file on disk, if there is one.
+ */
+static void
+pgsa_read_from_disk(void)
+{
+ struct stat statbuf;
+ FILE *file;
+ char *filebuf;
+ size_t nread;
+ char *p;
+ unsigned lineno;
+ pgsa_saved_stash_table_hash *saved_stashes;
+ int num_stashes = 0;
+ pgsa_saved_entry *entries;
+ int num_entries = 0;
+ int max_entries = 64;
+ MemoryContext tmpcxt;
+ MemoryContext oldcxt;
+
+ Assert(pgsa_entry_dshash != NULL);
+
+ /*
+ * Clear any existing shared memory state.
+ *
+ * Normally, there won't be any, but if this function was called before
+ * and failed after beginning to apply changes to shared memory, then we
+ * need to get rid of any entries created at that time before trying
+ * again.
+ */
+ LWLockAcquire(&pgsa_state->lock, LW_EXCLUSIVE);
+ pgsa_reset_all_stashes();
+ LWLockRelease(&pgsa_state->lock);
+
+ /* Open the dump file. If it doesn't exist, we're done. */
+ file = AllocateFile(PGSA_DUMP_FILE, "r");
+ if (!file)
+ {
+ if (errno == ENOENT)
+ return;
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not open file \"%s\": %m", PGSA_DUMP_FILE)));
+ }
+
+ /* Use a temporary context for all parse-phase allocations. */
+ tmpcxt = AllocSetContextCreate(CurrentMemoryContext,
+ "pg_stash_advice load",
+ ALLOCSET_DEFAULT_SIZES);
+ oldcxt = MemoryContextSwitchTo(tmpcxt);
+
+ /* Figure out how long the file is. */
+ if (fstat(fileno(file), &statbuf) != 0)
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not stat file \"%s\": %m", PGSA_DUMP_FILE)));
+
+ /*
+ * Slurp the entire file into memory all at once.
+ *
+ * We could avoid this by reading the file incrementally and applying
+ * changes to pgsa_stash_dshash and pgsa_entry_dshash as we go. Given the
+ * lockout mechanism implemented by stashes_ready, that shouldn't have any
+ * user-visible behavioral consequences, but it would consume shared
+ * memory to no benefit. It seems better to buffer everything in private
+ * memory first, and then only apply the changes once the file has been
+ * successfully parsed in its entirety.
+ *
+ * That also has the advantage of possibly being more future-proof: if we
+ * decide to remove the stashes_ready mechanism in the future, or say
+ * allow for multiple save files, fully validating the file before
+ * applying any changes will become much more important.
+ *
+ * Of course, this approach does have one major disadvantage, which is
+ * that we'll temporarily use about twice as much memory as we're
+ * ultimately going to need, but that seems like it shouldn't be a problem
+ * in practice. If there's so much stashed advice that parsing the disk
+ * file runs us out of memory, something has gone terribly wrong. In that
+ * situation, there probably also isn't enough free memory for the
+ * workload that the advice is attempting to manipulate to run
+ * successfully.
+ */
+ filebuf = palloc_extended(statbuf.st_size + 1, MCXT_ALLOC_HUGE);
+ nread = fread(filebuf, 1, statbuf.st_size, file);
+ if (ferror(file))
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not read file \"%s\": %m", PGSA_DUMP_FILE)));
+ FreeFile(file);
+ filebuf[nread] = '\0';
+
+ /* Initial memory allocations. */
+ saved_stashes = pgsa_saved_stash_table_create(tmpcxt, 64, NULL);
+ entries = palloc(max_entries * sizeof(pgsa_saved_entry));
+
+ /*
+ * For memory and CPU efficiency, we parse the file in place. The end of
+ * each line gets replaced with a NUL byte, and then the end of each field
+ * within a line gets the same treatment. The advice string is unescaped
+ * in place, and stash names and query IDs can't contain any special
+ * characters. All of the resulting pointers point right back into the
+ * buffer; we only need additional memory to grow the 'entries' array and
+ * the 'saved_stashes' hash table.
+ */
+ for (p = filebuf, lineno = 1; *p != '\0'; lineno++)
+ {
+ char *cursor = p;
+ char *eol;
+ char *line_type;
+
+ /* Find end of line and NUL-terminate. */
+ eol = strchr(p, '\n');
+ if (eol != NULL)
+ {
+ *eol = '\0';
+ p = eol + 1;
+ if (eol > cursor && eol[-1] == '\r')
+ eol[-1] = '\0';
+ }
+ else
+ p += strlen(p);
+
+ /* Skip empty lines. */
+ if (*cursor == '\0')
+ continue;
+
+ /* First field is the type of line, either "stash" or "entry". */
+ line_type = pgsa_next_tsv_field(&cursor);
+ if (strcmp(line_type, "stash") == 0)
+ {
+ char *name;
+ bool found;
+
+ /* Second field should be the stash name. */
+ name = pgsa_next_tsv_field(&cursor);
+ if (name == NULL || *name == '\0')
+ ereport(ERROR,
+ (errcode(ERRCODE_DATA_CORRUPTED),
+ errmsg("syntax error in file \"%s\" line %u: expected stash name",
+ PGSA_DUMP_FILE, lineno)));
+
+ /* No further fields are expected. */
+ if (*cursor != '\0')
+ ereport(ERROR,
+ (errcode(ERRCODE_DATA_CORRUPTED),
+ errmsg("syntax error in file \"%s\" line %u: expected end of line",
+ PGSA_DUMP_FILE, lineno)));
+
+ /* Duplicate check. */
+ (void) pgsa_saved_stash_table_insert(saved_stashes, name, &found);
+ if (found)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATA_CORRUPTED),
+ errmsg("syntax error in file \"%s\" line %u: duplicate stash name \"%s\"",
+ PGSA_DUMP_FILE, lineno, name)));
+ num_stashes++;
+ }
+ else if (strcmp(line_type, "entry") == 0)
+ {
+ char *stash_name;
+ char *queryid_str;
+ char *advice_str;
+ char *endptr;
+ int64 queryId;
+
+ /* Second field should be the stash name. */
+ stash_name = pgsa_next_tsv_field(&cursor);
+ if (stash_name == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATA_CORRUPTED),
+ errmsg("syntax error in file \"%s\" line %u: expected stash name",
+ PGSA_DUMP_FILE, lineno)));
+
+ /* Third field should be the query ID. */
+ queryid_str = pgsa_next_tsv_field(&cursor);
+ if (queryid_str == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATA_CORRUPTED),
+ errmsg("syntax error in file \"%s\" line %u: expected query ID",
+ PGSA_DUMP_FILE, lineno)));
+
+ /* Fourth field should be the advice string. */
+ advice_str = pgsa_next_tsv_field(&cursor);
+ if (advice_str == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATA_CORRUPTED),
+ errmsg("syntax error in file \"%s\" line %u: expected advice string",
+ PGSA_DUMP_FILE, lineno)));
+
+ /* No further fields are expected. */
+ if (*cursor != '\0')
+ ereport(ERROR,
+ (errcode(ERRCODE_DATA_CORRUPTED),
+ errmsg("syntax error in file \"%s\" line %u: expected end of line",
+ PGSA_DUMP_FILE, lineno)));
+
+ /* Make sure the stash is one we've actually seen. */
+ if (pgsa_saved_stash_table_lookup(saved_stashes,
+ stash_name) == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATA_CORRUPTED),
+ errmsg("syntax error in file \"%s\" line %u: unknown stash \"%s\"",
+ PGSA_DUMP_FILE, lineno, stash_name)));
+
+ /* Parse the query ID. */
+ errno = 0;
+ queryId = strtoll(queryid_str, &endptr, 10);
+ if (*endptr != '\0' || errno != 0 || queryid_str == endptr)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATA_CORRUPTED),
+ errmsg("syntax error in file \"%s\" line %u: invalid query ID \"%s\"",
+ PGSA_DUMP_FILE, lineno, queryid_str)));
+
+ /* Unescape the advice string. */
+ pgsa_unescape_tsv_field(advice_str, PGSA_DUMP_FILE, lineno);
+
+ /* Append to the entry array. */
+ if (num_entries >= max_entries)
+ {
+ max_entries *= 2;
+ entries = repalloc(entries,
+ max_entries * sizeof(pgsa_saved_entry));
+ }
+ entries[num_entries].stash_name = stash_name;
+ entries[num_entries].queryId = queryId;
+ entries[num_entries].advice_string = advice_str;
+ num_entries++;
+ }
+ else
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_DATA_CORRUPTED),
+ errmsg("syntax error in file \"%s\" line %u: unrecognized line type",
+ PGSA_DUMP_FILE, lineno)));
+ }
+ }
+
+ /*
+ * Parsing succeeded. Apply everything to shared memory.
+ *
+ * At this point, we know that the file we just read is fully valid, but
+ * it's still possible for this to fail if, for example, DSA memory cannot
+ * be allocated. If that happens, the worker will die, the postmaster will
+ * eventually restart it, and we'll try again after clearing any data that
+ * we did manage to put into shared memory. (Note that we call
+ * pgsa_reset_all_stashes() at the top of this function.)
+ */
+ pgsa_restore_stashes(saved_stashes);
+ pgsa_restore_entries(entries, num_entries);
+
+ /* Hooray, it worked! Notify the user. */
+ ereport(LOG,
+ (errmsg("loaded %d advice stashes and %d entries from \"%s\"",
+ num_stashes, num_entries, PGSA_DUMP_FILE)));
+
+ /* Clean up. */
+ MemoryContextSwitchTo(oldcxt);
+ MemoryContextDelete(tmpcxt);
+}
+
+/*
+ * Write all advice stash data to disk.
+ *
+ * The file format is a simple TSV with a line-type prefix:
+ * stash\tstash_name
+ * entry\tstash_name\tquery_id\tadvice_string
+ */
+static void
+pgsa_write_to_disk(void)
+{
+ pgsa_writer_context wctx = {0};
+ MemoryContext tmpcxt;
+ MemoryContext oldcxt;
+
+ Assert(pgsa_entry_dshash != NULL);
+
+ /* Use a temporary context so all allocations are freed at the end. */
+ tmpcxt = AllocSetContextCreate(CurrentMemoryContext,
+ "pg_stash_advice dump",
+ ALLOCSET_DEFAULT_SIZES);
+ oldcxt = MemoryContextSwitchTo(tmpcxt);
+
+ /* Set up the writer context. */
+ snprintf(wctx.pathname, MAXPGPATH, "%s.tmp", PGSA_DUMP_FILE);
+ wctx.file = AllocateFile(wctx.pathname, "w");
+ if (!wctx.file)
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not open file \"%s\": %m", wctx.pathname)));
+ wctx.nhash = pgsa_stash_name_table_create(tmpcxt, 64, NULL);
+ initStringInfo(&wctx.buf);
+
+ /* Write stash lines, then entry lines. */
+ pgsa_write_stashes(&wctx);
+ pgsa_write_entries(&wctx);
+
+ /*
+ * If nothing was written, remove both the temp file and any existing dump
+ * file rather than installing a zero-length file.
+ */
+ if (wctx.nhash->members == 0)
+ {
+ ereport(DEBUG1,
+ errmsg("there are no advice stashes to save"));
+ FreeFile(wctx.file);
+ unlink(wctx.pathname);
+ if (unlink(PGSA_DUMP_FILE) == 0)
+ ereport(DEBUG1,
+ errmsg("removed \"%s\"", PGSA_DUMP_FILE));
+ }
+ else
+ {
+ if (FreeFile(wctx.file) != 0)
+ {
+ int save_errno = errno;
+
+ unlink(wctx.pathname);
+ errno = save_errno;
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not close file \"%s\": %m",
+ wctx.pathname)));
+ }
+ (void) durable_rename(wctx.pathname, PGSA_DUMP_FILE, ERROR);
+
+ ereport(LOG,
+ errmsg("saved %d advice stashes and %d entries to \"%s\"",
+ (int) wctx.nhash->members, wctx.entries_written,
+ PGSA_DUMP_FILE));
+ }
+
+ MemoryContextSwitchTo(oldcxt);
+ MemoryContextDelete(tmpcxt);
+}
+
+/*
+ * Append the TSV-escaped form of str to buf.
+ *
+ * Backslash, tab, newline, and carriage return are escaped with backslash
+ * sequences. All other characters are passed through unchanged.
+ */
+static void
+pgsa_append_tsv_escaped_string(StringInfo buf, const char *str)
+{
+ for (const char *p = str; *p != '\0'; p++)
+ {
+ switch (*p)
+ {
+ case '\\':
+ appendStringInfoString(buf, "\\\\");
+ break;
+ case '\t':
+ appendStringInfoString(buf, "\\t");
+ break;
+ case '\n':
+ appendStringInfoString(buf, "\\n");
+ break;
+ case '\r':
+ appendStringInfoString(buf, "\\r");
+ break;
+ default:
+ appendStringInfoChar(buf, *p);
+ break;
+ }
+ }
+}
+
+/*
+ * Extract the next tab-delimited field from *cursor.
+ *
+ * The tab delimiter is replaced with '\0' and *cursor is advanced past it.
+ * If *cursor already points to '\0' (no more fields), returns NULL.
+ */
+static char *
+pgsa_next_tsv_field(char **cursor)
+{
+ char *start = *cursor;
+ char *p = start;
+
+ if (*p == '\0')
+ return NULL;
+
+ while (*p != '\0' && *p != '\t')
+ p++;
+
+ if (*p == '\t')
+ *p++ = '\0';
+
+ *cursor = p;
+ return start;
+}
+
+/*
+ * Insert entries into shared memory from the parsed entry array.
+ */
+static void
+pgsa_restore_entries(pgsa_saved_entry *entries, int num_entries)
+{
+ LWLockAcquire(&pgsa_state->lock, LW_SHARED);
+ for (int i = 0; i < num_entries; i++)
+ {
+ ereport(DEBUG2,
+ errmsg("restoring advice stash entry for \"%s\", query ID %" PRId64,
+ entries[i].stash_name, entries[i].queryId));
+ pgsa_set_advice_string(entries[i].stash_name,
+ entries[i].queryId,
+ entries[i].advice_string);
+ }
+ LWLockRelease(&pgsa_state->lock);
+}
+
+/*
+ * Create stashes in shared memory from the parsed stash hash table.
+ */
+static void
+pgsa_restore_stashes(pgsa_saved_stash_table_hash *saved_stashes)
+{
+ pgsa_saved_stash_table_iterator iter;
+ pgsa_saved_stash *s;
+
+ LWLockAcquire(&pgsa_state->lock, LW_EXCLUSIVE);
+ pgsa_saved_stash_table_start_iterate(saved_stashes, &iter);
+ while ((s = pgsa_saved_stash_table_iterate(saved_stashes,
+ &iter)) != NULL)
+ {
+ ereport(DEBUG2,
+ errmsg("restoring advice stash \"%s\"", s->name));
+ pgsa_create_stash(s->name);
+ }
+ LWLockRelease(&pgsa_state->lock);
+}
+
+/*
+ * Unescape a TSV field in place.
+ *
+ * Recognized escape sequences are \\, \t, \n, and \r. A trailing backslash
+ * or an unrecognized escape sequence is a syntax error.
+ */
+static void
+pgsa_unescape_tsv_field(char *str, const char *filename, unsigned lineno)
+{
+ char *src = str;
+ char *dst = str;
+
+ while (*src != '\0')
+ {
+ /* Just pass through anything that's not a backslash-escape. */
+ if (likely(*src != '\\'))
+ {
+ *dst++ = *src++;
+ continue;
+ }
+
+ /* Check what sort of escape we've got. */
+ switch (src[1])
+ {
+ case '\\':
+ *dst++ = '\\';
+ break;
+ case 't':
+ *dst++ = '\t';
+ break;
+ case 'n':
+ *dst++ = '\n';
+ break;
+ case 'r':
+ *dst++ = '\r';
+ break;
+ case '\0':
+ ereport(ERROR,
+ (errcode(ERRCODE_DATA_CORRUPTED),
+ errmsg("syntax error in file \"%s\" line %u: trailing backslash",
+ filename, lineno)));
+ break;
+ default:
+ ereport(ERROR,
+ (errcode(ERRCODE_DATA_CORRUPTED),
+ errmsg("syntax error in file \"%s\" line %u: unrecognized escape \"\\%c\"",
+ filename, lineno, src[1])));
+ break;
+ }
+
+ /* We consumed the backslash and the following character. */
+ src += 2;
+ }
+ *dst = '\0';
+}
+
+/*
+ * Write an entry line for each advice entry.
+ */
+static void
+pgsa_write_entries(pgsa_writer_context *wctx)
+{
+ dshash_seq_status iter;
+ pgsa_entry *entry;
+
+ dshash_seq_init(&iter, pgsa_entry_dshash, true);
+ while ((entry = dshash_seq_next(&iter)) != NULL)
+ {
+ pgsa_stash_name *n;
+ char *advice_string;
+
+ if (entry->advice_string == InvalidDsaPointer)
+ continue;
+
+ n = pgsa_stash_name_table_lookup(wctx->nhash,
+ entry->key.pgsa_stash_id);
+ if (n == NULL)
+ continue; /* orphan entry, skip */
+
+ advice_string = dsa_get_address(pgsa_dsa_area, entry->advice_string);
+
+ resetStringInfo(&wctx->buf);
+ appendStringInfo(&wctx->buf, "entry\t%s\t%" PRId64 "\t",
+ n->name, entry->key.queryId);
+ pgsa_append_tsv_escaped_string(&wctx->buf, advice_string);
+ appendStringInfoChar(&wctx->buf, '\n');
+ fwrite(wctx->buf.data, 1, wctx->buf.len, wctx->file);
+ if (ferror(wctx->file))
+ pgsa_write_error(wctx);
+ wctx->entries_written++;
+ }
+ dshash_seq_term(&iter);
+}
+
+/*
+ * Clean up and report a write error. Does not return.
+ */
+static void
+pgsa_write_error(pgsa_writer_context *wctx)
+{
+ int save_errno = errno;
+
+ FreeFile(wctx->file);
+ unlink(wctx->pathname);
+ errno = save_errno;
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not write to file \"%s\": %m", wctx->pathname)));
+}
+
+/*
+ * Write a stash line for each advice stash, and populate the ID-to-name
+ * hash table for use by pgsa_write_entries.
+ */
+static void
+pgsa_write_stashes(pgsa_writer_context *wctx)
+{
+ dshash_seq_status iter;
+ pgsa_stash *stash;
+
+ dshash_seq_init(&iter, pgsa_stash_dshash, true);
+ while ((stash = dshash_seq_next(&iter)) != NULL)
+ {
+ pgsa_stash_name *n;
+ bool found;
+
+ n = pgsa_stash_name_table_insert(wctx->nhash, stash->pgsa_stash_id,
+ &found);
+ Assert(!found);
+ n->name = pstrdup(stash->name);
+
+ resetStringInfo(&wctx->buf);
+ appendStringInfo(&wctx->buf, "stash\t%s\n", n->name);
+ fwrite(wctx->buf.data, 1, wctx->buf.len, wctx->file);
+ if (ferror(wctx->file))
+ pgsa_write_error(wctx);
+ }
+ dshash_seq_term(&iter);
+}
diff --git a/contrib/pg_stash_advice/t/001_persist.pl b/contrib/pg_stash_advice/t/001_persist.pl
new file mode 100644
index 00000000000..d1466166602
--- /dev/null
+++ b/contrib/pg_stash_advice/t/001_persist.pl
@@ -0,0 +1,84 @@
+
+# Copyright (c) 2016-2026, PostgreSQL Global Development Group
+
+use strict;
+use warnings FATAL => 'all';
+
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+my $node = PostgreSQL::Test::Cluster->new('main');
+
+$node->init;
+$node->append_conf(
+ 'postgresql.conf',
+ qq{shared_preload_libraries = 'pg_plan_advice, pg_stash_advice'
+pg_stash_advice.persist = true
+pg_stash_advice.persist_interval = 0});
+$node->start;
+
+$node->safe_psql("postgres",
+ "CREATE EXTENSION pg_stash_advice;\n");
+
+# Create two stashes: one with 2 entries, one with 1 entry.
+$node->safe_psql("postgres", qq{
+ SELECT pg_create_advice_stash('stash_a');
+ SELECT pg_set_stashed_advice('stash_a', 1001, 'IndexScan(t)');
+ SELECT pg_set_stashed_advice('stash_a', 1002, E'line1\\nline2\\ttab\\\\backslash');
+ SELECT pg_create_advice_stash('stash_b');
+ SELECT pg_set_stashed_advice('stash_b', 2001, 'SeqScan(t)');
+});
+
+# Verify before restart.
+my $result = $node->safe_psql("postgres",
+ "SELECT stash_name, num_entries FROM pg_get_advice_stashes() ORDER BY stash_name");
+is($result, "stash_a|2\nstash_b|1", 'stashes present before restart');
+
+# Restart and verify the data survived.
+$node->restart;
+$node->wait_for_log("loaded 2 advice stashes and 3 entries");
+
+$result = $node->safe_psql("postgres",
+ "SELECT stash_name, num_entries FROM pg_get_advice_stashes() ORDER BY stash_name");
+is($result, "stash_a|2\nstash_b|1", 'stashes survived restart');
+
+# Verify entry contents, including the one with special characters.
+$result = $node->safe_psql("postgres",
+ "SELECT stash_name, query_id, advice_string FROM pg_get_advice_stash_contents(NULL) ORDER BY stash_name, query_id");
+is($result,
+ "stash_a|1001|IndexScan(t)\nstash_a|1002|line1\nline2\ttab\\backslash\nstash_b|2001|SeqScan(t)",
+ 'entry contents survived restart with special characters intact');
+
+# Add a third stash with 0 entries.
+$node->safe_psql("postgres", qq{
+ SELECT pg_create_advice_stash('stash_c');
+});
+
+# Restart again and verify all three stashes are present.
+$node->restart;
+$node->wait_for_log("loaded 3 advice stashes and 3 entries");
+
+$result = $node->safe_psql("postgres",
+ "SELECT stash_name, num_entries FROM pg_get_advice_stashes() ORDER BY stash_name");
+is($result, "stash_a|2\nstash_b|1\nstash_c|0", 'all three stashes survived second restart');
+
+# Drop all stashes and verify the dump file is removed after restart.
+$node->safe_psql("postgres", qq{
+ SELECT pg_drop_advice_stash('stash_a');
+ SELECT pg_drop_advice_stash('stash_b');
+ SELECT pg_drop_advice_stash('stash_c');
+});
+
+$node->restart;
+
+$result = $node->safe_psql("postgres",
+ "SELECT count(*) FROM pg_get_advice_stashes()");
+is($result, "0", 'no stashes after dropping all and restarting');
+
+ok(!-f $node->data_dir . '/pg_stash_advice.tsv',
+ 'dump file removed after all stashes dropped');
+
+$node->stop;
+
+done_testing();
diff --git a/doc/src/sgml/pgstashadvice.sgml b/doc/src/sgml/pgstashadvice.sgml
index ec60552a447..810787fe814 100644
--- a/doc/src/sgml/pgstashadvice.sgml
+++ b/doc/src/sgml/pgstashadvice.sgml
@@ -15,10 +15,12 @@
<link linkend="guc-compute-query-id">query identifiers</link> to plan advice
strings. Whenever a session is asked to plan a query whose query ID appears
in the relevant advice stash, the plan advice string is automatically applied
- to guide planning. Note that advice stashes exist purely in memory. This
- means both that it is important to be mindful of memory consumption when
- deciding how much plan advice to stash, and also that advice stashes must
- be recreated and repopulated whenever the server is restarted.
+ to guide planning. Note that advice stashes are stored in dynamically
+ allocated shared memory. This means both that it is important to be mindful
+ of memory consumption when deciding how much plan advice to stash.
+ Optionally, advice stashes and their contents can automatically be persisted
+ to disk and reloaded from disk; see
+ <literal>pg_stash_advice.persist</literal>, below.
</para>
<para>
@@ -175,6 +177,28 @@
</listitem>
</varlistentry>
+ <varlistentry>
+ <term>
+ <function>pg_start_stash_advice_worker() returns void</function>
+ <indexterm>
+ <primary>pg_start_stash_advice_worker</primary>
+ </indexterm>
+ </term>
+
+ <listitem>
+ <para>
+ Starts the background worker, so that advice stash contents can be
+ automatically persisted to disk. If this module is included in
+ <xref linkend="guc-shared-preload-libraries"/> at startup time with
+ <literal>pg_stash_advice.persist = true</literal>, the worker will be
+ started automatically. When started manually, the worker will not load
+ anything from disk, but it will still persist data to disk. You can then
+ configure the server to start the worker automatically after the next
+ restart, preserving any stashed advice you add now.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</sect2>
@@ -184,6 +208,44 @@
<variablelist>
+ <varlistentry>
+ <term>
+ <varname>pg_stash_advice.persist</varname> (<type>boolean</type>)
+ <indexterm>
+ <primary><varname>pg_stash_advice.persist</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+
+ <listitem>
+ <para>
+ Controls whether the advice stashes and stash entries should be
+ persisted to disk. This is on by default. If any stashes are persisted,
+ a file named <literal>pg_stash_advice.tsv</literal> will be created in
+ the data directory. Stashes are loaded and saved using a background
+ worker process. This parameter can only be set at server start.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <varname>pg_stash_advice.persist_interval</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>pg_stash_advice.persist_interval</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+
+ <listitem>
+ <para>
+ Specifies the interval, in seconds, between checks for changes that
+ need to be written to <literal>pg_stash_advice.tsv</literal>. If set to
+ zero, changes are only written when the server shuts down. The default
+ value is <literal>30</literal>. This parameter can only be set in the
+ <filename>postgresql.conf</filename> file or on the server command line.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term>
<varname>pg_stash_advice.stash_name</varname> (<type>string</type>)
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 7f6f79875ed..c96b919d54d 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -4060,10 +4060,14 @@ pgpa_trove_slice
pgpa_unrolled_join
pgsa_entry
pgsa_entry_key
+pgsa_saved_entry
+pgsa_saved_stash
+pgsa_saved_stash_table_hash
pgsa_shared_state
pgsa_stash
pgsa_stash_count
pgsa_stash_name
+pgsa_writer_context
pgsocket
pgsql_thing_t
pgssEntry
--
2.51.0
view thread (184+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: pg_plan_advice
In-Reply-To: <CA+TgmoaeNuHXQ60P3ZZqJLrSjP3L1KYokW9kPfGbWDyt+1t=Ng@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox