Re: pg_dump versus views and opclasses

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. The attached file
test-view-opclass-deps.sql creates a simple database with a
user-defined composite type "comp", which consists of two integers
called "a" and "b".

It then puts together a simple btree opclass for that that type and
fills a table "stuff" with some generated values for the type.

It then creates a view "group_stuff" which groups on the type.

If you dump out the database, the view will be listed after the type,
but *before* the opclass and all its component operators and
underlying functions. I've attached a copy of the faulty dump file
for reference.

If you try to load that dump, the view won't be created.

To reproduce the error on 8.3.5:

$ psql -f test-view-opclass-deps.sql postgres
$ psql -c "create database test_view_opclass_deps2;" postgres
$ pg_dump test_view_opclass_deps | psql test_view_opclass_deps2

On HEAD, the dump is still screwy, but in a slightly different way.
It lists the type first, followed by the = and <= operators, then the
table and the view, and finally the remainder of the operators and the
opclass.

If you load the dump into an 8.4 server, the view gets created without
error because 8.4 has a default btree opclass, so the order of objects
in the dump isn't important -- at least not with regard to this
particular scenario. But I still find it a bit disturbing that the
order of objects in the 8.4 dump is so bizarre. Why the special
treatment for = and <=?

Cheers,
BJ