pg_dump tries to do too much per query - Mailing list pgsql-hackers

I was experimenting today with pg_dump's reaction to missing
dependencies, such as a rule that refers to a no-longer-existing
table.  It's pretty bad.  For example:

create table test (f1 int);
create view v_test as select f1+1 as f11 from test;
drop table test;

then run pg_dump:

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?

The immediate cause of this behavior is the initial data fetch in
getTables():
   appendPQExpBuffer(query,              "SELECT pg_class.oid, relname, relkind, relacl, usename, "
"relchecks,reltriggers, relhasindex, pg_get_viewdef(relname) as viewdef "                     "from pg_class, pg_user "
                   "where relowner = usesysid and relname !~ '^pg_' "                     "and relkind in ('%c', '%c',
'%c')"                     "order by oid",               RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW);
 
   res = PQexec(g_conn, query->data);   if (!res ||       PQresultStatus(res) != PGRES_TUPLES_OK)   {
fprintf(stderr,"getTables(): SELECT failed.  Explanation from backend: '%s'.\n",
PQerrorMessage(g_conn));      exit_nicely(g_conn);   }
 

This can be criticized on a couple of points:

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.  When it does fail
outright, as above, you have no way to identify which view it failed
for.  pg_get_viewdef() should be invoked retail, for one view at a time,
and only for things you have determined are indeed views.

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.

More generally I think there are comparable problems elsewhere in
pg_dump, caused by trying to do too much per query and not thinking
about what will happen if there's a failure.  It looks like the join-
against-pg_user problem exists for all object types, not just tables.
It'd be worth examining all the queries closely with an eye to failure
modes and whether you can give a usefully specific error message when
something is wrong.
        regards, tom lane