merge.sql

application/sql

Download

/*
 * "MERGE provides a single SQL statement that can conditionally INSERT, UPDATE
 *  or DELETE rows, a task that would otherwise require multiple procedural
 *  language statements."
 *
 *  - SQL MERGE docs from v14 of the patch
 *
 * This test case illustrates just how true this is.
 */

\set VERBOSITY default
\timing off
SET client_min_messages=notice;
DROP TABLE IF EXISTS sq_target;
DROP TABLE IF EXISTS sq_source;
CREATE TABLE sq_target (tid integer NOT NULL, balance integer);
CREATE TABLE sq_source (delta integer, sid integer, balance integer DEFAULT 0);

-- Create initial state
INSERT INTO sq_target(tid, balance) VALUES (1,100), (2,200), (3,300);
INSERT INTO sq_source(sid, delta) VALUES (1,10), (2,20), (4,40);

-------------------
-- Use SQL MERGE --
-------------------

MERGE INTO sq_target t
  USING sq_source s
  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;

-- Show results:
SELECT * FROM sq_target;

------------------------
-- Equivalent plpgsql --
------------------------

-- Create same initial state as before
TRUNCATE sq_target;
TRUNCATE sq_source;
INSERT INTO sq_target(tid, balance) VALUES (1,100), (2,200), (3,300);
INSERT INTO sq_source(sid, delta) VALUES (1,10), (2,20), (4,40);

-- My equivalent procedural code assumes that it doesn't have to guard against
-- a cardinality violation.
--
-- Note that Simon's patch guards against cardinality violations imperfectly,
-- since it only stops a row from being affected a second time.  That's not the
-- same thing as being sure that we won't even think about affecting a row a
-- second time.
--
-- I'm not arguing that Simon needs to do better; the point is that SQL MERGE
-- requires that we care about cardinality violations in that other sense so
-- that there is no ambiguity about any of the UPDATEs or DELETEs affecting
-- more than one row in each individual execution (as well as for the MERGE
-- operation as a whole -- a slightly different thing).
CREATE OR REPLACE FUNCTION simulate_merge() RETURNS void AS
$BODY$
DECLARE
r record;
BEGIN
  -- LOOP acquires snapshot:
  FOR r IN
    SELECT
    delta,
    sid,
    s.balance s_balance,
    tid,
    t.balance t_balance
    FROM sq_source s
    LEFT JOIN sq_target t ON tid = sid
    LOOP
      -- (r.tid IS NOT NULL) = WHEN MATCHED:
      IF (r.tid IS NOT NULL) and r.tid > 2 THEN
        -- This acquires new MVCC snapshot, which could easily differ from
        -- LEFT JOIN's:
        UPDATE sq_target SET balance = r.t_balance + r.delta WHERE tid = r.tid;
        IF FOUND THEN
          CONTINUE;
        END IF;
      END IF;
      -- (r.tid IS NULL) = WHEN NOT MATCHED:
      IF (r.tid IS NULL) THEN
        INSERT INTO sq_target(balance, tid) SELECT r.s_balance + r.delta, r.sid;
        -- (r.tid IS NOT NULL) = WHEN MATCHED:
      END IF;
      IF (r.tid IS NOT NULL) and r.tid < 2 THEN
        -- This acquires new MVCC snapshot, which could easily differ from
        -- either would-be UPDATE's, or LEFT JOIN's:
        DELETE FROM sq_target WHERE tid = r.tid;
      END IF;
    END LOOP;
  END
  $BODY$
  LANGUAGE plpgsql;

-- Execute:
SELECT * FROM simulate_merge();

-- Show results, which should match:
SELECT * FROM sq_target;