On 2018/03/19 16:45, Amit Langote wrote: > I have tried to make these changes and attached are the updated patches > containing those, including the change I suggested for 0001 (that is, > getting rid of mt_onconflict). I also expanded some comments in 0003 > while making those changes.
I realized that there should be a test where transition table is involved for an ON CONFLICT DO UPDATE on a partitioned table due to relevant statement trigger on the table; something like the attached. But due to a bug being discussed over at [1], we won't get the correct expected output for the test until the latest patch submitted for that bug [2] is committed as a bug fix. Thanks, Amit [1] https://www.postgresql.org/message-id/ba19eff9-2120-680e-4671-55a9bea9454f%40lab.ntt.co.jp [2] https://www.postgresql.org/message-id/df921671-32df-45ea-c0e4-9b51ee86ba3b%40lab.ntt.co.jp
>From 152c6d5afed21e775caf4862c00e5c6388f7403b Mon Sep 17 00:00:00 2001 From: amit <amitlangot...@gmail.com> Date: Mon, 19 Mar 2018 17:13:08 +0900 Subject: [PATCH] More tests for ON CONFLICT DO UPDATE on partitioned tables For transition tables of the DO UPDATE action. --- src/test/regress/expected/triggers.out | 33 +++++++++++++++++++++++++++++++++ src/test/regress/sql/triggers.sql | 33 +++++++++++++++++++++++++++++++++ 2 files changed, 66 insertions(+) diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out index 99be9ac6e9..e8b849f773 100644 --- a/src/test/regress/expected/triggers.out +++ b/src/test/regress/expected/triggers.out @@ -2328,6 +2328,39 @@ insert into my_table values (3, 'CCC'), (4, 'DDD') NOTICE: trigger = my_table_update_trig, old table = (3,CCC), (4,DDD), new table = (3,CCC:CCC), (4,DDD:DDD) NOTICE: trigger = my_table_insert_trig, new table = <NULL> -- +-- now using a partitioned table +-- +create table iocdu_tt_parted (a int primary key, b text) partition by list (a); +create table iocdu_tt_parted1 partition of iocdu_tt_parted for values in (1); +create table iocdu_tt_parted2 partition of iocdu_tt_parted for values in (2); +create table iocdu_tt_parted3 partition of iocdu_tt_parted for values in (3); +create table iocdu_tt_parted4 partition of iocdu_tt_parted for values in (4); +create trigger iocdu_tt_parted_insert_trig + after insert on iocdu_tt_parted referencing new table as new_table + for each statement execute procedure dump_insert(); +create trigger iocdu_tt_parted_update_trig + after update on iocdu_tt_parted referencing old table as old_table new table as new_table + for each statement execute procedure dump_update(); +-- inserts only +insert into iocdu_tt_parted values (1, 'AAA'), (2, 'BBB') + on conflict (a) do + update set b = iocdu_tt_parted.b || ':' || excluded.b; +NOTICE: trigger = iocdu_tt_parted_update_trig, old table = <NULL>, new table = <NULL> +NOTICE: trigger = iocdu_tt_parted_insert_trig, new table = (1,AAA), (2,BBB) +-- mixture of inserts and updates +insert into iocdu_tt_parted values (1, 'AAA'), (2, 'BBB'), (3, 'CCC'), (4, 'DDD') + on conflict (a) do + update set b = iocdu_tt_parted.b || ':' || excluded.b; +ERROR: new row for relation "iocdu_tt_parted1" violates partition constraint +DETAIL: Failing row contains (2, BBB). +-- updates only +insert into iocdu_tt_parted values (3, 'CCC'), (4, 'DDD') + on conflict (a) do + update set b = iocdu_tt_parted.b || ':' || excluded.b; +NOTICE: trigger = iocdu_tt_parted_update_trig, old table = <NULL>, new table = <NULL> +NOTICE: trigger = iocdu_tt_parted_insert_trig, new table = (3,CCC), (4,DDD) +drop table iocdu_tt_parted; +-- -- Verify that you can't create a trigger with transition tables for -- more than one event. -- diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql index 3354f4899f..3773c6bc98 100644 --- a/src/test/regress/sql/triggers.sql +++ b/src/test/regress/sql/triggers.sql @@ -1773,6 +1773,39 @@ insert into my_table values (3, 'CCC'), (4, 'DDD') update set b = my_table.b || ':' || excluded.b; -- +-- now using a partitioned table +-- + +create table iocdu_tt_parted (a int primary key, b text) partition by list (a); +create table iocdu_tt_parted1 partition of iocdu_tt_parted for values in (1); +create table iocdu_tt_parted2 partition of iocdu_tt_parted for values in (2); +create table iocdu_tt_parted3 partition of iocdu_tt_parted for values in (3); +create table iocdu_tt_parted4 partition of iocdu_tt_parted for values in (4); +create trigger iocdu_tt_parted_insert_trig + after insert on iocdu_tt_parted referencing new table as new_table + for each statement execute procedure dump_insert(); +create trigger iocdu_tt_parted_update_trig + after update on iocdu_tt_parted referencing old table as old_table new table as new_table + for each statement execute procedure dump_update(); + +-- inserts only +insert into iocdu_tt_parted values (1, 'AAA'), (2, 'BBB') + on conflict (a) do + update set b = iocdu_tt_parted.b || ':' || excluded.b; + +-- mixture of inserts and updates +insert into iocdu_tt_parted values (1, 'AAA'), (2, 'BBB'), (3, 'CCC'), (4, 'DDD') + on conflict (a) do + update set b = iocdu_tt_parted.b || ':' || excluded.b; + +-- updates only +insert into iocdu_tt_parted values (3, 'CCC'), (4, 'DDD') + on conflict (a) do + update set b = iocdu_tt_parted.b || ':' || excluded.b; + +drop table iocdu_tt_parted; + +-- -- Verify that you can't create a trigger with transition tables for -- more than one event. -- -- 2.11.0
