Re: Introducing find_all_inheritors_ordered()

On Mon, 07 Sep 2026, Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com> wrote:
> My understanding is that PG doesn’t intend to support cyclic inheritance. The code explicitly rejects it:

To me it seems like that it discourages it and tries to prevent it
where it's possible to do so with reasonable effort, but it ends up
trying to support it because it's a valid scenario that can happen in
some workloads (as long as that also doesn't require too much effort).

> A cycle can cause it to exceed the stack depth limit, so it is reasonable to fail the command when a cycle is detected.

Or it might succeed in other cases.

Here's a repro with an isolation test. Perm 1 fails on master, works
with the patch. Perm 2 works on master, fails with the patch.
We could improve this by only using the new function in the enforced
direction, I don't have a better idea currently. With that, the
patched behavior would be the same as on master.

setup
{
CREATE TABLE r (x int CONSTRAINT cc CHECK (x > 0));
CREATE TABLE a (x int CONSTRAINT cc CHECK (x > 0));
CREATE TABLE b (x int CONSTRAINT cc CHECK (x > 0));
CREATE TABLE c (x int CONSTRAINT cc CHECK (x > 0));
CREATE TABLE d (x int CONSTRAINT cc CHECK (x > 0));
ALTER TABLE a INHERIT r;
ALTER TABLE b INHERIT a;
ALTER TABLE d INHERIT c;
}

teardown
{
DROP TABLE IF EXISTS r, a, b, c, d CASCADE;
}

session s1
step s1b { BEGIN; }
step s1i { ALTER TABLE c INHERIT b; }
step s1c { COMMIT; }

session s2
step s2b { BEGIN; }
step s2i { ALTER TABLE a INHERIT d; }
step s2c { COMMIT; }

session s3
step s3pre { ALTER TABLE r ALTER CONSTRAINT cc NOT ENFORCED;
ALTER TABLE c ALTER CONSTRAINT cc NOT ENFORCED; }
step s3e { SELECT inhrelid::regclass::text AS child,
inhparent::regclass::text AS parent
FROM pg_inherits
WHERE inhrelid::regclass::text IN ('r','a','b','c','d')
ORDER BY 1, 2; }
step s3not { ALTER TABLE r ALTER CONSTRAINT cc NOT ENFORCED; }
step s3enf { ALTER TABLE r ALTER CONSTRAINT cc ENFORCED; }
step s3s { SELECT conrelid::regclass::text AS rel, conenforced
FROM pg_constraint
WHERE conname = 'cc'
AND conrelid::regclass::text IN ('r','a','b','c','d')
ORDER BY 1; }

permutation s1b s2b s1i s2i s1c s2c s3e s3not s3s
permutation s1b s2b s3pre s1i s2i s1c s2c s3e s3enf s3s