pg_dump tries to do too much per query
От | Tom Lane |
---|---|
Тема | pg_dump tries to do too much per query |
Дата | |
Msg-id | 7189.969222559@sss.pgh.pa.us обсуждение исходный текст |
Ответы |
Re: pg_dump tries to do too much per query
(Philip Warner <pjw@rhyme.com.au>)
Re: pg_dump tries to do too much per query (Philip Warner <pjw@rhyme.com.au>) Re: pg_dump tries to do too much per query (Philip Warner <pjw@rhyme.com.au>) |
Список | 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
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Oleg BartunovДата:
Сообщение: Re: broken locale in 7.0.2 without multibyte support (FreeBSD 4.1-RELEASE) ?