Re: shared memory size during upgrade pgsql with partitions (max_locks_per_transaction)

------=_NextPart_000_07C8_01D5B58C.2A49DBB0
Content-Type: text/plain;
	charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable

>> On Tue, Dec 17, 2019 at 08:03:41PM +0000, Piotr W=B3odarczyk wrote:
>> Currently we're working on PSQL 11.5 and we're trying upgrade to =
12.1.
>>=20
>> During that we have a problem:
>>=20
>> command: "/usr/pgsql-12/bin/pg_dump" --host /cluster/postgresql =
--port
50432
>> --username postgres --schema-only --quote-all-identifiers
--binary-upgrade
>> --format=3Dcustom  --file=3D"pg_upgrade_dump_281535902.custom"
'dbname=3Dsprint'
>> >> "pg_upgrade_dump_281535902.log" 2> &1
>> pg_dump: error: query failed: ERROR:  out of shared memory
>> HINT:  You might need to increase max_locks_per_transaction.
>> pg_dump: error: query was: LOCK TABLE
>> "some_schemaa"."table_part_80000000_2018q3" IN ACCESS SHARE MODE
>>=20
>> On current instance we have about one thousand of partitions, =
partitioned
in
>> two levels: first by id_product, and second level by quarter of the =
year,
as
>> you can see on above log.
>>=20
>> How have we to calculate shared memory, and (eventually
>> max_locks_per_transaction) to be fit to the limits during upgrade?=20
>=20
>=20
>=20
> Great question.  Clearly, if you can run that (or similar) pg_dump
command,
> then you can pg_upgrade.  I think you could also do pg_upgrade =
--check,

pg_upgrade --check doesn't prompt any error or warning=20

>=20
>=20
>=20
> The query looks like
> 		FROM pg_class c...
> 		WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c',
'%c') "
>=20
>=20
>=20
> ..and then does:
>=20
>=20
>=20
>                if (tblinfo[i].dobj.dump &&
>                        (tblinfo[i].relkind =3D=3D RELKIND_RELATION ||
>                         tblinfo-> relkind =3D=3D =
RELKIND_PARTITIONED_TABLE) &&
>                        (tblinfo[i].dobj.dump &
DUMP_COMPONENTS_REQUIRING_LOCK))
>                {
>                        resetPQExpBuffer(query);
>                        appendPQExpBuffer(query,
>                                                          "LOCK TABLE =
%s IN
ACCESS SHARE MODE",
>
fmtQualifiedDumpable(&tblinfo[i]));
>                        ExecuteSqlStatement(fout, query-> data);
>                }
>=20
>=20
>=20
> ..then filters by -N/-n/-t/-T (which doesn't apply to pg_upgrade):
>                        selectDumpableTable(&tblinfo[i], fout);
>=20
>=20
>=20
> So it looks like COUNT(1) FROM pg_class WHERE relkind IN ('r','p') =
should
do it.
>=20
>=20
>=20
> But actually, during pg_upgrade, since nothing else is running, you
actually
> have max_connections*max_locks_per_transaction total locks.
>=20
>=20
>=20
> Said differently, I think you could set max_locks_per_transaction to:
> SELECT (SELECT COUNT(1) FROM pg_class WHERE relkind IN
('r','p'))/current_setting('max_connections')::int;
>=20
>=20
>=20
> ..probably with a fudge factor of +10 for any system process (and due =
to
> integer truncation).
>=20
>=20
>=20
> Someone might say that pg_upgrade or pg_dump could check for that
specifically..

Yes, and temporarily increase, or HINT how to calculate proper value.

>=20
>=20
>=20
> Justin
>=20
>=20

We realized that the problem is with pg_dump doing during pg_upgreade.

Now we're after upgrade and we can't check Yours calculation. We simply
increased max_connections until migration passed :)=20

