MergeTestExamples

From PostgreSQL wiki
Jump to navigationJump to search

This page contains examples that can be used to test the MERGE command as developed during the GSoC 2010.

This was never integrated into PostgreSQL, and requires significant work to be production quality

Sample Tables

For testing the MERGE command, we create three sample tables firstly.


1. The Stock table, which records the amount of each item on hand. 

CREATE TABLE Stock(item_id int UNIQUE, balance int);

INSERT INTO Stock VALUES (10, 2200);
INSERT INTO Stock VALUES (20, 1900);

SELECT * FROM Stock;
 item_id | balance
---------+---------
      10 |    2200
      20 |    1900
(2 rows)


2. The Buy table, which records the amount we bought today for each item. 

CREATE TABLE Buy(item_id int, volume int);

INSERT INTO Buy values(10, 1000);
INSERT INTO Buy values(30, 300);

SELECT * FROM Buy;
 item_id | volume
---------+--------
      10 |   1000
      30 |    300
(2 rows)


3. The Sale table, which records the amount we sold today for each item. 

CREATE TABLE Sale(item_id int, volume int);

INSERT INTO Sale VALUES (10, 2200);
INSERT INTO Sale VALUES (20, 1000);

SELECT * FROM Sale;
 item_id | volume
---------+--------
      10 |   2200
      20 |   1000
(2 rows)

Simple use of MERGE

The first example

We can use a MERGE command to add the items we bought today into the Stock table. 

BEGIN;

MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id
 WHEN MATCHED THEN UPDATE SET balance = balance + Buy.volume
 WHEN NOT MATCHED THEN INSERT VALUES (Buy.item_id, Buy.volume);
 MERGE 2

SELECT * FROM Stock ORDER BY item_id;
 item_id | balance
---------+---------
      10 |	3200
      20 |	1900
      30 |	 300
(3 rows)
  
ROLLBACK;

As we can see, the item 10 has increase by 1000 and itme 30 is inserted into stock.

Merge Action with Quals

The merge action can specify their additional quals. 

BEGIN;

MERGE INTO Stock USING Sale ON Stock.item_id = Sale.item_id
   WHEN MATCHED AND balance - volume > 0 THEN UPDATE SET balance = balance - volume
   WHEN MATCHED THEN DELETE;
MERGE 2
   
SELECT * FROM Stock ORDER BY item_id;
  item_id | balance
 ---------+---------
       20 |	  900
  (1 row)


ROLLBACK;

In this example, the volume of Sale is subtracted from the balance in Stock.


For item 20, the original balance is 1900 and we sold 1000 today. The remaining amount is 900 which is larger than 0. Thus, this tuple fulfills the condition of the UPDATE action and is updated accordingly. Note that the DELETE action is not taken on item 20 because it has a lower priority, although its condition is also fulfilled.


For item 10, the remaining balance is 3200 - 3200 = 0, so it is deleted.

DO NOTHING action

In MERGE command, user can specify a spectial "DO NOTHING" action. All the tuples caught by this action will be ignored. DO NOTHING can also have additional quals, and works in both MATCHED and NOT MATCHED.

In the following example, item 20 matches the requirement of the DO NOTHING action. So it remains unchanged, while item 10 is deleted by the DELETE action. 

BEGIN;

MERGE INTO Stock USING Sale ON Stock.item_id = Sale.item_id
  WHEN MATCHED AND balance - volume > 0 THEN DO NOTHING
  WHEN MATCHED THEN DELETE;
MERGE 1

SELECT * FROM Stock;
 item_id | balance
---------+---------
      20 |    1900
(1 row)

ROLLBACK;

DO NOTHING also works in the NOT MATCHED case: 

BEGIN;

MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id
 WHEN MATCHED THEN UPDATE SET balance = balance + Buy.volume
 WHEN NOT MATCHED THEN DO NOTHING;
MERGE 1

SELECT * FROM Stock;
 item_id | balance
---------+---------
      20 |    1900
      10 |    3200
