File: pg_track_settings--1.1.0.sql

package info (click to toggle)
pg-track-settings 2.0.1-1
  • links: PTS, VCS
  • area: main
  • in suites: bullseye
  • size: 208 kB
  • sloc: sql: 2,314; makefile: 29; sh: 2
file content (319 lines) | stat: -rw-r--r-- 10,515 bytes parent folder | download
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
-- This program is open source, licensed under the PostgreSQL License.
-- For license terms, see the LICENSE file.
--
-- Copyright (C) 2015-2020: Julien Rouhaud

-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION pg_track_settings" to load this file. \quit

SET client_encoding = 'UTF8';

CREATE TABLE pg_track_settings_list (
    name text PRIMARY KEY
);
SELECT pg_catalog.pg_extension_config_dump('pg_track_settings_list', '');

CREATE TABLE pg_track_settings_history (
    ts timestamp with time zone,
    name text NOT NULL,
    setting text,
    is_dropped boolean NOT NULL DEFAULT false,
    setting_pretty text,
    PRIMARY KEY(ts, name)
);
SELECT pg_catalog.pg_extension_config_dump('pg_track_settings_history', '');

CREATE TABLE pg_track_db_role_settings_list (
    name text,
    setdatabase oid,
    setrole oid,
    PRIMARY KEY (name, setdatabase, setrole)
);
SELECT pg_catalog.pg_extension_config_dump('pg_track_db_role_settings_list', '');

CREATE TABLE pg_track_db_role_settings_history (
    ts timestamp with time zone,
    name text,
    setdatabase oid,
    setrole oid,
    setting text,
    is_dropped boolean NOT NULL DEFAULT false,
    PRIMARY KEY(ts, name, setdatabase, setrole)
);
SELECT pg_catalog.pg_extension_config_dump('pg_track_db_role_settings_history', '');

CREATE TABLE pg_reboot (
    ts timestamp with time zone PRIMARY KEY
);
SELECT pg_catalog.pg_extension_config_dump('pg_reboot', '');

