Re: PostgreSQL Write Performance

Tim Uckun wrote:
>> Technically you *can* disable triggers, including RI checks, but it's VERY
>> unwise and almost completely defeats the purpose of having the checks. In
>> most such situations you're much better off dropping the constraints then
>> adding them again at the end of the load.
>
>
> I know that the SQL server bulk loader defaults to not firing the
> triggers and I was hoping there would be an option on the COPY command
> to accomplish the same thing.
>
> pg_dump has a --disable-triggers option too.

pg_restore? Yes, it does. However, it knows that the data you're loading
came from a PostgreSQL database where those triggers have already fired
when the data was originally inserted. It can trust that the data is OK.

I don't personally think that COPY should make it easy to disable
triggers. You can do it if you want to (see the manual for how) but in
almost all cases its much wiser to drop triggers and constraints instead.

Rather than disabling RI constraints, it'd be desirable for COPY to have
an option that *deferred* RI constraint checking, then re-checked the
constraints for all rows at once and rolled back the COPY if any failed.
That'd be a lot faster for many uses, but (importantly) would preserve
referential integrity.

While COPY doesn't offer an easy way to do that, you can emulate the
behavior by:

- Beginning a transaction
- Disabling RI constraints
- Running COPY
- Re-enabling RI constraints
- Re-checking the RI constraints and raising an exception to abort the
  transaction if the checks fail.

Unfortunately you can't (yet) do this with deferrable RI constraints,
because Pg isn't clever enough to notice when large numbers of
individual checks have built up and merge them into a single re-check
query that verifies the whole constraint in one pass. So you have to do
that yourself.

> It seems to me that the COPY FROM  should have an option that bypasses
> the triggers as a convience.

I, for one, would loudly and firmly resist the addition of such a
feature. Almost-as-fast options such as intelligent re-checking of
deferred constraints would solve the problem better and much more
safely. If you really want the dangerous way you can already get it,
it's just a bit more work to disable the triggers yourself, and by doing
so you're saying "I understand what I am doing and take responsibility
for the dangers inherent in doing so".

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.

--
Craig Ringer