Thread: Bulk inserts within a Perl script?

On Apr 18, 2006, at 4:03 PM, Kynn Jones wrote:

> I have a Perl script that is supposed to make a large number of
> inserts in a PostgreSQL database.  Performing individual inserts
> with SQL's INSERT command is too slow, however, I can use a
> "COPY ... from stdin" approach that is fast enough.  Basically, I
> fork a psql process (yes, I'm on unix) and have the parent write
> the new records into the child, as part of a COPY from stdin
> command.  Admittedly, this is very convoluted, but it works.
>
> Well, it almost works.  It did in my preliminary tests, but failed
> in the real run with the actual database because the owner of the
> tables that get modified by this COPY statement is a user for which
> the psql command requires a password ( i.e. this user exists solely
> for the purpose of owning the database in question; it does not
> have a UID, nor disk space assigned to it), and I don't know how to
> give this password programmatically.
>
> So I'm back at the drawing board.  How can I make fast bulk inserts
> into a PostgreSQL database from within a Perl script?
>

Assuming you're using DBI, perldoc DBD::Pg will be enlightening.

Check the pg_putline and pg_endcopy functions.

Cheers,
   Steve

"Kynn Jones" <kynnjo@gmail.com> writes:
> So I'm back at the drawing board.  How can I make fast bulk inserts into a
> PostgreSQL database from within a Perl script?

The simplest and most effective thing you can do is to wrap many inserts
into a single transaction block.  After that, if you're using a driver
that has "real" prepared statement support, setting up a prepared
INSERT statement should help some.

            regards, tom lane

$dbi->do("COPY TABLE FROM stdin;");
for(;;) {
    $dbi->func( "$idd\t$tid\n", 'putline');
}
$dbi->func("\\.\n", 'putline');
$dbi->func('endcopy');

I don't known what about modern versions of DBI and DBD::Pg,
but it worked at 2001 year :)

Kynn Jones wrote:
> I have a Perl script that is supposed to make a large number of inserts
> in a PostgreSQL database.  Performing individual inserts with SQL's
> INSERT command is too slow, however, I can use a "COPY ... from stdin"
> approach that is fast enough.  Basically, I fork a psql process (yes,
> I'm on unix) and have the parent write the new records into the child,
> as part of a COPY from stdin command.  Admittedly, this is very
> convoluted, but it works.
>
> Well, it almost works.  It did in my preliminary tests, but failed in
> the real run with the actual database because the owner of the tables
> that get modified by this COPY statement is a user for which the psql
> command requires a password ( i.e. this user exists solely for the
> purpose of owning the database in question; it does not have a UID, nor
> disk space assigned to it), and I don't know how to give this password
> programmatically.
>
> So I'm back at the drawing board.  How can I make fast bulk inserts into
> a PostgreSQL database from within a Perl script?
>
> Thanks!
>
> kj
>

--
Teodor Sigaev                                   E-mail: teodor@sigaev.ru
                                                    WWW: http://www.sigaev.ru/