From: Jason E. Stewart <jason@openinformatics.com>
To: pgsql-interfaces@postgresql.org
Subject: Re: Frontend/Backend protocol changes?
Date: 22 Nov 2002 09:37:34 -0700
Message-ID: <87of8ha7ch.fsf@openinformatics.com> (raw)
In-Reply-To: <200211221546.gAMFkBi21622@candle.pha.pa.us>
Hey all,
I'm not sure if this is the correct list to ask this question, so I
appologize ahead of time if it is misguided.
I'm using DBD::Pg to insert large amounts of data into a Postgres
installation. We will occassionally (once every few weeks, perhaps)
get new chip layouts that need to be added. Each layout may have up to
250k spots which go into a Spot table.
My current code is glacially slow - on an otherwise zippy
dual-processor P4, it seems this insert will take 3 days.
I've gotten a bit of feedback from the Perl dbi-users list:
1) Transactions: My current approach was to do this inside a
transaction, but apparently the write-ahead-logging will not handle
250k logged inserts well.
Is this true, and I should commit after every 20 or so spots?
2) Indices: apparently every insert updates the indices on the
table. From my reading of the documentation, the indices aren't
updated inside a transaction, but instead at the end.
3) COPY: I could use COPY, but apparently triggers are not, well,
triggered under COPY.
Is this true? I have datestamps on my inserts for audit trails.
Thanks ahead of time for any help,
jas.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: jason@openinformatics.com
Subject: Re: Frontend/Backend protocol changes?
In-Reply-To: <87of8ha7ch.fsf@openinformatics.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox