The following bug has been logged on the website:
Bug reference: 15934
Logged by: Tom Gottfried
Email address: tom@intevation.de
PostgreSQL version: 11.4
Operating system: Linux (Ubuntu 18.04)
Description:
Dear PostgreSQL developers,
consider the following to reproduce (to keep the example short, the operator
class is reduced to the minimum needed to reproduce the problem):
createdb test
psql -d test -c '
CREATE OPERATOR ~~ (
FUNCTION = int4eq,
LEFTARG = int,
RIGHTARG = int
);
CREATE OPERATOR CLASS testclass FOR TYPE int USING btree AS
OPERATOR 3 ~~;
CREATE TYPE testrange AS RANGE (
subtype = int,
subtype_opclass = testclass
);'
pg_dump -OcC test | psql -v ON_ERROR_STOP=
The output is as follows:
[...]
CREATE OPERATOR FAMILY
ERROR: operator does not exist: integer public.~~ integer
Dropping and recreating the database as above and looking at the output of
`pg_dump -OcC test' shows that the DDL for creating the operator used in
the operator class comes at the end (after the DDL for the operator
class):
--
-- PostgreSQL database dump
--
[...]
--
-- Name: testclass; Type: OPERATOR FAMILY; Schema: public; Owner: -
--
CREATE OPERATOR FAMILY public.testclass USING btree;
--
-- Name: testclass; Type: OPERATOR CLASS; Schema: public; Owner: -
--
CREATE OPERATOR CLASS public.testclass
FOR TYPE integer USING btree FAMILY public.testclass AS
OPERATOR 3 public.~~(integer,integer);
--
-- Name: testrange; Type: TYPE; Schema: public; Owner: -
--
CREATE TYPE public.testrange AS RANGE (
subtype = integer,
subtype_opclass = public.testclass
);
--
-- Name: ~~; Type: OPERATOR; Schema: public; Owner: -
--
CREATE OPERATOR public.~~ (
FUNCTION = int4eq,
LEFTARG = integer,
RIGHTARG = integer
);
--
-- PostgreSQL database dump complete
--
A similar error can be produced using a custom-format archive file:
createdb test_new
pg_dump -Fc test | pg_restore -ed test_new
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1522; 2616 77795 OPERATOR
CLASS testclass postgres
pg_restore: [archiver (db)] could not execute query: ERROR: operator does
not exist: integer public.~~ integer
Command was: CREATE OPERATOR CLASS public.testclass
FOR TYPE integer USING btree FAMILY public.testclass AS
OPERATOR 3 public.~~(integer,integer);
Dropping and recreating the database as above again but without the
subtype_opclass in the type definition and running `pg_dump -OcC test | psql
-v ON_ERROR_STOP=' gives the following output without any error:
[...]
CREATE TYPE
CREATE OPERATOR
CREATE OPERATOR FAMILY
CREATE OPERATOR CLASS
If using the subtype_opclass, I expected a similar output but with the
'CREATE TYPE' at the end.
System information:
psql -tc 'select version()'
PostgreSQL 11.4 (Ubuntu 11.4-1.pgdg18.04+1) on x86_64-pc-linux-gnu,
compiled by
gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
Thanks and best regards,
Tom