BEGIN;
CREATE SCHEMA audit_ddl;
SET LOCAL search_path TO audit_ddl;
CREATE FUNCTION audit_ddl_on_start()
RETURNS event_trigger
LANGUAGE plpgsql
AS $on_start$
DECLARE
lockid INTEGER := 31337;
BEGIN
-- make sure we only create the temp table (and take a snapshot)
-- once per transaction.
PERFORM 1 FROM pg_locks
WHERE locktype='advisory'
AND objid = lockid AND objsubid = 1
AND pid=pg_backend_pid();
IF NOT FOUND THEN
-- set the flag to disable future audit_ddl_on_start() calls.
PERFORM pg_advisory_xact_lock_shared(lockid);
RAISE NOTICE 'on_start: taking catalog snapshot...';
-- take snapshots of the catalog.
-- NOTE: the view's key is (reloid, attnum): reloid never changes,
-- and attnum is never reused.
CREATE OR REPLACE TEMPORARY VIEW relation_columns AS
SELECT pg_class.oid AS reloid, nspname, relname, attnum, attname
FROM pg_class
JOIN pg_namespace ON (relnamespace = pg_namespace.oid)
JOIN pg_attribute ON (attrelid = pg_class.oid)
WHERE relkind IN ('r', 'v') AND attnum > 0 AND NOT attisdropped
AND nspname NOT LIKE 'pg_%' AND nspname <> 'information_schema';
CREATE TEMPORARY TABLE relation_columns_snapshot
ON COMMIT DROP
AS SELECT * FROM relation_columns;
-- queue a call to audit_ddl_on_commit() for transaction commit.
CREATE TEMPORARY TABLE on_commit_table (foo TEXT) ON COMMIT DROP;
CREATE CONSTRAINT TRIGGER on_commit_trigger
AFTER INSERT ON on_commit_table
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE PROCEDURE audit_ddl.audit_ddl_on_commit();
INSERT INTO on_commit_table (foo) VALUES ('');
END IF;
END;
$on_start$;
CREATE FUNCTION audit_ddl_on_commit()
RETURNS trigger
LANGUAGE plpgsql
AS $on_commit$
DECLARE
result RECORD;
message TEXT;
BEGIN
RAISE NOTICE 'on_commit: checking for DDL actions.';
FOR result IN
SELECT DISTINCT
curr.reloid::regclass AS rel,
prev.nspname AS prevnsp,
prev.relname AS prevname,
curr.relname AS currname
FROM relation_columns AS curr
FULL OUTER JOIN relation_columns_snapshot AS prev
ON (prev.reloid = curr.reloid)
WHERE prev.relname <> curr.relname
AND prev IS NOT NULL AND curr IS NOT NULL
LOOP
message := format('table %I.%I renamed to %s.',
result.prevnsp, result.prevname, result.rel);
RAISE NOTICE 'on_commit: %', message;
END LOOP;
FOR result IN
SELECT curr.reloid::regclass AS rel,
prev.attname AS prevname,
curr.attname AS currname
FROM relation_columns AS curr
FULL OUTER JOIN relation_columns_snapshot AS prev
ON (prev.reloid = curr.reloid AND prev.attnum = curr.attnum)
WHERE prev.attname <> curr.attname
AND prev IS NOT NULL AND curr IS NOT NULL
LOOP
message := format('table %s column %I renamed to %I.',
result.rel, result.prevname, result.currname);
RAISE NOTICE 'on_commit: %', message;
END LOOP;
RETURN NULL;
END;
$on_commit$;
CREATE EVENT TRIGGER audit_ddl_event_trigger
ON ddl_command_start EXECUTE PROCEDURE audit_ddl_on_start();
COMMIT;