From: Tom Lane <tgl@sss.pgh.pa.us>
To: jian he <jian.universality@gmail.com>
Cc: exclusion@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18970: Atempt to alter type of table column used in row type with check leads to assertion failure
Date: Sun, 29 Jun 2025 13:01:53 -0400
Message-ID: <2482012.1751216513@sss.pgh.pa.us> (raw)
In-Reply-To: <CACJufxGYBfGbOj3x0C0U-FsMR4K-gDEyxE6hjBJ870FnkDAO4w@mail.gmail.com>
References: <18970-a7d1cfe1f8d5d8d9@postgresql.org>
<CACJufxGYBfGbOj3x0C0U-FsMR4K-gDEyxE6hjBJ870FnkDAO4w@mail.gmail.com>
jian he <jian.universality@gmail.com> writes:
> On Sun, Jun 29, 2025 at 1:35 AM PG Bug reporting form
> <noreply@postgresql.org> wrote:
>> CREATE TABLE t1(a int);
>> CREATE TABLE t2(b t1 CHECK ((b).a IS NOT NULL));
>> ALTER TABLE t1 ALTER COLUMN a TYPE numeric;
>> triggers
>> 2025-06-28 06:52:21.201 UTC [2233016] LOG: statement: ALTER TABLE t1 ALTER
>> COLUMN a TYPE numeric;
>> TRAP: failed Assert("lockmode != NoLock || IsBootstrapProcessingMode() ||
>> CheckRelationLockedByMe(r, AccessShareLock, true)"), File: "relation.c",
>> Line: 67, PID: 2233016
> in ATPostAlterTypeCleanup
> /*
> * When rebuilding an FK constraint that references the table we're
> * modifying, we might not yet have any lock on the FK's table, so get
> * one now. We'll need AccessExclusiveLock for the DROP CONSTRAINT
> * step, so there's no value in asking for anything weaker.
> */
> if (relid != tab->relid && contype == CONSTRAINT_FOREIGN)
> LockRelationOid(relid, AccessExclusiveLock);
> we can change to
> if (relid != tab->relid)
> LockRelationOid(relid, AccessExclusiveLock);
> obviously, the comments need to be updated.
Yeah, I came to the same conclusion after studying it for awhile.
The problem is that because of the "(b).a" column reference in
the CHECK expression, we record a direct dependency of t2's CHECK
constraint on t1.a, and ATPostAlterTypeCleanup is evidently not
expecting that.
> When altering the data type of a column in one relation causes a constraint of
> another table rebuild, the other table should be locked with
> AccessExclusiveLock.
I wonder if there might be cases where a lesser lock is sufficient.
However, this is apparently a very edgy edge case, so it's probably
not worth obsessing over the lock level too much.
It's somewhat annoying that we're just going to fail later.
I thought about whether find_composite_type_dependencies
ought to be run sooner, so that we'd error out before potentially
doing a lot of work. But I think the design idea is that
someday find_composite_type_dependencies would actually propagate
the changes, in which case its current placement is correct.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, jian.universality@gmail.com, exclusion@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18970: Atempt to alter type of table column used in row type with check leads to assertion failure
In-Reply-To: <2482012.1751216513@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox