Обсуждение: Re: [HACKERS] Declarative partitioning - another take
On Fri, Apr 28, 2017 at 11:43 AM, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> wrote:
Hi Amit,
I have applied given patch, could see below behaviour with statement trigger.
When trying to delete value within partition range, triggers got fired (with zero row affected) as expected, but if trying to delete value which is outside of partition range (with zero row affected), No trigger fired. is this expected??
Below are steps to reproduce.
CREATE TABLE trigger_test_table (a INT, b INT) PARTITION BY RANGE(a);
CREATE TABLE trigger_test_table1 PARTITION OF trigger_test_table FOR VALUES FROM (0) to (6);
INSERT INTO trigger_test_table (a,b) SELECT i,i FROM generate_series(1,3)i;
CREATE TABLE trigger_test_statatics(TG_NAME varchar,TG_TABLE_NAME varchar,TG_LEVEL varchar,TG_WHEN varchar, TG_OP varchar);
CREATE FUNCTION trigger_test_procedure() RETURNS TRIGGER AS $ttp$
BEGIN
INSERT INTO trigger_test_statatics SELECT TG_NAME,TG_TABLE_NAME,TG_LEVEL,TG_WHEN,TG_OP;
RETURN OLD;
END;
$ttp$ LANGUAGE plpgsql;
CREATE TRIGGER trigger_test11 AFTER DELETE ON trigger_test_table FOR EACH STATEMENT EXECUTE PROCEDURE trigger_test_procedure();
CREATE TRIGGER trigger_test12 AFTER DELETE ON trigger_test_table1 FOR EACH STATEMENT EXECUTE PROCEDURE trigger_test_procedure();
postgres=# DELETE FROM trigger_test_table WHERE a = 5;
DELETE 0
postgres=# SELECT * FROM trigger_test_statatics;
tg_name | tg_table_name | tg_level | tg_when | tg_op
----------------+--------------------+-----------+---------+--------
trigger_test11 | trigger_test_table | STATEMENT | AFTER | DELETE
(1 row)
TRUNCATE TABLE trigger_test_statatics;
--statement trigger NOT fired, when trying to delete data outside partition range.
postgres=# DELETE FROM trigger_test_table WHERE a = 10;
DELETE 0
postgres=# SELECT * FROM trigger_test_statatics;
tg_name | tg_table_name | tg_level | tg_when | tg_op
---------+---------------+----------+---------+-------
(0 rows)
Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation
Updated patch attached.
Hi Amit,
I have applied given patch, could see below behaviour with statement trigger.
When trying to delete value within partition range, triggers got fired (with zero row affected) as expected, but if trying to delete value which is outside of partition range (with zero row affected), No trigger fired. is this expected??
Below are steps to reproduce.
CREATE TABLE trigger_test_table (a INT, b INT) PARTITION BY RANGE(a);
CREATE TABLE trigger_test_table1 PARTITION OF trigger_test_table FOR VALUES FROM (0) to (6);
INSERT INTO trigger_test_table (a,b) SELECT i,i FROM generate_series(1,3)i;
CREATE TABLE trigger_test_statatics(TG_NAME varchar,TG_TABLE_NAME varchar,TG_LEVEL varchar,TG_WHEN varchar, TG_OP varchar);
CREATE FUNCTION trigger_test_procedure() RETURNS TRIGGER AS $ttp$
BEGIN
INSERT INTO trigger_test_statatics SELECT TG_NAME,TG_TABLE_NAME,TG_LEVEL,TG_WHEN,TG_OP;
RETURN OLD;
END;
$ttp$ LANGUAGE plpgsql;
CREATE TRIGGER trigger_test11 AFTER DELETE ON trigger_test_table FOR EACH STATEMENT EXECUTE PROCEDURE trigger_test_procedure();
CREATE TRIGGER trigger_test12 AFTER DELETE ON trigger_test_table1 FOR EACH STATEMENT EXECUTE PROCEDURE trigger_test_procedure();
postgres=# DELETE FROM trigger_test_table WHERE a = 5;
DELETE 0
postgres=# SELECT * FROM trigger_test_statatics;
tg_name | tg_table_name | tg_level | tg_when | tg_op
----------------+--------------------+-----------+---------+--------
trigger_test11 | trigger_test_table | STATEMENT | AFTER | DELETE
(1 row)
TRUNCATE TABLE trigger_test_statatics;
--statement trigger NOT fired, when trying to delete data outside partition range.
postgres=# DELETE FROM trigger_test_table WHERE a = 10;
DELETE 0
postgres=# SELECT * FROM trigger_test_statatics;
tg_name | tg_table_name | tg_level | tg_when | tg_op
---------+---------------+----------+---------+-------
(0 rows)
Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation
Hi Rajkumar, On 2017/04/28 17:11, Rajkumar Raghuwanshi wrote: > On Fri, Apr 28, 2017 at 11:43 AM, Amit Langote < >> Updated patch attached. > > I have applied given patch, could see below behaviour with statement > trigger. > > When trying to delete value within partition range, triggers got fired > (with zero row affected) as expected, but if trying to delete value which > is outside of partition range (with zero row affected), No trigger fired. > is this expected?? Good catch. I'm afraid though that this is not a defect of this patch, but some unrelated (maybe) bug, which affects not only the partitioned tables but inheritance in general. Problem is that the plan created is such that the executor has no opportunity to fire the trigger in question, because the plan contains no information about which table is affected by the statement. You can see that with inheritance. See below: create table foo (); create table bar () inherits (foo); create or replace function trig_notice() returns trigger as $$ begin raise notice 'trigger fired'; return null; end; $$ language plpgsql; create trigger foo_del_before before delete on foo for each statement execute procedure trig_notice(); explain delete from foo where false; QUERY PLAN ------------------------------------------Result (cost=0.00..0.00 rows=0 width=0) One-Time Filter: false (2 rows) -- no trigger fired delete from foo where false; DELETE 0 Trigger *is* fired though, if inheritance is not used. explain delete from only foo where false; QUERY PLAN -------------------------------------------------Delete on foo (cost=0.00..0.00 rows=0 width=0) -> Result (cost=0.00..0.00rows=0 width=6) One-Time Filter: false (3 rows) delete from only foo where false; NOTICE: trigger fired DELETE 0 I'm not sure how to go about fixing this, if at all. Or to fix it at least for partitioned tables. Would like to hear what others think. Thanks, Amit