Thread: Event Triggers and Dropping Objects

Event Triggers and Dropping Objects

From

Miles Elam

Date:

04 October 2019, 23:38:18

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

Re: Event Triggers and Dropping Objects

From

Luca Ferrari

Date:

05 October 2019, 11:50:14

On Fri, Oct 4, 2019 at 10:38 PM Miles Elam <miles.elam@productops.com> wrote:
>
> 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
againthe matrix lists DROP commands in the ddl_command_end event. 

Yes, I think this is a little misleading:
<https://www.postgresql.org/docs/11/event-trigger-matrix.html>.
The ddl_command_end is issued, and the function is invoked, but
pg_event_trigger_ddl_commands() returns NULL on such invocation
because sql_drop is the event with the attached data.

Hope this helps,
Luca

Re: Event Triggers and Dropping Objects

From

Miles Elam

Date:

05 October 2019, 23:27:38

Thanks, it does!

On Sat, Oct 5, 2019 at 1:50 AM Luca Ferrari <fluca1978@gmail.com> wrote:

On Fri, Oct 4, 2019 at 10:38 PM Miles Elam <miles.elam@productops.com> wrote:
>
> 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.

Yes, I think this is a little misleading:
<https://www.postgresql.org/docs/11/event-trigger-matrix.html>.
The ddl_command_end is issued, and the function is invoked, but
pg_event_trigger_ddl_commands() returns NULL on such invocation
because sql_drop is the event with the attached data.

Hope this helps,
Luca

Re: Event Triggers and Dropping Objects

From

Bruce Momjian

Date:

07 October 2019, 23:12:52

On Sat, Oct  5, 2019 at 10:50:14AM +0200, Luca Ferrari wrote:
> On Fri, Oct 4, 2019 at 10:38 PM Miles Elam <miles.elam@productops.com> wrote:
> >
> > 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
againthe matrix lists DROP commands in the ddl_command_end event.
 
> 
> Yes, I think this is a little misleading:
> <https://www.postgresql.org/docs/11/event-trigger-matrix.html>.
> The ddl_command_end is issued, and the function is invoked, but
> pg_event_trigger_ddl_commands() returns NULL on such invocation
> because sql_drop is the event with the attached data.

Do the Postgres docs need improvement here?

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

Re: Event Triggers and Dropping Objects

From

Luca Ferrari

Date:

09 October 2019, 15:02:52

On Mon, Oct 7, 2019 at 10:12 PM Bruce Momjian <bruce@momjian.us> wrote:
> Do the Postgres docs need improvement here?

I don't know, but I would probably add a flag column in the firing
matrix to indicate when the related function will return a null tuple.

Luca

Re: Event Triggers and Dropping Objects

From

"chikolette-postgresql@yahoo.com"

Date:

16 December 2019, 13:52:50

Le lundi 16 décembre 2019 à 11:13:10 UTC+1, Luca Ferrari <fluca1978@gmail.com> a écrit :

On Mon, Oct 7, 2019 at 10:12 PM Bruce Momjian <bruce@momjian.us> wrote:

> Do the Postgres docs need improvement here?

I don't know, but I would probably add a flag column in the firing

matrix to indicate when the related function will return a null tuple.

Luca

Hi folks,

i think the docs are confusing, in chapter 39.1 it tell us that the following event trigger ddl_command_start and ddl_command_end

are trapping the DROP but in the real they returns nothing usable.

The matrix in chapter 39.2 also tells us that DROP is trapped by these two.

I'm also wondering using event trigger sql_drop why 3 lines are returned :

zof [20410]=# CREATE OR REPLACE FUNCTION ddl_log_sql_drop()

zof-# RETURNS event_trigger

zof-# LANGUAGE plpgsql

zof-# SECURITY DEFINER

zof-# AS $function$

zof$# DECLARE

zof$# obj record;

zof$# BEGIN

zof$# FOR obj IN

zof$# SELECT *

zof$# FROM pg_catalog.pg_event_trigger_dropped_objects()

zof$# LOOP

zof$# RAISE NOTICE 'DROP objet : % % % %',

zof$# obj.object_type,

zof$# obj.schema_name,

zof$# obj.object_name,

zof$# obj.object_identity;

zof$# END LOOP;

zof$# END;

zof$# $function$;

CREATE EVENT TRIGGER ddl_log_sql_drop ON sql_drop

NOTICE: CREATE FUNCTION objet : function zof.zof.ddl_log_sql_drop()

EXECUTE PROCEDURE ddl_log_sql_drop(); CREATE FUNCTION

zof [20410]=# CREATE EVENT TRIGGER ddl_log_sql_drop ON sql_drop

zof-# EXECUTE PROCEDURE ddl_log_sql_drop();

CREATE EVENT TRIGGER

zof [20410]=# drop table za;

NOTICE: DROP objet : table zof za zof.za

NOTICE: DROP objet : type zof za zof.za

NOTICE: DROP objet : type zof _za zof.za[]

DROP TABLE

Thx

Olf