Re: Freeze avoidance of very large table.

On Thu, Nov 19, 2015 at 6:44 AM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> On Thu, Nov 19, 2015 at 5:54 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>> On Wed, Nov 18, 2015 at 11:18 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>>>
>>> I get an error when running pg_upgrade from 9.4 to 9.6-this
>>>
>>> error while copying relation "mediawiki.archive"
>>> ("/tmp/data/base/16414/21043_vm" to
>>> "/tmp/data_fm/base/16400/21043_vm"): No such file or directory
>>
>> OK, so the problem seems to be that rewriteVisibilitymap can get
>> called with errno already set to a nonzero value.
>>
>> It never clears it, and then fails at the end despite that no error
>> has actually occurred.
>>
>> Just setting it to 0 at the top of the function seems to be correct
>> thing to do. Or does it need to save the old value and restore it?
>
> Thank you for testing!
> I think that the former is better, so attached latest patch.
>
>> But now when I want to do the upgrade faster, I run into this:
>>
>> "This utility cannot upgrade from PostgreSQL version from 9.5 or
>> before to 9.6 or later with link mode."
>>
>> Is this really an acceptable a tradeoff? Surely we can arrange to
>> link everything else and rewrite just the _vm, which is a tiny portion
>> of the data directory. I don't think that -k promises to link
>> everything it possibly can.
>
> I agree.
> I've changed the patch so that.
> pg_upgarde creates new _vm file and rewrites it even if upgrading to
> 9.6 with link mode.

The rewrite code thinks that only the first page of a vm has a header
of size SizeOfPageHeaderData, and the rest of the pages have a zero
size header. So the resulting _vm is corrupt.

After pg_upgrade, doing a vacuum freeze verbose gives:

WARNING: invalid page in block 1 of relation base/16402/22430_vm;
zeroing out page
WARNING: invalid page in block 1 of relation base/16402/22430_vm;
zeroing out page
WARNING: invalid page in block 2 of relation base/16402/22430_vm;
zeroing out page
WARNING: invalid page in block 2 of relation base/16402/22430_vm;
zeroing out page
WARNING: invalid page in block 3 of relation base/16402/22430_vm;
zeroing out page
WARNING: invalid page in block 3 of relation base/16402/22430_vm;
zeroing out page
WARNING: invalid page in block 4 of relation base/16402/22430_vm;
zeroing out page
WARNING: invalid page in block 4 of relation base/16402/22430_vm;
zeroing out page
WARNING: invalid page in block 5 of relation base/16402/22430_vm;
zeroing out page
WARNING: invalid page in block 5 of relation base/16402/22430_vm;
zeroing out page
WARNING: invalid page in block 6 of relation base/16402/22430_vm;
zeroing out page
WARNING: invalid page in block 6 of relation base/16402/22430_vm;
zeroing out page
WARNING: invalid page in block 7 of relation base/16402/22430_vm;
zeroing out page
WARNING: invalid page in block 7 of relation base/16402/22430_vm;
zeroing out page
WARNING: invalid page in block 8 of relation base/16402/22430_vm;
zeroing out page
WARNING: invalid page in block 8 of relation base/16402/22430_vm;
zeroing out page

Cheers,

Jeff