RE: Parallel INSERT SELECT take 2

On Monday, May 11, 2026 7:44 PM Tomas Vondra <tomas(at)vondra(dot)me> wrote:
> On 5/11/26 09:21, Zhijie Hou (Fujitsu) wrote:
> > On Saturday, May 9, 2026 5:29 PM Tomas Vondra <tomas(at)vondra(dot)me>
> wrote:
> >> ...
> > After searching my memory and reviewing the old discussion, I recall
> > another locking-related issue...
> >
> > For example, consider creating a new table that uses a function in an
> > index expression (or altering a table to add a new expression or
> > function). If a user tries to alter the function used in that
> > expression concurrently, the ALTER FUNCTION command cannot see the
> newly created table because it hasn't been committed yet.
> > As a result, we cannot ensure that pg_class.parallel_safety is updated
> > in a concurrency-safe manner. The cause is that we don't hold a lock
> > on the function when creating or altering a table.
> ...
>
> Yes, problems like this may be tricky.
>
> But I don't see a problem with requiring an exclusive lock on a function when
> changing the parallel safety for a function (and updating the pg_class attribute
> for all relations that use it). Yes, it's not great, it'd be nice to do it with weaker
> locks, but if we can't ... sorry.

Per an off-list discussion, I'm sharing an alternative approach for
consideration. The basic idea is to cache parallel safety in the relcache and
invalidate the cached value whenever: a parallel-safety-relevant object (e.g.,
trigger, index, constraint) is added to or dropped from a table, or a function's
parallel-safety flag is altered. This approach essentially follows the design
from the earlier thread [1], but remove the parlalel safety declaration and
extends it slightly by also attempting to compute safety for partitioned tables.

The detailed design is as follows:

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.

Checking all of that for every query is expensive, so cache the result in the
relation's relcache entry: rd_paralleldml holds the worst hazard level found
(one of the PROPARALLEL_xxx values), or zero if it has not been computed yet.
The new RelationGetParallelDmlSafety() function computes the value on first use
and returns the cached value thereafter.

The cached value is invalidated whenever a function's parallel-safety flag is
altered, or whenever a parallel-safety-relevant object is added to or dropped
from the table or, for a partitioned table, from any of its partitions.

When a function's parallel safety changes, we invalidate the cached
parallel-safety flag in all relcache entries, rather than introducing heavier
locking or reverse-engineering the set of tables that reference the function.
Function-safety changes are expected to be rare, so this broad invalidation
should be acceptable.

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.

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.

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.

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.

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.

[1] https://www.postgresql.org/message-id/OS0PR01MB5716DB1E3F723F86314D080094F09%40OS0PR01MB5716.jpnprd01.prod.outlook.com

Best Regards,
Zhijie Hou