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:
> Looking at it some more, I notice that the SortGroupClause dependencies
> are on the individual operators, which probably isn't good enough: the
> operator *classes* have to exist or the parser will complain when trying
> to make sense of the view. So that would be a good thing to change for
> 8.4 (and it's not too late yet).

I haven't come up with a stripped-down test case for this yet, but I
do have some further results to report from dumping and restoring my
database into different versions of postgres.

I wanted to check whether the problem was actually reproducible or
some freak occurence in the original database. So I loaded my dump
(with the opclass created after the view) into a fresh 8.3.5 database.
This failed to create the view as expected. I then manually created
the view, which worked fine because the opclass was now present. I
then ran pg_dump on the new database and got the same results as
before. The opclass and its operators were listed *after* the view.
So it's not just some kind of one-off weirdness, there really is a
dependency problem somewhere in here.

I'm not doing anything funny with the dump/reload. The dump is using
the same pg_dump version as the server, with no command line options,
just the name of the database. The reload is just piping the contents
of the dump file into psql, again the same version as the server and
no special options.

I tried the same experiment on HEAD and found something very
surprising. The dump was loaded 100% successfully. Note that this is
the very same dump, and it does indeed create the objects in the wrong
order. But for some reason, HEAD doesn't seem to have a problem with
that.

This bodes well for anyone wanting to upgrade a database with such a
dependency glitch from 8.3 to 8.4 ... but I'm confused as to why 8.4
doesn't freak out when told to create a view it doesn't actually know
how to execute. Have the rules for evaluating views changed?

Note also that after creating the database on HEAD, the pg_dump from
HEAD still outputs the objects in the wrong order.

I will continue working on getting a test case together.

Cheers,
BJ