Re: FOR EACH ROW triggers on partitioned tables

From: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
To: Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>
Cc: 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-01-23 22:10:27
Message-ID: 20180123221027.2qenwwpvgplrrx3d@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Peter Eisentraut wrote:
> On 12/29/17 17:53, Alvaro Herrera wrote:
> > This patch enables FOR EACH ROW triggers on partitioned tables.
> >
> > As presented, this patch is sufficient to discuss the semantics that we
> > want for triggers on partitioned tables, which is the most pressing
> > question here ISTM.
> This seems pretty straightforward. What semantics questions do you have?

The main question is this: when running the trigger function, it is
going to look as it is running in the context of the partition, not in
the context of the parent partitioned table (TG_RELNAME etc). That
seems mildly ugly: some users may be expecting that the partitioning
stuff is invisible to the rest of the system, so if you have triggers on
a regular table and later on decide to partition that table, the
behavior of triggers will change, which is maybe unexpected. Maybe this
is not really a problem, but I'm not sure and would like further

Anyway, the attached v2 has the following changes
any triggers from the main table, as if the trigger had been created
with the partitions in place.

2. dependencies work correctly: dropping the trigger on a partition is
disallowed; dropping the table removes the trigger. This is pretty
much the same behavior we have for indexes in partitions; I've reused
the new dependency type.

While existing pg_dump tests pass, I have not verified that it does
anything remotely sensible.

> Also, does ALTER TABLE ... ENABLE/DISABLE TRIGGER do the right things on
> partitioned tables?

Haven't done this yet, either. I like Simon's suggestion of outright
disallowing this.

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

Attachment Content-Type Size
v2-0001-Allow-FOR-EACH-ROW-triggers-on-partitioned-tables.patch text/plain 32.2 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Geoghegan 2018-01-23 22:10:51 Re: [HACKERS] parallel.c oblivion of worker-startup failures
Previous Message Thomas Munro 2018-01-23 22:08:53 Re: [HACKERS] Parallel tuplesort (for parallel B-Tree index creation)