PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15934
PG Version11.4
OSLinux (Ubuntu 18.04)
Opened2019-07-31 10:12:25+00
Reported byTom Gottfried
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15934
Logged by:          Tom Gottfried
Email address:      (redacted)
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

Messages

DateAuthorSubject
2019-07-31 10:12:25+00PG Bug reporting formBUG #15934: pg_dump output in wrong order if custom operator class is used as subtype_opclass in a range type
2019-07-31 16:07:11+00Tom LaneRe: BUG #15934: pg_dump output in wrong order if custom operator class is used as subtype_opclass in a range type
2019-07-31 18:26:36+00Tom LaneRe: BUG #15934: pg_dump output in wrong order if custom operator class is used as subtype_opclass in a range type