PG17 --transaction-size=1000 caused MVU failure

Virender Singla <virender.cse@gmail.com>

From: Virender Singla <virender.cse@gmail.com>

To: pgsql-bugs@lists.postgresql.org

Cc: Aniket Jha <aniketkumarj@gmail.com>

Date: 2025-02-18T09:08:17Z

Lists: pgsql-bugs

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Invent --transaction-size option for pg_restore.
- 959b38d770ba 17.0 cited

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.

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