pg_track_settings--2.1.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 UNLOGGED TABLE @extschema@.pg_track_settings_settings_src_tmp (
    srvid integer NOT NULL,
    ts timestamp with time zone NOT NULL,
    name    text NOT NULL,
    setting text,
    current_setting text
);
-- no need to backup this table

CREATE TABLE @extschema@.pg_track_settings_list (
    srvid integer NOT NULL,
    name text,
    PRIMARY KEY (srvid, name)
);
SELECT pg_catalog.pg_extension_config_dump('@extschema@.pg_track_settings_list', '');

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

CREATE UNLOGGED TABLE @extschema@.pg_track_settings_rds_src_tmp (
    srvid integer NOT NULL,
    ts timestamp with time zone NOT NULL,
    name text NOT NULL,
    setting text,
    setdatabase oid NOT NULL,
    setrole oid NOT NULL
);
-- no need to backup this table

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

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

CREATE UNLOGGED TABLE @extschema@.pg_track_settings_reboot_src_tmp (
    srvid integer NOT NULL,
    ts timestamp with time zone NOT NULL,
    postmaster_ts timestamp with time zone NOT NULL
);
-- no need to backup this table

CREATE TABLE @extschema@.pg_reboot (
    srvid integer NOT NULL,
    ts timestamp with time zone,
    PRIMARY KEY (srvid, ts)
);
SELECT pg_catalog.pg_extension_config_dump('@extschema@.pg_reboot', '');

----------------------
-- source functions --
----------------------
CREATE OR REPLACE FUNCTION @extschema@.pg_track_settings_settings_src (
    IN _srvid integer,
    OUT ts timestamp with time zone,
    OUT name text,
    OUT setting text,
    OUT current_setting text
)
RETURNS SETOF record AS $PROC$
BEGIN
    IF (_srvid = 0) THEN
        RETURN QUERY SELECT now(),
            s.name, s.setting, pg_catalog.current_setting(s.name)
        FROM pg_catalog.pg_settings s;
    ELSE
        RETURN QUERY SELECT s.ts,
            s.name, s.setting, s.current_setting
        FROM @extschema@.pg_track_settings_settings_src_tmp s;
    END IF;
END;
$PROC$ LANGUAGE plpgsql; /* end of pg_track_settings_settings_src */

CREATE OR REPLACE FUNCTION @extschema@.pg_track_settings_rds_src (
    IN _srvid integer,
    OUT ts timestamp with time zone,
    OUT name text,
    OUT setting text,
    OUT setdatabase oid,
    OUT setrole oid
)
RETURNS SETOF record AS $PROC$
BEGIN
    IF (_srvid = 0) THEN
        RETURN QUERY SELECT now(),
            (regexp_split_to_array(unnest(s.setconfig),'=')::text[])[1] AS name,
            (regexp_split_to_array(unnest(s.setconfig),'=')::text[])[2] AS setting,
            s.setdatabase, s.setrole
        FROM pg_catalog.pg_db_role_setting s;
    ELSE
        RETURN QUERY SELECT s.ts,
            s.name, s.setting, s.setdatabase, s.setrole
        FROM @extschema@.pg_track_settings_rds_src_tmp s;
    END IF;
END;
$PROC$ LANGUAGE plpgsql; /* end of pg_track_settings_rds_src */

CREATE OR REPLACE FUNCTION @extschema@.pg_track_settings_reboot_src (
    IN _srvid integer,
    OUT ts timestamp with time zone,
    OUT postmaster_ts timestamp with time zone
)
RETURNS SETOF record AS $PROC$
BEGIN
    IF (_srvid = 0) THEN
        RETURN QUERY SELECT now(),
            pg_postmaster_start_time();
    ELSE
        RETURN QUERY SELECT s.ts,
            s.postmaster_ts
        FROM @extschema@.pg_track_settings_reboot_src_tmp s;
    END IF;
END;
$PROC$ LANGUAGE plpgsql; /* end of pg_track_settings_reboot_src */

------------------------
-- snapshot functions --
------------------------
CREATE OR REPLACE FUNCTION @extschema@.pg_track_settings_snapshot_settings(_srvid integer)
    RETURNS boolean AS
$_$
DECLARE
    _snap_ts timestamp with time zone = NULL;
