BUG #16794: BEFORE UPDATE FOR EACH ROW triggers on partitioned tables can break tuple moving UPDATEs - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      16794
Logged by:          Philipp Menke
Email address:      pg@pmenke.de
PostgreSQL version: 13.1
Operating system:   Linux
Description:

Hi there,

i was testing the PG13 enhancement that should allow BEFORE ROW triggers on
partitioned tables, as long as they don't move the tuple to a different
partition (original thread:
https://postgr.es/m/20200227165158.GA2071@alvherre.pgsql). The actual
restriction on "not to move the tuple to a different partition" seems to be
a bit stronger though, as the trigger fails, even though not itself, but the
overarching UPDATE command, did move the tuple. Maybe this is best shown by
an example:

```
CREATE TABLE parted (
    part_key INT,
    changed_at TIMESTAMPTZ DEFAULT now()
) PARTITION BY RANGE(part_key);

CREATE TABLE parted_p0_9 PARTITION OF parted FOR VALUES FROM (0) TO (9);
CREATE TABLE parted_p10_19 PARTITION OF parted FOR VALUES FROM (10) TO
(19);

CREATE FUNCTION parted_audit_trig() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    NEW.changed_at = now();
    RETURN NEW;
END;
$$;

CREATE TRIGGER a01_audit_trig BEFORE UPDATE ON parted FOR EACH ROW EXECUTE
PROCEDURE parted_audit_trig();

INSERT INTO parted(part_key) VALUES (1);

UPDATE parted SET part_key = 11 WHERE part_key = 1;
```

The final UPDATE statement fails with:
```
[0A000] ERROR: moving row to another partition during a BEFORE trigger is
not supported
Detail: Before executing trigger "a01_audit_trig", the row was to be in
partition "public.parted_p0_9".
```

At least according to the documentation
(https://www.postgresql.org/docs/13/ddl-partitioning.html 5.11.2.3.
Limitations) i would have expected that the UPDATE succeeds and moves the
tuple to parted_p10_19.

Interestingly the error seems to only occur if the trigger function actually
assigns a value to any field in NEW - even if it is the same value (as in
`NEW.changed_at = NEW.changed_at;`). If the trigger function does nothing /
performs checks etc. but doesn't assign any field in NEW, the statement
completes successfully.

Thanks and Kind Regards,
Philipp