|From:||Wolfgang Walther <walther(at)technowledgy(dot)de>|
|To:||"osumi(dot)takamichi(at)fujitsu(dot)com" <osumi(dot)takamichi(at)fujitsu(dot)com>, 'Tom Lane' <tgl(at)sss(dot)pgh(dot)pa(dot)us>|
|Cc:||'Michael Paquier' <michael(at)paquier(dot)xyz>, Thomas Munro <thomas(dot)munro(at)gmail(dot)com>, Surafel Temesgen <surafel3000(at)gmail(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>|
|Subject:||Re: extension patch of CREATE OR REPLACE TRIGGER|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
>> * 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:
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!
|Next Message||Fabien COELHO||2020-08-03 19:34:47||Re: psql - improve test coverage from 41% to 88%|
|Previous Message||Andrew Dunstan||2020-08-03 19:18:47||Re: Support for NSS as a libpq TLS backend|