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@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@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@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@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
