Thread

MERGE: AFTER ROW trigger failure for cross-partition update

Dean Rasheed <dean.a.rasheed@gmail.com> — 2023-11-07T15:10:10Z

While working on the MERGE RETURNING patch, I noticed a pre-existing
MERGE bug --- ExecMergeMatched() should not call ExecUpdateEpilogue()
if ExecUpdateAct() indicates that it did a cross-partition update.

The immediate consequence is that it incorrectly tries (and fails) to
fire AFTER UPDATE ROW triggers, which it should not do if the UPDATE
has been turned into a DELETE and an INSERT:

DROP TABLE IF EXISTS foo CASCADE;

CREATE TABLE foo (a int) PARTITION BY LIST (a);
CREATE TABLE foo_p1 PARTITION OF foo FOR VALUES IN (1);
CREATE TABLE foo_p2 PARTITION OF foo FOR VALUES IN (2);
INSERT INTO foo VALUES (1);

CREATE OR REPLACE FUNCTION foo_trig_fn() RETURNS trigger AS
$$
BEGIN
  RAISE NOTICE 'Trigger: % %', TG_WHEN, TG_OP;
  IF TG_OP = 'DELETE' THEN RETURN OLD; END IF;
  RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER foo_b_trig BEFORE INSERT OR UPDATE OR DELETE ON foo
  FOR EACH ROW EXECUTE FUNCTION foo_trig_fn();
CREATE TRIGGER foo_a_trig AFTER INSERT OR UPDATE OR DELETE ON foo
  FOR EACH ROW EXECUTE FUNCTION foo_trig_fn();

UPDATE foo SET a = 2 WHERE a = 1;

NOTICE:  Trigger: BEFORE UPDATE
NOTICE:  Trigger: BEFORE DELETE
NOTICE:  Trigger: BEFORE INSERT
NOTICE:  Trigger: AFTER DELETE
NOTICE:  Trigger: AFTER INSERT
UPDATE 1

MERGE INTO foo USING (VALUES (1)) AS v(a) ON true
  WHEN MATCHED THEN UPDATE SET a = v.a;

NOTICE:  Trigger: BEFORE UPDATE
NOTICE:  Trigger: BEFORE DELETE
NOTICE:  Trigger: BEFORE INSERT
NOTICE:  Trigger: AFTER DELETE
NOTICE:  Trigger: AFTER INSERT
ERROR:  failed to fetch tuple2 for AFTER trigger

The attached patch fixes that, making the UPDATE path in
ExecMergeMatched() consistent with ExecUpdate().

(If there were no AFTER ROW triggers, the old code would go on to do
other unnecessary things, like WCO/RLS checks, which I didn't really
look into. This patch will stop any of that too.)

Regards,
Dean