pg_track_settings--1.0.0.sql

-- This program is open source, licensed under the PostgreSQL License.
-- For license terms, see the LICENSE file.
--
-- Copyright (C) 2015-2024: 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,
    setting text,
    is_dropped boolean NOT NULL DEFAULT false,
    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, is_dropped)
        SELECT now(), name, 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)
    SELECT now(), s.name, s.setting
    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 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;

CREATE OR REPLACE FUNCTION pg_track_settings(_ts timestamp with time zone DEFAULT now())
RETURNS TABLE (name text, setting text) AS
$_$
BEGIN
    RETURN QUERY
        SELECT s.name, s.setting
        FROM (
            SELECT h.name, h.setting, 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 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) 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
        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) AS
$_$
BEGIN
    RETURN QUERY
        SELECT h.ts, h.name, NOT h.is_dropped, h.setting
        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;