Re: [HACKERS] Declarative partitioning - another take
От | Amit Langote |
---|---|
Тема | Re: [HACKERS] Declarative partitioning - another take |
Дата | |
Msg-id | 4b498969-fd37-9c3d-3981-389507515cc6@lab.ntt.co.jp обсуждение исходный текст |
Ответ на | Re: [HACKERS] Declarative partitioning - another take (Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com>) |
Ответы |
Re: [HACKERS] Declarative partitioning - another take
(Robert Haas <robertmhaas@gmail.com>)
Re: [HACKERS] Declarative partitioning - another take (Noah Misch <noah@leadboat.com>) |
Список | pgsql-hackers |
Hi Rajkumar, Thanks for testing and the report. On 2017/04/21 17:00, Rajkumar Raghuwanshi wrote: > Hi, > > I have observed below with the statement triggers. > > I am able to create statement triggers at root partition, but these > triggers, not getting fired on updating partition. > > CREATE TABLE pt (a INT, b INT) PARTITION BY RANGE(a); > CREATE TABLE pt1 PARTITION OF pt FOR VALUES FROM (1) to (7); > CREATE TABLE pt2 PARTITION OF pt FOR VALUES FROM (7) to (11); > INSERT INTO pt (a,b) SELECT i,i FROM generate_series(1,10)i; > CREATE TABLE pt_trigger(TG_NAME varchar,TG_TABLE_NAME varchar,TG_LEVEL > varchar,TG_WHEN varchar); > CREATE OR REPLACE FUNCTION process_pt_trigger() RETURNS TRIGGER AS $pttg$ > BEGIN > IF (TG_OP = 'UPDATE') THEN > INSERT INTO pt_trigger SELECT > TG_NAME,TG_TABLE_NAME,TG_LEVEL,TG_WHEN; > RETURN NEW; > END IF; > RETURN NULL; > END; > $pttg$ LANGUAGE plpgsql; > CREATE TRIGGER pt_trigger_after_p0 AFTER UPDATE ON pt FOR EACH STATEMENT > EXECUTE PROCEDURE process_pt_trigger(); > CREATE TRIGGER pt_trigger_before_p0 BEFORE UPDATE ON pt FOR EACH STATEMENT > EXECUTE PROCEDURE process_pt_trigger(); > > postgres=# UPDATE pt SET a = 2 WHERE a = 1; > UPDATE 1 > postgres=# SELECT * FROM pt_trigger ORDER BY 1; > tg_name | tg_table_name | tg_level | tg_when > ---------+---------------+----------+--------- > (0 rows) > > no statement level trigger fired in this case, is this expected behaviour?? I think we discussed this during the development and decided to allow per-statement triggers on partitioned tables [1], but it seems it doesn't quite work for update/delete as your example shows. You can however see that per-statement triggers of the root partitioned table *are* fired in the case of insert. The reason it doesn't work is that we do not allocate ResultRelInfos for partitioned tables (not even for the root partitioned table in the update/delete cases), because the current implementation assumes they are not required. That's fine only so long as we consider that no rows are inserted into them, no indexes need to be updated, and that no row-level triggers are to be fired. But it misses the fact that we do allow statement-level triggers on partitioned tables. So, we should fix things such that ResultRelInfos are allocated so that any statement-level triggers are fired. But there are following questions to consider: 1. Should we consider only the root partitioned table or all partitioned tables in a given hierarchy, including the child partitioned tables? Note that this is related to a current limitation of updating/deleting inheritance hierarchies that we do not currently fire per-statement triggers of the child tables. See the TODO item in wiki: https://wiki.postgresql.org/wiki/Todo#Triggers, which says: "When statement-level triggers are defined on a parent table, have them fire only on the parent table, and fire child table triggers only where appropriate" 2. Do we apply the same to inserts on the partitioned tables? Since insert on a partitioned table implicitly affects its partitions, one might say that we would need to fire per-statement insert triggers of all the partitions. Meanwhile, attached is a set of patches to fix this. Descriptions follow: 0001: fire per-statement triggers of the partitioned table mentioned in a given update or delete statement 0002: fire per-statement triggers of the child tables during update/delete of inheritance hierarchies (including the partitioned table case) Depending on the answer to 2 above, we can arrange for the per-statement triggers of all the partitions to be fired upon insert into the partitioned table. > but if i am creating triggers on leaf partition, trigger is getting fired. > > CREATE TRIGGER pt_trigger_after_p1 AFTER UPDATE ON pt1 FOR EACH STATEMENT > EXECUTE PROCEDURE process_pt_trigger(); > CREATE TRIGGER pt_trigger_before_p1 BEFORE UPDATE ON pt1 FOR EACH STATEMENT > EXECUTE PROCEDURE process_pt_trigger(); > > postgres=# UPDATE pt SET a = 5 WHERE a = 4; > UPDATE 1 > postgres=# SELECT * FROM pt_trigger ORDER BY 1; > tg_name | tg_table_name | tg_level | tg_when > ----------------------+---------------+-----------+--------- > pt_trigger_after_p1 | pt1 | STATEMENT | AFTER > pt_trigger_before_p1 | pt1 | STATEMENT | BEFORE > (2 rows) Actually, this works only by accident (with the current implementation, the *first* partition's triggers will get fired). If you create another partition, its per-statement triggers won't get fired. The patches described above fixes that. It would be great if you could check if the patches fix the issues. Also, adding this to the PostgreSQL 10 open items list. Thanks, Amit [1] https://www.postgresql.org/message-id/8e05817e-14c8-13e4-502b-e440adb24258%40lab.ntt.co.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Вложения
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Alexander KorotkovДата:
Сообщение: Re: [HACKERS] Cached plans and statement generalization
Следующее
От: Robert HaasДата:
Сообщение: Re: [HACKERS] Adding support for Default partition in partitioning