Re: Disabling triggers / constraints - Mailing list pgsql-patches

Tom Lane wrote:

>Jorge Pereira <jrp@ideiaprima.com> writes:
>
>
>>... decided to add a couple little variables to control disabling constraints and triggers.
>>
>>
>I'm not of the opinion that we actually want any such thing, as it's a
>blatant violation of the fundamental concept of data integrity.
>
>
I can understand your concerns. But for the sake of context for context,
here's an example of the code generated by pg_dump --disable-triggers:

| -- Disable triggers
| UPDATE pg_catalog.pg_class SET reltriggers = 0 WHERE oid =
'table_1'::pg_catalog.regclass;
| -- INSERT / UPDATE statements;
| -- Enable triggers
| UPDATE pg_catalog.pg_class SET reltriggers = (SELECT
pg_catalog.count(*) FROM pg_catalog.pg_trigger where pg_class.oid =
tgrelid) WHERE oid = 'table_1'::pg_catalog.regclass;


What I propose is

| SET disable_triggers=1;
| -- INSERT / UPDATE statements;
| SET disable_triggers=0;

This is not an option for daily use, just something that can be set to
allow large volumes of data that is known to be conforming to be put
into the database. It is critical for datawarehousing operations, where
large volumes of data  (on the TB scale) already processed and validated
need to be put into the database. It is quite useful also for situations
where checks depend on the existence of data in the database. I can put
forward a few examples if it's deemed appropriate. :)
In comparison, most DBs I've experienced with (Oracle, MySQL and argh
MSSQL) have some way of disablling integrity checks and triggers (mainly
for loading large sets of data known to be good).

I don't see a need to do it on a per-table basis, seeing as this is
mostly a per-datablock need - I couldn't think of a situation where
enabling it only on one table would be benefitial, as that would imply
that some of tha data you are inputing might not be conforming - which
in turn means you shouldn't even be using this.
On the other hand, you're absolutely right in that this is clearly
something that should be done only by the database owner. a) would
something similar be considered if such permission check was added (for
owner only)? b) would it be considered only if changeable on a per-table
basis?

I'm new here. :) I hope I don't come across as someone trying to force
his view of things, really just trying to pass on the experience I've
had before, and which led me to the despair of having to go and tweak
code. ;) Good thing of OS that I could.

Cheers
- Jorge Pereira