(2 rows)

ROLLBACK;

In this example, the MATCHED item (item 10) is updated, while the NOT MATCHED item (item 30) is ignored.

RAISE ERROR action

Another special action in MERGE is RAISE ERROR. The tuple fit this action will cause an ERROR. Currently, the ERROR handling is just to throw a short error message. This will be extended in future. The main loop of the MERGE command will stop on this kind of ERROR. The syntax of RAISE ERROR is similar to that of DO NOTHING.

1. It can fit in both MATCHED and NOT MATCHED situation

2. It will not change the tuple. 

BEGIN;

MERGE INTO Stock USING Sale ON Stock.item_id = Sale.item_id
   WHEN MATCHED AND balance - volume > 0 THEN RAISE ERROR
   WHEN MATCHED THEN DELETE;
NOTICE:  one tuple is ERROR
MERGE 1


SELECT * FROM Stock;
 item_id | balance
---------+---------
      20 |    1900
(1 row)

ROLLBACK;

For example, in the above query, the tuple of item 20 leads to an ERROR, while item 10 is deleted after the ERROR.

RAISE ERROR is the default action for the tuples that match with no actions. 


BEGIN;

MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id
 WHEN MATCHED THEN UPDATE SET balance = balance + Buy.volume;
NOTICE:  one tuple is ERROR
MERGE 1

Select * FROM stock;
 item_id | balance
---------+---------
      20 |    1900
      10 |    3200
(2 rows)

ROLLBACK;

As we can see, there is a NOT MATCHED tuple (item 30) which is missed by the user-specified action. So, it raises an error.

More complicated use

Use query as source table

The source table could be a query with alias, as shown in the following example. 

BEGIN;

MERGE INTO Stock USING
	(SELECT Buy.item_id, (Buy.volume - Sale.volume) as v
	FROM Buy, Sale
	WHERE Buy.item_id = Sale.item_id) 
        AS BS
ON Stock.item_id = BS.item_id
    WHEN MATCHED THEN UPDATE SET balance = balance + BS.v;
MERGE 1
	
SELECT * FROM STOCK ORDER BY item_id;
 item_id | balance
---------+---------
      10 |	  1000
      20 |	  1900
(2 rows)
	
ROLLBACK;

Here we want to sum up the Buy and Sale volume together and merge the result in Stock. We bought 1000 and sold 2200 for item 10 today. The old balance of item 10 is 2200. Thus, we have only 1000 remained. Item 20 is not changed, because the source query "BS" has only the tuple for item 10.

Subplan/sublinks in action

For example, suppose there is a table Extra that records all the trivial transactions of the stocks. When we update the stock balance by MERGE command, it is necessary to include these trivial transactions. 


CREATE TABLE Extra (item_id int, volume int);

INSERT INTO Extra VALUES (10, 20);
INSERT INTO Extra VALUES (10, -7);
INSERT INTO Extra VALUES (20, 16);
INSERT INTO Extra VALUES (20, 5);
INSERT INTO Extra VALUES (30, 9);

Then new merge query of the first example becomes: 


BEGIN;

MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id
 WHEN MATCHED THEN UPDATE SET 
      balance = balance + Buy.volume + (SELECT sum(volume) FROM Extra WHERE Extra.item_id = Buy.item_id)
 WHEN NOT MATCHED THEN INSERT VALUES 
     (Buy.item_id, Buy.volume + (SELECT sum(volume) FROM Extra WHERE Extra.item_id = Buy.item_id));
MERGE 2

SELECT * FROM Stock;
 item_id | balance
---------+---------
      20 |    1900
      10 |    3213
      30 |     309
(3 rows)

ROLLBACK;

In this example, we add the sum of all the trivial transactions. Here we can see, the new balance of item 10 becomes 3213 (used to be 3200), where the extra 13 come from the Extra table.

MERGE on inheritance

MERGE can also be applied on inherited tables. The child tables of the target table will be scanned and modified by default.

