Re: Event Triggers: adding information

On Mon, Jan 28, 2013 at 6:19 PM, Dimitri Fontaine
<dimitri(at)2ndquadrant(dot)fr> wrote:
> Christopher Browne <cbbrowne(at)gmail(dot)com> writes:
>> I'm poking at event triggers a bit; would like to set up some examples
>> (and see if they
>> work, or break down badly; both are interesting results) to do some
>> validation of schema
>> for Slony.
>
> Cool, thanks!
>
>> What I'm basically thinking about is to set up some event triggers that run on
>> DROP TABLE / DROP SEQUENCE, and see about cleaning up the replication
>> side of things (e.g. - inject a request to drop the table/sequence
>> from replication).
>
> Sure. In what got commited from the current patch series, you will only
> know that a DROP TABLE (or DROP SEQUENCE) occured, and we're trying to
> get to an agreement with Robert if we should prefer to add visibility to
> such events that occurs in a CASCADE statement or rather add the OID
> (and maybe the name) of the Object that's going to be dropped.
>
> Your opinion is worth a lot on that matter, if you have one to share! :)

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.

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

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.

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.

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.

>> I have a bit of a complaint as to what documentation is included; I don't see
>> any references in the documentation to ddl_command_start / ddl_command_end,
>> which seem to be necessary values for event triggers.
>
> What we have now here:
>
> http://www.postgresql.org/docs/devel/static/event-trigger-matrix.html
> http://www.postgresql.org/docs/devel/static/sql-createeventtrigger.html
> http://www.postgresql.org/docs/devel/static/plpgsql-trigger.html#PLPGSQL-EVENT-TRIGGER
>
> Is it not visible enough, or really missing the point?

Ah, I missed the second one; I was looking under CREATE TRIGGER,
didn't notice that
CREATE EVENT TRIGGER was separately available; that resolves most of
what I thought
was missing.

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.

>> I'd tend to think that there should be a new subsection in the "man page" for
>> CREATE TRIGGER that includes at least two fully formed examples of event
>> triggers, involving the two events in question. Is change of that
>> sort in progress?
>
> The event triggers are addressed in a whole new chapter of the docs,
> maybe that's why you didn't find the docs?

I found that chapter, just not the command :-).
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"