MERGE: AFTER ROW trigger failure for cross-partition update

From: Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: MERGE: AFTER ROW trigger failure for cross-partition update
Date: 2023-11-07 15:10:10
Message-ID: CAEZATCWjBgagyNZs02vgDF0DvASYj-iHTFtXG2-nP3orZhmtcw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
fix-merge-after-triggers-for-cp-update.patch text/x-patch 6.0 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2023-11-07 15:24:32 Re: Relids instead of Bitmapset * in plannode.h
Previous Message Nathan Bossart 2023-11-07 15:04:46 Re: Commitfest: older Waiting on Author entries