pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Reduce number of commands dumpTableSchema emits for binary upgra 2+ messages / 1 participants [nested] [flat]
* pgsql: Reduce number of commands dumpTableSchema emits for binary upgra @ 2024-07-29 16:17 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2024-07-29 16:17 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Reduce number of commands dumpTableSchema emits for binary upgrade. Avoid issuing a separate SQL UPDATE command for each column when directly manipulating pg_attribute contents in binary upgrade mode. With the separate updates, we triggered a relcache invalidation with each update. For a table with N columns, that causes O(N^2) relcache bloat in txn_size mode because the table's newly-created relcache entry can't be flushed till end of transaction. Reducing the number of commands should make it marginally faster as well as avoiding that problem. While at it, likewise avoid issuing a separate UPDATE on pg_constraint for each inherited constraint. This is less exciting, first because inherited (non-partitioned) constraints are relatively rare, and second because the backend has a good deal of trouble anyway with restoring tables containing many such constraints, due to MergeConstraintsIntoExisting being horribly inefficient. But it seems more consistent to do it this way here too, and it surely can't hurt. In passing, fix one place in dumpTableSchema that failed to use ONLY in ALTER TABLE. That's not a live bug, but it's inconsistent. Also avoid silently casting away const from string literals. Per report from Justin Pryzby. Back-patch to v17 where txn_size mode was introduced. Discussion: https://postgr.es/m/ZqEND4ZcTDBmcv31@pryzbyj2023 Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/b3f0e0503f333938df638a8f499909ce4901b40c Modified Files -------------- src/bin/pg_dump/pg_dump.c | 134 ++++++++++++++++++++++++++++----------- src/bin/pg_dump/t/002_pg_dump.pl | 2 +- 2 files changed, 99 insertions(+), 37 deletions(-) ^ permalink raw reply [nested|flat] 2+ messages in thread
* pgsql: Reduce number of commands dumpTableSchema emits for binary upgra @ 2024-07-29 16:17 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2024-07-29 16:17 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Reduce number of commands dumpTableSchema emits for binary upgrade. Avoid issuing a separate SQL UPDATE command for each column when directly manipulating pg_attribute contents in binary upgrade mode. With the separate updates, we triggered a relcache invalidation with each update. For a table with N columns, that causes O(N^2) relcache bloat in txn_size mode because the table's newly-created relcache entry can't be flushed till end of transaction. Reducing the number of commands should make it marginally faster as well as avoiding that problem. While at it, likewise avoid issuing a separate UPDATE on pg_constraint for each inherited constraint. This is less exciting, first because inherited (non-partitioned) constraints are relatively rare, and second because the backend has a good deal of trouble anyway with restoring tables containing many such constraints, due to MergeConstraintsIntoExisting being horribly inefficient. But it seems more consistent to do it this way here too, and it surely can't hurt. In passing, fix one place in dumpTableSchema that failed to use ONLY in ALTER TABLE. That's not a live bug, but it's inconsistent. Also avoid silently casting away const from string literals. Per report from Justin Pryzby. Back-patch to v17 where txn_size mode was introduced. Discussion: https://postgr.es/m/ZqEND4ZcTDBmcv31@pryzbyj2023 Branch ------ REL_17_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/2fa989e6a3407b9da625e1524c8694bc028e25ba Modified Files -------------- src/bin/pg_dump/pg_dump.c | 134 ++++++++++++++++++++++++++++----------- src/bin/pg_dump/t/002_pg_dump.pl | 2 +- 2 files changed, 99 insertions(+), 37 deletions(-) ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-07-29 16:17 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-07-29 16:17 pgsql: Reduce number of commands dumpTableSchema emits for binary upgra Tom Lane <tgl@sss.pgh.pa.us> 2024-07-29 16:17 pgsql: Reduce number of commands dumpTableSchema emits for binary upgra Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox