Re: pg_dump versus views and opclasses - Mailing list pgsql-hackers

On Sun, Jan 18, 2009 at 5:56 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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.

Congratulations on identifying the source!

Does this also explain the extra-weird output from HEAD, with the <=
and = operators sorting earlier than the view, but the rest of the
operators sorting after it?

> I'm surprised nobody noticed before that 8.3 is emitting considerably
> different object ordering from 8.2.

Well I can't speak for anybody else, but I have noticed changes to the
dump order in the past, but I dismissed it as something that Just
Happens in a major release and got on with my life =).  I've long
since accepted that you can't expect dumps to be directly comparable
over the long term.

Plus, the difference in object ordering only manifests with composite
types, and that's probably something that only a slim portion of users
are actually playing with.

> In particular this mostly destroys
> the intended behavior that operators and operator classes come out
> before table definitions.

Yeah, that seems like a generally sane intention.

> 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.

Sounds good.

> 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.

Not to mention that, with the new default btree opclass in 8.4 that I
stumbled across earlier, dropping an opclass that a view uses may not
necessarily murder the view ... it may simply cause it to fall back to
the default opclass.  If falling back to the default opclass was
actually what the user wanted to achieve by dropping the user-defined
opclass, a dependency such as you describe might leave him with no way
to do that.

Worst case scenario is that you drop an opclass which causes a view to
die, and next time you try to run the view you get an intelligible
error message telling you why it doesn't work anymore.  That's
probably not the end of the world.

> 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?

Honestly, I feel that messing around with operator classes is
something that doesn't happen with any great frequency, and you need
to be careful and hands-on when you're doing it anyway.  I could live
without the dependency.

Cheers,
BJ