Re: pg_dump versus views and opclasses

"Brendan Jurd" <direvus(at)gmail(dot)com> writes:
> On Fri, Jan 16, 2009 at 10:23 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> However --- it's also the case that
>> pg_dump should dump all operators *and* operator classes before it gets
>> to views. So either you were doing something funny with the dump/reload
>> or else there's a circular dependency in your DB that pg_dump is
>> breaking in a bad place. I look forward to the test case ;-)

> Okay, here's the test case.

Interesting --- it works as expected in 8.2. The problem seems to have
been created by the introduction of arrays over composites in 8.3.
Although those array types don't get dumped separately, they still have
to participate in pg_dump's dependency sort (since they might form part
of dependency arcs). pg_dump initially sorts them as if they are
ordinary types, which puts them pretty high up in the dependency order,
and then the tables or composite types that they depend on also get
pushed forward in the ordering. (The topological sort for dependencies
basically causes items to get pushed forward to before anything that
depends on them; nothing ever moves backward.)

I'm surprised nobody noticed before that 8.3 is emitting considerably
different object ordering from 8.2. In particular this mostly destroys
the intended behavior that operators and operator classes come out
before table definitions.

The right fix seems to be to treat auto-generated array types the same
way pg_dump already treats auto-generated table types, ie, mark them
with a different DumpableObject type code that causes them to sort much
lower in the initial ordering. Fortunately, this is also a
back-patchable fix, since it's entirely internal to pg_dump. I've got
some social obligations today but I'll have a patch in a day or so.

I'm thinking that adding operator family info to SortGroupClause in
HEAD might be a good thing too, but it is for a different issue than
this --- it's to prevent you dropping a family that a view has a
semantic dependency on. However there might be room for a bit of
argument there: if there's both a btree and a hash opfamily then
the view could use either, so preventing you dropping one would be
a bit arbitrary. In any case it wouldn't stop you doing an ALTER
OPERATOR FAMILY to remove the sort operators from the family, so
maybe there's not much point here. Thoughts?

regards, tom lane