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/

Thank you all for cluing me in on pg_putline and pg_endcopy. Much cleaner
than my kluge.

kj