Re: FOR EACH ROW triggers on partitioned tables

On 2018/01/30 5:30, Peter Eisentraut wrote:
> On 1/23/18 17:10, Alvaro Herrera wrote:
>> 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
>> opinions.
>
> One could go either way on this, but I think reporting the actual table
> partition is acceptable and preferable.

+1

> 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.

I see that the only parent-child relationship for triggers created
recursively is recorded in the form of a dependency. I wonder why not a
flag in, say, pg_trigger to indicate that a trigger may have been created
recursively. With the committed for inherited indexes, I can see that
inheritance is explicitly recorded in pg_inherits because indexes are
relations, so it's possible in the indexes' case to get the parent in
which a given inherited index originated.

> Similarly, transition tables should be OK. You only get the current
> partition to look at, of course.

+1

> The function name CloneRowTriggersOnPartition() confused me. A more
> accurate phrasing might be CloneRowTriggersToPartition(), or maybe
> reword altogether.

CloneRowTriggers*For*Partition()?

Thanks,
Amit