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  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@postgresql.org)
> 

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