For example, we create a new stock table and a child table inheriting from it. To make the situation more complex, we add one attribute after the tables are created. 

CREATE TABLE p_Stock(item_id int UNIQUE);
CREATE TABLE c_Stock(ex int) INHERITS (p_Stock);
ALTER TABLE p_Stock ADD COLUMN balance int;

INSERT INTO p_Stock VALUES (10, 2200);
INSERT INTO p_Stock VALUES (20, 1900);

INSERT INTO c_Stock VALUES (30, 0, 700);

Now the p_Stock table has two attribute and c_stock table has three attibutes. Note that balance is the second attribute in p_stock but the third attribute in c_stock. 

SELECT * FROM ONLY p_Stock;
 item_id | balance
---------+---------
      10 |    2200
      20 |    1900
(2 rows)

SELECT * FROM c_Stock;
 item_id | ex | balance
---------+----+---------
      30 |  0 |     700
(1 row)

Now we apply the MERGE command on Stock and Buy. 

BEGIN;
MERGE INTO p_Stock USING Buy ON p_Stock.item_id = Buy.item_id
 WHEN MATCHED THEN UPDATE SET balance = balance + Buy.volume;
NOTICE:  one tuple is ERROR
NOTICE:  one tuple is ERROR
MERGE 2

SELECT * FROM ONLY p_Stock;
 item_id | balance
---------+---------
      20 |    1900
      10 |    3200
(2 rows)

SELECT * FROM c_Stock;
 item_id | ex | balance
---------+----+---------
      30 |  0 |    1000
(1 row)

ROLLBACK;

In this query, the item 10 in p_stock is updated to a balance of 3200 and the item 30 in c_stock is updated to a balance of 1000.

Explain Merge

In general, the EXPLAIN result of a MERGE command has 4 parts:

1. Title: the first line is a title of “MERGE” which contains the costs of the whole plan (if cost display is not off).

2. Init plans: all the Init Plans will be displayed, if any. Init plans are executed first , so they are displayed first.

3 Merge actions: After init plans, there is the list of all merge actions, in the order of user’s input. Each action will tell its action type, action qual and action return target list (if VERBOSE is on). If any subplan is involved in one action, they will be printed out immediately under the action.

4. Main Plan: the join plan for source table LEFT JOIN target table

For example, a simple MERGE EXPLAIN may like the following: 

EXPLAIN
MERGE INTO Stock USING Sale ON Stock.item_id = Sale.item_id
  WHEN MATCHED AND balance - volume > 0 THEN UPDATE SET balance = balance - volume
  WHEN MATCHED THEN DELETE;
		               QUERY PLAN
----------------------------------------------------------------------------
 Merge	(cost=58.15..121.65 rows=2140 width=22)
   Action 1: Update When Matched And
     qual: ((stock.balance - sale.volume) > 0)
   Action 2: Delete When Matched
   MainPlan:
   ->  Hash Left Join  (cost=58.15..121.65 rows=2140 width=22)
	 Hash Cond: (sale.item_id = stock.item_id)
	 ->  Seq Scan on sale  (cost=0.00..31.40 rows=2140 width=8)
	 ->  Hash  (cost=31.40..31.40 rows=2140 width=14)
	       ->  Seq Scan on stock  (cost=0.00..31.40 rows=2140 width=14)
(10 rows)


As other command, EXPLAIN ANALYZE MERGE ... will execute the merge command and tell the real running time. 


BEGIN;

