From: | Justin Pryzby <pryzby(at)telsasoft(dot)com> |
---|---|
To: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
Cc: | Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> |
Subject: | DETACH PARTITION and FOR EACH ROW triggers on partitioned tables |
Date: | 2020-04-08 15:24:12 |
Message-ID: | 20200408152412.GZ2228@telsasoft.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
This seems to be a bug in master, v12, and (probably) v11, where "FOR EACH FOR"
was first allowed on partition tables (86f575948).
I thought this would work like partitioned indexes (8b08f7d48), where detaching
a partition makes its index non-inherited, and attaching a partition marks a
pre-existing, matching partition as inherited rather than creating a new one.
DROP TABLE t, t1;
CREATE TABLE t(i int)PARTITION BY RANGE(i);
CREATE TABLE t1 PARTITION OF t FOR VALUES FROM(1)TO(2);
CREATE OR REPLACE FUNCTION trigf() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN END $$;
CREATE TRIGGER trig AFTER INSERT ON t FOR EACH ROW EXECUTE FUNCTION trigf();
SELECT tgrelid::regclass, * FROM pg_trigger WHERE tgrelid='t1'::regclass;
ALTER TABLE t DETACH PARTITION t1;
ALTER TABLE t ATTACH PARTITION t1 FOR VALUES FROM (1)TO(2);
ERROR: trigger "trig" for relation "t1" already exists
DROP TRIGGER trig ON t1;
ERROR: cannot drop trigger trig on table t1 because trigger trig on table t requires it
HINT: You can drop trigger trig on table t instead.
I remember these, but they don't seem to be relevant to this issue, which seems
to be independant.
1fa846f1c9 Fix cloning of row triggers to sub-partitions
b9b408c487 Record parents of triggers
The commit for partitioned indexes talks about using an pre-existing index on
the child as a "convenience gadget", puts indexes into pg_inherit, and
introduces "ALTER INDEX..ATTACH PARTITION" and "CREATE INDEX..ON ONLY".
It's probably rare for a duplicate index to be useful (unless rebuilding to be
more optimal, which is probably not reasonably interspersed with altering
inheritence). But I don't know if that's equally true for triggers. So I'm
not sure what the intended behavior is, so I've stopped after implementing
a partial fix.
Attachment | Content-Type | Size |
---|---|---|
0001-WIP-fix-detaching-tables-with-inherited-triggers.patch | text/x-diff | 1.8 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | David Steele | 2020-04-08 15:29:25 | Re: [PATCH] Incremental sort |
Previous Message | David Steele | 2020-04-08 15:16:57 | Re: Conflict handling for COPY FROM |