Re: psql vs perl prepared inserts

On 4/13/05, Matt Van Mater <matt(dot)vanmater(at)gmail(dot)com> wrote:
> > 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.

I believe that COPY handles things like unique constraints and
referer integerity just fine (of the latter I am not sure) -- well,
its hard to imagine PostgreSQL allowing itself to loose integrity
of the data, don't you agree?

A simple test:
qnex=# CREATE TABLE a (a int PRIMARY KEY);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"a_pkey" for table "a"
CREATE TABLEqnex=# COPY a FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1
>> 2
>> 3
>> 1
>> \.
ERROR: duplicate key violates unique constraint "a_pkey"
CONTEXT: COPY a, line 4: "1"

...you may have to rethink your design if there are any RULEs or
TRIGGERs associated with said table. Apart from that, there
should be no problems at the risk that one "bad" row will cancel
whole COPY commands, so if you trust your data, it will be fine. :)

Regards,
Dawid