Re: pg_dump tries to do too much per query

At 16:29 17/09/00 -0400, Tom Lane wrote:
>
>getTables(): SELECT failed. Explanation from backend: 'ERROR: cache
lookup of attribute 1 in relation 400384 failed
>'.
>
>This is just about entirely useless as an error message, wouldn't you
>say?

I agree but the is representative of the error handling throughout pg_dump
(eg. the notorious 'you are hosed' error message). Over time I will try to
clean it up where possible.

There are a number of different kinds of errors to deal with; ones
resulting a corrupt database seem to be low on the list. In fact I would
argue that 'DROP TABLE' should not work on a view relation. Secondly, your
comments probably highlight the need for a database verification utility.

That said, I will address your posints below.

>
>1. It invokes pg_get_viewdef() on every table and sequence, which is a
>big waste of time even when it doesn't fail outright.

Either pg_get_viewdef is a lot less efficient that I expected, or this is
an exageration. If it helps, I can replace it with a case statement:

case
when relkind='v' then pg_get_viewdef()
else ''
end

but this seems a little pointless and won't prevent errors when the db is
corrupt.

Being forced to break up SQL statements because the backend produces
unclear errors from a function seems to be a case of the tail wagging the
dog: perhaps pg_get_viewdef should at least identify itself as the source
of the error, if that is what is happening.

> When it does fail
>outright, as above, you have no way to identify which view it failed
>for.

Good point. This is going to affect anybody who calls get_viewdef. Maybe it
can be modified to indicate (a) that the error occurred in get_viewdef, and
(b) which view is corrupt.

Try:

select * from pg_views;

Same error.

>pg_get_viewdef() should be invoked retail, for one view at a time,
>and only for things you have determined are indeed views.

Do you truly, ruly believe the first part?

>2. As somebody pointed out a few days ago, pg_dump silently loses tables
>whose owners can't be identified. The cause is the inner join being
>done here against pg_user --- pg_dump will never even notice that a
>table exists if there's not a matching pg_user row for it. This is not
>robust.
>
>You should be able to fix the latter problem by doing an outer join,
>though it doesn't quite work yet in current sources. pg_get_userbyid()
>offers a different solution, although it won't return NULL for unknown
>IDs, which might be an easier failure case to check for.

This sounds sensible; and I think you are right - pg_dump crosses with user
info relations all the time. I'll look at using pg_get_userbyid, LOJ and/or
column selects now that they are available.

Based on this suggestion, maybe pg_get_viewdef should return NULL if the
view table does not exist. But I would still prefer a meaningful error
message, since it really does reflect DB corruption.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/