From: Tom Lane <tgl@sss.pgh.pa.us>
To: tom@intevation.de
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #15934: pg_dump output in wrong order if custom operator
 class is used as subtype_opclass in a range type
In-reply-to: <15934-58b8c8ab7a09ea15@postgresql.org>
References: <15934-58b8c8ab7a09ea15@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Wed, 31 Jul 2019 10:12:25 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <5344.1564589231.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 31 Jul 2019 12:07:11 -0400
Message-ID: <5345.1564589231@sss.pgh.pa.us>
Precedence: bulk

PG Bug reporting form <noreply@postgresql.org> writes:
> consider the following to reproduce (to keep the example short, the oper=
ator
> class is reduced to the minimum needed to reproduce the problem):
> ...
> 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):

Huh.  I'm surprised nobody has tripped over this before, because it's
been broken since range types were introduced.

The core of the problem is that pg_dump doesn't treat opclass members
(pg_amop rows) as independent objects, which is problematic because
pg_depend does think they're independent objects.  So the dependency
entries involving this opclass are

 operator 3 (integer, integer) of operator family testclass for access met=
hod btree: ~~(integer,integer) | operator ~~(integer,integer)             =
         | n
 operator 3 (integer, integer) of operator family testclass for access met=
hod btree: ~~(integer,integer) | operator class testclass for access metho=
d btree  | i
 operator class testclass for access method btree                         =
                               | operator family testclass for access meth=
od btree | a
 type testrange                                                           =
                               | operator class testclass for access metho=
d btree  | n

pg_dump ignores the first two of these, meaning it has no knowledge
that operator ~~ needs to be dumped before opclass testclass.
Now it does have a general rule that all else being equal, operators
should be dumped before opclasses, and that saves it most of the time.
But in this example, all else is not equal, because the last-mentioned
dependency forces the opclass to be moved up, to before type testrange.
And the same general sort rules say that types come before operators,
so without a dependency to force the operator to be moved up too,
we get the wrong result.

Clearly, we gotta upgrade pg_dump's intelligence about handling this
dependency structure.  The most straightforward fix would involve
promoting pg_amop (and pg_amproc) entries to be full DumpableObjects,
but I'm apprehensive about the overhead that would add ...

I'll try to work on a fix later this week, with hopes of getting
it into next month's releases.

Thanks for the report!

			regards, tom lane