Re: Re[2]: PG 14.5 -- Impossible to restore dump due to interaction/order of views, functions, and generated columns

"Nunya Business" <nb3425586(at)gmail(dot)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 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