[Dbix-class] I get error with last chunk in ->insert_bulk

Oleg Pronin syber.rus at gmail.com
Thu Mar 12 14:31:53 GMT 2009


Hello.
I'm trying to insert a millions of records into database with ->insert_bulk
method.
I do this by parts, 5000 records per chunk for memory economy.

    my $ars =3D $c->BalanceArchiveRS;
    my $str =3D $c->schema->storage;
    my @names =3D qw/user date event amount reason reason_type game/;
    my $i =3D 1;
    my @buf;

    while (my $row =3D $rs->next) {
        push @buf, [
            $row->{user}, "$row->{y}-$row->{m}-$row->{d}", $row->{event},
            $row->{amsum}, $row->{reason}, $row->{reason_type},
$row->{game},
        ];

        next if $i++ % 5000;
        $str->insert_bulk($ars->result_source, \@names, \@buf);
        @buf =3D ();
    }
    $str->insert_bulk($ars->result_source, \@names, \@buf) if @buf;

Everything is going great until last line which flushes remaining buffer
into database:
    $str->insert_bulk($ars->result_source, \@names, \@buf) if @buf;

It has less than 5000 rows and i get error:

[12/03/09 05:44:37] balance_history.plx [emergency]: This service has died:
DBIx::Class::Storage::DBI::insert_bulk(): DBI Exception: DBD::Pg::st
bind_param_array failed: Arrayref for parameter 1 has 178 elements but
parameter 6 has 5000 [for Statement "INSERT INTO "balance_archive"
("amount", "date", "event", "game", "reason", "reason_type", "user") VALUES
(?, ?, ?, ?, ?, ?, ?)" with ParamValues: 1=3D'2.1', 2=3D'2009-2-25', 3=3D'2=
',
4=3D'6', 5=3D'1', 6=3Dundef, 7=3D'9588407'] at
./script/maintenance/balance_history.plx line 45

It seems like $sth remember values counts (Storage::DBI calls prepare_cached
to get $sth handle) and won't insert with cached $sth different number of
records.

If i do
$str->disable_sth_caching(1);
before last buffer flushing then everythings going great but it seems like a
hack for me.

Is there any good solution for this problem?

Thanks.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20090312/17e=
b4108/attachment.htm


More information about the DBIx-Class mailing list