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

On Fri, Nov 13, 2020 at 8:14 PM Greg Nancarrow <gregn4422(at)gmail(dot)com> wrote:
>
> On Wed, Nov 4, 2020 at 2:18 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> >
> > Or you might want to consider moving the check related to
> > IsModifySupportedInParallelMode() inside
> > PrepareParallelModeForModify(). That way the code might look a bit
> > cleaner.
> >
>
> Posting an updated Parallel SELECT for "INSERT INTO ... SELECT ..."
> patch which addresses previously-identified issues and suggestions,
> and adds some tests and doc updates.
> I won't post an updated Parallel INSERT patch just yet (which just
> builds on the 1st patch), because there's at least a couple of issues
> in this 1st patch which need to be discussed first.
>
> Firstly, in order to perform parallel-safety checks in the case of
> partitions, the patch currently recursively locks/unlocks
> (AccessShareLock) each partition during such checks (as each partition
> may itself be a partitioned table). Is there a better way of
> performing the parallel-safety checks and reducing the locking
> requirements?
>
> Secondly, I found that when running "make check-world", the
> "partition-concurrent-attach" test fails, because it is expecting a
> partition constraint to be violated on insert, while an "alter table
> attach partition ..." is concurrently being executed in another
> transaction. Because of the partition locking done by the patch's
> parallel-safety checking code, the insert blocks on the exclusive lock
> held by the "alter table" in the other transaction until the
> transaction ends, so the insert ends up successfully completing (and
> thus fails the test) when the other transaction ends. To overcome this
> test failure, the patch code was updated to instead perform a
> conditional lock on the partition, and on failure (i.e. because of an
> exclusive lock held somewhere else), just assume it's parallel-unsafe
> because the parallel-safety can't be determined without blocking on
> the lock. This is not ideal, but I'm not sure of what other approach
> could be used and I am somewhat reluctant to change that test. If
> anybody is familiar with the "partition-concurrent-attach" test, any
> ideas or insights would be appreciated.
>

Posting an updated set of patches, with some additional testing and
documentation updates, and including the latest version of the
Parallel Insert patch.
Any feedback appreciated, especially on the two points mentioned in
the previous post.

Regards,
Greg Nancarrow
Fujitsu Australia