Re: extension patch of CREATE OR REPLACE TRIGGER

osumi(dot)takamichi(at)fujitsu(dot)com:
>> * I'm a little bit concerned about the semantics of changing the
>> tgdeferrable/tginitdeferred properties of an existing trigger. If there are trigger
>> events pending, and the trigger is redefined in such a way that those events
>> should already have been fired, what then?
> OK. I need a discussion about this point.
> There would be two ideas to define the behavior of this semantics change, I think.
> The first idea is to throw an error that means
> the *pending* trigger can't be replaced during the session.
> The second one is just to replace the trigger and ignite the new trigger
> at the end of the session when its tginitdeferred is set true.
> For me, the first one sounds safer. Yet, I'd like to know other opinions.

IMHO, constraint triggers should behave the same in that regard as other
constraints. I just checked:

BEGIN;
CREATE TABLE t1 (a int CONSTRAINT u UNIQUE DEFERRABLE INITIALLY DEFERRED);
INSERT INTO t1 VALUES (1),(1);
ALTER TABLE t1 ALTER CONSTRAINT u NOT DEFERRABLE;

will throw with:

ERROR: cannot ALTER TABLE "t1" because it has pending trigger events
SQL state: 55006

So if a trigger event is pending, CREATE OR REPLACE for that trigger
should throw. I think it should do in any case, not just when changing
deferrability. This makes it easier to reason about.

If the user has a pending trigger, they can still do SET CONSTRAINTS
trigger_name IMMEDIATE; to resolve that and then do CREATE OR REPLACE
TRIGGER, just like in the ALTER TABLE case.

>> regression=# create constraint trigger my_trig after insert on trig_table
>> deferrable initially deferred for each row execute procedure
>> before_replacement(); CREATE TRIGGER regression=# begin; BEGIN
>> regression=*# insert into trig_table default values; INSERT 0 1 regression=*#
>> drop trigger my_trig on trig_table; DROP TRIGGER regression=*# commit;
>> ERROR: relation 38489 has no triggers
> I could reproduce this bug, using the current master without my patch.
> So this is another issue.
> I'm thinking that throwing an error when *pending* trigger is dropped
> makes sense. Does everyone agree with it ?

Just tested the same example as above, but with DROP TABLE t1; instead
of ALTER TABLE. This throws with:

ERROR: cannot DROP TABLE "t1" because it has pending trigger events
SQL state: 55006

So yes, your suggestion makes a lot of sense!