I'll try to check it on empty, fake database.=20

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

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIISejCCBTow
ggMioAMCAQICEBm1SlhGhyG6TItEbUzqtdMwDQYJKoZIhvcNAQELBQAwHTEbMBkGA1UEAxMSR0VU
SU4gQmFuayBSb290IENBMB4XDTA2MTExNjA5NDU1NVoXDTM2MTIxNDE2MTgyNVowHTEbMBkGA1UE
AxMSR0VUSU4gQmFuayBSb290IENBMIICIjANBgkqhkiG9w0BAQEFAAOCAg8AMIICCgKCAgEAsF1q
8IHZ5O9M0JAJ+oPRzb4tuWxSeusQeRpKD9wC6CKQIYhxUn4IpZ+uLUUlu+hDXMw2fMtmFC10hc51
YO/OyN7eju5fkpWS83rR/TD8AQeRsHf8tFQZmCa4Q9ihZzoBR620s+D1Naf0sJP/AfCktEoFEnNn
HUg5z/OvTHQqZ/FXJQbSBIE/rq59pNoapJZZyvv7qgeQp7sGWqUN/YHlLgG9UnRlO+kWOoRphI9G
w1leTCTq20joZ4STj5D0YLWMRkR8dV3a6zuWJw/U0t8TThsSEDmMBNVj6z8VqtT2IXwqU0z6Og0Y
ibotl0VW8OyQ4QCwEUxzQnVtHdpQMeVvM+HFzxo7vgg3yhio59sXE61c6cFIix0owLLfteAj6w64
/60rjVEEo+COZo3amklBeZjp03xwoYBSV1GJJVOiaVvBv2i4IYP1aQnHOEGbufFcbhIxSDGNvxNN
VsIf8gl0lRP4T5x+mLQ+ZWJktCe+sRJ6vKPq7Zb9Tr2/PVPWs6cQzp/I9Ku2kFD5laXW6h+qxn21
wRw8S7swJGbJXCeYA0ehXc209MP+e6BKkSerziA5ePKUWLwgP0qE2sLxI+608vH3wBuVJq44zid6
wjvU2QnCLaonn2NJH7doZS+Hgw9tt9DNmbjHwF8kba5sKsrjOMtpF8F325qyq5CebZXXbqUCAwEA
AaN2MHQwCwYDVR0PBAQDAgGGMA8GA1UdEwEB/wQFMAMBAf8wHQYDVR0OBBYEFFpbOoxPlrUmNhn/
qjJPFMJDOzy3MBAGCSsGAQQBgjcVAQQDAgEBMCMGCSsGAQQBgjcVAgQWBBRVWJycPs6ZLMSUvMM2
IZNU5GdPMjANBgkqhkiG9w0BAQsFAAOCAgEATBQx/8TtuXKUOW+7mmg+LuhBjgE77mTn/Gsdva1X
jemnoCO3aSapLDZvjWIqkOahgmBhDkIlOsfFnoLps5S7qjckRcFSd6uypIPouj2crRaWCNMFFWFx
VY0fDZELHCsKPKN/K1qPNbS2JmYbengFYKEe//SVmpSm099ulmQDMxpNKSKh1BGn5ghg3VHny+Jm
cqlSbTOBZ2DCbeHzPxMfgG3OtVjS/GthoUkEKgWMsrTMo3TbFzgaabuopEYczzwhkxW9L3GilHea
a5mTrTKT8mus48Ef8qJawkzGn7f9RmTl0pYnMTMLOOnksTyQ1llwFtfrzIQX4oq1jNN4aQI1Nsb8
Q6eGmkcuVEBN4ZmXEwW6BLv+GuAFMr5WR/QjIzlW/0uUXEifuKtyp9hH7zDAO3N11xWs5DcbDV9N
aY5i6gxValXVeaW4KAC4EjrK1Ll2aRgyFtLU9paaob/s6oAR1ayOFtLCwIqXleOHS15I2n6cq/o7
i9H1ZPLVaHVi6c31ekpmZmnBju3HUpunwNG2bMpG3gIlzYwadPyBIB/vJud2VAj3sAPl8rqyVtVL
+BlqObHFXUIZZMlgS3Ke9+Md7Z4G2Qdm3tk1UYfa2n/c1yiZta4MwP0kP1fQVRLpvJw4W71L/bwW
ihFP4WZBTeqR8gOjE+nyXuIdhfk0vFbAijMwggZwMIIFWKADAgECAhMqAACG/jfeXRw8ln4dAAMA
AIb+MA0GCSqGSIb3DQEBCwUAMFkxFDASBgoJkiaJk/IsZAEZFgRjb3JwMRIwEAYKCZImiZPyLGQB
GRYCYWQxLTArBgNVBAMTJEdFVElOIEJhbmsgU3ViIENBIFBLSUFETTAyIC0gQ2xhc3MgMTAeFw0x
OTAxMDIxMjU2MDNaFw0yMTAxMDExMjU2MDNaMFMxKTAnBgNVBAMMIFfFgm9kYXJjenlrIFBpb3Ry
IERlcGFydGFtZW50IElUMSYwJAYJKoZIhvcNAQkBFhdwaW90ci53bG9kYXJjenlrQGduYi5wbDCB
nzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEAw/WQ+Xt7vli5/RmVQi3Va27PJX3+1xB4hL6NC/Bs
YPhN+BZfcs3NG46vTZFbBFSompfBkByS9T8p3rjvEp7Jm5k8Um5J6Oh2t4t/WAoBZTGckIDQSBuC
YyHMAX6+GNu1f+quFC88SKngMIuF10yYiwSqwLTvmR0V52Sj4+otW7ECAwEAAaOCA7kwggO1MAsG
A1UdDwQEAwIEsDA9BgkrBgEEAYI3FQcEMDAuBiYrBgEEAYI3FQiDyLg1hLebEIa1lyWG2dd65NwC
gTaDj74lhaCicwIBZAIBGjBEBgkqhkiG9w0BCQ8ENzA1MA4GCCqGSIb3DQMCAgIAgDAOBggqhkiG
9w0DBAICAIAwBwYFKw4DAgcwCgYIKoZIhvcNAwcwHQYDVR0lBBYwFAYIKwYBBQUHAwIGCCsGAQUF
BwMEMCcGCSsGAQQBgjcVCgQaMBgwCgYIKwYBBQUHAwIwCgYIKwYBBQUHAwQwSwYDVR0RBEQwQqAn
BgorBgEEAYI3FAIDoBkMF1Bpb3RyLldsb2RhcmN6eWtAZ25iLnBsgRdwaW90ci53bG9kYXJjenlr
QGduYi5wbDAdBgNVHQ4EFgQUf3+6rUH7lWREU04dYOJYPOXvodYwHwYDVR0jBBgwFoAUx+bj0g3o
gN5f+oYufXdw+Q5g46swggEoBgNVHR8EggEfMIIBGzCCARegggEToIIBD4aB2WxkYXA6Ly8vQ049
R0VUSU4lMjBCYW5rJTIwU3ViJTIwQ0ElMjBQS0lBRE0wMiUyMC0lMjBDbGFzcyUyMDEoMyksQ049
UEtJQURNMDIsQ049Q0RQLENOPVB1YmxpYyUyMEtleSUyMFNlcnZpY2VzLENOPVNlcnZpY2VzLENO
PUNvbmZpZ3VyYXRpb24sREM9YWQsREM9Y29ycD9jZXJ0aWZpY2F0ZVJldm9jYXRpb25MaXN0P2Jh
c2U/b2JqZWN0Q2xhc3M9Y1JMRGlzdHJpYnV0aW9uUG9pbnSGMWh0dHA6Ly9wa2kuZ25iLnBsL0Nl
cnRFbnJvbGwvR05CX1NVQl9DQV8wMigzKS5jcmwwggEeBggrBgEFBQcBAQSCARAwggEMMIHNBggr
BgEFBQcwAoaBwGxkYXA6Ly8vQ049R0VUSU4lMjBCYW5rJTIwU3ViJTIwQ0ElMjBQS0lBRE0wMiUy
MC0lMjBDbGFzcyUyMDEsQ049QUlBLENOPVB1YmxpYyUyMEtleSUyMFNlcnZpY2VzLENOPVNlcnZp
Y2VzLENOPUNvbmZpZ3VyYXRpb24sREM9YWQsREM9Y29ycD9jQUNlcnRpZmljYXRlP2Jhc2U/b2Jq
ZWN0Q2xhc3M9Y2VydGlmaWNhdGlvbkF1dGhvcml0eTA6BggrBgEFBQcwAoYuaHR0cDovL3BraS5n
bmIucGwvQ2VydEVucm9sbC9HTkJfU1VCX0NBXzAyLmNydDANBgkqhkiG9w0BAQsFAAOCAQEAc6hN
CeqlY+tueVdnzc2y+FI0imauRq4I24hyPB5DI13MlQYGGt4lADxk+jixvU8ODIbjuGVLfIAhMGef
IS6kF4+nsSNQLKU3dKDckUv0r5zipxGrLN1EGe/FXdofm8vXS28ynIeOUBB68MLkL1Sye+ngE5OH
oZOtRdsRX22eK3nTDiqQN+A/hFUUc2Aq/Te5fl/mT+v7T6QT7De7UIWcZiYW4DVXM8kFm1RQU0wP
WHMkG3uxW4X78kXDSi9GJeciQK7HtIM9+wKTh9ZEvwJmOCyYwRJX/B4cJHyR8R5MR9++i9Ok7AEd
IsYvNnlO1ONxjeEioGXcxIPNCkrfmKVEWTCCBsQwggSsoAMCAQICE3AAAAATDt1X0RJgBgoAAQAA
ABMwDQYJKoZIhvcNAQELBQAwHTEbMBkGA1UEAxMSR0VUSU4gQmFuayBSb290IENBMB4XDTE2MTIx
NDE3MzUyNVoXDTI2MTIxNDE3NDUyNVowWTEUMBIGCgmSJomT8ixkARkWBGNvcnAxEjAQBgoJkiaJ
k/IsZAEZFgJhZDEtMCsGA1UEAxMkR0VUSU4gQmFuayBTdWIgQ0EgUEtJQURNMDIgLSBDbGFzcyAx
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAonUsdYlmhH36J4TrT4ZQTv/sgTvuDwHL
3fYtVxcTUtev5DnmMs6K7w9elWtCk2yhXNxiJDIFTAoMjI6eLhWSTxVmXe7OIkk8AW0Ppw4idbp0
YfZZDyQ5d9EKoLKOgpJe4t9ZkQTfRFGGePasqwwUl+5KXvHwMFuwvDuMHqVf/rn6maUQ2knG5IAe
d0e3x4DJW1GT6/RtqDFmp3G2aZLtSLFx3xSXIamI8cscJBmsK9dqEH0lDRGUXE4LOwvpCGSQYFS7
/gzWRxfPAshGRgFZfDkb4i+bK29NBfe3tcd+dTNgH99vEE1uIAVQfEFauMlqNF3Td18KA4tMujEX
q7GH9wIDAQABo4ICvzCCArswEgYJKwYBBAGCNxUBBAUCAwMAAzAjBgkrBgEEAYI3FQIEFgQUlISC
MWNRx1ZZ3DZYnRzTC5q+GQkwHQYDVR0OBBYEFMfm49IN6IDeX/qGLn13cPkOYOOrMBkGCSsGAQQB
gjcUAgQMHgoAUwB1AGIAQwBBMAsGA1UdDwQEAwIBhjAPBgNVHRMBAf8EBTADAQH/MB8GA1UdIwQY
MBaAFFpbOoxPlrUmNhn/qjJPFMJDOzy3MIIBAQYDVR0fBIH5MIH2MIHzoIHwoIHthoG8bGRhcDov
Ly9DTj1HRVRJTiUyMEJhbmslMjBSb290JTIwQ0EsQ049UEtJQURNMDEsQ049Q0RQLENOPVB1Ymxp
YyUyMEtleSUyMFNlcnZpY2VzLENOPVNlcnZpY2VzLENOPUNvbmZpZ3VyYXRpb24sREM9YWQsREM9
Y29ycD9jZXJ0aWZpY2F0ZVJldm9jYXRpb25MaXN0P2Jhc2U/b2JqZWN0Q2xhc3M9Y1JMRGlzdHJp
YnV0aW9uUG9pbnSGLGh0dHA6Ly9wa2kuZ25iLnBsL0NlcnRFbnJvbGwvR05CX1Jvb3RfQ0EuY3Js
MIIBAAYIKwYBBQUHAQEEgfMwgfAwgbMGCCsGAQUFBzAChoGmbGRhcDovLy9jbj1HRVRJTiUyMEJh
bmslMjBSb290JTIwQ0EsY249QUlBLGNuPVB1YmxpYyUyMEtleSUyMFNlcnZpY2VzLGNuPVNlcnZp
Y2VzLGNuPUNvbmZpZ3VyYXRpb24sZGM9YWQsZGM9Y29ycD9jQUNlcnRpZmljYXRlP2Jhc2U/b2Jq
ZWN0Q2xhc3M9Y2VydGlmaWNhdGlvbkF1dGhvcml0eTA4BggrBgEFBQcwAoYsaHR0cDovL3BraS5n
bmIucGwvQ2VydEVucm9sbC9HTkJfUm9vdF9DQS5jcnQwDQYJKoZIhvcNAQELBQADggIBACU+Z5tp
WX+tKjW/sL18LWackZemcktt0pmPLXPlcgCb6Cjfb5kEy+hdqIn8t9H7zJRJQt8ygAzSQ70P1pTp
W/1YYk8BjGhnZsqyhMbw1SCJ40BYTnOKhw2duI7sJU5hapdii414Ajyu0yfQOT3qmRtPph0xl/EQ
WV8/J7yqdrl/oGBKOiMGjQ0vW6Rr+2pEKWti6K26P8+yP8XpGL65r5D3M3YT2OhVwAH0qpACyfEn
RnVMPQSCXAZhAHn3kaW0sC9+PmYm8kwpcWh3u/iiuNB5V7SNbhGeZmJmv40pbh9/meCR6x6kJuV/
UZ7ReW0W8ZNAowHxalXjDNtNizGrPmZrj7h/BAOYWMDIg15J4RHTw+NUFqK6eG9tGXPKVqOuP8Sa
6sa9ows4npYeFVrNlK+hnY1Je9HcE27WVwPlryagoLq3/6R+/G0OxPzC4edeY+qah8V0teyYyAqh
OFqArQF0tByt49THVBtlBLW1zyuFaqpyXxweRaL5QdFXB+JctTmKQo8zsnHCj8m2W5AfrIYtVcpV
KeCuAKMhVjE76CkQ20+1OoVxCfNfbitcHaKNEY/DcPSyPU9klyOF2eh9gKZVxNmR2jOkVuBNjnOG
p0hTjr0v2yduLlMqRvRpPmpKIbPkITFV9V13oIpyQH5uwjKklF3iOZjJTD7UiKwZJRM9MYIDKjCC
AyYCAQEwcDBZMRQwEgYKCZImiZPyLGQBGRYEY29ycDESMBAGCgmSJomT8ixkARkWAmFkMS0wKwYD
VQQDEyRHRVRJTiBCYW5rIFN1YiBDQSBQS0lBRE0wMiAtIENsYXNzIDECEyoAAIb+N95dHDyWfh0A
AwAAhv4wCQYFKw4DAhoFAKCCAhAwGAYJKoZIhvcNAQkDMQsGCSqGSIb3DQEHATAcBgkqhkiG9w0B
CQUxDxcNMTkxMjE4MDkxNjA4WjAjBgkqhkiG9w0BCQQxFgQUcoWRU+0Zs7OQYp0aU7tzH2uBbfkw
fwYJKwYBBAGCNxAEMXIwcDBZMRQwEgYKCZImiZPyLGQBGRYEY29ycDESMBAGCgmSJomT8ixkARkW
AmFkMS0wKwYDVQQDEyRHRVRJTiBCYW5rIFN1YiBDQSBQS0lBRE0wMiAtIENsYXNzIDECEyoAAIb+
N95dHDyWfh0AAwAAhv4wgYEGCyqGSIb3DQEJEAILMXKgcDBZMRQwEgYKCZImiZPyLGQBGRYEY29y
cDESMBAGCgmSJomT8ixkARkWAmFkMS0wKwYDVQQDEyRHRVRJTiBCYW5rIFN1YiBDQSBQS0lBRE0w
MiAtIENsYXNzIDECEyoAAIb+N95dHDyWfh0AAwAAhv4wgasGCSqGSIb3DQEJDzGBnTCBmjAKBggq
hkiG9w0DBzALBglghkgBZQMEASowCwYJYIZIAWUDBAEWMAsGCWCGSAFlAwQBAjAOBggqhkiG9w0D
AgICAIAwBwYFKw4DAgcwDQYIKoZIhvcNAwICAUAwDQYIKoZIhvcNAwICASgwBwYFKw4DAhowCwYJ
YIZIAWUDBAIDMAsGCWCGSAFlAwQCAjALBglghkgBZQMEAgEwDQYJKoZIhvcNAQEBBQAEgYCR41B9
BT2uNcP/gD9FS0pxGBpGy1uiYUzXTSlHg/a08P1UM5iY5/1+grYxprDt1sJNjbUluVJEeZgqX5WJ
1PXuoTkaOh/7kYiGz5qo6uxTVJZEHa4+QB1wnKCjG64vr7cXtKDyDeKcS7UT9/9Puzx6uBx/B9me
N6AGSu/7BQfbVAAAAAAAAA==

------=_NextPart_000_07C8_01D5B58C.2A49DBB0--