On Tue, Oct 20, 2020 at 04:04:20PM -0300, Alvaro Herrera wrote: > On 2020-Sep-30, Justin Pryzby wrote: > > > CREATE TABLE t(i int) PARTITION BY RANGE(i); > > CREATE TABLE t1 PARTITION OF t FOR VALUES FROM (1) TO (10); > > CREATE OR REPLACE FUNCTION tgf() RETURNS trigger LANGUAGE plpgsql AS $$ > > begin raise exception 'except'; end $$; > > CREATE TRIGGER tg AFTER INSERT ON t FOR EACH ROW EXECUTE FUNCTION tgf(); > > ALTER TABLE t1 DISABLE TRIGGER tg; > > INSERT INTO t VALUES(1); -- inserts when trigger is disabled: good > > ALTER TABLE t DISABLE TRIGGER tg; > > CREATE TABLE t2 PARTITION OF t FOR VALUES FROM (10) TO (20); > > > > postgres=# SELECT tgrelid::regclass, tgenabled FROM pg_trigger WHERE > > tgrelid::regclass::text IN ('t1','t2'); > > tgrelid | tgenabled > > ---------+----------- > > t1 | D > > t2 | O > > (2 rows) > > > > I consider this a bug,but CreateTrigStmt doesn't have any "enabled" member > > (since it's impossible to CREATE TRIGGER .. DISABLED), so I'm not sure where > > the fix should be. > > Hmm, next question: should we backpatch a fix for this? (This applies > all the way back to 11.) If we do, then we would change behavior of > partition creation. It's hard to see that the current behavior is > desirable ... and I think anybody who would have come across this, would > wish it behaved the other way. But still -- it would definitely be a > behavior change.
+0.8 to backpatch. To v13 if not further. We don't normally disable triggers, otherwise I would say +1. For context, I ran into this issue while migrating a customer to a new server using pg_restore and a custom backup script which loops around pg_dump, and handles partitioned tables differently depending if they're recent or historic. Our backup job works well, but is technically a bit of a hack. It doesn't do the right thing (causes sql errors and pg_restore warnings) for inherited indexes and, apparently, triggers. Disabling the trigger was my 4th attempt to handle an error restoring a specific table (mismatched column type between parent dump and child dumped several days earlier). I eventually (5th or 6th attempt) dropped the parent trigger, created the child tables using --section=pre-data, ALTERed a column to match, and then ran post-data and attached it. -- Justin