On Mon, Nov 28, 2011 at 8:40 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
>> I wonder if it would be worthwhile to build some sort of tool to scan
>> the heap and ensure that there are index entries for all heap items,
>> just to test the hypothesis. Not that this would enlighten on the
>> source of the actual problem.
>
> Seems like the hypothesis could be proven or disproven just by counting
> the heap and index entries while the DB is otherwise idle. It used to
> be that VACUUM VERBOSE was sufficient for that sort of cross-check ...
> but I'm not totally sure what push-ups are required nowadays to prevent
> it from deciding that it's smarter than you are so it needn't scan the
> whole table. Is VACUUM FREEZE VERBOSE still trustworthy for this?
Yes. It will scan the whole table if relfrozenxid is too far back,
and FREEZE defines "too far back" to 0 transaction IDs, which is
always satisfied.
It might be useful to add an option to VACUUM to scan the whole table
without otherwise altering the behavior, in case you distrust the
visibility map or whatever but don't care about freezing anything.
The obvious name for the option would be "full", but since that's
already taken we'd need to invent something different. PARANOID?
ALL? SCAN_ALL? INEFFICIENTLY?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company