pg_drop_events--0.1.sql

/* pg_drop_events/pg_drop_events--0.1.sql */

-- complain if script is sourced in psql, rather than via create extension
\echo Use "CREATE EXTENSION pg_drop_events VERSION '0.1'" to load this file. \quit
DO $$
DECLARE pg_version int;
BEGIN
    pg_version := pg_catalog.current_setting('server_version_num')::int;

    IF pg_version < 130000 THEN
        CREATE TABLE public.pg_drop_events (
            pid             int,
            usename        text,
            query           text,
            xact_id         bigint,
            wal_position    pg_lsn,
            objid           oid,
            object_name     text,
            object_type     text,
            xact_time       timestamp with time zone
        );
    ELSE
        CREATE TABLE public.pg_drop_events (
            pid             int,
            usename        text,
            query           text,
            xact_id         xid8,
            wal_position    pg_lsn,
            objid           oid,
            object_name     text,
            object_type     text,
            xact_time       timestamp with time zone
    );
    END IF;

    IF pg_version < 100000 THEN
        CREATE OR REPLACE FUNCTION public.pg_drop_events()
          RETURNS event_trigger AS $LD$
        DECLARE
            tbd record;
        BEGIN
            FOR tbd IN
                SELECT
                    o.objid,
                    o.object_type,
                    o.object_identity
                FROM pg_catalog.pg_event_trigger_dropped_objects() o
                JOIN pg_catalog.pg_class c ON c.oid = o.classid
                JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
                WHERE NOT o.is_temporary
                AND c.relname = 'pg_class'
                AND n.nspname = 'pg_catalog'
                AND o.classid = c.oid
                AND o.object_type = ANY ('{table, table column, materialized view}')
            LOOP
                RAISE NOTICE '% % dropped by transaction %.', tbd.object_type, tbd.object_identity, pg_catalog.txid_current();

                INSERT INTO public.pg_drop_events (
                    pid,
                    usename,
                    query,
                    xact_id,
                    wal_position,
                    objid,
                    object_name,
                    object_type,
                    xact_time
                )
                SELECT
                    pg_catalog.pg_backend_pid(),
                    pg_catalog.session_user(),
                    trim(trailing ';' from pg_catalog.current_query()),
                    pg_catalog.txid_current(),
                    pg_catalog.pg_current_xlog_location(),
                    tbd.objid,
                    tbd.object_identity,
                    tbd.object_type,
                    pg_catalog.now();
            END LOOP;
        END;
        $LD$ LANGUAGE plpgsql;
    ELSIF pg_version >= 130000 THEN
        CREATE OR REPLACE FUNCTION public.pg_drop_events()
          RETURNS event_trigger AS $LD$
        DECLARE
            tbd record;
        BEGIN
            FOR tbd IN
                SELECT
                    o.objid,
                    o.object_type,
                    o.object_identity
                FROM pg_catalog.pg_event_trigger_dropped_objects() o
                JOIN pg_catalog.pg_class c ON c.oid = o.classid
                JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
                WHERE NOT o.is_temporary
                AND c.relname = 'pg_class'
                AND n.nspname = 'pg_catalog'
                AND o.classid = c.oid
                AND o.object_type = ANY ('{table, table column, materialized view}')
            LOOP
                RAISE NOTICE '% % dropped by transaction %.', tbd.object_type, tbd.object_identity, pg_catalog.pg_current_xact_id();

                INSERT INTO public.pg_drop_events (
                    pid,
                    usename,
                    query,
                    xact_id,
                    wal_position,
                    objid,
                    object_name,
                    object_type,
                    xact_time
                )
                SELECT
                    pg_catalog.pg_backend_pid(),
                    pg_catalog.session_user(),
                    trim(trailing ';' from pg_catalog.current_query()),
                    pg_catalog.pg_current_xact_id(),
                    pg_catalog.pg_current_wal_lsn(),
                    tbd.objid,
                    tbd.object_identity,
                    tbd.object_type,
                    pg_catalog.now();
            END LOOP;
        END;
        $LD$ LANGUAGE plpgsql;
    ELSE
        CREATE OR REPLACE FUNCTION public.pg_drop_events()
          RETURNS event_trigger AS $LD$
        DECLARE
            tbd record;
        BEGIN
            FOR tbd IN
                SELECT
                    o.objid,
                    o.object_type,
                    o.object_identity
                FROM pg_event_trigger_dropped_objects() o
                JOIN pg_catalog.pg_class c ON c.oid = o.classid
                JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
                WHERE NOT o.is_temporary
                AND c.relname = 'pg_class'
                AND n.nspname = 'pg_catalog'
                AND o.classid = c.oid
                AND o.object_type = ANY ('{table, table column, materialized view}')
            LOOP
                RAISE NOTICE '% % dropped by transaction %.', tbd.object_type, tbd.object_identity, pg_catalog.txid_current();

                INSERT INTO public.pg_drop_events (
                    pid,
                    usename,
                    query,
                    xact_id,
                    wal_position,
                    objid,
                    object_name,
                    object_type,
                    xact_time
                )
                SELECT
                    pg_catalog.pg_backend_pid(),
                    pg_catalog.session_user(),
                    trim(trailing ';' from pg_catalog.current_query()),
                    pg_catalog.txid_current(),
                    pg_catalog.pg_current_wal_lsn(),
                    tbd.objid,
                    tbd.object_identity,
                    tbd.object_type,
                    pg_catalog.now();
            END LOOP;
        END;
        $LD$ LANGUAGE plpgsql;
    END IF;

    COMMENT ON FUNCTION public.pg_drop_events() IS 'logs transaction ids of drop table, drop column, drop materialized view statements to aid point in time recovery.';

    CREATE OR REPLACE FUNCTION public.pg_drop_events_reset()
      RETURNS void AS $ST$
        TRUNCATE public.pg_drop_events;
    $ST$ LANGUAGE sql;

    COMMENT ON FUNCTION public.pg_drop_events_reset() IS 'reset all logged transaction ids of drop table, drop column, drop materialized view statements.';

    CREATE OR REPLACE FUNCTION public.pg_drop_events_reset_single(poid oid)
      RETURNS void AS $ST$
        DELETE FROM public.pg_drop_events
        WHERE objid = poid;
    $ST$ LANGUAGE sql;

    COMMENT ON FUNCTION public.pg_drop_events_reset_single(oid) IS 'reset logged transaction id of a particular object.';

    --- Privileges management
    REVOKE ALL ON TABLE public.pg_drop_events FROM PUBLIC;
    REVOKE ALL ON FUNCTION public.pg_drop_events_reset() FROM PUBLIC;
    REVOKE ALL ON FUNCTION public.pg_drop_events_reset_single(oid) FROM PUBLIC;
    GRANT SELECT, INSERT ON TABLE public.pg_drop_events TO PUBLIC;
    GRANT EXECUTE ON FUNCTION public.pg_drop_events_reset() TO postgres;
    GRANT EXECUTE ON FUNCTION public.pg_drop_events_reset_single(oid) TO postgres;

  END;
$$;

DROP EVENT TRIGGER IF EXISTS ZZZ_pg_drop_events;

CREATE EVENT TRIGGER ZZZ_pg_drop_events ON sql_drop
    EXECUTE PROCEDURE public.pg_drop_events();