Hello hackers,
I happened to observe an interesting failure of `make check` being
executed concurrently against multiple databases:
diff -U3 --minimal .../regress_input_27/expected/domain.out
.../regress_output_27/results/domain.out
--- .../expected/domain.out 2026-10-02 08:03:59.474274001 +0000
+++ .../results/domain.out 2026-10-02 08:26:49.225597020 +0000
@@ -1337,11 +1337,7 @@
FROM information_schema.domain_constraints
WHERE domain_name IN ('con', 'dom', 'pos_int', 'things'))
ORDER BY constraint_name;
- constraint_catalog | constraint_schema | constraint_name | check_clause
---------------------+-------------------+------------------+-------------------
- regress027 | public | con_check | (VALUE > 0)
- regress027 | public | meow | (VALUE < 11)
- regress027 | public | pos_int_check | (VALUE > 0)
- regress027 | public | pos_int_not_null | VALUE IS NOT NULL
-(4 rows)
-
+ERROR: deadlock detected
+DETAIL: Process 322216 waits for AccessShareLock on relation 1197300 of
database 16411; blocked by process 322217.
+Process 322217 waits for AccessExclusiveLock on relation 1197314 of database
16411; blocked by process 322216.
+HINT: See server log for query details.
I've managed to reproduce this with the attached patch:
# diff -U3 .../src/test/regress/expected/alter_table.out
.../src/test/regress/results/alter_table.out
# --- .../src/test/regress/expected/alter_table.out 2026-10-07
12:53:24.843538437 -0400
# +++ .../src/test/regress/results/alter_table.out 2026-10-07
14:58:08.602062642 -0400
# @@ -4808,7 +4808,10 @@
# (1 row)
#
# alter table attbl alter column a type numeric; -- someday this should work
# -ERROR: cannot alter table "attbl" because column "atref.b" uses its row type
# +ERROR: deadlock detected
# +DETAIL: Process 2884690 waits for AccessExclusiveLock on relation 45440 of
database 16384; blocked by process 2884685.
# +Process 2884685 waits for AccessShareLock on relation 45437 of database
16384; blocked by process 2884690.
# +HINT: See server log for query details.
# alter table atref drop constraint atref_b_check;
# create statistics atref_stat on ((b).a is not null) from atref;
# alter table attbl alter column a type numeric; -- someday this should work
That is, the deadlock occurs between
"SELECT * FROM information_schema.check_constraint" / pg_get_constraintdef()
and
"alter table attbl alter column a type numeric;" / ATPostAlterTypeCleanup().
Reproduced starting from 8319e5cb5.
Best regards,
Alexander
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 93ad49e59fd..e9d1c43d903 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -16183,7 +16183,10 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
* CONSTRAINT step, so there's no value in asking for anything weaker.
*/
if (relid != tab->relid)
+{
+char *relname = get_rel_name(relid); if (relname && strcmp(relname, "atref") == 0) pg_usleep(500000);
LockRelationOid(relid, AccessExclusiveLock);
+}
ATPostAlterTypeParse(oldId, relid, confrelid, InvalidOid,
(char *) lfirst(def_item),
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 5e8e1683db0..d48c3383ea6 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -2772,6 +2772,7 @@ pg_get_expr_worker(text *expr, Oid relid, int prettyFlags)
rel = try_relation_open(relid, AccessShareLock);
if (rel == NULL)
return NULL;
+if (strcmp(RelationGetRelationName(rel), "atref") == 0) pg_usleep(2000000);
context = deparse_context_for(RelationGetRelationName(rel), relid);
}
else
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 20707c186b3..aba2f363945 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -4801,6 +4801,12 @@ drop table attbl, atref;
/* Test case for bug #18970 */
create table attbl(a int);
create table atref(b attbl check ((b).a is not null));
+SELECT pg_sleep(2);
+ pg_sleep
+----------
+
+(1 row)
+
alter table attbl alter column a type numeric; -- someday this should work
ERROR: cannot alter table "attbl" because column "atref.b" uses its row type
alter table atref drop constraint atref_b_check;
diff --git a/src/test/regress/expected/domain.out b/src/test/regress/expected/domain.out
index 62a48a523a2..c0b9af6124a 100644
--- a/src/test/regress/expected/domain.out
+++ b/src/test/regress/expected/domain.out
@@ -1412,6 +1412,12 @@ SELECT * FROM information_schema.domains
regression | public | things | integer | | | | | | | | | 32 | 2 | 0 | | | | | regression | pg_catalog | int4 | | | | | 1
(4 rows)
+SELECT pg_sleep(3);
+ pg_sleep
+----------
+
+(1 row)
+
SELECT * FROM information_schema.check_constraints
WHERE (constraint_schema, constraint_name)
IN (SELECT constraint_schema, constraint_name
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 18f3fdac57a..a3bef2c8066 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -3118,6 +3118,7 @@ drop table attbl, atref;
create table attbl(a int);
create table atref(b attbl check ((b).a is not null));
+SELECT pg_sleep(2);
alter table attbl alter column a type numeric; -- someday this should work
alter table atref drop constraint atref_b_check;
diff --git a/src/test/regress/sql/domain.sql b/src/test/regress/sql/domain.sql
index b8f5a639712..58ce83c864f 100644
--- a/src/test/regress/sql/domain.sql
+++ b/src/test/regress/sql/domain.sql
@@ -910,6 +910,7 @@ SELECT * FROM information_schema.domains
WHERE domain_name IN ('con', 'dom', 'pos_int', 'things')
ORDER BY domain_name;
+SELECT pg_sleep(3);
SELECT * FROM information_schema.check_constraints
WHERE (constraint_schema, constraint_name)
IN (SELECT constraint_schema, constraint_name
