Обсуждение: pg_dumpall + restore = missing view

During last night's "maintenance window" (er, unplanned loss of
connectivity due to workers diamond-core-drilling through an OC12 as
part of, I kid you not, installation of a new toilet) I upgraded from
7.4.1 to 7.4.6.

Basic method:
Build 7.4.6
Backup db: pg_dumpall > dumpfile
Install: stop 7.4.1/move datadir/install 7.4.6/copy configs
Start: initdb/start PG
Restore: psql -f dumpfile template1

This appears to have all gone well execpt that one view is missing.
I've restored that view by hand but am curious if this is a PG bug or
failure of the nut behind the wheel.

The view involves the union of many tables and its creation failed
because creation of one of the tables does not take place until later
in the dump file.

Ideas?

Cheers,
Steve

Steve Crawford <scrawford@pinpointresearch.com> writes:
> This appears to have all gone well execpt that one view is missing.
> I've restored that view by hand but am curious if this is a PG bug or
> failure of the nut behind the wheel.

> The view involves the union of many tables and its creation failed
> because creation of one of the tables does not take place until later
> in the dump file.

This is a longstanding pg_dump bug: it's not very bright about order of
creation of objects.  (In this case I surmise that you created the view,
and later altered it to reference a table that didn't exist when the
view was originally created.)

As of 8.0 pg_dump examines dependency information and should theoretically
always get this right, but in prior versions it's a real hazard.

            regards, tom lane

For the record, you shouldn't have needed to do a dump restore between
7.4.1 and 7.4.6 should you?

-tfo

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005

On Nov 19, 2004, at 7:24 PM, Tom Lane wrote:

> Steve Crawford <scrawford@pinpointresearch.com> writes:
>> This appears to have all gone well execpt that one view is missing.
>> I've restored that view by hand but am curious if this is a PG bug or
>> failure of the nut behind the wheel.
>
>> The view involves the union of many tables and its creation failed
>> because creation of one of the tables does not take place until later
>> in the dump file.
>
> This is a longstanding pg_dump bug: it's not very bright about order of
> creation of objects.  (In this case I surmise that you created the
> view,
> and later altered it to reference a table that didn't exist when the
> view was originally created.)
>
> As of 8.0 pg_dump examines dependency information and should
> theoretically
> always get this right, but in prior versions it's a real hazard.
>
>             regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html

"Thomas F.O'Connell" <tfo@sitening.com> wrote:
>
> For the record, you shouldn't have needed to do a dump restore between
> 7.4.1 and 7.4.6 should you?

IIRC, it was 7.4.1 -> 7.4.2 that required either that or some
manual fixing-up.

Jim