[Chicago-talk] Re: postgres and COPY statements

[Jim Thomason]

What an ever-lovin' pain in the ass. But I finally
tracked it down and figured it out.

After a lot of searching, tons of testing,
iterations,ideas, dead-ends, etc., I managed to track
down a page off of google groups in Japanese (I think,
I'm not exactly up on my asian languages).

Fortunately, perl is perl, and it was still readable.
It also referenced a little function I'd never seen
before that proved to be the solution.

$dbh->func('endcopy');

So the full thing for me becomes:

$copy_into_student_answers->execute();
foreach (@insert) {
  my $ret = $dbh->func($_, 'putline');
  print "INSERT ($ret) $_";
};

my $r = $dbh->func("\\.\n", 'putline'); 
$dbh->func('endcopy');

And it runs like a dream. At this point, it didn't
shave a lot of runtime off of my test file (since I'm
caching other values like mad), but it still brought
the time down from 1m30s to 1m10s and it should have a
bigger effect on files that require more database
reads.

that 'endcopy' does exist in DBD::Pg (in Pg.xs,
large_obhect.h and large_object.c) but is utterly
undocumented anywhere in DBD::Pg. Doesn't appear to be
part of DBI, either, so it's not there.

What a pain in the ass. An utterly vital component,
and no reference to it whatsoever. It's incidents like
this that turn people off to open source.

Document your code!

-Jim.....

__________________________________
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/