EXPLAIN ANALYZE
MERGE INTO Stock USING Sale ON Stock.item_id = Sale.item_id
   WHEN MATCHED AND balance - volume > 0 THEN UPDATE SET balance = balance - volume
   WHEN MATCHED THEN DELETE;
				                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Merge  (cost=58.15..121.65 rows=2140 width=22) (actual time=0.382..0.382 rows=0 loops=1)
   Action 1: Update When Matched And
   Qual: ((stock.balance - sale.volume) > 0)
   Action 2: Delete When Matched
   MainPlan:
   ->  Hash Left Join  (cost=58.15..121.65 rows=2140 width=22) (actual time=0.25 3..0.265 rows=2 loops=1)
         Hash Cond: (sale.item_id = stock.item_id)
	 ->   Seq Scan on sale  (cost=0.00..31.40 rows=2140 width=8) (actual time=0.080..0.084 rows=2 loops=1)
	 ->   Hash  (cost=31.40..31.40 rows=2140 width=14) (actual time=0.075..0.075 rows=2 loops=1)
		Buckets: 1024  Batches: 1  Memory Usage: 1kB
		->  Seq Scan on stock  (cost=0.00..31.40 rows=2140 width=14) (actual time=0.061..0.065 rows=2 loops=1)
 Total runtime: 0.511 ms
(12 rows)

SELECT * FROM Stock;
 item_id | balance
---------+---------
      20 |	900
(1 row)

ROLLBACK;

A more complicated (but less meaningful) MERGE query is explained as : 


EXPLAIN VERBOSE
MERGE INTO Stock USING Sale ON Stock.item_id = Sale.item_id
   WHEN MATCHED AND balance - volume > 0 THEN UPDATE
      SET balance = balance - volume + (SELECT sum(volume) FROM Extra WHERE Extra.item_id = Sale.item_id)
   WHEN MATCHED THEN UPDATE SET balance = (SELECT COUNT(*) FROM generate_series(1,10)) ;
						   QUERY PLAN
-------------------------------------------------------------------------------------------------
Merge	(cost=70.67..134.16 rows=2140 width=22)
  InitPlan 2 (returns $1)
    ->  Aggregate   (cost=12.50..12.51 rows=1 width=0)
	  Output: count(*)
	  -> Function Scan on pg_catalog.generate_series    (cost=0.00..10.00 rows=1000 width=0)
	        Output: generate_series.generate_series
  Action 1: Update When Matched And
    qual: ((stock.balance - sale.volume) > 0)
  Output: stock.item_id, ((stock.balance - sale.volume) + (SubPlan 1))
  SubPlan 1
    ->  Aggregate   (cost=36.78..36.79 rows=1 width=4)
	  Output: sum(extra.volume)
	    ->  Seq Scan on public.extra  (cost=0.00..36.75 rows=11 width=4)
		  Output: extra.item_id, extra.volume
		  Filter: (extra.item_id = $0)
  Action 2: Update When Matched
  Output: stock.item_id, $1
  MainPlan:
  ->  Hash Left Join  (cost=58.15..121.65 rows=2140 width=22)
	Output: sale.item_id, sale.volume, stock.item_id, stock.balance, stock.ctid
	Hash Cond: (sale.item_id = stock.item_id)
	->  Seq Scan on public.sale  (cost=0.00..31.40 rows=2140 width=8)
	      Output: sale.item_id, sale.volume
	->  Hash  (cost=31.40..31.40 rows=2140 width=14)
	      Output: stock.item_id, stock.balance, stock.ctid
	      ->  Seq Scan on public.stock  (cost=0.00..31.40 rows=2140 width=14)
		    Output: stock.item_id, stock.balance, stock.ctid
(27 rows)

MERGE with rules

Rules fired by actions

If we define rules on the target table of MERGE command, the MERGE actions will apply the rule.

For example, suppose a table "count_by_rule" is created for recording the times of modification on the "Stock" table. 

CREATE TABLE count_by_rule (update INT, insert INT, delete INT);

INSERT INTO count_by_rule VALUES (0,0,0);

We need to create rules for maintaining the count table automatically. 

CREATE RULE "update_count" AS
ON UPDATE TO stock
DO UPDATE count_by_rule SET update=update+1;

CREATE RULE "delete_count" AS
ON DELETE TO stock
DO UPDATE count_by_rule SET delete=delete+1;

CREATE RULE "insert_count" AS
ON INSERT TO stock
DO UPDATE count_by_rule SET insert=insert+1;

Now let's run the second example again. 

BEGIN;

