Re: psql vs perl prepared inserts

On Apr 13, 2005, at 9:57 AM, Matt Van Mater wrote:

> Thanks to all who replied. Thanks for the tip on that last thread
> Tom, I don't know how I missed it. I have a hunch that it's not
> applicable to me at this time because I'm running a year and a half
> old software (included in OpenBSD 3.4), but I will have to check which
> version of DBD::Pg was installed.
>
>> The intent of prepared statements is to reduce the overhead of running
>> the parser, rewriter and planner multiple times for a statement that
>> is
>> executed multiple times. For an INSERT query without any sub-selects
>> that is not rewritten by any rules, the cost to parse, rewrite and
>> plan
>> the statement is trivial. So I wouldn't expect prepared statements to
>> be
>> a big win -- you would gain a lot more from batching multiple inserts
>> into a single transaction, and more still from using COPY.
>
> I was thinking something along the same lines, and was considering
> using the COPY statement as my next step, but as someone mentioned
> then I have to load it into a temporary database and then do some more
> internal magic to preserve referential integrity, unique contraints,
> etc. For that reason I was hoping to keep it in perl, and it's always
> nice to keep everything in a single neat portable package.

You can use pl/perl stored functions to do exactly what you do in perl
on the client side, but it happens much faster since the data is
already on the server. Then, your neat little portable package IS the
database.

Sean