CREATE OR REPLACE FUNCTION pg_track_settings_snapshot() RETURNS boolean AS
$_$
BEGIN
    -- Handle dropped GUC
    WITH dropped AS (
        SELECT l.name
        FROM pg_track_settings_list l
        LEFT JOIN pg_settings s ON s.name = l.name
        WHERE s.name IS NULL
    ),
    mark_dropped AS (
        INSERT INTO pg_track_settings_history (ts, name, setting,
            setting_pretty, is_dropped)
        SELECT now(), name, NULL, NULL, true
        FROM dropped
    )
    DELETE FROM pg_track_settings_list l
    USING dropped d
    WHERE d.name = l.name;

    -- Insert missing settings
    INSERT INTO pg_track_settings_list (name)
    SELECT name
    FROM pg_settings s
    WHERE NOT EXISTS (SELECT 1
        FROM pg_track_settings_list l
        WHERE l.name = s.name
    );

    -- Detect changed GUC, insert new vals
    WITH last_snapshot AS (
        SELECT name, setting
        FROM (
            SELECT name, setting, row_number() OVER (PARTITION BY NAME ORDER BY ts DESC) rownum
            FROM pg_track_settings_history
        ) all_snapshots
        WHERE rownum = 1
    )
    INSERT INTO pg_track_settings_history (ts, name, setting, setting_pretty)
    SELECT now(), s.name, s.setting, pg_catalog.current_setting(s.name)
    FROM pg_settings s
    LEFT JOIN last_snapshot l ON l.name = s.name
    WHERE l.name IS NULL
    OR l.setting IS DISTINCT FROM s.setting;

    -- Handle dropped db_role_setting
    WITH rds AS (
        SELECT setdatabase, setrole,
            (regexp_split_to_array(unnest(setconfig),'=')::text[])[1] as name,
            (regexp_split_to_array(unnest(setconfig),'=')::text[])[2] as setting
        FROM pg_db_role_setting
    ),
    dropped AS (
        SELECT l.setdatabase, l.setrole, l.name
        FROM pg_track_db_role_settings_list l
        LEFT JOIN rds s ON (
            s.setdatabase = l.setdatabase
            AND s.setrole = l.setrole
            AND s.name = l.name
        )
        WHERE s.setdatabase IS NULL
            AND s.setrole IS NULL
            AND s.name IS NULL
    ),
    mark_dropped AS (
        INSERT INTO pg_track_db_role_settings_history
            (ts, setdatabase, setrole, name, setting, is_dropped)
        SELECT now(), setdatabase, setrole, name, NULL, true
        FROM dropped
    )
    DELETE FROM pg_track_db_role_settings_list l
    USING dropped d
    WHERE
        d.setdatabase = l.setdatabase
        AND d.setrole = l.setrole
        AND d.name = l.name;

    -- Insert missing settings
    WITH rds AS (
        SELECT setdatabase, setrole,
            (regexp_split_to_array(unnest(setconfig),'=')::text[])[1] as name,
            (regexp_split_to_array(unnest(setconfig),'=')::text[])[2] as setting
        FROM pg_db_role_setting
    )
    INSERT INTO pg_track_db_role_settings_list
        (setdatabase, setrole, name)
    SELECT setdatabase, setrole, name
    FROM rds s
    WHERE NOT EXISTS (SELECT 1
        FROM pg_track_db_role_settings_list l
        WHERE
            l.setdatabase = s.setdatabase
            AND l.setrole = l.setrole
            AND l.name = s.name
    );

    -- Detect changed GUC, insert new vals
    WITH rds AS (
        SELECT setdatabase, setrole,
            (regexp_split_to_array(unnest(setconfig),'=')::text[])[1] as name,
            (regexp_split_to_array(unnest(setconfig),'=')::text[])[2] as setting
        FROM pg_db_role_setting
    ),
    last_snapshot AS (
        SELECT setdatabase, setrole, name, setting
        FROM (
            SELECT setdatabase, setrole, name, setting,
                row_number() OVER (PARTITION BY name, setdatabase, setrole ORDER BY ts DESC) rownum
            FROM pg_track_db_role_settings_history
        ) all_snapshots
        WHERE rownum = 1
    )
    INSERT INTO pg_track_db_role_settings_history
        (ts, setdatabase, setrole, name, setting)
    SELECT now(), s.setdatabase, s.setrole, s.name, s.setting
    FROM rds s
    LEFT JOIN last_snapshot l ON
        l.setdatabase = s.setdatabase
        AND l.setrole = s.setrole
        AND l.name = s.name
    WHERE l.setdatabase IS NULL
        AND l.setrole IS NULL
        AND l.name IS NULL
    OR l.setting IS DISTINCT FROM s.setting;

    -- Detect is postmaster restarted since last call
    WITH last_reboot AS (
        SELECT t FROM pg_postmaster_start_time() t
    )
    INSERT INTO pg_reboot (ts)
    SELECT t FROM last_reboot lr
    WHERE NOT EXISTS (SELECT 1
        FROM pg_reboot r
        WHERE r.ts = lr.t
    );

    RETURN true;
END;
$_$
LANGUAGE plpgsql;
/* end of pg_track_settings_snapshot() */

CREATE OR REPLACE FUNCTION pg_track_settings(_ts timestamp with time zone DEFAULT now())
RETURNS TABLE (name text, setting text, setting_pretty text) AS
$_$
BEGIN
    RETURN QUERY
        SELECT s.name, s.setting, s.setting_pretty
        FROM (
            SELECT h.name, h.setting, h.setting_pretty, h.is_dropped,
            row_number() OVER (PARTITION BY h.name ORDER BY h.ts DESC) AS rownum
            FROM pg_track_settings_history h
            WHERE ts <= _ts
        ) s
        WHERE s.rownum = 1
        AND NOT s.is_dropped
        ORDER BY s.name;
