Hi,
While investigating an anti-wraparound shutdown issue of Peter
H. Ezetta (cced) on IRC the issue came up that when you:
CREATE TABLE foo(id int, text text);
CREATE RULE "_RETURN" AS ON SELECT TO foo DO INSTEAD SELECT 1::int AS id, ''::text AS text;
a) the view keeps its relfrozenxid value
b) the toast table remains
Peter is running (or rather migrating away from) 8.3 and in 8.3 toast
tables cannot be vacuumed by
1) manual VACUUMS, since vacuum() passes RELKIND_RELATION to vacuum_rel() which thus errors out when vacuuming either
theview or the toast table directly:if (onerel->rd_rel->relkind != expected_relkind){ ereport(WARNING,
(errmsg("skipping\"%s\" --- cannot vacuum indexes, views, or special system tables",
RelationGetRelationName(onerel))));
2) autovacuum recognizes that the toast table needs vacuuming but uses
the following brute force trick to search for the table to find the
relation to vacuum:foreach(cell, toast_oids){ Oid toastoid = lfirst_oid(cell); ListCell *cell2;
foreach(cell2, table_toast_list) { av_relation *ar = lfirst(cell2);
if (ar->ar_toastrelid == toastoid) { table_oids = lappend_oid(table_oids, ar->ar_relid);
break; } }}
due to no respective element being in in table_toast_list nothing is
vacuumed and you cannot escape the situation. Not very nice. I wonder if
we should do something about it even due 8.3 is formally out of support,
not being able to migrate away from 8.3 because it shutdown is kinda
bad.
Due to some lucky coding 8.4+'s autovacuum (I tested only HEAD, but the
code in 8.4 looks fine) manages to vacuum the toast relation even though
no main table exists for it as it only consults the mapping for autovac
options. Its now also allowed to directly vacuum toast tables.
The current behaviour doesn't seem to be a terribly good idea. I propose
to drop the toast table and reset the relfrozenxid in DefineQueryRewrite
in the RelisBecomingView case. Currently the code only does: * * XXX what about getting rid of its TOAST table? For
now,we don't. */if (RelisBecomingView){ RelationDropStorage(event_relation);
DeleteSystemAttributeTuples(event_relid);}
Dropping the toast table seems like its important, it currently only
works by accident, I really doubt everbody working on (auto-)vacuum is
aware of that case.
I would also vote for resetting relfrozenxid of the main relation, but
thats more of a cosmetical issue.
Opinions?
Greetings,
Andres Freund
-- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services
