From: "Tharakan, Robins" <tharar@amazon.com>
To: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
CC: "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: Re: pg_upgrade failing for 200+ million Large Objects
Thread-Topic: pg_upgrade failing for 200+ million Large Objects
Thread-Index: AdcUCngGVUrqTeFCS/6IXxMGCH55iw==
Date: Mon, 8 Mar 2021 11:02:04 +0000
Message-ID: <a9f9376f1c3343a6bb319dce294e20ac@EX13D05UWC001.ant.amazon.com>
Accept-Language: en-US
Content-Language: en-US
Content-Type: multipart/signed; protocol="application/x-pkcs7-signature";
	micalg=SHA1; boundary="----=_NextPart_000_0103_01D71466.AB832F80"
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/a9f9376f1c3343a6bb319dce294e20ac%40EX13D05UWC001.ant.amazon.com>
Precedence: bulk

------=_NextPart_000_0103_01D71466.AB832F80
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Thanks=A0Peter.

The original email [1] had some more context that somehow didn't get
associated with this=A0recent=A0email. Apologies for any confusion.

In short, pg_resetxlog (and pg_resetwal) employs a magic constant [2] =
(for
both v9.6 as well as master) which seems to have been selected to force =
an
aggressive autovacuum as soon as the upgrade completes. Although that =
works
as planned, it narrows the window of Transaction IDs available for the
upgrade (before which XID wraparound protection kicks and aborts the
upgrade) to 146 Million.

Reducing this magic constant allows a larger XID window, which is what =
the
patch is trying to do. With the patch, I was able to upgrade a cluster =
with
500m Large Objects successfully (which otherwise reliably fails). In the
original email [1] I had also listed a few other possible workarounds, =
but
was unsure which would be a good direction to start working on.... thus =
this
patch to make a start.

Reference:
1)=A0https://www.postgresql.org/message-id/12601596dbbc4c01b86b4ac4d2bd4d=
48%40
EX13D05UWC001.ant.amazon.com
2)=A0https://github.com/postgres/postgres/blob/master/src/bin/pg_resetwal=
/pg_r
esetwal.c#L444

-
robins | tharar@ | syd12


> -----Original Message-----
> From: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
> Sent: Monday, 8 March 2021 9:25 PM
> To: Tharakan, Robins <tharar@amazon.com>; pgsql-hackers@postgresql.org
> Subject: [EXTERNAL] [UNVERIFIED SENDER] Re: pg_upgrade failing for =
200+
> million Large Objects
>=20
> CAUTION: This email originated from outside of the organization. Do =
not
> click links or open attachments unless you can confirm the sender and
> know the content is safe.
>=20
>=20
>=20
> On 07.03.21 09:43, Tharakan, Robins wrote:
> > Attached is a proof-of-concept patch that allows Postgres to perform
> > pg_upgrade if the instance has Millions of objects.
> >
> > It would be great if someone could take a look and see if this patch
> > is in the right direction. There are some pending tasks (such as
> > documentation / pg_resetxlog vs pg_resetwal related changes) but for
> > now, the patch helps remove a stalemate where if a Postgres instance
> > has a large number (accurately speaking 146+ Million) of Large
> > Objects, pg_upgrade fails. This is easily reproducible and besides
> > deleting Large Objects before upgrade, there is no other (apparent) =
way
> for pg_upgrade to complete.
> >
> > The patch (attached):
> > - Applies cleanly on REL9_6_STABLE -
> > c7a4fc3dd001646d5938687ad59ab84545d5d043
> > - 'make check' passes
> > - Allows the user to provide a constant via pg_upgrade command-line,
> > that overrides the 2 billion constant in pg_resetxlog [1] thereby
> > increasing the (window of) Transaction IDs available for pg_upgrade =
to
> complete.
>=20
> Could you explain what your analysis of the problem is and why this =
patch
> (might) fix it?
>=20
> Right now, all I see here is, pass a big number via a command-line =
option
> and hope it works.

