MergeTestExamples

此页面包含一些示例，可用于测试 GSoC 2010 中开发的 MERGE 命令。

此命令从未集成到 PostgreSQL 中，需要大量工作才能达到生产质量

示例表

为了测试 MERGE 命令，我们首先创建三个示例表。

1. Stock 表，记录每种商品的库存数量。

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. Buy 表，记录我们今天为每种商品购买的数量。

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. Sale 表，记录我们今天为每种商品销售的数量。

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)

MERGE 的简单使用

第一个示例

我们可以使用 MERGE 命令将我们今天购买的商品添加到 Stock 表中。

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;

如我们所见，商品 10 的数量增加了 1000，商品 30 被插入到库存中。

带限定条件的合并操作

合并操作可以指定其附加限定条件。

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;

在这个例子中，Sale 的数量从 Stock 的余额中减去。

对于商品 20，原始余额为 1900，我们今天销售了 1000。剩余数量为 900，大于 0。因此，此元组满足 UPDATE 操作的条件，并相应更新。请注意，DELETE 操作不会对商品 20 进行，因为它具有较低的优先级，即使它的条件也满足。

对于商品 10，剩余余额为 3200 - 3200 = 0，因此它被删除。

DO NOTHING 操作

在 MERGE 命令中，用户可以指定一个特殊的 "DO NOTHING" 操作。所有被此操作捕获的元组将被忽略。DO NOTHING 也可以有附加限定条件，并在 MATCHED 和 NOT MATCHED 中工作。

在以下示例中，商品 20 匹配 DO NOTHING 操作的要求。因此它保持不变，而商品 10 被 DELETE 操作删除。

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 也在 NOT MATCHED 情况下工作

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;

在这个例子中，MATCHED 商品（商品 10）被更新，而 NOT MATCHED 商品（商品 30）被忽略。

RAISE ERROR 操作

MERGE 中的另一个特殊操作是 RAISE ERROR。符合此操作的元组会导致错误。目前，错误处理只是抛出一个简短的错误消息。这将在将来扩展。MERGE 命令的主循环将在这种错误的情况下停止。RAISE ERROR 的语法类似于 DO NOTHING。

1. 它可以适合 MATCHED 和 NOT MATCHED 情况

2. 它不会改变元组。

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;

例如，在上面的查询中，商品 20 的元组导致错误，而商品 10 在错误之后被删除。

RAISE ERROR 是对与任何操作都不匹配的元组的默认操作。

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;

如我们所见，有一个 NOT MATCHED 元组（商品 30），它被用户指定的操作遗漏。因此，它引发错误。

更复杂的用法

使用查询作为源表

源表可以是带别名的查询，如下面的示例所示。

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;

在这里，我们想将 Buy 和 Sale 的数量加起来，并将结果合并到 Stock 中。我们今天为商品 10 购买了 1000 并销售了 2200。商品 10 的旧余额为 2200。因此，我们只剩下 1000。商品 20 没有改变，因为源查询 "BS" 只有商品 10 的元组。

子计划/子链接在操作中的应用

例如，假设有一个 Extra 表，记录股票的所有琐碎交易。当我们通过 MERGE 命令更新股票余额时，有必要包含这些琐碎的交易。

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);

那么第一个示例的新合并查询变为

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;

在这个例子中，我们添加了所有琐碎交易的总和。在这里我们可以看到，商品 10 的新余额变为 3213（原来是 3200），其中额外的 13 来自 Extra 表。

继承的 MERGE

MERGE 也可以应用于继承表。目标表的子表将默认扫描和修改。

例如，我们创建一个新的股票表，以及一个从它继承的子表。为了使情况更加复杂，我们在创建表之后添加了一个属性。

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);

现在 p_Stock 表有两个属性，c_stock 表有三个属性。请注意，balance 是 p_stock 中的第二个属性，但它是 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)

现在我们将 MERGE 命令应用于 Stock 和 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;

