Re: Event Triggers: adding information - Mailing list pgsql-hackers

Christopher Browne <cbbrowne@gmail.com> writes:
> Hmm.  I think some information about the object is pretty needful.
>
> For the immediate case I'm poking at, namely looking for dropped tables,I
> could determine that which object is gone by inference; if I run the trigger
> as part of the ddl_command_end event, then I could run a query that
> searches the slony table sl_table, and if I find any tables for which there
> is no longer a corresponding table in pg_catalog.pg_class, then I infer
> which table got dropped.
>
> But I think I'd really rather know more explicitly which table is being dropped.
>
> Having the oid available in some trigger variable should suffice.

Ack. I think I'm going to send a patch tomorrow with support for
exposing the OID of the object (when at the time of firing the event
trigger it still exists) and support for DROP CASCADE objects thanks to
a SRF that you will be able to call from your event trigger function.

> It appears to me as though it's relevant to return an OID for all of the command
> tags.

Yeah, I've been working on that before and the refactoring in utility.c
is already commited by Robert, so that's definitely in-scope.

> Something useful to clarify in the documentation is what differences are
> meaningful between ddl_command_start and ddl_command_end to make
> it easier to determine which event one would most want to use.

Will add.

> Musing a bit...  It seems to me that it might be a slick idea to run a
> trigger at
> both _start and _end, capturing metadata about the object into temp tables
> at both times, which would then allow the _end function to compare the data
> in the temp table to figure out what to do next.  I wouldn't think
> that's apropos
> as default behaviour; that's something for the crafty developer that's building
> a trigger function to do.

Please remember that at the _end of a DROP the object no longer exists
in the catalogs, so you will get a NULL for its OID, and same at _start
of a CREATE command. For implementation reasons, same as CREATE for the
ALTER case.

> Having a parse tree for the query that initiates the event would be
> mighty useful,
> as would be a canonicalized form of the query.
>
> I think we could add some useful "protection" (e.g. - such as my example of
> an event trigger that generates "DROP TABLE FROM REPLICATION") using
> the present functionality, even perhaps without OIDs, but I don't
> think I'd want
> to get into trying to forward arbitrary DDL without having the canonicalized
> query available.

Thanks. As soon as the OID+CASCADE patch is done, I'm back on working on
Command String Normalisation.

> I think a bit more needs to be said about the meanings of the events
> and the command tags,
> but what I imagined missing wasn't.

Definitely, another pass on the docs will be needed here. I'd like to be
able to postpone that to beta times, as I'm arranging my schedule so as
to be available and participating then.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support