------=_NextPart_000_0103_01D71466.AB832F80
Content-Type: application/pkcs7-signature; name="smime.p7s"
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename="smime.p7s"

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIQATCCBA8w
ggL3oAMCAQICEFeHFPCN4dmrSiV65deu/iEwDQYJKoZIhvcNAQEFBQAwOTE3MDUGA1UEAxMuQW1h
em9uLmNvbSBJbnRlcm5hbCBSb290IENlcnRpZmljYXRlIEF1dGhvcml0eTAeFw0wNzA4MzAxODAy
MjVaFw0yNzA4MzAxODEwNTlaMDkxNzA1BgNVBAMTLkFtYXpvbi5jb20gSW50ZXJuYWwgUm9vdCBD
ZXJ0aWZpY2F0ZSBBdXRob3JpdHkwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQDugYdG
nDajFgUbfr6ek3APnSiy6nHUWUEbvxKPFc4eiWaWcGBge4JTiamxVmZu3yFh86klFGYBxixwJ32z
oD56NfWlubhv2feMQFxxKKOiK3dZSIDLbGqCMvILDW/hYHLGL6/rFDFPYbSbs7mJLxFBmWdyCFvf
pDFEMDcPVOFNw4EEQL7TgmPn5loWvtgkSAue5EIgpEcKwy86ylpvzq/Oj/GEWqD8sXAUnhWLgSm6
r1jsAKlk1tmaKsaWBjMC4fiSg8Zq1JI/CQ+FckZ5nHkiCBztzWEYp1m7uBQBBcZ/+l2qdz93u/7f
DxmyICIE4enGr5xTWSv9MDNwQQd9YLh9AgMBAAGjggERMIIBDTALBgNVHQ8EBAMCAYYwDwYDVR0T
AQH/BAUwAwEB/zAdBgNVHQ4EFgQUf42xTkyimArciye/YgVpPCUSs8IwEAYJKwYBBAGCNxUBBAMC
AQAwgbsGA1UdIASBszCBsDCBrQYMKwYBBAGla4FIAQEBMIGcMHIGCCsGAQUFBwICMGYeZABBAG0A
YQB6AG8AbgAuAGMAbwBtACAASQBuAHQAZQByAG4AYQBsACAAQwBlAHIAdABpAGYAaQBjAGEAdABl
ACAAUAByAGEAYwB0AGkAYwBlACAAUwB0AGEAdABlAG0AZQBuAHQwJgYIKwYBBQUHAgEWGmh0dHA6
Ly9wa2kuYW1hem9uLmNvbS9jcHMvMA0GCSqGSIb3DQEBBQUAA4IBAQB0AW6ePZaQ83mcE9Etduc1
aSp4nvLUoJ2NAI1v4UDB3A0iBggNo9XfEsfin/tJoXkWuHxtB5ucZNAW3ZletXQfW3DAbWVr5kAZ
Tv4h/u/9OqAVZCOuxYMUZqfwJiPybm4xj9dnll6F9mF7Ur5I7D+PX+MmuJNsEza3MqcJaxcefrI5
0XTn9uCMgxo6/x56LqWD46AxrYBe4YjF8FQ9VBRz4S1cS0KI7mA40i5dyOc2nmnET6e+iIQPGHzQ
iTuereCRhGybLkKi3yChe4Uw6KqQ4qSVVAYf1nJjrDYk3RUHHF95JcWCHyTh4safTHcTETNWx8F9
MWWlF96gZ4B8/ORlMIIFjjCCBHagAwIBAgIKYRwhegAAAAAAJjANBgkqhkiG9w0BAQsFADA5MTcw
NQYDVQQDEy5BbWF6b24uY29tIEludGVybmFsIFJvb3QgQ2VydGlmaWNhdGUgQXV0aG9yaXR5MB4X
DTE4MTEwNjIzMzExOFoXDTIzMTEwNjIzNDExOFowZDETMBEGCgmSJomT8ixkARkWA2NvbTEWMBQG
CgmSJomT8ixkARkWBmFtYXpvbjETMBEGCgmSJomT8ixkARkWA2FudDEgMB4GA1UEAxMXQW1hem9u
LmNvbSBDSUEgQ0EgRzQgMDEwggIiMA0GCSqGSIb3DQEBAQUAA4ICDwAwggIKAoICAQDBNKNhXeYZ
RggQpGc194zGplBxDWOWYsMY7jc4EkhplrCkkPWqcfCuJh0SLJpcbZwin70hnu3DXwmt6N5nZu7c
Yd1h40YL/p6zULMebqn0p9AwUMK+8qV514J79VjioN/GE7h5G8ZkyjUjmqeBtTczJUifwAJ72n6b
wSFLLZS28Nar4blskiGdX6ZaB+N3nNp4DN+RzCqfFc15Ta9iVIj50ChknahX8Ia4Hjw3azNKWJl1
JGTsZIETr9N/m6ta1LMjuAMpJCqwCx8Ndbkml+cjyul6T5MMHNMBYCeABm7P0HWcSoinZbK7qNJO
TZDfP6NWFm/6VgSr/U/WqMJKZrN9Gb3KEr3kVBDMG07Al7YM6nwzLG60P3oPodOUW5MSU+zpN3Fa
vqWsopJ5uBBk26YFpn1+8CJnuwwT4VjQtRF6SQk04fNpowHxOtgrVlIr7Wuf4bD3eMU6A8mbSZyp
9SBHxR1n8GLO8o3/IQhC75AawgBTm04X+K9eQdoaLdk5rNxxMHJ+y15fwZpab5WfpBNN71Qi/fWO
/hoimZ1ytSMHSMmAnf4Q/jQC7GLXTIRikjR4f+KLSPrWu17h4Cqv2ehbHlOS8tJG866cViWlm9pc
JNfz1QOpgoPn1fT6UpoVh40YsigC/qJktW3HmyCWOzXU0u7H61BpgxJBZS7AMvBSqQIDAQABo4IB
azCCAWcwEAYJKwYBBAGCNxUBBAMCAQAwHQYDVR0OBBYEFOAp5m6qUdZa9admS/aG9DXnqw+rMBkG
CSsGAQQBgjcUAgQMHgoAUwB1AGIAQwBBMAsGA1UdDwQEAwIBhjASBgNVHRMBAf8ECDAGAQH/AgEA
MB8GA1UdIwQYMBaAFH+NsU5MopgK3Isnv2IFaTwlErPCMGUGA1UdHwReMFwwWqBYoFaGVGh0dHA6
Ly9wa2kuYW1hem9uLmNvbS9jcmwvQW1hem9uLmNvbSUyMEludGVybmFsJTIwUm9vdCUyMENlcnRp
ZmljYXRlJTIwQXV0aG9yaXR5LmNybDBwBggrBgEFBQcBAQRkMGIwYAYIKwYBBQUHMAKGVGh0dHA6
Ly9wa2kuYW1hem9uLmNvbS9jcnQvQW1hem9uLmNvbSUyMEludGVybmFsJTIwUm9vdCUyMENlcnRp
ZmljYXRlJTIwQXV0aG9yaXR5LmNydDANBgkqhkiG9w0BAQsFAAOCAQEAFY6kjPmVGLYIPhHlDX+f
HshBGUEoGXAPbtNLg+WV7vvpu5dT3vgNsAFb9tjfa1j46D0ZppFMtxpM6GHGP89yqB4r/6+R3dMg
kwd5uinR22ExYISRwS4Aw8f6KwS4xK1SMnVGNWj2PhYrUyLRqPmRK/FviIgbCR55tPEZrDtsvaVW
GMlfpLFa3WIozj8Zkd7Q0Wn7H3+SRWwcSnf88bIbGyllr2Hr6QzC2tJlaD2VxcZdPqFxRkA7as3X
RQ2u/GnavSdbs/oTh5UO7mEMNASHfyl2pAgpGyLDy2ab8F5FjihnNCxHaKP9AXVrkm6ncqsYAHdG
ipZ3WqWrIosMV6m+VjCCBlgwggRAoAMCAQICE2gAyK8og25ErglxdPcAAADIrygwDQYJKoZIhvcN
AQELBQAwZDETMBEGCgmSJomT8ixkARkWA2NvbTEWMBQGCgmSJomT8ixkARkWBmFtYXpvbjETMBEG
CgmSJomT8ixkARkWA2FudDEgMB4GA1UEAxMXQW1hem9uLmNvbSBDSUEgQ0EgRzQgMDEwHhcNMjAx
MTEzMDczNDQ1WhcNMjExMTEzMDczNDQ1WjCBtzETMBEGCgmSJomT8ixkARkWA2NvbTEWMBQGCgmS
JomT8ixkARkWBmFtYXpvbjETMBEGCgmSJomT8ixkARkWA2FudDEMMAoGA1UECxMDT1JHMRIwEAYD
VQQLEwlBdXN0cmFsaWExDjAMBgNVBAsTBVNZRDEwMQ4wDAYDVQQLEwVVc2VyczEPMA0GA1UEAxMG
dGhhcmFyMSAwHgYJKoZIhvcNAQkBFhF0aGFyYXJAYW1hem9uLmNvbTCCASIwDQYJKoZIhvcNAQEB
BQADggEPADCCAQoCggEBAPXXLe5ApZHjG7dzdJANAxxaqxU2yp0rcBOtQEWdjIDVxTkI6AiSyEMe
0yaQMy0oaWAzfennPaRwzduDwIw6B91hsFqP3bE0QDcc6GUgRfW8WfAN1nfsCJZbBVZWBp5kV/B/
kTHoKwlgWW58EnpBdwg5C+WmcO1vgUGGZ7aAa5yAuhpN6gLqJR7atBxC4VpX+cgnpu7Q47E97acq
OIYfuYcpZy+zuCKNZ0Vi6bnnHw8Opc+xFeM0EF9YHwoA+1P4FMGvYvRWZ9UDnakYTWdpRRB+BPXU
eKdziAEinKFjtU9pa42k6/GpyBWtdaA2OIuhXLnNVWZKfaJkS6SGvufLdrUCAwEAAaOCAa0wggGp
MDwGCSsGAQQBgjcVBwQvMC0GJSsGAQQBgjcVCITFkUGFu5oogsGRL4TQqFfGmyMIh6brIoeJ+xoC
AWQCAQswEwYDVR0lBAwwCgYIKwYBBQUHAwQwCwYDVR0PBAQDAgXgMBsGCSsGAQQBgjcVCgQOMAww
CgYIKwYBBQUHAwQwHQYDVR0OBBYEFE14CkFPHcko33oMylI28+V9rXpyMB8GA1UdIwQYMBaAFOAp
5m6qUdZa9admS/aG9DXnqw+rME4GA1UdHwRHMEUwQ6BBoD+GPWh0dHA6Ly9wa2kuYW1hem9uLmNv
bS9jcmwvQW1hem9uLmNvbSUyMENJQSUyMENBJTIwRzQlMjAwMS5jcmwwWQYIKwYBBQUHAQEETTBL
MEkGCCsGAQUFBzAChj1odHRwOi8vcGtpLmFtYXpvbi5jb20vY3J0L0FtYXpvbi5jb20lMjBDSUEl
MjBDQSUyMEc0JTIwMDEuY3J0MD8GA1UdEQQ4MDagIQYKKwYBBAGCNxQCA6ATDBF0aGFyYXJAYW1h
em9uLmNvbYERdGhhcmFyQGFtYXpvbi5jb20wDQYJKoZIhvcNAQELBQADggIBAL1rzJpNq8ppMxZo
7viCegsnANvePrebNGn4QrkAk/xtgOQ8do1WypOCsGMbdSc8EKAEjlaufXXGf4KUkAQ5XLZoRLX+
DlrL1vZB38PILiaxtK0HnyN0kWQYA7GfKfBuUYr3AMjCsGbfTvoFcGQcTHOQZryXOW3xugDKedGU
LDp/cVn1jxlUwDmcNhXu9Yd+XjHNbEAeDhMw/QY6I1o/DFYXweazPICE8MkT4yEHoOotDxBlpbCA
gIYQSreJ0yYT8wtS2INBl3HCLoMMatUDmP2HeYHsLT/PKEqEou68eAwTrc44k5Bjvy1sHMOD4OAo
C92vb33Y8Sp/uvXT93tvBExLkIILBN+Py95Kb+6WEg5Ko741XmAv+z6DJ54MbAcJC/dT05e6Yt22
dBYVHW1rRWcrfJbmuS18dcuXhRizYBK3QuVXWU46bJgBft3VaPAqbxMX9E7jfD7Yh3Q9S1sPL9ad
gmaiDwnLRpXoCwiuZ57LLWPn2BoAphx7IexWIDNXac36j7hLw9PyY8tWsVz6VPowquWPfO4IoOmu
VXKaKUvlmp+ugCNqXRHpJkL+hbTsyxzeCvBMDVlz01aE3PDZIfc3wd5cmrXJT4XMS0nSX563BS9C
xA820RSUn1hFuFTg0zzovcksHMfuPxAWfjoKxi+bieOhbmXr+dlxh9EmY7jRMYIDtTCCA7ECAQEw
ezBkMRMwEQYKCZImiZPyLGQBGRYDY29tMRYwFAYKCZImiZPyLGQBGRYGYW1hem9uMRMwEQYKCZIm
iZPyLGQBGRYDYW50MSAwHgYDVQQDExdBbWF6b24uY29tIENJQSBDQSBHNCAwMQITaADIryiDbkSu
CXF09wAAAMivKDAJBgUrDgMCGgUAoIICDzAYBgkqhkiG9w0BCQMxCwYJKoZIhvcNAQcBMBwGCSqG
SIb3DQEJBTEPFw0yMTAzMDgxMTAyMDJaMCMGCSqGSIb3DQEJBDEWBBSrub40i/mCyVhz+t7NmJLJ
LACfqTCBigYJKwYBBAGCNxAEMX0wezBkMRMwEQYKCZImiZPyLGQBGRYDY29tMRYwFAYKCZImiZPy
LGQBGRYGYW1hem9uMRMwEQYKCZImiZPyLGQBGRYDYW50MSAwHgYDVQQDExdBbWF6b24uY29tIENJ
QSBDQSBHNCAwMQITaADIryiDbkSuCXF09wAAAMivKDCBjAYLKoZIhvcNAQkQAgsxfaB7MGQxEzAR
BgoJkiaJk/IsZAEZFgNjb20xFjAUBgoJkiaJk/IsZAEZFgZhbWF6b24xEzARBgoJkiaJk/IsZAEZ
FgNhbnQxIDAeBgNVBAMTF0FtYXpvbi5jb20gQ0lBIENBIEc0IDAxAhNoAMivKINuRK4JcXT3AAAA
yK8oMIGTBgkqhkiG9w0BCQ8xgYUwgYIwCwYJYIZIAWUDBAEqMAsGCWCGSAFlAwQBFjAKBggqhkiG
9w0DBzALBglghkgBZQMEAQIwDgYIKoZIhvcNAwICAgCAMA0GCCqGSIb3DQMCAgFAMAcGBSsOAwIa
MAsGCWCGSAFlAwQCAzALBglghkgBZQMEAgIwCwYJYIZIAWUDBAIBMA0GCSqGSIb3DQEBAQUABIIB
AG0bWgAoO4Cg83+Gx5pTTWiBCOPP799P2NKGe/bg9UIRYGnHdtww5qiKqhJreNrurtH4pVoM/Bh9
kEStCA8CLI9UQ0FS33WsfnAG4xJzc+JC02LyGSEvN7L3PPQ8n3Zk4pQRl68i1ls7iOys4y6Gg5sC
Uhu7x3wvWIraSQc+b1zD2AgfGhFY1aNOUKczWzI0rVFmeasBXhI0cx6l+mWJw6ADRTJt5G8emsf/
/7syiG/aIlO02ugACyip+Q4nYQUIQSdrHb3bUD2/bSA8jZWEE/lbvWjlAfwPJq08LTmUBcxO5JaD
/98im4cmi0/chEi60qYytdPoWTdsp0829UZ5MtoAAAAAAAA=

------=_NextPart_000_0103_01D71466.AB832F80--