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@xxxxxxxxxxxxx>
To "Nunya Business" <nb3425586@xxxxxxxxx>
Cc pgsql-general@xxxxxxxxxxxxxx; pgsql-bugs@xxxxxxxxxxxxxx
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@xxxxxxxxx> 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 that references the table. The production schema where I ran into this is pretty large and complex, so the contrived example that follows may not be the minimum working example but it's pretty small and has the same behavior regarding the SQL 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
