Re: Command Triggers

On Fri, Feb 17, 2012 at 3:54 AM, Dimitri Fontaine
<dimitri(at)2ndquadrant(dot)fr> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> Wait, we already have ALTER TRIGGER bob ON ANY COMMAND SET DISABLED;
>>
>> Eh, so what happens then if someone sets a command trigger on ALTER TRIGGER?
>
> We should remove support for command triggers on alter command triggers.
> Well I could also go with the GUC idea, it's only that I'm not entirely
> sold it's the best we can do yet and I'd like to avoid yet another GUC.

I'm OK with not supporting command triggers on command triggers, but I
still think the GUC is useful. Keep in mind that flipping a GUC is
really cheap compared to a catalog change, and can affect just one
session. Those are significant advantages. However, if you want to
just not support triggers on statements that modify command triggers,
I'm OK with that, too.

>>> Why would we do it that way (a single entry for multiple commands)?  The
>>> way it is now, it's only syntactic sugar, so I think it's easier to
>>> implement, document and use.
>>
>> Well, for one thing, it's consistent with how we handle it for regular
>> triggers. For two things, if you create an object named bob, you
>
> I don't think so, if you attach the same procedure to more than one
> table each time with the same name, you get multiple entries in
> pg_trigger:
>
>    "pg_trigger_tgrelid_tgname_index" UNIQUE, btree (tgrelid, tgname)
>
> create trigger footg after insert on tg.foo for each row execute procedure tg.trigfunc();
> create trigger footg after insert on tg.bar for each row execute procedure tg.trigfunc();
> create trigger footg after insert on tg.baz for each row execute procedure tg.trigfunc();

Sure, but if you run the same trigger on multiple operations - INSERT
OR UPDATE OR DELETE.

>> expect to end up with an object named bob - not 47 objects (or
>> whatever) that are all named bob.  Also, suppose you create a trigger
>> on ALL COMMANDS, and then a new version of PG adds a new command.
>
> You create a trigger on ANY command :)

Oh. Well, then +1 for me on the ANY COMMAND thing, but -1 on ALL
COMMANDS. I can't see that there's enough utility to having a
bulk-create functionality to justify its existence. The ANY COMMAND
thing I think is what people will want.

> ANY COMMAND triggers are just one entry in pg_cmdtrigger, with the
> command name registered as "ANY", which is only safe as long as we don't
> provide a new SQL command whose command tag is ANY. We could decide that
> we want to name this magic ANY command "__ANY__", but it does not look
> like it fits the project usual naming style.

I am thinking that we should ditch the idea of keeping track of
commands using strings and instead assign a bunch of integer constants
using a big enum. The parser can translate from what the user enters
to these constants and then use those throughout, including in the
system catalogs.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company