Re: PostgreSQL Write Performance

On Wed, 2010-01-06 at 15:30 +1300, Tim Uckun wrote:
> > I, for one, would loudly and firmly resist the addition of such a
> > feature. Almost-as-fast options such as intelligent re-checking of
>
> Even if it was not the default behavior?
>
> >
> > If you really want to do that, look at the manual for how to disable
> > triggers, but understand that you are throwing away the database's data
> > integrity protection by doing it.
> >
>
> I guess it's a matter of philosophy. I kind of think as the DBA I
> should be the final authority in determining what is right and wrong.
> It's my data after all. Yes I would expect pg to perform every check I
> specify and execute every trigger I write but if I want I should be
> able to bypass those things "just this once".
>
> As you point out I can already do this by manually going through and
> disabling every trigger or even dropping the triggers. Many people
> have said I could drop the constraints and re-set them up. The fact
> that the COPY command does not have a convenient way for me to do this
> doesn't prevent me from "shooting myself in the foot" if I want to.
> It would just be a flag. If you want you can enable it, if you don't
> they no harm no foul.

The reason we don't do this is because COPY can be run concurrently with
queries, which is different to most other load utilities. The only time
it is safe to disable triggers is in a transaction in which we either
create the table or truncate it. That path is already optimised.

We did discuss a version of COPY that locks the table to allow various
performance optimisations, but that prevented running multiple COPYs
concurrently and the loss in performance from doing that was more than
the expected gain from the optimisation, so we didn't bother.

> Anyway this is getting offtopic. I got my question answered. COPY does
> not do this. If I want to do it I have to manually iterate through all
> the triggers and disable them or drop them before running copy.

There is a command to disable all triggers at once.

--
Simon Riggs www.2ndQuadrant.com