Re: FOR EACH ROW triggers on partitioned tables

From: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: 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-07 17:17:53
Message-ID: 20180307171753.27xnxnjhlphur6bs@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Here's another version of this patch. It is virtually identical to the
previous one, except for a small doc update and whitespace changes.

To recap: when a row-level trigger is created on a partitioned table, it
is marked tginherits; partitions all have their pg_class row modified
with relhastriggers=true. No clone of the pg_trigger row is created for
the partitions. Instead, when the relcache entry for the partition is
created, pg_trigger is scanned to look for entries for its ancestors.
So the trigger list for a partition is created by repeatedly scanning
pg_trigger and pg_inherits, until only entries with relhastriggers=f are

I reserve the right to revise this further, as I'm going to spend a
couple of hours looking at it this afternoon, particularly to see how
concurrent DDL behaves, but I don't see anything obviously wrong with

Robert Haas wrote:

> Elsewhere, we've put a lot of blood, sweat, and tears into making sure
> that we only traverse the inheritance hierarchy from top to bottom.
> Otherwise, we're adding deadlock hazards. I think it's categorically
> unacceptable to do traversals in the opposite order -- if you do, then
> an UPDATE on a child could deadlock with a LOCK TABLE on the parent.
> That will not win us any awards.

We don't actually open relations or acquire locks in the traversal I was
talking about, though; the only thing we do is scan pg_trigger using
first the partition relid, then seek the ancestor(s) by scanning
pg_inherits and recurse. We don't acquire locks on the involved
relations, so there should be no danger of deadlocks. Changes in the
definitions ought to be handled by the cache invalidations that are
sent, although I admit to not having tested this specifically. I'll do
that later today.

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

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

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2018-03-07 17:30:17 Re: [HACKERS] GSoC 2017: weekly progress reports (week 6)
Previous Message Stephen Frost 2018-03-07 17:17:32 Re: public schema default ACL