On Fri, Jan 16, 2009 at 10:23 AM, Tom Lane <t...@sss.pgh.pa.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
test-view-opclass-deps.sql
Description: Binary data
test-view-opclass-deps.dump
Description: Binary data
test-view-opclass-deps.HEAD.dump
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
