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

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@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
>
> ---------------------------(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