Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On Wed, May 2, 2018 at 9:17 AM, Ashutosh Bapat
<ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
> On Wed, May 2, 2018 at 11:56 AM, Amit Langote
> <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>> But one could very well argue that BEFORE ROW triggers on the
>> parent should run before performing the tuple routing and not be cloned to
>> individual partitions, in which case the result would not have been the
>> same. Perhaps that's the motivation behind leaving this to be considered
>> later.
>
> +1 for that. We should associate before row triggers with the
> partitioned table and not with the partitions. Those should be
> executed before tuple routing (for that partition level) kicks in. But
> then that would be asymetric with AFTER row trigger behaviour. From
> this POV, pushing AFTER row triggers to the individual partitions
> doesn't look good.

The asymmetry doesn't seem horrible to me on its own merits, but it
would lead to some odd behavior: suppose you defined a BEFORE ROW
trigger and an AFTER ROW trigger on the parent, and then inserted one
row into the parent table and a second row directly into a partition.
It seems like the BEFORE ROW trigger would fire only for the parent
insert, but the AFTER ROW trigger would fire in both cases.

What seems like a better idea is to have the BEFORE ROW trigger get
cloned to each partition just as we do with AFTER ROW triggers, but
arrange things so that if it already got fired for the parent table,
it doesn't get fired again after tuple routing. This would be a bit
tricky to get correct in cases where there are multiple levels of
partitioning involved, but it seems doable.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company