From 1f5bd03290f6397c41cad58a9a27d5b5731c4914 Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Sat, 12 Mar 2022 00:45:30 -0300 Subject: [PATCH v15 4/4] test additions from Simon --- src/test/regress/expected/merge.out | 62 +++++++++++++++++++++++++++-- src/test/regress/sql/merge.sql | 36 ++++++++++++++++- 2 files changed, 94 insertions(+), 4 deletions(-) diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out index 8219d68418..d30f16c7bf 100644 --- a/src/test/regress/expected/merge.out +++ b/src/test/regress/expected/merge.out @@ -428,7 +428,7 @@ WHEN MATCHED THEN ERROR: MERGE command cannot affect row a second time HINT: Ensure that not more than one source row matches any one target row. ROLLBACK; --- correct source data +-- remove duplicate MATCHED data from source data DELETE FROM source WHERE sid = 2; INSERT INTO source VALUES (2, 5); SELECT * FROM source ORDER BY sid; @@ -447,6 +447,38 @@ SELECT * FROM target ORDER BY tid; 3 | 30 (3 rows) +-- duplicate source row on INSERT should fail because of target_pkey +INSERT INTO source VALUES (4, 40); +BEGIN; +MERGE INTO target t +USING source AS s +ON t.tid = s.sid +WHEN NOT MATCHED THEN + INSERT VALUES (4, NULL); +ERROR: duplicate key value violates unique constraint "target_pkey" +DETAIL: Key (tid)=(4) already exists. +SELECT * FROM target ORDER BY tid; +ERROR: current transaction is aborted, commands ignored until end of transaction block +ROLLBACK; +-- remove duplicate NOT MATCHED data from source data +DELETE FROM source WHERE sid = 4; +INSERT INTO source VALUES (4, 40); +SELECT * FROM source ORDER BY sid; + sid | delta +-----+------- + 2 | 5 + 3 | 20 + 4 | 40 +(3 rows) + +SELECT * FROM target ORDER BY tid; + tid | balance +-----+--------- + 1 | 10 + 2 | 20 + 3 | 30 +(3 rows) + -- remove constraints alter table target drop CONSTRAINT target_pkey; alter table target alter column tid drop not null; @@ -522,6 +554,30 @@ SELECT * FROM target ORDER BY tid; (4 rows) ROLLBACK; +-- and again with duplicate source rows +INSERT INTO source VALUES (5, 50); +INSERT INTO source VALUES (5, 50); +-- do a simple equivalent of an INSERT SELECT +BEGIN; +MERGE INTO target t +USING source AS s +ON t.tid = s.sid +WHEN NOT MATCHED THEN + INSERT VALUES (s.sid, s.delta); +SELECT * FROM target ORDER BY tid; + tid | balance +-----+--------- + 1 | 10 + 2 | 20 + 3 | 30 + 4 | 40 + 5 | 50 + 5 | 50 +(6 rows) + +ROLLBACK; +-- removing duplicate source rows +DELETE FROM source WHERE sid = 5; -- and again with explicitly identified column list BEGIN; MERGE INTO target t @@ -824,12 +880,12 @@ WHEN NOT MATCHED THEN NOTICE: BEFORE INSERT STATEMENT trigger NOTICE: BEFORE UPDATE STATEMENT trigger NOTICE: BEFORE DELETE STATEMENT trigger -NOTICE: BEFORE INSERT ROW trigger NOTICE: BEFORE DELETE ROW trigger NOTICE: BEFORE UPDATE ROW trigger -NOTICE: AFTER INSERT ROW trigger +NOTICE: BEFORE INSERT ROW trigger NOTICE: AFTER DELETE ROW trigger NOTICE: AFTER UPDATE ROW trigger +NOTICE: AFTER INSERT ROW trigger NOTICE: AFTER DELETE STATEMENT trigger NOTICE: AFTER UPDATE STATEMENT trigger NOTICE: AFTER INSERT STATEMENT trigger diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql index 42b150785f..8458dc8d79 100644 --- a/src/test/regress/sql/merge.sql +++ b/src/test/regress/sql/merge.sql @@ -295,12 +295,29 @@ WHEN MATCHED THEN DELETE; ROLLBACK; --- correct source data +-- remove duplicate MATCHED data from source data DELETE FROM source WHERE sid = 2; INSERT INTO source VALUES (2, 5); SELECT * FROM source ORDER BY sid; SELECT * FROM target ORDER BY tid; +-- duplicate source row on INSERT should fail because of target_pkey +INSERT INTO source VALUES (4, 40); +BEGIN; +MERGE INTO target t +USING source AS s +ON t.tid = s.sid +WHEN NOT MATCHED THEN + INSERT VALUES (4, NULL); +SELECT * FROM target ORDER BY tid; +ROLLBACK; + +-- remove duplicate NOT MATCHED data from source data +DELETE FROM source WHERE sid = 4; +INSERT INTO source VALUES (4, 40); +SELECT * FROM source ORDER BY sid; +SELECT * FROM target ORDER BY tid; + -- remove constraints alter table target drop CONSTRAINT target_pkey; alter table target alter column tid drop not null; @@ -350,6 +367,23 @@ WHEN NOT MATCHED THEN SELECT * FROM target ORDER BY tid; ROLLBACK; +-- and again with duplicate source rows +INSERT INTO source VALUES (5, 50); +INSERT INTO source VALUES (5, 50); + +-- do a simple equivalent of an INSERT SELECT +BEGIN; +MERGE INTO target t +USING source AS s +ON t.tid = s.sid +WHEN NOT MATCHED THEN + INSERT VALUES (s.sid, s.delta); +SELECT * FROM target ORDER BY tid; +ROLLBACK; + +-- removing duplicate source rows +DELETE FROM source WHERE sid = 5; + -- and again with explicitly identified column list BEGIN; MERGE INTO target t -- 2.30.2