Thread: PG17 --transaction-size=1000 caused MVU failure
Recently we encountered failures during a direct major version upgrade from PG14 to PG17 on an instance, however an upgrade from PG14 to either PG15 or PG16 went fine (on a cloned instance).
Further debugging revealed that a hard-coded "--transaction-size=1000" parameter caused the v14 to v17 upgrade failure. This change was introduced in the following commit:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=959b38d770ba1f8f35edab27ef3ccf8b1d99f5dd
Our schema analysis revealed numerous foreign key constraints and indexes on parent and child tables. Due to foreign key creation acquiring locks on all related table indexes, pg_restore resulted in lock table overflow with a "1000" batch size. We eventually did change max_locks_per_transaction flag for a successful upgrade.
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=959b38d770ba1f8f35edab27ef3ccf8b1d99f5dd
Our schema analysis revealed numerous foreign key constraints and indexes on parent and child tables. Due to foreign key creation acquiring locks on all related table indexes, pg_restore resulted in lock table overflow with a "1000" batch size. We eventually did change max_locks_per_transaction flag for a successful upgrade.
This is an edge case and not exactly a bug, but rather a behavior change introduced between PG16 and PG17. It is being reported for awareness (may open a discussion for a configurable "tansaction_size" during upgrades).
Thanks,
Virender