The event trigger firing matrix lists tags like DROP TABLE and DROP
FUNCTION are listed below the ddl_command_end event, but when I created a
basic audit table and event trigger, they don't seem to fire. I know
sql_drop exists, but again the matrix lists DROP commands in the
ddl_command_end event.
For example:
CREATE TABLE IF NOT EXISTS ddl_info (
classid oid,
objid oid,
objsubid integer,
command_tag text,
object_type text,
schema_name text,
object_identity text,
in_extension bool,
transaction_id bigint NOT NULL DEFAULT txid_current(),
inserted timestamptz NOT NULL DEFAULT clock_timestamp()
);
CREATE OR REPLACE FUNCTION ddl_log()
RETURNS EVENT_TRIGGER
LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO ddl_info (
classid, objid, objsubid, command_tag, object_type,
schema_name, object_identity, in_extension
)
SELECT
classid, objid, objsubid, command_tag, object_type,
schema_name, object_identity, in_extension
FROM pg_event_trigger_ddl_commands();
END;
$$;
CREATE EVENT TRIGGER aa_ddl_info ON ddl_command_end
EXECUTE PROCEDURE ddl_log();
---------------------
About as simple as I can make it. If I run the following, either in the
same transaction or separately, I see the CREATE TABLE tag, the CREATE
INDEX tag for the primary key, the CREATE FUNCTION tag, and the CREATE
COMMENT for the function, but no DROP tags.
CREATE TABLE test (
test_id uuid NOT NULL PRIMARY KEY,
description text
);
CREATE FUNCTION simple_add(integer a, integer b)
RETURNS void
LANGUAGE sql STRICT IMMUTABLE PARALLEL SAFE AS $$
SELECT a + b;
$$;
COMMENT ON FUNCTION simple_add(integer, integer) IS 'A basic addition';
DROP TABLE test;
DROP FUNCTION simple_add(integer, integer);
---------------------
I didn't see anything obvious in the docs that states that DROP statements
require explicit tagging, so I assumed that not specifying any tags would
include all tags. Is this an oversight in the docs and expected behavior or
is this a bug? Doesn't fire in any version from 9.6 on. I didn't test
versions before 9.6.
Thanks in advance,
Miles Elam
