Re: Parallel INSERT (INTO ... SELECT ...)

On Tue, Oct 6, 2020 at 4:13 PM Greg Nancarrow <gregn4422(at)gmail(dot)com> wrote:
>
> On Tue, Oct 6, 2020 at 9:10 PM Bharath Rupireddy
> <bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
>
> > But the problem may arise if a partitioned table has foreign table as
> > a partition, I think we can not allow parallelism for this case too,
> > but it's hard to determine ahead of time whether a table has a foreign
> > partition.(See [1] in copy.c)
> >
>
> Thanks, I had seen that as a potential issue when scanning the code,
> but had forgotten to note it. I'll check your code again.
>

In parallel, we are not doing anything(due to the same reason
explained in above comment) to find whether there is a foreign
partition or not while deciding to go with parallel/non-parallel copy,
we are just throwing an error during the first tuple insertion into
the partition.

errmsg("cannot perform PARALLEL COPY if partition has BEFORE/INSTEAD
OF triggers, or if the partition is foreign partition"),
errhint("Try COPY without PARALLEL option")));

> > >
> > > - Added support for before/after statement-level INSERT triggers
> > > (can't allow parallel workers to execute these)
> > >
> >
> > I think we can allow parallelism for before statement level-triggers.
> > Leader can execute this trigger and go for parallel inserts.
> >
>
> My attached patch implements the before/after statement-level trigger
> invocation.
> (For INSERT INTO ... SELECT... case, it needs to account for parallel
> and non-parallel INSERT, and also the fact that, as the patch
> currently stands, the leader also participates in a parallel INSERT -
> so I found it necessary to invoke those triggers at the Gather node
> level in that case).
>

Allowing the leader to execute before statement triggers at Gather
node level before invoking the parallel plan and then parallel inserts
makes sense. But if there are any after statement triggers, there may
come transition tables, see Amit's findings under Case-1 in [1] and we
must disable parallelism in that case.

[1] - https://www.postgresql.org/message-id/flat/CAA4eK1%2BANNEaMJCCXm4naweP5PLY6LhJMvGo_V7-Pnfbh6GsOA%40mail.gmail.com

With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com