END;
$_$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pg_track_db_role_settings(_ts timestamp with time zone DEFAULT now())
RETURNS TABLE (setdatabase oid, setrole oid, name text, setting text) AS
$_$
BEGIN
    RETURN QUERY
        SELECT s.setdatabase, s.setrole, s.name, s.setting
        FROM (
            SELECT h.setdatabase, h.setrole, h.name, h.setting, h.is_dropped,
                row_number() OVER (PARTITION BY h.name, h.setdatabase, h.setrole ORDER BY h.ts DESC) AS rownum
            FROM pg_track_db_role_settings_history h
            WHERE ts <= _ts
        ) s
        WHERE s.rownum = 1
        AND NOT s.is_dropped
        ORDER BY s.setdatabase, s.setrole, s.name;
END;
$_$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pg_track_settings_diff(_from timestamp with time zone, _to timestamp with time zone)
RETURNS TABLE (name text, from_setting text, from_exists boolean,
    to_setting text, to_exists boolean,
    from_setting_pretty text, to_setting_pretty text) AS
$_$
BEGIN
    RETURN QUERY
        SELECT COALESCE(s1.name, s2.name),
               s1.setting AS from_setting,
               CASE WHEN s1.setting IS NULL THEN false ELSE true END,
               s2.setting AS to_setting,
               CASE WHEN s2.setting IS NULL THEN false ELSE true END,
               s1.setting_pretty AS from_setting_pretty,
               s2.setting_pretty AS to_setting_pretty
        FROM pg_track_settings(_from) s1
        FULL OUTER JOIN pg_track_settings(_to) s2 ON s2.name = s1.name
        WHERE s1.setting IS DISTINCT FROM s2.setting
        ORDER BY 1;
END;
$_$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pg_track_db_role_settings_diff(_from timestamp with time zone, _to timestamp with time zone)
RETURNS TABLE (setdatabase oid, setrole oid, name text,
    from_setting text, from_exists boolean, to_setting text, to_exists boolean)
AS
$_$
BEGIN
    RETURN QUERY
        SELECT COALESCE(s1.setdatabase, s2.setdatabase),
               COALESCE(s1.setrole, s2.setrole),
               COALESCE(s1.name, s2.name),
               s1.setting AS from_setting,
               CASE WHEN s1.setting IS NULL THEN false ELSE true END,
               s2.setting AS to_setting,
               CASE WHEN s2.setting IS NULL THEN false ELSE true END
        FROM pg_track_db_role_settings(_from) s1
        FULL OUTER JOIN pg_track_db_role_settings(_to) s2 ON
            s2.setdatabase = s1.setdatabase
            AND s2.setrole = s1.setrole
            AND s2.name = s1.name
        WHERE
            s1.setdatabase IS DISTINCT FROM s2.setdatabase
            AND s1.setrole IS DISTINCT FROM s2.setrole
            AND s1.setting IS DISTINCT FROM s2.setting
        ORDER BY 1, 2, 3;
END;
$_$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pg_track_settings_log(_name text)
RETURNS TABLE (ts timestamp with time zone, name text, setting_exists boolean,
    setting text, setting_pretty text) AS
$_$
BEGIN
    RETURN QUERY
        SELECT h.ts, h.name, NOT h.is_dropped, h.setting, h.setting_pretty
        FROM pg_track_settings_history h
        WHERE h.name = _name
        ORDER BY ts DESC;
END;
$_$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pg_track_db_role_settings_log(_name text)
RETURNS TABLE (ts timestamp with time zone, setdatabase oid, setrole oid,
    name text, setting_exists boolean, setting text) AS
$_$
BEGIN
    RETURN QUERY
        SELECT h.ts, h.setdatabase, h.setrole, h.name, NOT h.is_dropped, h.setting
        FROM pg_track_db_role_settings_history h
        WHERE h.name = _name
        ORDER BY ts, setdatabase, setrole DESC;
END;
$_$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pg_track_settings_reset()
RETURNS void AS
$_$
BEGIN
    TRUNCATE pg_track_settings_list;
    TRUNCATE pg_track_settings_history;
    TRUNCATE pg_track_db_role_settings_list;
    TRUNCATE pg_track_db_role_settings_history;
    TRUNCATE pg_reboot;
END;
$_$
LANGUAGE plpgsql;