Hello,
I originally posted this as a StackOverflow question[1], but one of the
responses there suggested I may get further help here on pgsql-general. The
question is perhaps a little more fleshed out than what follows, but I hope
this email is self-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 the terminal 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
lines to 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 dumps every 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 something wrong 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));
```
