Re: Disabling triggers / constraints

Yes, agreed. I think we decided that super-user-only could disable
trigger on a global basis. I prevent folks from mucking with the system
tables to do it.

---------------------------------------------------------------------------

Jorge Pereira wrote:
> Tom Lane wrote:
>
> >Jorge Pereira <jrp(at)ideiaprima(dot)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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073