From: Justin Pryzby <pryzby@telsasoft.com>
To: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: DETACH PARTITION and FOR EACH ROW triggers on partitioned tables
Date: Sun, 19 Apr 2020 14:18:54 -0500
Message-ID: <20200419191854.GA14956@telsasoft.com> (raw)
In-Reply-To: <20200408164433.GE2228@telsasoft.com>
References: <20200408152412.GZ2228@telsasoft.com>
<20200408160239.GA13987@alvherre.pgsql>
<20200408164433.GE2228@telsasoft.com>
On Wed, Apr 08, 2020 at 11:44:33AM -0500, Justin Pryzby wrote:
> On Wed, Apr 08, 2020 at 12:02:39PM -0400, Alvaro Herrera wrote:
> > On 2020-Apr-08, Justin Pryzby wrote:
> >
> > > This seems to be a bug in master, v12, and (probably) v11, where "FOR EACH FOR"
> > > was first allowed on partition tables (86f575948).
> > >
> > > I thought this would work like partitioned indexes (8b08f7d48), where detaching
> > > a partition makes its index non-inherited, and attaching a partition marks a
> > > pre-existing, matching partition as inherited rather than creating a new one.
> >
> > Hmm. Let's agree to what behavior we want, and then we implement that.
> > It seems to me there are two choices:
> >
> > 1. on detach, keep the trigger but make it independent of the trigger on
> > parent. (This requires that the trigger is made dependent on the
> > trigger on parent, if the table is attached as partition again;
> > otherwise you'd end up with multiple copies of the trigger if you
> > detach/attach multiple times).
> >
> > 2. on detach, remove the trigger from the partition.
> >
> > I think (2) is easier to implement, but (1) is the more convenient
> > behavior.
>
> At telsasoft, we don't care (we uninherit tables before ALTERing parents to
> avoid disruptive locking and to avoid worst-case disk use).
I realized that I was wrong about what would be most desirable for us, for an
uncommon case:
Our loader INSERTs into the child table, not the parent (I think I did that to
try to implement UPSERT before partitioned indexes in v11).
All but the newest partitions are DETACHed when we need to promote a column.
It's probably rare that we'd be inserting into a table old enough to be
detached, and normally that would be ok, but if a trigger were missing, it
would misbehave. In our use-case, we're creating trigger on the parent as a
convenient way to maintain them on the partitions, which doesn't work if a
table exists but detached..
So we'd actually prefer the behavior of indexes/constraints, where the trigger
is preserved if the child is detached. I'm not requesting to do that just for
our use case, which may be atypical or not a good model, but adding our one
data point.
--
Justin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: pryzby@telsasoft.com, alvherre@2ndquadrant.com, pgsql-hackers@lists.postgresql.org
Subject: Re: DETACH PARTITION and FOR EACH ROW triggers on partitioned tables
In-Reply-To: <20200419191854.GA14956@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox