Re: Idea for getting rid of VACUUM FREEZE on cold pages

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Hm, that is an excellent point. The WAL trace would actually be a
>> lot superior in terms of being able to figure out what went wrong.
>> But I don't quite see how we tell people "either keep xmin or keep
>> your old WAL". Also, for production sites the amount of WAL you'd
>> have to hang onto seems a bit daunting.

> Any thoughts on how far back the WAL would need to go to deal with
> the issues where such information has been useful? (For example, we
> always have at least two weeks worth, but I don't know if that's a
> useful range or not.)

Well, it's a "how long does it take you to notice data corruption"
kind of issue. The most recent case I can think of where xmin was
helpful was in trying to sort out a problem with an index being
inconsistent with the heap, which manifested as wrong query answers
for the user. I don't know how long it took him to recognize and
report the problem. (We never did locate the bug-if-any, IIRC...
it would have been much more helpful to have the WAL trace. xmin
did let me rule out some theories, though.)

>> Other problems are the cost of shipping it to a developer, and the
>> impracticality of sanitizing private data in it before you show it
>> to somebody.

> Yeah, this wouldn't be a practical answer to the need unless
> PostgreSQL shipped with a tool which could scan WAL and extract the
> relevant information (probably under direction of someone from the
> list or a private support organization). Is the required
> information predictable enough to make developing such a tool a
> tractable problem?

Hard to tell. If we were actually going in this direction we'd
want to write a much better WAL-text-dump tool than we have, and then
in principle somebody could sanitize the text output before shipping
it off. But going through a large volume of data that way could be
pretty impractical. Also, we (or at least I) have nearly zip experience
with trying to debug problems by examining WAL, so it's not real clear
to me which details might be important.

regards, tom lane