Re: Fast COPY FROM based on batch insert

On Wed, Jul 27, 2022 at 2:42 PM Andrey Lepikhov
<a(dot)lepikhov(at)postgrespro(dot)ru> wrote:
> On 7/22/22 13:14, Etsuro Fujita wrote:
> > On Fri, Jul 22, 2022 at 3:39 PM Andrey Lepikhov
> > <a(dot)lepikhov(at)postgrespro(dot)ru> wrote:
> >> Analyzing multi-level heterogeneous partitioned configurations I
> >> realized, that single write into a partition with a trigger will flush
> >> buffers for all other partitions of the parent table even if the parent
> >> haven't any triggers.
> >> It relates to the code:
> >> else if (insertMethod == CIM_MULTI_CONDITIONAL &&
> >> !CopyMultiInsertInfoIsEmpty(&multiInsertInfo))
> >> {
> >> /*
> >> * Flush pending inserts if this partition can't use
> >> * batching, so rows are visible to triggers etc.
> >> */
> >> CopyMultiInsertInfoFlush(&multiInsertInfo, resultRelInfo);
> >> }
> >>
> >> Why such cascade flush is really necessary, especially for BEFORE and
> >> INSTEAD OF triggers?
> >
> > BEFORE triggers on the chosen partition might query the parent table,
> > not just the partition, so I think we need to do this so that such
> > triggers can see all the rows that have been inserted into the parent
> > table until then.
> if you'll excuse me, I will add one more argument.
> It wasn't clear, so I've made an experiment: result of a SELECT in an
> INSERT trigger function shows only data, existed in the parent table
> before the start of COPY.

Is the trigger function declared VOLATILE? If so, the trigger should
see modifications to the parent table as well. See:

https://www.postgresql.org/docs/15/trigger-datachanges.html

Best regards,
Etsuro Fujita