MERGE INTO Stock USING Sale ON Stock.item_id = Sale.item_id
   WHEN MATCHED AND balance - volume > 0THEN UPDATE SET balance = balance - volume
   WHEN MATCHED THEN DELETE;
MERGE 2

SELECT * FROM count_by_rule;
 update | insert | delete
--------+--------+--------
      1 |      0 |      1
(1 row)

SELECT * FROM stock;
 item_id | balance
---------+---------
      20 |     900
(1 row)

ROLLBACK;

We can see that the update and delete field in count table has increased by 1, which means the rule of “update_count” and “delete_count” are both triggered by the MERGE command.

Multiple actions of the same type

If one MERGE command has multiple actions of the same type, the rules of this action type will only be activated once. 

BEGIN;

MERGE INTO Stock USING Sale ON Stock.item_id = Sale.item_id
   WHEN MATCHED AND balance - volume > 0 THEN UPDATE SET balance = balance - volume
   WHEN MATCHED AND balance - volume <= 0 THEN UPDATE SET balance = 0;
MERGE 2
	
SELECT * FROM Stock;
 item_id | balance
---------+---------
      10 |       0
      20 |     900
(2 rows)


SELECT * FROM count_by_rule;
 update | insert | delete
--------+--------+--------
      1 |      0 |      0
(1 row)


ROLLBACK;

In the above example, we have two UPDATE actions in the MERGE command. After the query, we can find that the value of "count.update" is just increased by 1 (not 2), which means the "update_count" rule has been activated for one time.

INSTEAD rules

Replace the update rule with a INSTEAD rule. 


CREATE OR REPLACE RULE "update_count" AS
ON UPDATE TO stock
DO INSTEAD
UPDATE count_by_rule SET update=update+1;

Try the MERGE query again. 


BEGIN;

MERGE INTO Stock USING Sale ON Stock.item_id = Sale.item_id
   WHEN MATCHED AND balance - volume > 0THEN UPDATE SET balance = balance - volume
   WHEN MATCHED THEN DELETE;
MERGE 1

SELECT * FROM stock;
 item_id | balance
---------+---------
      20 |    1900
(1 row)

SELECT * FROM count_by_rule;
 update | insert | delete
--------+--------+--------
      1 |      0 |      1
(1 row)

ROLLBACK;

From this example we can find that, the "update_count" and "delete_count" rules updated the count table correctly.

As we can see, the item 10 is deleted by the DELETE action.

On the other hand, since the update action is replaced by INSTEAD rule, the balance of item 20 is not changed.

MERGE with triggers

Row level triggers

The row level triggers of the target table of a MERGE will be activated by the action of the same type. For example, suppose we want to log all the new tuples come to Stock table. We can firstly create a mirror_stock table.As shown below: 

CREATE TABLE mirror_stock (item_id int, balance int, source text);


Then create row level triggers on Stock, which will insert all new tuples created by INSERT or UPDATE command in to mirror_stock. 

CREATE FUNCTION log_insert_stock() RETURNS trigger 
AS 
$$
        BEGIN
                INSERT INTO mirror_stock VALUES (new.item_id, new.balance, 'from INSERT action');
		    RETURN NULL;	
        END;
$$ 
LANGUAGE plpgsql;

CREATE TRIGGER log_insert
	AFTER INSERT ON Stock
	FOR EACH ROW    	
	EXECUTE PROCEDURE log_insert_stock();


CREATE FUNCTION log_update_stock() RETURNS trigger 
AS 
$$
        BEGIN
                INSERT INTO mirror_stock VALUES (new.item_id, new.balance, 'from UPDATE action');
		    RETURN NULL;	
        END;
$$ 
LANGUAGE plpgsql;

CREATE TRIGGER log_update
	AFTER UPDATE ON Stock
	FOR EACH ROW    	
	EXECUTE PROCEDURE log_update_stock();

Now, let try the UPDATE+INSERT example again: 

BEGIN;

MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id
 WHEN MATCHED THEN UPDATE SET balance = balance + Buy.volume
 WHEN NOT MATCHED THEN INSERT VALUES (Buy.item_id, Buy.volume);
MERGE 2

SELECT * FROM Stock ORDER BY itme_id;
 item_id | balance
---------+---------
      10 |    3200
      20 |    1900
      30 |     300
(3 rows)

SELECT * FROM mirror_stock;
 item_id | balance |       source
---------+---------+--------------------
      10 |    3200 | from UPDATE action
      30 |     300 | from INSERT action
(2 rows)

ROLLBACK;

In this example, the two row level triggers are fired by the two different types of MERGE actions respectively and insert log tuples in mirror_stock as we expected.

Statement level triggers

For example, we can use BEFORE statement triggers to count how many times Stock is applied to the command of UPDATE/INSERT.

Before we start, drop the INSTEAD rules we create before. 

DROP RULE update_count ON Stock CASCADE;


Firstly, create a table for counting. 


CREATE TABLE count_by_trigger (update int, insert int, delete int);

INSERT INTO count_by_trigger VALUES (0,0,0);

Then, create the triggers. 

CREATE FUNCTION count_update_stock() RETURNS trigger 
AS 
$$
        BEGIN
                UPDATE count_by_trigger SET update = update + 1;
		    RETURN NULL;	
        END;
$$ 
LANGUAGE plpgsql;


CREATE TRIGGER count_update
	BEFORE UPDATE ON Stock
	EXECUTE PROCEDURE count_update_stock();


CREATE FUNCTION count_insert_stock() RETURNS trigger 
AS 
$$
        BEGIN
                UPDATE count_by_trigger SET insert = insert + 1;
		    RETURN NULL;	
        END;
$$ 
LANGUAGE plpgsql;

CREATE TRIGGER count_insert
	BEFORE INSERT ON Stock
	EXECUTE PROCEDURE count_insert_stock();

Now, run the MERGE command. 

BEGIN;

MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id
 WHEN MATCHED THEN UPDATE SET balance = balance + Buy.volume
 WHEN NOT MATCHED THEN INSERT VALUES (Buy.item_id, Buy.volume);
MERGE 2

SELECT * FROM count_by_trigger;
 update | insert | delete
--------+--------+--------
      1 |      1 |      0
(1 row)

ROLLBACK;

As we can see, both triggers (the "count_insert" trigger and "count_update" trigger) are fired for this statement.

Multiple actions of same type

If a MERGE command has more than one action of the same type, the corresponding statement trigger will be fired only once.

See the following example. The MERGE command has two UPDATE actions, but the count_by_trigger table is updated only once. 

BEGIN;

MERGE INTO Stock USING Sale ON Stock.item_id = Sale.item_id
   WHEN MATCHED AND balance - volume > 0 THEN UPDATE SET balance = balance - volume
   WHEN MATCHED AND balance - volume <= 0 THEN UPDATE SET balance = 0;
MERGE 2

SELECT * FROM count_by_trigger;
 update | insert | delete
--------+--------+--------
      1 |      0 |      0
(1 row)

ROLLBACK;

INSTEAD rule blocks the triggers

If one kind of action is replaced by INSTEAD rules, it will not fire triggers.

Let's create an INSTEAD rule. 

CREATE OR REPLACE RULE "update_cont" AS
ON UPDATE TO stock
DO INSTEAD
UPDATE count_by_rule SET update=update+1;

Then, run the same example again. 

BEGIN;
 
MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id
 WHEN MATCHED THEN UPDATE SET balance = balance + Buy.volume
 WHEN NOT MATCHED THEN INSERT VALUES (Buy.item_id, Buy.volume);
MERGE 1

SELECT * FROM count_by_trigger;
 update | insert | delete
--------+--------+--------
      0 |      1 |      0
(1 row)

ROLLBACK;

The UPDATE action is replaced by rule, so it will not fire the UPDATE triggers.

Retrieved from "https://wiki.postgresql.org/index.php?title=MergeTestExamples&oldid=21868"