I've got a rather large schema. Since a recent tweak to some functions and views I am unable to restore it by either psql or pg_restore.
~$ psql -f schema.sql foo -v ON_ERROR_STOP=1
....[SNIP]
psql:schema.sql:2405: ERROR: operator does not exist: public.iprange = public.iprange
The view that it fails on is as follows for completeness. On my existing production database from which the dump is taken it works fine:
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));
But I know this operator does exist. The really weird thing is that I can take the database at the point it left off and copy-paste the view in without problem: the view is created right at the point the restore left off.
I can also edit the view thus and rerun psql -f schema.sql:
CREATE VIEW archive.subnet_dhcp_options AS
SELECT -- SAME_COLUMNS_AS_ABOVE
FROM (data.subnet_dhcp_options sdo
JOIN public.subnets s ON (s.subnet_range::text = sdo.subnet_range::text));
Basically I've changed the iprange= operator for a text operator, and this isn't a workable solution for us.
I've tried restoring to 9.6 and 13. Both have the same error. The schema uses the ossp-uuid and ip4r extensions.
Any help would be gratefully received. I've never had a database be unable to restore from a pg_dump and I'm really scratching my head on this.
pg_dump has been run without arguments and with --schema-only. Both exhibit this behaviour.
Edit:
Just to clarify
ip4r is installed and running fine. In fact I'm restoring the database to the same system and the same cluster, just a different database name.
The changes to the schema unfortunately are quite large as it was a big new release. The way the software works is that there are a number of views and functions inside the database and it looks like it's tripping up on one of these views, even though I can copy and paste it into the half-completed restore.
Probably a repeat of the previous point, but ip4r is installed in the public schema. In fact it must be because the tables (rather than views) on which this view is based are created fine.
subnet_range is of type iprange for both tables, although one of those tables is itself a view
While I feel the schema is too verbose to share on SO, it's not a trade secret. If anyone knows of a means of sharing it with an interested party I'm happy to do that.
Edit 2:
Unfortunately the migrations are generated programmatically so while possible to extract a diff, it'd be rather big and difficult to unpick. However, and this is probably better, I've managed to condense the schema down to the minimum that gives the error. When you run this through psql -f -, you get the error above at the last view. You can then launch a psql terminal and paste in the final VIEW and it works fine.
This is a pg_dump of the database where I've only deleted lines not connected with the error, and comments. I haven't added any lines.
Hopefully that's enough to show the problem I'm experiencing
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));
I cannot claim credit for this. I had to ask the PostgreSQL general mailing list, but I got very helpful replies, which I will try to paraphrase.
The problem is this bit of the view
JOIN ... USING (subnet_range))
This USING is the same as ON (s.subnet_range = sdo.subnet_range), or so I thought. In fact, the = is an operator which itself is in a schema. When you create the view at the terminal the "public" schema is implied. By contrast, pg_dump explicitly removes schema resolution (via pg_catalog.set_config('search_path', '', false)) for security reasons. It then fully-qualifies all objects in the subsequent dump. Unfortunately in the case of USING, it cannot do that because the operator is implied rather than explicitly stated.
However, if I change the USING to an ON, the dump can "see" the operator and thus rewrite it so it is fully qualified. Making the change to my database schema and dumping it, I now get the line
JOIN public.subnets s ON ((s.subnet_range OPERATOR(public.=) sdo.subnet_range)));
And I can now happily dump and restore the database again. Happy days.