Re: BEFORE ROW triggers for partitioned tables

From: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
To: Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>
Cc: Pg Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, ashutosh(dot)bapat(at)2ndquadrant(dot)com
Subject: Re: BEFORE ROW triggers for partitioned tables
Date: 2020-03-13 16:25:20
Message-ID: 20200313162520.GA22557@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 2020-Mar-11, Ashutosh Bapat wrote:

> On Thu, Feb 27, 2020 at 10:22 PM Alvaro Herrera
> <alvherre(at)2ndquadrant(dot)com> wrote:

> > * The new function I added, ReportTriggerPartkeyChange(), contains one
> > serious bug (namely: it doesn't map attribute numbers properly if
> > partitions are differently defined).
> IIUC the code in your patch, it seems you are just looking at
> partnatts. But partition key can contain expressions also which are
> stored in partexprs. So, I think the code won't catch change in the
> partition key values when it contains expression. Using
> RelationGetPartitionQual() will fix this problem and also problem of
> attribute remapping across the partition hierarchy.

Oh, of course.

In fact, I don't need to deal with PartitionQual directly; I can just
use ExecPartitionCheck(), since in ExecBRInsertTriggers et al we already
have all we need. v2 attached.

Here's some example output. With my previous patch, this was the error
we reported:

insert into parted values (1, 1, 'uno uno v2'); -- fail
ERROR: changing partitioning columns in a before trigger is not supported
DETAIL: Column "a" was changed by trigger "t".

Now, passing emitError=true to ExecPartitionCheck, I get this:

insert into parted values (1, 1, 'uno uno v2'); -- fail
ERROR: new row for relation "parted_1_1" violates partition constraint
DETAIL: Failing row contains (2, 1, uno uno v2).

Note the discrepancy in the table named in the INSERT vs. the one in the
error message. This is a low-quality error IMO. So I'd instead pass
emitError=false, and produce my own error. It's useful to report the
trigger name and original partition name:

insert into parted values (1, 1, 'uno uno v2'); -- fail
ERROR: moving row to another partition during a BEFORE trigger is not supported
DETAIL: Before trigger "t", row was to be in partition "public.parted_1_1"

Note that in this implementation I no longer know which column is the
problematic one, but I suppose users have clue enough. Wording
suggestions welcome.

Álvaro Herrera
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachment Content-Type Size
v2-0001-Enable-BEFORE-row-level-triggers-for-partitioned-.patch text/x-diff 11.2 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2020-03-13 16:30:58 Re: Re: Optimize crash recovery
Previous Message Thunder 2020-03-13 16:22:16 Re:Re: Optimize crash recovery