Re: Detaching a child table makes an expression using it unrestorable

On Sep 10, 2026, at 22:12, Heikki Linnakangas <hlinnaka(at)iki(dot)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