From: Jinqing Kuang <kuangjinqingcn@gmail.com>
To: Heikki Linnakangas <hlinnaka@iki.fi>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: Detaching a child table makes an expression using it unrestorable
Date: Tue, 15 Sep 2026 09:53:31 +0800
Message-ID: <8E44C4A2-6184-473F-B536-F73F63AEF1D0@gmail.com> (raw)
In-Reply-To: <c638c39b-de93-46a4-b7e9-e5613952fcb3@iki.fi>
References: <c638c39b-de93-46a4-b7e9-e5613952fcb3@iki.fi>
On Sep 10, 2026, at 22:12, Heikki Linnakangas <hlinnaka@iki.fi> wrote:
>
> I bumped into a sequence of commands that breaks pg_dump & restore:
>
> ------------
> create table at_tab (a int, b int);
> create table at_tab_child (a int, b int);
> alter table at_tab_child inherit at_tab;
>
> -- You can use the child's rowtype in the DEFAULT without a cast.
> CREATE FUNCTION func_with_default(
> arg at_tab DEFAULT ('(1, 2)'::at_tab_child)
> ) RETURNS integer LANGUAGE plpgsql as $$
> begin
> return arg.b;
> end;
> $$;
>
> -- We allow detaching the child from the parent, despite the DEFAULT
> -- expression. That is a not good, because if you try to recreate the
> -- function, it's not accepted. I.e. pg_dump & restore is broken
> alter table at_tab_child no inherit at_tab;
> ------------
>
> The function still works after that. But if you run pg_dump (or do \ef or something), the CREATE FUNCTION is deparsed as above, and when you try to restore it you get an error:
>
> ERROR: argument of DEFAULT must be type at_tab, not type at_tab_child
> LINE 1: ...CTION public.func_with_default(arg at_tab DEFAULT '(1,2)'::a...
>
> You get the same effect with ATTACH/DETACH PARTITION instead of INHERIT/NO INHERIT.
>
> - Heikki
>
Hi Heikki,
I reproduced your example on 20-devel at 92aaf50e230. The function
still works after NO INHERIT, but restoring the dump fails.
I’d first try to preserve the current coercion rules:
1. Reject NO INHERIT/DETACH when it removes the last inheritance path
needed by a stored ConvertRowtypeExpr, including indirect paths.
2. In pg_dump, make objects using these conversions depend on the
required TABLE ATTACH entries, including intermediate partitions.
This needs a way to find affected expressions in existing databases;
recording dependencies only when creating new objects would miss them.
It would also leave already-detached conversions needing repair.
Alternatively, we could allow explicit casts between named composite
types without inheritance if the source has matching names, types and
typmods for all target fields, and deparse ConvertRowtypeExpr with that
cast. Implicit coercions would still require inheritance. This would
keep DETACH working, but would broaden the explicit-cast rules. Do you
think that is preferable?
The partition variant also fails to restore before any DETACH:
CREATE TABLE p(a int, b int) PARTITION BY RANGE(a);
CREATE TABLE c(a int, b int);
ALTER TABLE p ATTACH PARTITION c FOR VALUES FROM (0) TO (10);
CREATE FUNCTION f(arg p DEFAULT '(1,2)'::c) RETURNS integer
LANGUAGE plpgsql AS $$BEGIN RETURN arg.b; END$$;
-- pg_dump -s puts CREATE FUNCTION before ATTACH; restore fails.
-- Moving ATTACH before CREATE FUNCTION makes restore succeed.
Regards,
Jinqing
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: kuangjinqingcn@gmail.com, hlinnaka@iki.fi, pgsql-bugs@lists.postgresql.org
Subject: Re: Detaching a child table makes an expression using it unrestorable
In-Reply-To: <8E44C4A2-6184-473F-B536-F73F63AEF1D0@gmail.com>
* 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