pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Count individual SQL commands in pg_restore's --transaction-size 2+ messages / 1 participants [nested] [flat]
* pgsql: Count individual SQL commands in pg_restore's --transaction-size @ 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 Count individual SQL commands in pg_restore's --transaction-size mode. The initial implementation in commit 959b38d77 counted one action per TOC entry (except for some special cases for multi-blob BLOBS entries). This assumes that TOC entries are all about equally complex, but it turns out that that assumption doesn't hold up very well in binary-upgrade mode. For example, even after the previous commit I was able to cause backend bloat with tables having many inherited constraints. There may be other cases too. (Since no serious problems have been reported with --single-transaction mode, we can conclude that the backend copes well with psql's regular restore scripts; but before 959b38d77 we never ran binary-upgrade restores with multi-command transactions.) To fix, count multi-command TOC entries as N actions, allowing the transaction size to be scaled down when we hit a complex TOC entry. Rather than add a SQL parser to pg_restore, approximate "multi command" by counting semicolons in the TOC entry's defn string. This will be fooled by semicolons appearing in string literals --- but the error is in the conservative direction, so it doesn't seem worth working harder. The biggest risk is with function/procedure TOC entries, but we can just explicitly skip those. (This is undoubtedly a hack, and maybe someday we'll be able to revert it after fixing the backend's bloat issues or rethinking what pg_dump emits in binary upgrade mode. But that surely isn't a project for v17.) Thanks to Alexander Korotkov for the let's-count-semicolons idea. 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/0f129052150487afab9fe64889c5bf7534f7bbc3 Modified Files -------------- src/bin/pg_dump/pg_backup_archiver.c | 28 +++++++++++++++++++++++++--- 1 file changed, 25 insertions(+), 3 deletions(-) ^ permalink raw reply [nested|flat] 2+ messages in thread
* pgsql: Count individual SQL commands in pg_restore's --transaction-size @ 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 Count individual SQL commands in pg_restore's --transaction-size mode. The initial implementation in commit 959b38d77 counted one action per TOC entry (except for some special cases for multi-blob BLOBS entries). This assumes that TOC entries are all about equally complex, but it turns out that that assumption doesn't hold up very well in binary-upgrade mode. For example, even after the previous commit I was able to cause backend bloat with tables having many inherited constraints. There may be other cases too. (Since no serious problems have been reported with --single-transaction mode, we can conclude that the backend copes well with psql's regular restore scripts; but before 959b38d77 we never ran binary-upgrade restores with multi-command transactions.) To fix, count multi-command TOC entries as N actions, allowing the transaction size to be scaled down when we hit a complex TOC entry. Rather than add a SQL parser to pg_restore, approximate "multi command" by counting semicolons in the TOC entry's defn string. This will be fooled by semicolons appearing in string literals --- but the error is in the conservative direction, so it doesn't seem worth working harder. The biggest risk is with function/procedure TOC entries, but we can just explicitly skip those. (This is undoubtedly a hack, and maybe someday we'll be able to revert it after fixing the backend's bloat issues or rethinking what pg_dump emits in binary upgrade mode. But that surely isn't a project for v17.) Thanks to Alexander Korotkov for the let's-count-semicolons idea. 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/81db073a287842cbf0a17cb32108b214a335670b Modified Files -------------- src/bin/pg_dump/pg_backup_archiver.c | 28 +++++++++++++++++++++++++--- 1 file changed, 25 insertions(+), 3 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: Count individual SQL commands in pg_restore's --transaction-size Tom Lane <tgl@sss.pgh.pa.us> 2024-07-29 16:17 pgsql: Count individual SQL commands in pg_restore's --transaction-size 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