From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Christopher Causer" <chy(dot)causer(at)gmail(dot)com> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: pg_dump's restore gives "operator does not exist: public.iprange = public.iprange" but copy paste works |
Date: | 2021-07-08 20:09:02 |
Message-ID: | 2040892.1625774942@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"Christopher Causer" <chy(dot)causer(at)gmail(dot)com> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Ron | 2021-07-08 20:09:56 | Re: On partitioning, PKs and FKs |
Previous Message | David G. Johnston | 2021-07-08 20:02:20 | Re: pg_dump's restore gives "operator does not exist: public.iprange = public.iprange" but copy paste works |