On 2018/03/23 3:42, Pavan Deolasee wrote:
> A slightly improved version attached. Apart from doc cleanup based on
> earlier feedback, fixed one assertion failure based on Rahila's report.
> This was happening when target relation is referenced in the source
> subquery. Fixed that and added a test case to test that situation.
>
> Rebased on current master.
I tried these patches (applied 0002 on top of 0001). When applying 0002,
I got some apply errors:
The next patch would create the file
src/test/isolation/expected/merge-delete.out,
which already exists! Assume -R? [n]
I managed to apply it by ignoring the errors, but couldn't get make check
to pass; attached regressions.diffs if you want to take a look.
Btw, is 0001 redundant with the latest patch on ON CONFLICT DO UPDATE
thread? Can I apply just 0002 on top of that patch? So, I tried that --
that is, skipped your 0001 and instead applied ON CONFLICT DO UPDATE
patch, and then applied your 0002. I had to fix a couple of places to get
MERGE working correctly for partitioned tables; attached find a delta
patch for the fixes I made, which were needed because I skipped 0001 in
favor of the ON CONFLICT DO UPDATE patch. But the regression test failure
I mentioned above didn't go away, so it seems to have nothing to do with
partitioning.
Thanks,
Amit
diff --git a/src/backend/executor/nodeModifyTable.c
b/src/backend/executor/nodeModifyTable.c
index 00d241f232..4927bfebfa 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -1552,7 +1552,6 @@ ExecOnConflictUpdate(ModifyTableState *mtstate,
ExecCheckHeapTupleVisible(estate, &tuple, buffer);
/* Store target's existing tuple in the state's dedicated slot */
- ExecSetSlotDescriptor(mtstate->mt_existing, relation->rd_att);
ExecStoreTuple(&tuple, mtstate->mt_existing, buffer, false);
/*
diff --git a/src/backend/optimizer/prep/preptlist.c
b/src/backend/optimizer/prep/preptlist.c
index 3ff8d86853..4a864b2340 100644
--- a/src/backend/optimizer/prep/preptlist.c
+++ b/src/backend/optimizer/prep/preptlist.c
@@ -142,7 +142,7 @@ preprocess_targetlist(PlannerInfo *root)
action->targetList =
expand_targetlist(action->targetList,
action->commandType,
result_relation,
-
RelationGetDescr(target_relation));
+
target_relation);
break;
case CMD_DELETE:
break;
*** /home/amit/pg/mygit/src/test/regress/expected/merge.out 2018-01-30
11:50:31.297108552 +0900
--- /home/amit/pg/mygit/src/test/regress/results/merge.out 2018-03-23
13:18:25.034107527 +0900
***************
*** 39,52 ****
WHEN MATCHED THEN
DELETE
;
! QUERY PLAN
! ----------------------------------------
Merge on target t
-> Merge Join
! Merge Cond: (t.tid = s.sid)
-> Sort
! Sort Key: t.tid
! -> Seq Scan on target t
-> Sort
Sort Key: s.sid
-> Seq Scan on source s
--- 39,52 ----
WHEN MATCHED THEN
DELETE
;
! QUERY PLAN
! ------------------------------------------
Merge on target t
-> Merge Join
! Merge Cond: (t_1.tid = s.sid)
-> Sort
! Sort Key: t_1.tid
! -> Seq Scan on target t_1
-> Sort
Sort Key: s.sid
-> Seq Scan on source s
***************
*** 137,142 ****
--- 137,154 ----
INSERT DEFAULT VALUES
;
ERROR: permission denied for table target2
+ -- check if the target can be accessed from source relation subquery; we
should
+ -- not be able to do so
+ MERGE INTO target t
+ USING (SELECT * FROM source WHERE t.tid > sid) s
+ ON t.tid = s.sid
+ WHEN NOT MATCHED THEN
+ INSERT DEFAULT VALUES
+ ;
+ ERROR: invalid reference to FROM-clause entry for table "t"
+ LINE 2: USING (SELECT * FROM source WHERE t.tid > sid) s
+ ^
+ HINT: There is an entry for table "t", but it cannot be referenced from this
part of the query.
--
-- initial tests
--
***************
*** 229,242 ****
WHEN MATCHED THEN
UPDATE SET balance = 0
;
! QUERY PLAN
! ----------------------------------------
Merge on target t
-> Hash Join
! Hash Cond: (s.sid = t.tid)
-> Seq Scan on source s
-> Hash
! -> Seq Scan on target t
(6 rows)
EXPLAIN (COSTS OFF)
--- 241,254 ----
WHEN MATCHED THEN
UPDATE SET balance = 0
;
! QUERY PLAN
! ------------------------------------------
Merge on target t
-> Hash Join
! Hash Cond: (s.sid = t_1.tid)
-> Seq Scan on source s
-> Hash
! -> Seq Scan on target t_1
(6 rows)
EXPLAIN (COSTS OFF)
***************
*** 246,259 ****
WHEN MATCHED THEN
DELETE
;
! QUERY PLAN
! ----------------------------------------
Merge on target t
-> Hash Join
! Hash Cond: (s.sid = t.tid)
-> Seq Scan on source s
-> Hash
! -> Seq Scan on target t
(6 rows)
EXPLAIN (COSTS OFF)
--- 258,271 ----
WHEN MATCHED THEN
DELETE
;
! QUERY PLAN
! ------------------------------------------
Merge on target t
-> Hash Join
! Hash Cond: (s.sid = t_1.tid)
-> Seq Scan on source s
-> Hash
! -> Seq Scan on target t_1
(6 rows)
EXPLAIN (COSTS OFF)
***************
*** 262,275 ****
ON t.tid = s.sid
WHEN NOT MATCHED THEN
INSERT VALUES (4, NULL);
! QUERY PLAN
! ----------------------------------------
Merge on target t
-> Hash Left Join
! Hash Cond: (s.sid = t.tid)
-> Seq Scan on source s
-> Hash
! -> Seq Scan on target t
(6 rows)
;
--- 274,287 ----
ON t.tid = s.sid
WHEN NOT MATCHED THEN
INSERT VALUES (4, NULL);
! QUERY PLAN
! ------------------------------------------
Merge on target t
-> Hash Left Join
! Hash Cond: (s.sid = t_1.tid)
-> Seq Scan on source s
-> Hash
! -> Seq Scan on target t_1
(6 rows)
;
***************
*** 370,375 ****
--- 382,388 ----
UPDATE SET balance = 0
;
ERROR: MERGE command cannot affect row a second time
+ HINT: Ensure that not more than one source rows match any one target row
ROLLBACK;
BEGIN;
MERGE INTO target t
***************
*** 379,384 ****
--- 392,398 ----
DELETE
;
ERROR: MERGE command cannot affect row a second time
+ HINT: Ensure that not more than one source rows match any one target row
ROLLBACK;
-- correct source data
DELETE FROM source WHERE sid = 2;
***************
*** 696,701 ****
--- 710,720 ----
1 | 299
(1 row)
+ -- check if subqueries work in the conditions?
+ MERGE INTO wq_target t
+ USING wq_source s ON t.tid = s.sid
+ WHEN MATCHED AND t.balance > (SELECT max(balance) FROM target) THEN
+ UPDATE SET balance = t.balance + s.balance;
-- check if we can access system columns in the conditions
MERGE INTO wq_target t
USING wq_source s ON t.tid = s.sid
***************
*** 704,729 ****
ERROR: system column "xmin" reference in WHEN AND condition is invalid
LINE 3: WHEN MATCHED AND t.xmin = t.xmax THEN
^
SELECT * FROM wq_target;
tid | balance
-----+---------
! 1 | 299
(1 row)
- -- check if subqueries work in the conditions?
MERGE INTO wq_target t
USING wq_source s ON t.tid = s.sid
! WHEN MATCHED AND t.balance > (SELECT max(balance) FROM target) THEN
UPDATE SET balance = t.balance + s.balance;
- ERROR: cannot use subquery in WHEN AND condition
- LINE 3: WHEN MATCHED AND t.balance > (SELECT max(balance) FROM targe...
- ^
SELECT * FROM wq_target;
tid | balance
-----+---------
! 1 | 299
(1 row)
DROP TABLE wq_target, wq_source;
-- test triggers
create or replace function merge_trigfunc () returns trigger
--- 723,761 ----
ERROR: system column "xmin" reference in WHEN AND condition is invalid
LINE 3: WHEN MATCHED AND t.xmin = t.xmax THEN
^
+ ALTER TABLE wq_target SET WITH OIDS;
SELECT * FROM wq_target;
tid | balance
-----+---------
! 1 | 399
(1 row)
MERGE INTO wq_target t
USING wq_source s ON t.tid = s.sid
! WHEN MATCHED AND t.oid >= 0 THEN
UPDATE SET balance = t.balance + s.balance;
SELECT * FROM wq_target;
tid | balance
-----+---------
! 1 | 499
(1 row)
+ -- test preventing WHEN AND conditions from writing to the database
+ create or replace function merge_when_and_write() returns boolean
+ language plpgsql as
+ $$
+ BEGIN
+ INSERT INTO target VALUES (100, 100);
+ RETURN TRUE;
+ END;
+ $$;
+ BEGIN;
+ MERGE INTO wq_target t
+ USING wq_source s ON t.tid = s.sid
+ WHEN MATCHED AND (merge_when_and_write()) THEN
+ UPDATE SET balance = t.balance + s.balance;
+ ROLLBACK;
+ drop function merge_when_and_write();
DROP TABLE wq_target, wq_source;
-- test triggers
create or replace function merge_trigfunc () returns trigger
***************
*** 761,766 ****
--- 793,799 ----
NOTICE: BEFORE UPDATE ROW trigger
NOTICE: AFTER UPDATE ROW trigger
NOTICE: AFTER UPDATE STATEMENT trigger
+ EXPLAIN (ANALYZE ON, COSTS OFF, SUMMARY OFF, TIMING OFF)
MERGE INTO target t
USING source AS s
ON t.tid = s.sid
***************
*** 783,788 ****
--- 816,847 ----
NOTICE: AFTER DELETE STATEMENT trigger
NOTICE: AFTER UPDATE STATEMENT trigger
NOTICE: AFTER INSERT STATEMENT trigger
+ QUERY PLAN
+ ------------------------------------------------------------------
+ Merge on target t (actual rows=0 loops=1)
+ Tuples Inserted: 1
+ Tuples Updated: 1
+ Tuples Deleted: 1
+ -> Hash Left Join (actual rows=3 loops=1)
+ Hash Cond: (s.sid = t_1.tid)
+ -> Seq Scan on source s (actual rows=3 loops=1)
+ -> Hash (actual rows=3 loops=1)
+ Buckets: 1024 Batches: 1 Memory Usage: 9kB
+ -> Seq Scan on target t_1 (actual rows=3 loops=1)
+ Trigger merge_ard: calls=1
+ Trigger merge_ari: calls=1
+ Trigger merge_aru: calls=1
+ Trigger merge_asd: calls=1
+ Trigger merge_asi: calls=1
+ Trigger merge_asu: calls=1
+ Trigger merge_brd: calls=1
+ Trigger merge_bri: calls=1
+ Trigger merge_bru: calls=1
+ Trigger merge_bsd: calls=1
+ Trigger merge_bsi: calls=1
+ Trigger merge_bsu: calls=1
+ (22 rows)
+
SELECT * FROM target ORDER BY tid;
tid | balance
-----+---------
***************
*** 879,884 ****
--- 938,971 ----
ROLLBACK;
--self-merge
BEGIN;
+ MERGE INTO target t1
+ USING target t2
+ ON t1.tid = t2.tid
+ WHEN MATCHED THEN
+ UPDATE SET balance = t1.balance + t2.balance
+ WHEN NOT MATCHED THEN
+ INSERT VALUES (t2.tid, t2.balance)
+ ;
+ NOTICE: BEFORE INSERT STATEMENT trigger
+ NOTICE: BEFORE UPDATE STATEMENT trigger
+ NOTICE: BEFORE UPDATE ROW trigger
+ NOTICE: BEFORE UPDATE ROW trigger
+ NOTICE: BEFORE UPDATE ROW trigger
+ NOTICE: AFTER UPDATE ROW trigger
+ NOTICE: AFTER UPDATE ROW trigger
+ NOTICE: AFTER UPDATE ROW trigger
+ NOTICE: AFTER UPDATE STATEMENT trigger
+ NOTICE: AFTER INSERT STATEMENT trigger
+ SELECT * FROM target ORDER BY tid;
+ tid | balance
+ -----+---------
+ 1 | 20
+ 2 | 40
+ 3 | 60
+ (3 rows)
+
+ ROLLBACK;
+ BEGIN;
MERGE INTO target t
USING (SELECT tid as sid, balance as delta FROM target WHERE balance > 0) AS s
ON t.tid = s.sid
***************
*** 963,974 ****
--- 1050,1108 ----
ROLLBACK;
-- PREPARE
+ BEGIN;
prepare foom as merge into target t using (select 1 as sid) s on (t.tid =
s.sid) when matched then update set balance = 1;
execute foom;
NOTICE: BEFORE UPDATE STATEMENT trigger
NOTICE: BEFORE UPDATE ROW trigger
NOTICE: AFTER UPDATE ROW trigger
NOTICE: AFTER UPDATE STATEMENT trigger
+ SELECT * FROM target ORDER BY tid;
+ tid | balance
+ -----+---------
+ 1 | 1
+ 2 | 20
+ 3 | 30
+ (3 rows)
+
+ ROLLBACK;
+ BEGIN;
+ PREPARE foom2 (integer, integer) AS
+ MERGE INTO target t
+ USING (SELECT 1) s
+ ON t.tid = $1
+ WHEN MATCHED THEN
+ UPDATE SET balance = $2;
+ EXPLAIN (ANALYZE ON, COSTS OFF, SUMMARY OFF, TIMING OFF)
+ execute foom2 (1, 1);
+ NOTICE: BEFORE UPDATE STATEMENT trigger
+ NOTICE: BEFORE UPDATE ROW trigger
+ NOTICE: AFTER UPDATE ROW trigger
+ NOTICE: AFTER UPDATE STATEMENT trigger
+ QUERY PLAN
+ ------------------------------------------------------
+ Merge on target t (actual rows=0 loops=1)
+ Tuples Inserted: 0
+ Tuples Updated: 1
+ Tuples Deleted: 0
+ -> Seq Scan on target t_1 (actual rows=1 loops=1)
+ Filter: (tid = 1)
+ Rows Removed by Filter: 2
+ Trigger merge_aru: calls=1
+ Trigger merge_asu: calls=1
+ Trigger merge_bru: calls=1
+ Trigger merge_bsu: calls=1
+ (11 rows)
+
+ SELECT * FROM target ORDER BY tid;
+ tid | balance
+ -----+---------
+ 1 | 1
+ 2 | 20
+ 3 | 30
+ (3 rows)
+
+ ROLLBACK;
-- subqueries in source relation
CREATE TABLE sq_target (tid integer NOT NULL, balance integer);
CREATE TABLE sq_source (delta integer, sid integer, balance integer DEFAULT
0);
***************
*** 1020,1027 ****
ERROR: column reference "balance" is ambiguous
LINE 5: UPDATE SET balance = balance + delta
^
- SELECT * FROM sq_target;
- ERROR: current transaction is aborted, commands ignored until end of
transaction block
ROLLBACK;
BEGIN;
INSERT INTO sq_source (sid, balance, delta) VALUES (-1, -1, -10);
--- 1154,1159 ----
***************
*** 1043,1050 ****
--- 1175,1593 ----
(3 rows)
ROLLBACK;
+ -- CTEs
+ BEGIN;
+ INSERT INTO sq_source (sid, balance, delta) VALUES (-1, -1, -10);
+ WITH targq AS (
+ SELECT * FROM v
+ )
+ MERGE INTO sq_target t
+ USING v
+ ON tid = sid
+ WHEN MATCHED AND tid > 2 THEN
+ UPDATE SET balance = t.balance + delta
+ WHEN NOT MATCHED THEN
+ INSERT (balance, tid) VALUES (balance + delta, sid)
+ WHEN MATCHED AND tid < 2 THEN
+ DELETE
+ ;
+ ERROR: syntax error at or near "MERGE"
+ LINE 4: MERGE INTO sq_target t
+ ^
+ ROLLBACK;
+ -- RETURNING
+ BEGIN;
+ INSERT INTO sq_source (sid, balance, delta) VALUES (-1, -1, -10);
+ MERGE INTO sq_target t
+ USING v
+ ON tid = sid
+ WHEN MATCHED AND tid > 2 THEN
+ UPDATE SET balance = t.balance + delta
+ WHEN NOT MATCHED THEN
+ INSERT (balance, tid) VALUES (balance + delta, sid)
+ WHEN MATCHED AND tid < 2 THEN
+ DELETE
+ RETURNING *
+ ;
+ ERROR: syntax error at or near "RETURNING"
+ LINE 10: RETURNING *
+ ^
+ ROLLBACK;
+ -- Subqueries
+ BEGIN;
+ MERGE INTO sq_target t
+ USING v
+ ON tid = sid
+ WHEN MATCHED THEN
+ UPDATE SET balance = (SELECT count(*) FROM sq_target)
+ ;
+ ROLLBACK;
+ BEGIN;
+ MERGE INTO sq_target t
+ USING v
+ ON tid = sid
+ WHEN MATCHED AND (SELECT count(*) > 0 FROM sq_target) THEN
+ UPDATE SET balance = 42
+ ;
+ ROLLBACK;
+ BEGIN;
+ MERGE INTO sq_target t
+ USING v
+ ON tid = sid AND (SELECT count(*) > 0 FROM sq_target)
+ WHEN MATCHED THEN
+ UPDATE SET balance = 42
+ ;
+ ROLLBACK;
DROP TABLE sq_target, sq_source CASCADE;
NOTICE: drop cascades to view v
+ CREATE TABLE pa_target (tid integer, balance float, val text)
+ PARTITION BY LIST (tid);
+ CREATE TABLE part1 PARTITION OF pa_target FOR VALUES IN (1,4);
+ CREATE TABLE part2 PARTITION OF pa_target FOR VALUES IN (2,5,6);
+ CREATE TABLE part3 PARTITION OF pa_target FOR VALUES IN (3,8,9);
+ CREATE TABLE part4 PARTITION OF pa_target DEFAULT;
+ CREATE TABLE pa_source (sid integer, delta float);
+ -- insert many rows to the source table
+ INSERT INTO pa_source SELECT id, id * 10 FROM generate_series(1,14) AS id;
+ -- insert a few rows in the target table (odd numbered tid)
+ INSERT INTO pa_target SELECT id, id * 100, 'initial' FROM
generate_series(1,14,2) AS id;
+ -- try simple MERGE
+ BEGIN;
+ MERGE INTO pa_target t
+ USING pa_source s
+ ON t.tid = s.sid
+ WHEN MATCHED THEN
+ UPDATE SET balance = balance + delta, val = val || ' updated by merge'
+ WHEN NOT MATCHED THEN
+ INSERT VALUES (sid, delta, 'inserted by merge');
+ SELECT * FROM pa_target ORDER BY tid;
+ tid | balance | val
+ -----+---------+--------------------------
+ 1 | 110 | initial updated by merge
+ 2 | 20 | inserted by merge
+ 3 | 330 | initial updated by merge
+ 4 | 40 | inserted by merge
+ 5 | 550 | initial updated by merge
+ 6 | 60 | inserted by merge
+ 7 | 770 | initial updated by merge
+ 8 | 80 | inserted by merge
+ 9 | 990 | initial updated by merge
+ 10 | 100 | inserted by merge
+ 11 | 1210 | initial updated by merge
+ 12 | 120 | inserted by merge
+ 13 | 1430 | initial updated by merge
+ 14 | 140 | inserted by merge
+ (14 rows)
+
+ ROLLBACK;
+ -- same with a constant qual
+ BEGIN;
+ MERGE INTO pa_target t
+ USING pa_source s
+ ON t.tid = s.sid AND tid = 1
+ WHEN MATCHED THEN
+ UPDATE SET balance = balance + delta, val = val || ' updated by merge'
+ WHEN NOT MATCHED THEN
+ INSERT VALUES (sid, delta, 'inserted by merge');
+ SELECT * FROM pa_target ORDER BY tid;
+ tid | balance | val
+ -----+---------+--------------------------
+ 1 | 110 | initial updated by merge
+ 2 | 20 | inserted by merge
+ 3 | 30 | inserted by merge
+ 3 | 300 | initial
+ 4 | 40 | inserted by merge
+ 5 | 500 | initial
+ 5 | 50 | inserted by merge
+ 6 | 60 | inserted by merge
+ 7 | 700 | initial
+ 7 | 70 | inserted by merge
+ 8 | 80 | inserted by merge
+ 9 | 90 | inserted by merge
+ 9 | 900 | initial
+ 10 | 100 | inserted by merge
+ 11 | 1100 | initial
+ 11 | 110 | inserted by merge
+ 12 | 120 | inserted by merge
+ 13 | 1300 | initial
+ 13 | 130 | inserted by merge
+ 14 | 140 | inserted by merge
+ (20 rows)
+
+ ROLLBACK;
+ -- try updating the partition key column
+ BEGIN;
+ MERGE INTO pa_target t
+ USING pa_source s
+ ON t.tid = s.sid
+ WHEN MATCHED THEN
+ UPDATE SET tid = tid + 1, balance = balance + delta, val = val || '
updated by merge'
+ WHEN NOT MATCHED THEN
+ INSERT VALUES (sid, delta, 'inserted by merge');
+ SELECT * FROM pa_target ORDER BY tid;
+ tid | balance | val
+ -----+---------+--------------------------
+ 2 | 110 | initial updated by merge
+ 2 | 20 | inserted by merge
+ 4 | 40 | inserted by merge
+ 4 | 330 | initial updated by merge
+ 6 | 550 | initial updated by merge
+ 6 | 60 | inserted by merge
+ 8 | 80 | inserted by merge
+ 8 | 770 | initial updated by merge
+ 10 | 990 | initial updated by merge
+ 10 | 100 | inserted by merge
+ 12 | 1210 | initial updated by merge
+ 12 | 120 | inserted by merge
+ 14 | 1430 | initial updated by merge
+ 14 | 140 | inserted by merge
+ (14 rows)
+
+ ROLLBACK;
+ DROP TABLE pa_target CASCADE;
+ -- The target table is partitioned in the same way, but this time by attaching
+ -- partitions which have columns in different order, dropped columns etc.
+ CREATE TABLE pa_target (tid integer, balance float, val text)
+ PARTITION BY LIST (tid);
+ CREATE TABLE part1 (tid integer, balance float, val text);
+ CREATE TABLE part2 (balance float, tid integer, val text);
+ CREATE TABLE part3 (tid integer, balance float, val text);
+ CREATE TABLE part4 (extraid text, tid integer, balance float, val text);
+ ALTER TABLE part4 DROP COLUMN extraid;
+ ALTER TABLE pa_target ATTACH PARTITION part1 FOR VALUES IN (1,4);
+ ALTER TABLE pa_target ATTACH PARTITION part2 FOR VALUES IN (2,5,6);
+ ALTER TABLE pa_target ATTACH PARTITION part3 FOR VALUES IN (3,8,9);
+ ALTER TABLE pa_target ATTACH PARTITION part4 DEFAULT;
+ -- insert a few rows in the target table (odd numbered tid)
+ INSERT INTO pa_target SELECT id, id * 100, 'initial' FROM
generate_series(1,14,2) AS id;
+ -- try simple MERGE
+ BEGIN;
+ MERGE INTO pa_target t
+ USING pa_source s
+ ON t.tid = s.sid
+ WHEN MATCHED THEN
+ UPDATE SET balance = balance + delta, val = val || ' updated by merge'
+ WHEN NOT MATCHED THEN
+ INSERT VALUES (sid, delta, 'inserted by merge');
+ SELECT * FROM pa_target ORDER BY tid;
+ tid | balance | val
+ -----+---------+--------------------------
+ 1 | 110 | initial updated by merge
+ 2 | 20 | inserted by merge
+ 3 | 330 | initial updated by merge
+ 4 | 40 | inserted by merge
+ 5 | 550 | initial updated by merge
+ 6 | 60 | inserted by merge
+ 7 | 770 | initial updated by merge
+ 8 | 80 | inserted by merge
+ 9 | 990 | initial updated by merge
+ 10 | 100 | inserted by merge
+ 11 | 1210 | initial updated by merge
+ 12 | 120 | inserted by merge
+ 13 | 1430 | initial updated by merge
+ 14 | 140 | inserted by merge
+ (14 rows)
+
+ ROLLBACK;
+ -- same with a constant qual
+ BEGIN;
+ MERGE INTO pa_target t
+ USING pa_source s
+ ON t.tid = s.sid AND tid = 1
+ WHEN MATCHED THEN
+ UPDATE SET balance = balance + delta, val = val || ' updated by merge'
+ WHEN NOT MATCHED THEN
+ INSERT VALUES (sid, delta, 'inserted by merge');
+ SELECT * FROM pa_target ORDER BY tid;
+ tid | balance | val
+ -----+---------+--------------------------
+ 1 | 110 | initial updated by merge
+ 2 | 20 | inserted by merge
+ 3 | 30 | inserted by merge
+ 3 | 300 | initial
+ 4 | 40 | inserted by merge
+ 5 | 500 | initial
+ 5 | 50 | inserted by merge
+ 6 | 60 | inserted by merge
+ 7 | 700 | initial
+ 7 | 70 | inserted by merge
+ 8 | 80 | inserted by merge
+ 9 | 90 | inserted by merge
+ 9 | 900 | initial
+ 10 | 100 | inserted by merge
+ 11 | 1100 | initial
+ 11 | 110 | inserted by merge
+ 12 | 120 | inserted by merge
+ 13 | 1300 | initial
+ 13 | 130 | inserted by merge
+ 14 | 140 | inserted by merge
+ (20 rows)
+
+ ROLLBACK;
+ -- try updating the partition key column
+ BEGIN;
+ MERGE INTO pa_target t
+ USING pa_source s
+ ON t.tid = s.sid
+ WHEN MATCHED THEN
+ UPDATE SET tid = tid + 1, balance = balance + delta, val = val || '
updated by merge'
+ WHEN NOT MATCHED THEN
+ INSERT VALUES (sid, delta, 'inserted by merge');
+ SELECT * FROM pa_target ORDER BY tid;
+ tid | balance | val
+ -----+---------+--------------------------
+ 2 | 110 | initial updated by merge
+ 2 | 20 | inserted by merge
+ 4 | 40 | inserted by merge
+ 4 | 330 | initial updated by merge
+ 6 | 550 | initial updated by merge
+ 6 | 60 | inserted by merge
+ 8 | 80 | inserted by merge
+ 8 | 770 | initial updated by merge
+ 10 | 990 | initial updated by merge
+ 10 | 100 | inserted by merge
+ 12 | 1210 | initial updated by merge
+ 12 | 120 | inserted by merge
+ 14 | 1430 | initial updated by merge
+ 14 | 140 | inserted by merge
+ (14 rows)
+
+ ROLLBACK;
+ DROP TABLE pa_source;
+ DROP TABLE pa_target CASCADE;
+ -- Sub-partitionin
+ CREATE TABLE pa_target (logts timestamp, tid integer, balance float, val text)
+ PARTITION BY RANGE (logts);
+ CREATE TABLE part_m01 PARTITION OF pa_target
+ FOR VALUES FROM ('2017-01-01') TO ('2017-02-01')
+ PARTITION BY LIST (tid);
+ CREATE TABLE part_m01_odd PARTITION OF part_m01
+ FOR VALUES IN (1,3,5,7,9);
+ CREATE TABLE part_m01_even PARTITION OF part_m01
+ FOR VALUES IN (2,4,6,8);
+ CREATE TABLE part_m02 PARTITION OF pa_target
+ FOR VALUES FROM ('2017-02-01') TO ('2017-03-01')
+ PARTITION BY LIST (tid);
+ CREATE TABLE part_m02_odd PARTITION OF part_m02
+ FOR VALUES IN (1,3,5,7,9);
+ CREATE TABLE part_m02_even PARTITION OF part_m02
+ FOR VALUES IN (2,4,6,8);
+ CREATE TABLE pa_source (sid integer, delta float);
+ -- insert many rows to the source table
+ INSERT INTO pa_source SELECT id, id * 10 FROM generate_series(1,14) AS id;
+ -- insert a few rows in the target table (odd numbered tid)
+ INSERT INTO pa_target SELECT '2017-01-31', id, id * 100, 'initial' FROM
generate_series(1,9,3) AS id;
+ INSERT INTO pa_target SELECT '2017-02-28', id, id * 100, 'initial' FROM
generate_series(2,9,3) AS id;
+ -- try simple MERGE
+ BEGIN;
+ MERGE INTO pa_target t
+ USING (SELECT '2017-01-15' AS slogts, * FROM pa_source WHERE sid < 10) s
+ ON t.tid = s.sid
+ WHEN MATCHED THEN
+ UPDATE SET balance = balance + delta, val = val || ' updated by merge'
+ WHEN NOT MATCHED THEN
+ INSERT VALUES (slogts::timestamp, sid, delta, 'inserted by merge');
+ SELECT * FROM pa_target ORDER BY tid;
+ logts | tid | balance | val
+ --------------------------+-----+---------+--------------------------
+ Tue Jan 31 00:00:00 2017 | 1 | 110 | initial updated by merge
+ Tue Feb 28 00:00:00 2017 | 2 | 220 | initial updated by merge
+ Sun Jan 15 00:00:00 2017 | 3 | 30 | inserted by merge
+ Tue Jan 31 00:00:00 2017 | 4 | 440 | initial updated by merge
+ Tue Feb 28 00:00:00 2017 | 5 | 550 | initial updated by merge
+ Sun Jan 15 00:00:00 2017 | 6 | 60 | inserted by merge
+ Tue Jan 31 00:00:00 2017 | 7 | 770 | initial updated by merge
+ Tue Feb 28 00:00:00 2017 | 8 | 880 | initial updated by merge
+ Sun Jan 15 00:00:00 2017 | 9 | 90 | inserted by merge
+ (9 rows)
+
+ ROLLBACK;
+ DROP TABLE pa_source;
+ DROP TABLE pa_target CASCADE;
+ -- some complex joins on the source side
+ CREATE TABLE cj_target (tid integer, balance float, val text);
+ CREATE TABLE cj_source1 (sid1 integer, scat integer, delta integer);
+ CREATE TABLE cj_source2 (sid2 integer, sval text);
+ INSERT INTO cj_source1 VALUES (1, 10, 100);
+ INSERT INTO cj_source1 VALUES (1, 20, 200);
+ INSERT INTO cj_source1 VALUES (2, 20, 300);
+ INSERT INTO cj_source1 VALUES (3, 10, 400);
+ INSERT INTO cj_source2 VALUES (1, 'initial source2');
+ INSERT INTO cj_source2 VALUES (2, 'initial source2');
+ INSERT INTO cj_source2 VALUES (3, 'initial source2');
+ -- source relation is an unalised join
+ MERGE INTO cj_target t
+ USING cj_source1 s1
+ INNER JOIN cj_source2 s2 ON sid1 = sid2
+ ON t.tid = sid1
+ WHEN NOT MATCHED THEN
+ INSERT VALUES (sid1, delta, sval);
+ -- try accessing columns from either side of the source join
+ MERGE INTO cj_target t
+ USING cj_source2 s2
+ INNER JOIN cj_source1 s1 ON sid1 = sid2 AND scat = 20
+ ON t.tid = sid1
+ WHEN NOT MATCHED THEN
+ INSERT VALUES (sid2, delta, sval)
+ WHEN MATCHED THEN
+ DELETE;
+ -- some simple expressions in INSERT targetlist
+ MERGE INTO cj_target t
+ USING cj_source2 s2
+ INNER JOIN cj_source1 s1 ON sid1 = sid2
+ ON t.tid = sid1
+ WHEN NOT MATCHED THEN
+ INSERT VALUES (sid2, delta + scat, sval)
+ WHEN MATCHED THEN
+ UPDATE SET val = val || ' updated by merge';
+ MERGE INTO cj_target t
+ USING cj_source2 s2
+ INNER JOIN cj_source1 s1 ON sid1 = sid2 AND scat = 20
+ ON t.tid = sid1
+ WHEN MATCHED THEN
+ UPDATE SET val = val || ' ' || delta::text;
+ SELECT * FROM cj_target;
+ tid | balance | val
+ -----+---------+----------------------------------
+ 3 | 400 | initial source2 updated by merge
+ 1 | 220 | initial source2 200
+ 1 | 110 | initial source2 200
+ 2 | 320 | initial source2 300
+ (4 rows)
+
+ ALTER TABLE cj_source1 RENAME COLUMN sid1 TO sid;
+ ALTER TABLE cj_source2 RENAME COLUMN sid2 TO sid;
+ TRUNCATE cj_target;
+ MERGE INTO cj_target t
+ USING cj_source1 s1
+ INNER JOIN cj_source2 s2 ON s1.sid = s2.sid
+ ON t.tid = s1.sid
+ WHEN NOT MATCHED THEN
+ INSERT VALUES (s2.sid, delta, sval);
+ DROP TABLE cj_source2, cj_source1, cj_target;
+ -- Function scans
+ CREATE TABLE fs_target (a int, b int, c text);
+ MERGE INTO fs_target t
+ USING generate_series(1,100,1) AS id
+ ON t.a = id
+ WHEN MATCHED THEN
+ UPDATE SET b = b + id
+ WHEN NOT MATCHED THEN
+ INSERT VALUES (id, -1);
+ MERGE INTO fs_target t
+ USING generate_series(1,100,2) AS id
+ ON t.a = id
+ WHEN MATCHED THEN
+ UPDATE SET b = b + id, c = 'updated '|| id.*::text
+ WHEN NOT MATCHED THEN
+ INSERT VALUES (id, -1, 'inserted ' || id.*::text);
+ SELECT count(*) FROM fs_target;
+ count
+ -------
+ 100
+ (1 row)
+
+ DROP TABLE fs_target;
-- SERIALIZABLE test
-- handled in isolation tests
-- prepare
======================================================================
