Re: Freeze avoidance of very large table.

On Tue, Dec 1, 2015 at 10:40 AM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> On Tue, Dec 1, 2015 at 3:04 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>> On Mon, Nov 30, 2015 at 9:18 AM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>>> On Sun, Nov 29, 2015 at 2:21 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>>>> On Tue, Nov 24, 2015 at 3:13 PM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>>>>>
>>>>> Yeah, we need to consider to compute checksum if enabled.
>>>>> I've changed the patch, and attached.
>>>>> Please review it.
>>>>
>>>> Thanks for the update. This now conflicts with the updates doesn to
>>>> fix pg_upgrade out-of-space issue on Windows. I've fixed (I think) the
>>>> conflict in order to do some testing, but I'd like to get an updated
>>>> patch from the author in case I did it wrong. I don't want to find
>>>> bugs that I just introduced myself.
>>>>
>>>
>>> Thank you for having a look.
>>>
>>> Attached updated v28 patch.
>>> Please review it.
>>>
>>> Regards,
>>
>> After running pg_upgrade, if I manually vacuum a table a start getting warnings:
>>
>> WARNING: page is not marked all-visible (and all-frozen) but
>> visibility map bit(s) is set in relation "foo" page 32756
>> WARNING: page is not marked all-visible (and all-frozen) but
>> visibility map bit(s) is set in relation "foo" page 32756
>> WARNING: page is not marked all-visible (and all-frozen) but
>> visibility map bit(s) is set in relation "foo" page 32757
>> WARNING: page is not marked all-visible (and all-frozen) but
>> visibility map bit(s) is set in relation "foo" page 32757
>>
>> The warnings are right where the blocks would start using the 2nd page
>> of the _vm, so I think the problem is there. And looking at the code,
>> I think that "cur += SizeOfPageHeaderData;" in the inner loop cannot
>> be correct. We can't skip a header in the current (old) block each
>> time we reach the end of the new block. The thing we are skipping in
>> the current block is half the time not a header, but the data at the
>> halfway point through the block.
>>
>
> Thank you for reviewing.
>
> You're right, it's not necessary.
> Attached latest v29 patch which removes the mention in pg_upgrade documentation.

I could successfully upgrade with this patch, with the link option and
without. After the update the tables seemed to have their correct
visibility status, and after a VACUUM FREEZE then had the correct
freeze status as well.

Then I manually corrupted the vm file, just to make sure a corrupted
one would get detected. And much to my surprise, I didn't get any
errors or warning when starting it back up and running vacuum freeze
(unless I had page checksums turned on, then I got warnings and zeroed
out pages). But I guess this is not considered a warnable condition
for bits to be off when they should be on, only the opposite.

Consecutive VACUUM FREEZE operations with no DML activity between were
not sped up by as much as I thought they would be, because it still
had to walk all the indexes even though it didn't touch the table at
all. In real-world usage there would almost always be some dead
tuples that would require an index scan anyway for a normal vacuum.

Cheers,

Jeff