PG Bug reporting form <noreply@postgresql.org> writes:
> consider the following to reproduce (to keep the example short, the operator
> 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 method btree: ~~(integer,integer) | operator
~~(integer,integer) | n
operator 3 (integer, integer) of operator family testclass for access method btree: ~~(integer,integer) | operator
classtestclass for access method btree | i
operator class testclass for access method btree | operator
familytestclass for access method btree | a
type testrange | operator
classtestclass for access method 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