pg_dump's restore gives "operator does not exist: public.iprange = public.iprange" but copy paste works - Mailing list pgsql-general

Hello,

I originally posted this as a StackOverflow question[1], but one of the responses there suggested I may get further
helphere on pgsql-general. The question is perhaps a little more fleshed out than what follows, but I hope this email
isself-contained.
 

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
theterminal afterwards is successful. 
 

To cut a long story short, I pruned the schema to ~100 lines but something that can reproduce the error shown in the
subject.The testcase is appended to this email for completeness[2]. No lines have been changed; I have only deleted
linesto whittle the production dump down to something that is copy-paste friendly.
 

One helpful person there was able to find the line created by pg_dump which causes the error 

```
SELECT pg_catalog.set_config('search_path', '', false);
```
However, I do not know the knock-on effects of removing this line, and it doesn't help that I will have to edit the
dumpsevery time I wish to restore them.
 

Is there something I can do to pg_dump which can suppress this error in the general case? Is this a bug or have I done
somethingwrong myself? The relevant details of the Debian stretch host:
 

ii  postgresql-9.6-ip4r                   2.4-1.pgdg80+1  
ii  postgresql-client-9.6                 9.6.22-0+deb9u1 
ii  postgresql-contrib-9.6                9.6.22-0+deb9u1

Many thanks for your time on this

Christopher


--------------------------------------------------------------------- 

[1]
https://stackoverflow.com/questions/68282538/pg-dumps-restore-gives-operator-does-not-exist-public-iprange-public-iprang?noredirect=1#comment120721406_68282538

[2]
```
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

CREATE SCHEMA archive;
CREATE SCHEMA auth;
CREATE SCHEMA data;
CREATE SCHEMA minion;
CREATE SCHEMA user_views;


CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
CREATE EXTENSION IF NOT EXISTS ip4r WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;


CREATE TABLE data.subnet_dhcp_options_updates_log (
    subnet_range public.iprange NOT NULL,
    txid bigint NOT NULL,
    last_update timestamp without time zone NOT NULL
);

CREATE FUNCTION public.subnet_dhcp_option_last_update(arg_subnet_range public.iprange) RETURNS timestamp without time
zone
    LANGUAGE sql STABLE
    AS $$
  select last_update from data.subnet_dhcp_options_updates_log where subnet_range = arg_subnet_range;
$$;


CREATE TABLE data.subnets (
    id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
    subnet_range public.iprange NOT NULL,
    comment text DEFAULT ''::text NOT NULL,
    created_in_transaction bigint DEFAULT txid_current() NOT NULL,
    deleted_in_transaction bigint,
    subnet_name text DEFAULT ''::text NOT NULL,
    is_visible boolean DEFAULT true NOT NULL
);

CREATE VIEW archive.subnets AS
 SELECT subnets.id,
    subnets.subnet_range,
    subnets.comment,
    subnets.created_in_transaction,
    subnets.deleted_in_transaction,
    subnets.subnet_name,
    subnets.is_visible,
    public.family(subnets.subnet_range) AS ip_version
   FROM data.subnets;



CREATE TABLE data.subnet_dhcp_options (
    id uuid NOT NULL,
    kea_subnet_id integer NOT NULL,
    subnet_range public.iprange NOT NULL,
    subnet_pools public.iprange[] DEFAULT '{}'::public.iprange[] NOT NULL,
    dhcp_options jsonb DEFAULT '{}'::jsonb NOT NULL,
    unknown_client_leases boolean NOT NULL,
    comment text DEFAULT ''::text NOT NULL,
    created_in_transaction bigint DEFAULT txid_current() NOT NULL,
    deleted_in_transaction bigint
);

CREATE VIEW public.subnets AS
 SELECT subnets.id,
    subnets.subnet_range,
    subnets.comment,
    subnets.created_in_transaction,
    subnets.deleted_in_transaction,
    subnets.subnet_name,
    subnets.is_visible,
    subnets.ip_version
   FROM archive.subnets
  WHERE (subnets.deleted_in_transaction IS NULL);

CREATE VIEW archive.subnet_dhcp_options AS
 SELECT sdo.id,
    sdo.subnet_range,
    (sdo.subnet_pools)::text[] AS subnet_pools,
    sdo.dhcp_options,
    sdo.unknown_client_leases,
    sdo.kea_subnet_id,
    public.family(sdo.subnet_range) AS ip_version,
    sdo.comment,
    sdo.created_in_transaction,
    sdo.deleted_in_transaction,
    array_to_string((sdo.subnet_pools)::text[], '
'::text) AS subnet_pools_as_string,
    public.subnet_dhcp_option_last_update(sdo.subnet_range) AS last_update,
    s.id AS subnet_id
   FROM (data.subnet_dhcp_options sdo
     JOIN public.subnets s USING (subnet_range));
```