Mailing List Archive

This is an automated email from the git hooks/post-receive script. It was
generated because a ref change was pushed to the repository containing
the project "rt".

The branch, 5.0/rt-importer-split-batch-for-large-query has been created
at 1576c531dc97576062489a14a028b7207c57507b (commit)

- Log -----------------------------------------------------------------
commit 1576c531dc97576062489a14a028b7207c57507b
Author: Brad Embree <brad@bestpractical.com>
Date: Sat Dec 16 15:21:42 2023 -0800

Ensure batches stay under 1GB for Postgres

Postgres has a hard coded query size limit of 1GB. If a batch is large
enough it can exceed the limit.

Added code to check the size of values in a batch and split the batch to
ensure it stays under the size limit.

diff --git a/lib/RT/Migrate/Importer.pm b/lib/RT/Migrate/Importer.pm
index f73240e522..ff89aaeaa8 100644
--- a/lib/RT/Migrate/Importer.pm
+++ b/lib/RT/Migrate/Importer.pm
@@ -837,11 +837,42 @@ sub _BatchCreate {
$values_paren = $1;
}

- # DBs have placeholder limitations(64k for Pg), here we replace
- # placeholders to support bigger batch sizes. The performance is similar.
- my $batch_sql
- = $RT::Handle->FillIn( $sql . ( ", $values_paren" x ( $count - 1 ) ), [ map @$_, @{ $query{$sql} } ] );
- $self->RunSQL($batch_sql);
+ if ( RT->Config->Get( 'DatabaseType' ) eq 'Pg' ) {
+ # Postgres has a max query string size of 1 GB
+ # check if we need to split up the batch
+ my @query_bind_vals;
+ my ( $vals_size, $bind_vals_batch ) = ( 0, [] );
+ foreach my $bind_vals ( @{ $query{$sql} } ) {
+ push @$bind_vals_batch, $bind_vals;
+ foreach my $val ( @$bind_vals ) {
+ $vals_size += length( defined $val ? $val : '' );
+ }
+ # check if over 900 MB to leave some extra room
+ if ( $vals_size > 943_718_400 ) {
+ push @query_bind_vals, $bind_vals_batch;
+ $vals_size = 0;
+ $bind_vals_batch = [];
+ }
+ }
+ push @query_bind_vals, $bind_vals_batch
+ if @$bind_vals_batch;
+
+ foreach my $bind_vals ( @query_bind_vals ) {
+ $count = @$bind_vals;
+ # DBs have placeholder limitations(64k for Pg), here we replace
+ # placeholders to support bigger batch sizes. The performance is similar.
+ my $batch_sql
+ = $RT::Handle->FillIn( $sql . ( ", $values_paren" x ( $count - 1 ) ), [ map @$_, @$bind_vals ] );
+ $self->RunSQL($batch_sql);
+ }
+ }
+ else {
+ # DBs have placeholder limitations(64k for Pg), here we replace
+ # placeholders to support bigger batch sizes. The performance is similar.
+ my $batch_sql
+ = $RT::Handle->FillIn( $sql . ( ", $values_paren" x ( $count - 1 ) ), [ map @$_, @{ $query{$sql} } ] );
+ $self->RunSQL($batch_sql);
+ }
}

# Clone doesn't need to return anything

-----------------------------------------------------------------------


hooks/post-receive
--
rt
_______________________________________________
rt-commit mailing list
rt-commit@lists.bestpractical.com
https://lists.bestpractical.com/mailman/listinfo/rt-commit