在这个查询中，p_stock 中的商品 10 被更新为 3200 的余额，而 c_stock 中的商品 30 被更新为 1000 的余额。

解释 MERGE

一般来说，MERGE 命令的 EXPLAIN 结果有 4 部分

1. 标题：第一行是“MERGE”的标题，包含整个计划的成本（如果成本显示未关闭）。

2. 初始化计划：如果有任何初始化计划，将显示所有初始化计划。初始化计划首先执行，因此它们首先显示。

3. 合并操作：在初始化计划之后，是所有合并操作的列表，按照用户输入的顺序排列。每个操作将告诉其操作类型、操作限定条件和操作返回目标列表（如果 VERBOSE 处于开启状态）。如果某个操作中涉及任何子计划，它们将立即在操作下方打印出来。

4. 主计划：源表 LEFT JOIN 目标表 的连接计划

例如，一个简单的 MERGE EXPLAIN 可能如下所示

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)

与其他命令一样，EXPLAIN ANALYZE MERGE ... 将执行合并命令并告诉实际运行时间。

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;

一个更复杂（但意义更小）的 MERGE 查询解释如下：

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

由操作触发的规则

如果我们在 MERGE 命令的目标表上定义规则，MERGE 操作将应用该规则。

例如，假设创建一个名为 "count_by_rule" 的表，用于记录对 "Stock" 表的修改次数。

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

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

我们需要创建规则以自动维护计数表。

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;

现在让我们再次运行第二个示例。

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;

我们可以看到，count 表中的 update 和 delete 字段都增加了 1，这意味着 "update_count" 和 "delete_count" 规则都被 MERGE 命令触发。

同一类型的多个操作

如果一个 MERGE 命令包含多个相同类型的操作，则该操作类型的规则只会被激活一次。

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;

在上面的例子中，我们在 MERGE 命令中有两个 UPDATE 操作。查询之后，我们可以发现 "count.update" 的值只增加了 1（而不是 2），这意味着 "update_count" 规则已被激活了一次。

INSTEAD 规则

用 INSTEAD 规则替换更新规则。

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

再次尝试 MERGE 查询。

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;

从这个例子我们可以发现，"update_count" 和 "delete_count" 规则正确更新了 count 表。

如我们所见，商品 10 被 DELETE 操作删除。

另一方面，由于更新操作被 INSTEAD 规则替换，因此商品 20 的余额没有改变。

使用触发器的 MERGE

行级触发器

MERGE 目标表的行级触发器将被相同类型的操作激活。例如，假设我们想记录所有进入 Stock 表的新元组。我们可以首先创建一个 mirror_stock 表。如下所示

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

然后在 Stock 上创建行级触发器，它将把 INSERT 或 UPDATE 命令创建的所有新元组插入 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();

现在，让我们再次尝试 UPDATE+INSERT 示例

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;

在这个例子中，两个行级触发器分别被两种不同类型的 MERGE 操作触发，并根据我们的预期在 mirror_stock 中插入日志元组。

语句级触发器

例如，我们可以使用 BEFORE 语句触发器来计算 Stock 被应用于 UPDATE/INSERT 命令的次数。

在我们开始之前，先删除之前创建的 INSTEAD 规则。

DROP RULE update_count ON Stock CASCADE; 

首先，创建一个用于计数的表。

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

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

然后，创建触发器。

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();

现在，运行 MERGE 命令。

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;

如我们所见，这两个触发器（"count_insert" 触发器和 "count_update" 触发器）都被此语句触发。

同一类型的多个操作

如果一个 MERGE 命令包含多个相同类型的操作，则相应的语句触发器只会被触发一次。

请参见以下示例。MERGE 命令有两个 UPDATE 操作，但 count_by_trigger 表只更新了一次。

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 规则阻止触发器

如果一种操作被 INSTEAD 规则替换，它将不会触发触发器。

让我们创建一个 INSTEAD 规则。

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

然后，再次运行同一个示例。

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;

UPDATE 操作被规则替换，因此它不会触发 UPDATE 触发器。