Re: pg_dump's restore gives "operator does not exist: public.iprange = public.iprange" but copy paste works

On Thu, Jul 8, 2021 at 1:29 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> So the problem is not lack of a server feature, it's persuading pg_dump
> to emit something other than what it does now.
>

So basically a different variation on the let someone else who feels hot
enough about it and is able to code in C figure out a solution.

If schema qualification within the query is not possible (this seems to be
the case given your previous statement that used the words messy and ugly)
then the only other approach is to get a known good search_path in place
before the CREATE VIEW command. Since that known good search path has a
meaningful order, and the parsed view throws that knowledge away, it is not
possible to use existing catalog data to solve the problem. We may not
need a SET clause on CREATE VIEW but instead the session's order dependent
search path can be stored alongside the view as critical metadata that
tools can reference, including pg_dump. Assuming CREATE OR REPLACE VIEW
would capture that metadata then an explicit SET option would not be
necessary.

Though, part of the appeal of such an option is to localize the search_path
change to just the view being created, leaving the session search_path
alone. Its more of a usability thing, with potential metadata benefits,
that probably doesn't offer enough improvement over just changing the
session search_path.

David J.