BEGIN
    SELECT max(ts) INTO _snap_ts
    FROM @extschema@.pg_track_settings_settings_src(_srvid);

    -- this function should have been called for previously saved data.  If
    -- not, probably somethig went wrong, so discard those data
    IF (_srvid != 0) THEN
        DELETE FROM @extschema@.pg_track_settings_settings_src_tmp
        WHERE ts != _snap_ts;
    END IF;

    -- Handle dropped GUC
    WITH src AS (
        SELECT * FROM @extschema@.pg_track_settings_settings_src(_srvid)
    ),
    dropped AS (
        SELECT s.ts, l.srvid, l.name
        FROM @extschema@.pg_track_settings_list l
        LEFT JOIN src s ON s.name = l.name
        WHERE l.srvid = _srvid
          AND s.name IS NULL
    ),
    mark_dropped AS (
        INSERT INTO @extschema@.pg_track_settings_history (srvid, ts, name, setting,
            setting_pretty, is_dropped)
        SELECT srvid, COALESCE(_snap_ts, now()), name, NULL, NULL, true
        FROM dropped
    )
    DELETE FROM @extschema@.pg_track_settings_list l
    USING dropped d
    WHERE d.name = l.name
      AND d.srvid = l.srvid
      AND l.srvid = _srvid;

    -- Insert missing settings
    INSERT INTO @extschema@.pg_track_settings_list (srvid, name)
    SELECT _srvid, name
    FROM @extschema@.pg_track_settings_settings_src(_srvid) s
    WHERE NOT EXISTS (SELECT 1
        FROM @extschema@.pg_track_settings_list l
        WHERE l.srvid = _srvid
          AND l.name = s.name
    );

    -- Detect changed GUC, insert new vals
    WITH src AS (
        SELECT * FROM @extschema@.pg_track_settings_settings_src(_srvid)
    ), last_snapshot AS (
        SELECT srvid, name, setting
        FROM (
            SELECT srvid, name, setting,
              row_number() OVER (PARTITION BY NAME ORDER BY ts DESC) AS rn
            FROM @extschema@.pg_track_settings_history h
            WHERE h.srvid = _srvid
        ) all_snapshots
        WHERE all_snapshots.rn = 1
    )
    INSERT INTO @extschema@.pg_track_settings_history
      (srvid, ts, name, setting, setting_pretty)
    SELECT _srvid, s.ts, s.name, s.setting, s.current_setting
    FROM src s
    LEFT JOIN last_snapshot l ON l.name = s.name
    WHERE (
        l.name IS NULL
        OR l.setting IS DISTINCT FROM s.setting
    );

    IF (_srvid != 0) THEN
        DELETE FROM @extschema@.pg_track_settings_settings_src_tmp
        WHERE srvid = _srvid;
    END IF;

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

CREATE OR REPLACE FUNCTION @extschema@.pg_track_settings_snapshot_rds(_srvid integer)
    RETURNS boolean AS
$_$
DECLARE
    _snap_ts timestamp with time zone;
BEGIN
    SELECT max(ts) INTO _snap_ts
    FROM @extschema@.pg_track_settings_rds_src(_srvid);

    -- this function should have been called for previously saved data.  If
    -- not, probably somethig went wrong, so discard those data
    IF (_srvid != 0) THEN
        DELETE FROM @extschema@.pg_track_settings_rds_src_tmp
        WHERE ts != _snap_ts;
    END IF;

    -- Handle dropped db_role_setting
    WITH rds AS (
        SELECT * FROM @extschema@.pg_track_settings_rds_src(_srvid)
    ),
    dropped AS (
        SELECT _snap_ts AS ts, l.setdatabase, l.setrole, l.name
        FROM @extschema@.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 l.srvid = _srvid
            AND s.setdatabase IS NULL
            AND s.setrole IS NULL
            AND s.name IS NULL
    ),
    mark_dropped AS (
        INSERT INTO @extschema@.pg_track_db_role_settings_history
            (srvid, ts, setdatabase, setrole, name, setting, is_dropped)
        SELECT _srvid, ts, d.setdatabase, d.setrole, d.name, NULL, true
        FROM dropped AS d
    )
    DELETE FROM @extschema@.pg_track_db_role_settings_list l
    USING dropped d
    WHERE
        l.srvid = _srvid
        AND d.setdatabase = l.setdatabase
        AND d.setrole = l.setrole
        AND d.name = l.name;

    -- Insert missing settings
    WITH rds AS (
        SELECT * FROM @extschema@.pg_track_settings_rds_src(_srvid)
    )
    INSERT INTO @extschema@.pg_track_db_role_settings_list
        (srvid, setdatabase, setrole, name)
    SELECT _srvid, setdatabase, setrole, name
    FROM rds s
    WHERE NOT EXISTS (SELECT 1
        FROM @extschema@.pg_track_db_role_settings_list l
        WHERE
            l.srvid = _srvid
            AND l.setdatabase = s.setdatabase
            AND l.setrole = l.setrole
            AND l.name = s.name
    );

    -- Detect changed GUC, insert new vals
    WITH rds AS (
        SELECT * FROM @extschema@.pg_track_settings_rds_src(_srvid)
    ),
    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) AS rn
            FROM @extschema@.pg_track_db_role_settings_history
            WHERE srvid = _srvid
        ) all_snapshots
        WHERE all_snapshots.rn = 1
    )
    INSERT INTO @extschema@.pg_track_db_role_settings_history
        (srvid, ts, setdatabase, setrole, name, setting)
    SELECT _srvid, s.ts, 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);

    IF (_srvid != 0) THEN
        DELETE FROM @extschema@.pg_track_settings_rds_src_tmp
        WHERE srvid = _srvid;
    END IF;

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

