how-do-triggers-work.sql

/*
 * This file is to show how triggers work in various contexts in Postgres.
 * The goal is to make sure what we do with UPDATE/DELETE FOR PORTION OF leftovers
 * is consistent. For example do the leftovers' INSERTs fire statement triggers?
 * Row triggers? Before or after the AFTER UPDATE/DELETE triggers fire?
 * What should be in the transition tables?
 *
 * We test:
 *
 * - INSERT ON CONFLICT DO NOTHING (without & with a conflict)
 * - INSERT ON CONFLICT DO UPDATE (without & with a conflict)
 * - INSERT ON CONFLICT DO UPDATE WHERE (with a conflict)
 * - MERGE DO NOTHING (without & with a conflict)
 * - MERGE UPDATE (without & with a conflict)
 * - cross-partition UPDATE
 * - ON DELETE CASCADE
 * - ON DELETE SET NULL
 */
DROP TABLE IF EXISTS t, t2, t3, t4 CASCADE;
CREATE TABLE t (
  id int primary key,
  name text
);

-- Print details about what fired.
CREATE OR REPLACE FUNCTION print_trig()
RETURNS trigger
AS
$$
DECLARE
BEGIN
  RAISE NOTICE '%: % % %:', tg_table_name, tg_when, tg_op, tg_level;
  IF tg_argv[0] THEN
    RAISE NOTICE '  old: %', (SELECT string_agg(oldt::text, '\n       ') FROM oldt);
  END IF;
  IF tg_argv[1] THEN
    RAISE NOTICE '  new: %', (SELECT string_agg(newt::text, '\n       ') FROM newt);
  END IF;
  IF tg_op = 'INSERT' OR tg_op = 'UPDATE' THEN
    RETURN NEW;
  ELSIF tg_op = 'DELETE' THEN
    RETURN OLD;
  END IF;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION add_triggers(table_name text, partitioned boolean = false)
RETURNS VOID
AS
$$
BEGIN
  -- Flagrantly ignore sqli danger:
  EXECUTE format($ddl$
    CREATE TRIGGER %1$s_before_stmt BEFORE INSERT OR UPDATE OR DELETE ON %1$s
    FOR EACH STATEMENT EXECUTE FUNCTION print_trig();

    CREATE TRIGGER %1$s_after_insert_stmt AFTER INSERT ON %1$s
    REFERENCING NEW TABLE AS newt
    FOR EACH STATEMENT EXECUTE FUNCTION print_trig(false, true);

    CREATE TRIGGER %1$s_after_update_stmt AFTER UPDATE ON %1$s
    REFERENCING OLD TABLE AS oldt NEW TABLE AS newt
    FOR EACH STATEMENT EXECUTE FUNCTION print_trig(true, true);

    CREATE TRIGGER %1$s_after_delete_stmt AFTER DELETE ON %1$s
    REFERENCING OLD TABLE AS oldt
    FOR EACH STATEMENT EXECUTE FUNCTION print_trig(true, false);

    CREATE TRIGGER %1$s_before_row BEFORE INSERT OR UPDATE OR DELETE ON %1$s
    FOR EACH ROW EXECUTE FUNCTION print_trig();
  $ddl$, table_name);

  IF partitioned THEN
    -- Row triggers with transition tables are not permitted on partitioned tables:
    EXECUTE format($ddl$
      CREATE TRIGGER %1$s_after_row AFTER INSERT OR UPDATE OR DELETE ON %1$s
      FOR EACH ROW EXECUTE FUNCTION print_trig();
    $ddl$, table_name);
  ELSE
    EXECUTE format($ddl$
      CREATE TRIGGER %1$s_after_insert_row AFTER INSERT ON %1$s
      REFERENCING NEW TABLE AS newt
      FOR EACH ROW EXECUTE FUNCTION print_trig(false, true);

      CREATE TRIGGER %1$s_after_update_row AFTER UPDATE ON %1$s
      REFERENCING OLD TABLE AS oldt NEW TABLE AS newt
      FOR EACH ROW EXECUTE FUNCTION print_trig(true, true);

      CREATE TRIGGER %1$s_after_delete_row AFTER DELETE ON %1$s
      REFERENCING OLD TABLE AS oldt
      FOR EACH ROW EXECUTE FUNCTION print_trig(true, false);
    $ddl$, table_name);
  END IF;
END;
$$
LANGUAGE plpgsql;

SELECT add_triggers('t');

INSERT INTO t VALUES (1, 'a');

INSERT INTO t VALUES (2, 'a') ON CONFLICT (id) DO NOTHING;
INSERT INTO t VALUES (2, 'b') ON CONFLICT (id) DO NOTHING;

INSERT INTO t VALUES (3, 'a') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;
INSERT INTO t VALUES (3, 'b') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;
INSERT INTO t VALUES (3, 'c') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name WHERE false;

WITH src(id, name) AS (
  (VALUES (4, 'a'))
)
MERGE INTO t
  USING src ON t.id = src.id
  WHEN MATCHED THEN DO NOTHING
  WHEN NOT MATCHED BY TARGET THEN INSERT VALUES (id, name);

WITH src(id, name) AS (
  (VALUES (4, 'b'))
)
MERGE INTO t
  USING src ON t.id = src.id
  WHEN MATCHED THEN DO NOTHING
  WHEN NOT MATCHED BY TARGET THEN INSERT VALUES (id, name);

WITH src(id, name) AS (
  (VALUES (5, 'a'))
)
MERGE INTO t
  USING src ON t.id = src.id
  WHEN MATCHED THEN UPDATE SET name = src.name
  WHEN NOT MATCHED BY TARGET THEN INSERT VALUES (id, name);

WITH src(id, name) AS (
  (VALUES (5, 'b'))
)
MERGE INTO t
  USING src ON t.id = src.id
  WHEN MATCHED THEN UPDATE SET name = src.name
  WHEN NOT MATCHED BY TARGET THEN INSERT VALUES (id, name);

CREATE TABLE t2 (
  id int primary key,
  name text
) PARTITION BY LIST (id);
CREATE TABLE t2p1 PARTITION OF t2 FOR VALUES IN (1, 3, 5, 7);
CREATE TABLE t2p2 PARTITION OF t2 FOR VALUES IN (2, 4, 6, 8);

SELECT add_triggers('t2', true);

INSERT INTO t2 VALUES (1, 'a');
UPDATE t2 SET id = 2 WHERE id = 1;

-- Do delete triggers fire when a foreign key has ON DELETE CASCADE?
CREATE TABLE t3 (
  id int primary key,
  parent_id int REFERENCES t (id) ON DELETE CASCADE
);
SELECT add_triggers('t3');
INSERT INTO t (id, name) VALUES (10, 'a');
INSERT INTO t3 (id, parent_id) VALUES (1, 10);
DELETE FROM t WHERE id = 10;

-- Do delete triggers fire when a foreign key has ON DELETE SET NULL?
CREATE TABLE t4 (
  id int primary key,
  parent_id int REFERENCES t (id) ON DELETE SET NULL
);
SELECT add_triggers('t4');
INSERT INTO t (id, name) VALUES (11, 'a');
INSERT INTO t4 (id, parent_id) VALUES (1, 11);
DELETE FROM t WHERE id = 11;