2 -- Glyn Astill 02/01/2011
3 -- Function to schedule in order to keep a history of server run-time parameters
6 CREATE OR REPLACE FUNCTION public.log_pg_settings_history()
13 -- Setup 2 tables to store the last snapshot of the settings and a history of changes
14 IF NOT EXISTS (SELECT 1 FROM pg_catalog.pg_tables WHERE schemaname = 'public' AND tablename = 'pg_settings_last') THEN
15 RAISE NOTICE 'A snapshot of the last contents of pg_settings will be stored in public.pg_settings_last';
16 CREATE TABLE public.pg_settings_last AS SELECT * FROM pg_settings;
17 ALTER TABLE pg_settings_last ADD COLUMN snapshot timestamp DEFAULT current_timestamp;
19 IF EXISTS (SELECT 1 FROM pg_catalog.pg_tables WHERE schemaname = 'public' AND tablename = 'pg_settings_history') THEN
20 RAISE EXCEPTION 'Please drop or rename table public.pg_settings_history and try again';
22 RAISE NOTICE 'History of changes to pg_settings will be stored in public.pg_settings_history';
23 CREATE TABLE public.pg_settings_history (LIKE public.pg_settings_last);
24 ALTER TABLE pg_settings_history ADD COLUMN version varchar(3);
26 CREATE OR REPLACE FUNCTION public.pg_settings_history_version() RETURNS trigger AS $TRIG$
28 IF NEW.snapshot = current_timestamp THEN
35 $TRIG$ LANGUAGE plpgsql;
37 CREATE TRIGGER g_settings_history_version_trigger
38 BEFORE INSERT ON public.pg_settings_history
40 EXECUTE PROCEDURE public.pg_settings_history_version();
46 -- Select out any changes since the last execution into our history table
47 INSERT INTO public.pg_settings_history
49 SELECT * FROM pg_settings
51 SELECT name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val,
52 max_val, enumvals, boot_val, reset_val, sourcefile, sourceline
55 SELECT *, current_timestamp AS snapshot FROM pg_settings WHERE name IN (SELECT name FROM changed c)
57 SELECT * FROM pg_settings_last WHERE name IN (SELECT name FROM changed c)
58 ORDER BY name, snapshot;
60 GET DIAGNOSTICS v_changes = ROW_COUNT;
62 IF (v_changes > 0) THEN
63 -- Update the snapshot ready for the next run.
64 TRUNCATE public.pg_settings_last;
65 INSERT INTO pg_settings_last (name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val,
66 max_val, enumvals, boot_val, reset_val, sourcefile, sourceline)
67 SELECT * FROM pg_settings;
77 LANGUAGE plpgsql VOLATILE;