CREATE OR REPLACE FUNCTION @extschema@.pg_track_settings_snapshot_reboot(_srvid integer)
    RETURNS boolean AS
$_$
BEGIN
    -- Detect is postmaster restarted since last call
    WITH last_reboot AS (
        SELECT t.postmaster_ts
        FROM @extschema@.pg_track_settings_reboot_src(_srvid) t
    )
    INSERT INTO @extschema@.pg_reboot (srvid, ts)
    SELECT _srvid, lr.postmaster_ts FROM last_reboot lr
    WHERE NOT EXISTS (SELECT 1
        FROM @extschema@.pg_reboot r
        WHERE r.srvid = _srvid
        AND r.ts = lr.postmaster_ts
        AND r.srvid = _srvid
    );

    IF (_srvid != 0) THEN
        DELETE FROM @extschema@.pg_track_settings_reboot_src_tmp
        WHERE srvid = _srvid;
    END IF;

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

-- global function doing all the work for local instance, kept for backward
-- compatibility
CREATE OR REPLACE FUNCTION @extschema@.pg_track_settings_snapshot()
RETURNS boolean AS
$_$
BEGIN
    PERFORM @extschema@.pg_track_settings_snapshot_settings(0);
    PERFORM @extschema@.pg_track_settings_snapshot_rds(0);
    PERFORM @extschema@.pg_track_settings_snapshot_reboot(0);

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

CREATE OR REPLACE FUNCTION @extschema@.pg_track_settings(
    _ts timestamp with time zone DEFAULT now(),
    _srvid integer DEFAULT 0)
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 rn
            FROM @extschema@.pg_track_settings_history h
            WHERE h.srvid = _srvid
            AND h.ts <= _ts
        ) s
        WHERE s.rn = 1
        AND NOT s.is_dropped
        ORDER BY s.name;
END;
$_$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION @extschema@.pg_track_db_role_settings(
    _ts timestamp with time zone DEFAULT now(),
    _srvid integer DEFAULT 0)
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 rn
            FROM @extschema@.pg_track_db_role_settings_history h
            WHERE h.srvid = _srvid
            AND h.ts <= _ts
        ) s
        WHERE s.rn = 1
        AND NOT s.is_dropped
        ORDER BY s.setdatabase, s.setrole, s.name;
END;
$_$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION @extschema@.pg_track_settings_diff(
    _from timestamp with time zone,
    _to timestamp with time zone,
    _srvid integer DEFAULT 0)
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 @extschema@.pg_track_settings(_from, _srvid) s1
        FULL OUTER JOIN @extschema@.pg_track_settings(_to, _srvid) s2 ON s2.name = s1.name
        WHERE s1.setting IS DISTINCT FROM s2.setting
        ORDER BY 1;
END;
$_$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION @extschema@.pg_track_db_role_settings_diff(
    _from timestamp with time zone,
    _to timestamp with time zone,
    _srvid integer DEFAULT 0)
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 @extschema@.pg_track_db_role_settings(_from, _srvid) s1
        FULL OUTER JOIN @extschema@.pg_track_db_role_settings(_to, _srvid) 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 @extschema@.pg_track_settings_log(
    _name text,
    _srvid integer DEFAULT 0)
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 @extschema@.pg_track_settings_history h
        WHERE h.srvid = _srvid
        AND h.name = _name
        ORDER BY ts DESC;
END;
$_$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION @extschema@.pg_track_db_role_settings_log(
    _name text,
    _srvid integer DEFAULT 0)
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 @extschema@.pg_track_db_role_settings_history h
        WHERE h.srvid = _srvid
        AND h.name = _name
        ORDER BY ts, setdatabase, setrole DESC;
END;
$_$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION @extschema@.pg_track_reboot_log(_srvid integer DEFAULT 0)
RETURNS TABLE (ts timestamp with time zone) AS
$_$
BEGIN
    RETURN QUERY
        SELECT r.ts
        FROM @extschema@.pg_reboot r
        WHERE r.srvid = _srvid
        ORDER BY r.ts;
END;
$_$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION @extschema@.pg_track_settings_reset(_srvid integer DEFAULT 0)
RETURNS void AS
$_$
BEGIN
    DELETE FROM @extschema@.pg_track_settings_settings_src_tmp WHERE srvid = _srvid;
    DELETE FROM @extschema@.pg_track_settings_rds_src_tmp WHERE srvid = _srvid;
    DELETE FROM @extschema@.pg_track_settings_reboot_src_tmp WHERE srvid = _srvid;
    DELETE FROM @extschema@.pg_track_settings_list WHERE srvid = _srvid;
    DELETE FROM @extschema@.pg_track_settings_history WHERE srvid = _srvid;
    DELETE FROM @extschema@.pg_track_db_role_settings_list WHERE srvid = _srvid;
    DELETE FROM @extschema@.pg_track_db_role_settings_history WHERE srvid = _srvid;
    DELETE FROM @extschema@.pg_reboot WHERE srvid = _srvid;
END;
$_$
LANGUAGE plpgsql;