RE: Parallel INSERT SELECT take 2

On Friday, August 7, 2026 2:01 AM Tomas Vondra <tomas(at)vondra(dot)me> wrote:
> On 8/4/26 05:45, Zhijie Hou (Fujitsu) wrote:
> > On Monday, May 11, 2026 7:44 PM Tomas Vondra <tomas(at)vondra(dot)me>
> wrote:
>
> Thanks for the patch.
>
> > The detailed design is as follows:

Thanks for checking.

> >
> > Modifying a table while in parallel mode is only safe if none of the
> > objects attached to the table involves parallel-unsafe or parallel-restricted
> functions:
> > triggers, index expressions and predicates, CHECK constraints, column
> > default expressions, and the partition key. For a partitioned table,
> > each partition is checked recursively as well.
> >
>
> Is this aiming to allow the insert to be parallel too, or just the select part? If
> only the select part is parallel, wouldn't it be OK to have parallel-restricted
> expressions?

Sorry for the typo. The current patch only supports parallel SELECT for INSERT,
so parallel-restricted is fine.

> > Because we do not lock the function while altering its safety, a race
> > is
> > possible: the safety flag could change after another backend has
> > already used the cached value to build its plan. This is no worse than
> > current HEAD behavior, since a function has always been free to be
> > altered without blocking concurrent DML.
> >
> So the user can execute a long-running DML, and while it's running someone
> could alter all kinds of function parameters, including the parallel safety. But
> the DML will keep running.
>
> I guess that's probably OK, or rather not a new issue.
>
> The next execution should use the correct "new" value, right? Or could it
> happen that we miss an invalidation? Let's say we start building the relcache
> entry, when a function gets altered. Could it happen that we end up with a
> "stale" value of the flag in relcache until the end of the session?

The v1 patch has a bug that could cause stale value, but I think
after fixing in v2, this won't happen anymore.

>
> > When a partition's parallel safety changes, we invalidate the cached
> > values of all its ancestors in the partition tree. We deliberately do
> > not take locks on the ancestors: this avoids introducing new deadlock
> > risk, and it avoids changing locking behavior in a way that would make
> > commands suddenly block normal DML that previously ran unimpeded - a
> > change that might be hard for users to reason about. Find the
> > ancestors without locking is safe because a partition cannot be
> > concurrently attached or detached - both ATTACH PARTITION and DETACH
> > PARTITION lock the child tables during DDL execution. The only
> > remaining race is that an unsafe object could be added to a partition
> > while a parallel INSERT ... SELECT is already executing; in that case,
> > execution detects the hazard and raises an ERROR before inserting into that
> partition. This rare, detectable execution-time error is the trade-off for
> keeping the locking scheme simple.
> >
>
> Hmm, OK. We need to document this argument somewhere (maybe it's in a
> comment already, not sure). Would it be possible to have a TAP test (with
> injection points?) / isolation test for this?

Yes, I added this in the doc and have tests in 0003 patch.

>
> > Because the cached value lives in relcache, every new session must
> > recompute it on first use. This is true of all relcache data, but it
> > is slightly more costly here since the safety computation can be
> > noticeable for tables with many partitions. A possible future
> > improvement is a fixed-size shared hash table storing parallel-safety
> > values, so that only the first session to touch a table pays the computation
> cost while later sessions can reuse the result.
> >
>
> This is my main concern - that we'll have to do this (possibly fairly
> expensive) check, even for short inserts that can't possibly benefit from
> parallelism. What's the worst case impact? Say I have a workload that runs
> small inserts into a partitioned table, each in a new connection. How
> expensive would that be?
>
> (I agree opening a new connection for each insert seems silly, it's merely a
> synthetic worst-case scenario.)

I ran some quick tests to measure planning time for INSERT into a partitioned
table:

- 100 partitions ~5ms
- 1000 partitions ~50ms
- 10000 partitions ~500ms

Test environment: Intel(R) Xeon(R) CPU E5-2690 v4 @ 2.60GHz, 56 CPUs, 200GB RAM

I agree that in the worst case, for large number of partitions, this could
increase planning time for short INSERT statements without using the cached
value, and can be optimized if we avoid opening new connection for each insert
select.

>
> > Just share this approach for discussing, if this approach and the old
> > approach[1] both turn out to be unacceptable, we could switch to
> > Tomas's approach of materialize the SELECT result. The attachment
> > implements this approach for reference.
> >
>
> +1 to using the spilling approach as a fall back, in case this turns out
> not to work.
>
> I wonder if we might want to do both. The spilling approach does not even
> need to check the parallel safety - it can parallelize even cases where the
> insert has parallel unsafe expressions, etc. I wonder how many cases would
> benefit from that.

It sounds reasonable to have both approaches, such as forcing the spilling
approach if the table is parallel unsafe. Since functions like nextval (which
can be used in column default expressions) are parallel unsafe, the spilling
mode could help improve performance in such cases.

>
> > To provide context for the old approach for comparison, here is a
> > brief overview of how it works. It is a hybrid approach: we allow the
> > user to explicitly specify the parallel safety of a table via a new DDL
> command, ALTER TABLE ...
> > PARALLEL SAFETY. If the user does not specify it, the behavior depends
> > on the table type: for non-partitioned tables, we automatically
> > compute the safety and cache it in relcache; for partitioned tables,
> > we default to disallowing parallel SELECT. To enable parallel INSERT
> > ... SELECT on a partitioned table, the user must set the safety
> > manually. When executing ALTER TABLE ... PARALLEL SAFETY, we validate
> > whether the table actually matches the specified safety level and raise an
> ERROR if it does not.
> >
>
> FWIW I agree we really don't want to require users to specify parallel safety
> for tables - doing that manually would be very tedious, error prone (how
> would the user even know with tables to modify?). Unlike for functions, the
> database is able that for tables.
>
> My main question is whether it's OK to store that just in the relcache, and why
> it's safer/better than actually keeping it in pg_class. I mean, we could reset
> that just like the relcache, the first session would update it with the fresh
> value, etc.

I thought about this a bit, but I'm not sure the same mechanism can be used to
maintain a catalog column. If we follow the same approach, ALTER FUNCTION
PARALLEL SAFETY would need to update every table record in pg_class. I think
that wouldn't be safe because we couldn't catch all tables due to visibility
issues - for example, if another table is being created concurrently, ALTER
FUNCTION won't see it and thus won't reset the safety for it. (To solve this, we
need to tackle another more general issue: we take no lock on the functions used in a
table, or when altering a function.)

Apart from this issue, updating a pg_class row would generate a full relcache
invalidation, which seems to broaden the scope of impact. If we let INSERT
SELECT compute safety and update it to pg_class, other sessions cannot see the
updated safety until the first session commits, and concurrent updates from
multiple sessions would require locking to ensure concurrency safety. For these
reasons, handling this in the relcache feels more simple to me.

Best Regards,
Zhijie Hou