"Christopher Causer" <chy.causer@xxxxxxxxx> writes:
> I have a large database whose schema I have recently changed. Since then I cannot restore its dump using the normal `psql -1 -f ...` route. Running `psql -1 -f ...` gives the error shown in the subject, yet pasting the failing view in the terminal afterwards is successful.
The core problem here is that this view definition:
CREATE VIEW archive.subnet_dhcp_options AS
...
JOIN public.subnets s USING (subnet_range));
is not safe against varying settings of the search_path. It needs the
"iprange = iprange" operator, which you've placed in the public schema;
so if that's not in the search_path when the view is defined, you lose.
Ordinarily pg_dump would account for this by writing out a
schema-qualified operator name, ie OPERATOR(public.=). But the
JOIN USING syntax involves no explicit operator name so there's
no way to do that.
This isn't the only SQL syntax that has implicit operators; CASE is
another example, and I think there are more. We've discussed inventing
non-SQL-spec syntax that can cope with explicitly writing a qualified
operator name in all these cases, but it looks like a messy project
with an ugly final result :-(, so nothing's been done yet.
I don't think there's any good solution right now. You could perhaps
put the iprange extension in pg_catalog not public, so that it's always
in the search path. I don't recall right now if that has any downsides.
regards, tom lane
