BUG #16794: BEFORE UPDATE FOR EACH ROW triggers on partitioned tables can break tuple moving UPDATEs

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: pg(at)pmenke(dot)de
Subject: BUG #16794: BEFORE UPDATE FOR EACH ROW triggers on partitioned tables can break tuple moving UPDATEs
Date: 2020-12-28 12:49:43
Message-ID: 16794-350a655580fbb9ae@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 16794
Logged by: Philipp Menke
Email address: pg(at)pmenke(dot)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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Michael Paquier 2020-12-28 13:22:42 Re: Large objects and out-of-memory
Previous Message tomohiro hiramitsu 2020-12-28 07:31:31 Re: BUG #16722: PG hanging on COPY when table has close to 2^32 toasts in the table.