Subject: Re: FOR EACH ROW triggers on partitioned tables
To: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>,
 Alvaro Herrera <alvherre@alvh.no-ip.org>
References: <20180123221027.2qenwwpvgplrrx3d@alvherre.pgsql>
 <171cb95a-35ec-2ace-3add-a8d16279f0bf@2ndquadrant.com>
 <bd2763a7-b845-8595-1cc1-a0244f692801@lab.ntt.co.jp>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>,
 Simon Riggs <simon@2ndquadrant.com>
From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Organization: 2ndQuadrant
Message-ID: <183aa6b7-468c-cc8d-12b2-aa5577bc807b@2ndquadrant.com>
Date: Tue, 30 Jan 2018 19:44:02 -0500
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.13; rv:52.0)
 Gecko/20100101 Thunderbird/52.5.2
MIME-Version: 1.0
In-Reply-To: <bd2763a7-b845-8595-1cc1-a0244f692801@lab.ntt.co.jp>
Content-Type: text/plain; charset=windows-1252
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk

On 1/30/18 04:49, Amit Langote wrote:
>> If you are writing a generic
>> trigger function, maybe to dump out all columns, you want to know the
>> physical table and its actual columns.  It's easy[citation needed] to
>> get the partition root for a given table, if the trigger code needs
>> that.  The other way around is not possible.
> 
> I guess you mean the root where a trigger originated, that is, ancestor
> table on which an inherited trigger was originally defined.  It is
> possible for a trigger to be defined on an intermediate parent and not the
> topmost root in a partition tree.

OK, so maybe not so "easy".

But this muddies the situation even further.  You could be updating
table A, which causes an update in intermediate partition B, which
causes an update in leaf partition C, which fires a trigger that was
logically defined on B and has a local child on C.  Under this proposal,
the trigger will see TG_RELNAME = C.  You could make arguments that the
trigger should also somehow know about B (where the trigger was defined)
and A (what the user actually targeted in their statement).  I'm not
sure how useful these would be.  But if you want to cover everything,
you'll need three values.

I think the patch can go ahead as proposed, and the other things could
be future separate additions.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services