Re: CREATE RULE "_RETURN" and toast tables

On 2013-02-14 20:47:11 -0500, Tom Lane wrote:
> Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> > 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,
>
> Out of support is out of support. We're certainly not going to update
> 8.3 to fix corner cases that escaped notice for the five years it was in
> support.

Well, its going to get more likely with age... But sure, I have no probelm

> (And it's not true that you can't get out of it --- if nothing
> else, you could manually update the toast table's relfrozenxid value.)

Yea, thats what we ended up with. For the benefit of people searching
for the problem, if you hit strange wraparound issues that cannot be
fixed in 8.3 you can escape the issue with:

UPDATE pg_class
SET relfrozenxid = (txid_current() % (1::bigint<<32))::text::xid
WHERE NOT relfrozenxid = '0' AND relkind = 't'
AND pg_class.oid IN ( SELECT reltoastrelid FROM pg_class WHERE relkind = 'v')
RETURNING *;

> > 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.
>
> Yeah, probably worth doing. At the time we thought that that code path
> was just a short-term legacy thing for loading ancient pg_dump files.
> However, given that even modern pg_dumps will use this syntax if
> necessary to break circular dependencies for views, we're probably never
> going to be rid of it completely.

Yep, thats what I thought. Will write something up.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services