Re: sql_drop Event Trigger

On Thu, Feb 28, 2013 at 3:20 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I think it's fairly obvious that
> (1) dealing with a DROP only after it's happened is pretty limiting;
> (2) allowing user-defined code to run mid-command is dangerous.
> What's at issue is the tradeoff we make between these inescapable
> facts, and I'm not sure if we can get consensus on that.

I'll note that Slony could do something useful with an ON EVENT trigger
even if there's NO data provided as to what tables got dropped.

We could get a "prevent dropping by accident" test that amounts to:

if exists (select 1 from _slony_schema.sl_table where
not exists (select 1 from pg_catalog.pg_class where oid =
tab_reloid)) then
raise exception 'You attempted to drop a replicated table. Shame on you!";
end if;

That could be extended to precede the exception by raising a warning
for each such table that was found. That's a nice "save the admin from
accidentally breaking replication" check.

If we're agonizing over "what more do we need to ensure it's useful?", and
it's looking pretty open-ended, well, for the above test, I don't need *any*
attributes to be passed to me by the event trigger in order to do something
that's useful enough. I wouldn't feel horribly badly if things stopped short
of having ON DROP do anything extra.

If I *really* wanted to do some sophisticated tracking of things, the apropos
route might be to set up both a BEFORE and an AFTER trigger, have the
BEFORE part capture relevant data in memory (with some question of
"how much relevant data") and then, in the AFTER trigger, refer back to
the captured data in order to do something. That again doesn't require
adding much of anything to the trigger attributes.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"