Re: Parallel INSERT SELECT take 2

On Sun, May 17, 2026, at 13:24, Tomas Vondra wrote:
> On 5/11/26 13:44, Tomas Vondra wrote:
>>>>>> alternative idea

We noticed some overlap of problems between this thread and [1], and decided to
split out our fix into a separate fix, which is 0001 in our patch set.
It could possibly be a solution to some of the problem scenarios you're
working on in this thread, hence this email.

For convenience, below is the git show --stat of the 0001 patch.

---
Serialize routine definition changes with dependency recording

Dependency recording now locks referenced objects before inserting
pg_depend rows. Routine definition changes also need to participate in
that object-lock protocol, because stored expressions can depend on
function properties such as volatility, strictness, or SQL-body
contents.

Take AccessExclusiveLock on the pg_proc object before CREATE OR REPLACE
FUNCTION updates an existing routine, and before ALTER FUNCTION changes
routine properties. After waiting, refetch the pg_proc tuple and repeat
the ownership and object-kind checks, because the routine could have
been dropped or reassigned while we waited.

Authors: Andreas Karlsson, Joel Jacobson, Arne Roland

src/backend/catalog/pg_proc.c | 22 ++++++++++++++++++++++
src/backend/commands/functioncmds.c | 29 +++++++++++++++++++++++++++++
2 files changed, 51 insertions(+)
---

Since you're discussing alternative ideas, we thought we should throw
this into the discussion.

--
-- master (21298c2)
--

joel(at)Mac postgresql % psql regression_dep_probe
psql (19devel)
Type "help" for help.

regression_dep_probe=# CREATE SCHEMA dep_probe;
CREATE SCHEMA
regression_dep_probe=# CREATE FUNCTION dep_probe.f_lock_probe(integer) RETURNS integer
regression_dep_probe-# LANGUAGE sql IMMUTABLE PARALLEL SAFE AS $$ SELECT $1 $$;
CREATE FUNCTION
regression_dep_probe=#
regression_dep_probe=# CREATE TABLE dep_probe.t_lock_probe(a integer);
CREATE TABLE
regression_dep_probe=# -- session 1
regression_dep_probe=# BEGIN;
BEGIN
regression_dep_probe=*# CREATE INDEX t_lock_probe_expr_idx
regression_dep_probe-*# ON dep_probe.t_lock_probe ((dep_probe.f_lock_probe(a)));
CREATE INDEX
regression_dep_probe=*# SELECT pg_sleep(8);
pg_sleep
----------

(1 row)

regression_dep_probe=*# COMMIT;
COMMIT
regression_dep_probe=#

joel(at)mac postgresql % psql regression_dep_probe
psql (19devel)
Type "help" for help.

regression_dep_probe=# -- session 2, while session 1 is open
regression_dep_probe=# SET lock_timeout = '1s';
SET
regression_dep_probe=# ALTER FUNCTION dep_probe.f_lock_probe(integer) PARALLEL UNSAFE;
ALTER FUNCTION
regression_dep_probe=#

--
-- patch 0001: Serialize routine definition changes with dependency recording
--
joel(at)Mac postgresql % psql regression_dep_probe
psql (19devel)
Type "help" for help.

regression_dep_probe=# CREATE SCHEMA dep_probe;
CREATE SCHEMA
regression_dep_probe=# CREATE FUNCTION dep_probe.f_lock_probe(integer) RETURNS integer
regression_dep_probe-# LANGUAGE sql IMMUTABLE PARALLEL SAFE AS $$ SELECT $1 $$;
CREATE FUNCTION
regression_dep_probe=#
regression_dep_probe=# CREATE TABLE dep_probe.t_lock_probe(a integer);
CREATE TABLE
regression_dep_probe=# -- session 1
regression_dep_probe=# BEGIN;
BEGIN
regression_dep_probe=*# CREATE INDEX t_lock_probe_expr_idx
regression_dep_probe-*# ON dep_probe.t_lock_probe ((dep_probe.f_lock_probe(a)));
CREATE INDEX
regression_dep_probe=*# SELECT pg_sleep(8);
pg_sleep
----------

(1 row)

regression_dep_probe=*# COMMIT;
COMMIT
regression_dep_probe=#

joel(at)mac postgresql % psql regression_dep_probe
psql (19devel)
Type "help" for help.

regression_dep_probe=# -- session 2, while session 1 is open
regression_dep_probe=# SET lock_timeout = '1s';
SET
regression_dep_probe=# ALTER FUNCTION dep_probe.f_lock_probe(integer) PARALLEL UNSAFE;
ERROR: canceling statement due to lock timeout
CONTEXT: waiting for AccessExclusiveLock on object 16387 of class 1255 of database 16385
regression_dep_probe=#

/Joel

[1] https://www.postgresql.org/message-id/flat/f7f02669-652d-4bbb-bc29-cccdb63c2233%40app.fastmail.com#0bcaf856172453d536f1fd9b6afa7a2f