Tom,
Thanks for the insight, I didn't even consider the search path being an
issue and I should have. I saw it explicitly specified in other parts
of the dump and just assumed it was being done in the function as well.
For example, the CREATE statements in the dump output all specify the
schema name even though it's not specified in the original statements.
I suppose expecting statements in the function body to be similarly
qualified in the dump is unreasonable considering the complexity of
doing so.
Anyway, thanks for the tip. If I run into something like this again in
the future I'll have a better idea of where to look and how to recover
what data I can in an emergency.
------ Original Message ------
From "Tom Lane" <tgl@sss.pgh.pa.us>
To "Nunya Business" <nb3425586@gmail.com>
Cc pgsql-general@postgresql.org; pgsql-bugs@postgresql.org
Date 2022-12-06 15:47:26
Subject Re: Re[2]: PG 14.5 -- Impossible to restore dump due to
interaction/order of views, functions, and generated columns
>"Nunya Business" <nb3425586@gmail.com> writes:
>>Thanks Tom. There are indeed circular references in the schema and the whole thing sort of doesn't pass the smell
test,but this is my first look at it. The generated column on the table calls a function which selects from a view
thatreferences the table. The production schema where I ran into this is pretty large and complex, so the contrived
examplethat follows may not be the minimum working example but it's pretty small and has the same behavior regarding
theSQL generated by pg_dumpall.
>
>Hm. The actual problem here is that fnA() is making unwarranted
>assumptions about the search_path it's run under, so it fails when the
>pg_dump script invokes it with a restrictive search_path. If you change
>the function text so that the references to viewA are schema-qualified,
>then it restores without errors.
>
>"Doesn't pass the smell test" is putting it mildly, btw. Labeling
>that function IMMUTABLE is a huge lie, and what it means is that
>your GENERATED column doesn't have the amount of stability that
>it's supposed to per spec. I'm not sure exactly what sorts of
>misbehaviors might ensue from that, but I'm pretty certain that the
>data in the GENERATED column after dump/restore won't match what
>you had there beforehand.
>
> regards, tom lane