Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



"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






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux