Re: FOR EACH ROW triggers on partitioned tables

From: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
To: Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>, Simon Riggs <simon(at)2ndquadrant(dot)com>
Subject: Re: FOR EACH ROW triggers on partitioned tables
Date: 2018-03-08 18:06:49
Message-ID: 20180308180649.zdcubcx76tymwnrh@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Thomas Munro wrote:

> What is this test for?
> +create trigger failed after update on parted_trig
> + referencing old table as old_table
> + for each statement execute procedure trigger_nothing();
> It doesn't fail as you apparently expected. Perhaps it was supposed
> to be "for each row" so you could hit your new error with
> errdetail("Triggers on partitioned tables cannot have transition
> tables.")?

You're absolutely right. Fixed in the attached version.

I also include two requisite fixes for missing CCI calls in existing
code: one is in StorePartitionBounds which I think is backpatchable to
pg10 (this is the one that was causing me to add the one Peter
complained about in [1]), and the others are in the partition indexing
code. In terms of the current tests, the first one is necessary in
order for things to work after this patch; the ones in the second patch
I only added after code review in order to understand where the first
one was. (In that second patch I also remove one which now seems
unnecessary and in hindsight was probably there because I was lacking
the others.)

Patch 0003 is the feature at hand. Compared to v3, this version adds
some recursing logic during ENABLE/DISABLE TRIGGER, so the test that was
previously failing now works correctly.

I kept the test on "irregular" partitioning from v5, too; it works here
without any further changes.

One thing I'd like to add before claiming this committable (backend-
side) is enabling constraint triggers. AFAICT that requires a bit of
additional logic, but it shouldn't be too terrible. This would allow
for deferrable unique constraints, for example.


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

Attachment Content-Type Size
v6-0001-add-missing-CommandCounterIncrement-in-StoreParti.patch text/plain 878 bytes
v6-0002-Add-missing-CommandCounterIncrement-in-partitione.patch text/plain 2.2 KB
v6-0003-Allow-FOR-EACH-ROW-triggers-on-partitioned-tables.patch text/plain 42.2 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2018-03-08 18:09:54 Re: Temporary tables prevent autovacuum, leading to XID wraparound
Previous Message Pavel Stehule 2018-03-08 18:00:36 Re: [HACKERS] proposal: schema variables