From: Tom Lane <tgl@sss.pgh.pa.us>
To: Amit Langote <amitlangote09@gmail.com>
cc: Alvaro Herrera <alvherre@2ndquadrant.com>,
        Justin Pryzby <pryzby@telsasoft.com>,
        PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: DETACH PARTITION and FOR EACH ROW triggers on partitioned tables
In-reply-to: 
 <CA+HiwqEwjmZqyCHahSU+vRM62w03U0t7fxPZ_MhYuj+r6-d0wA@mail.gmail.com>
References: <20200408180110.GA17093@alvherre.pgsql>
 <5954.1586369358@sss.pgh.pa.us>
 <CA+HiwqEwjmZqyCHahSU+vRM62w03U0t7fxPZ_MhYuj+r6-d0wA@mail.gmail.com>
Comments: In-reply-to Amit Langote <amitlangote09@gmail.com>
	message dated "Thu, 09 Apr 2020 22:04:02 +0900"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <27751.1586439998.1@sss.pgh.pa.us>
Date: Thu, 09 Apr 2020 09:46:38 -0400
Message-ID: <27752.1586439998@sss.pgh.pa.us>
Precedence: bulk

Amit Langote <amitlangote09@gmail.com> writes:
> On Thu, Apr 9, 2020 at 3:09 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> My point is that so long as you only allow the case of exactly one parent,
>> you can just delete the child trigger, because it must belong to that
>> parent.  As soon as there's any flexibility, you are going to end up
>> reinventing all the stuff we had to invent to manage
>> maybe-or-maybe-not-inherited columns.  So I think the "detach" idea
>> is the first step on that road, and I counsel not taking that step.

> As mentioned upthread, we have behavior #1 for indexes (attach
> existing / detach & keep), without any of the *islocal, *inhcount
> infrastructure. It is a bit complex, because we need logic to check
> the equivalence of an existing index on the partition being attached,
> so implementing the same behavior for trigger is going to have to be
> almost as complex.  Considering that #2 will be much simpler to
> implement, but would be asymmetric with everything else.

I think there is justification for jumping through some hoops for
indexes, because they can be extremely expensive to recreate.
The same argument doesn't hold even a little bit for child
triggers, though.

Also it can be expected that an index will still behave sensibly after
its table is standalone, whereas that's far from obvious for a trigger
that was meant to work on partition member tables.

			regards, tom lane