Lists: | pgsql-general |
---|
From: | Emi Lu <emilu(at)encs(dot)concordia(dot)ca> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Temporally disabled foreign key constraint check? |
Date: | 2011-10-21 15:03:09 |
Message-ID: | 4EA189AD.50102@encs.concordia.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Good morning,
Is there a way to temporally disabled foreign key constraints something
like:
SET FOREIGN_KEY_CHECKS=0
When population is done, will set FOREIGN_KEY_CHECKS=1
Thanks a lot!
Emi
--
Emi Lu, ENCS, Concordia University, Montreal H3G 1M8
emilu(at)encs(dot)concordia(dot)ca +1 514 848-2424 x5884
From: | raghu ram <raghuchennuru(at)gmail(dot)com> |
---|---|
To: | emilu(at)encs(dot)concordia(dot)ca |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Temporally disabled foreign key constraint check? |
Date: | 2011-10-21 15:12:56 |
Message-ID: | CALnrrJS9Lv-6fa5Z43CJcaoQJ6ybUMuCiDatTpCnRnpHckBadA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Fri, Oct 21, 2011 at 8:33 PM, Emi Lu <emilu(at)encs(dot)concordia(dot)ca> wrote:
> Good morning,
>
>
> Is there a way to temporally disabled foreign key constraints something
> like:
>
> SET FOREIGN_KEY_CHECKS=0
>
> When population is done, will set FOREIGN_KEY_CHECKS=1
>
>
You can disable *triggers* on a table (which will disable all the FK
constraints, but not things like 'not nul' or 'unique').
For Disable:
update pg_class set reltriggers=0 where relname = 'TEST';
For Enable:
update pg_class set reltriggers = count(*) from pg_trigger where
pg_class.oid=tgrelid and relname='TEST';
--Raghu
From: | Andreas Kretschmer <akretschmer(at)spamfence(dot)net> |
---|---|
To: | raghu ram <raghuchennuru(at)gmail(dot)com>, emilu(at)encs(dot)concordia(dot)ca |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Temporally disabled foreign key constraint check? |
Date: | 2011-10-21 15:22:31 |
Message-ID: | 98541615.95.1319210554250.JavaMail.open-xchange@oxweb01.ims-firmen.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
raghu ram <raghuchennuru(at)gmail(dot)com> hat am 21. Oktober 2011 um 17:12
geschrieben:
>
>
> On Fri, Oct 21, 2011 at 8:33 PM, Emi Lu<emilu(at)encs(dot)concordia(dot)ca>wrote:
>
> > Good morning,
> >
> >
> > Is there a way to temporally disabled foreign key constraints something
> > like:
> >
> > SET FOREIGN_KEY_CHECKS=0
> >
> > When population is done, will set FOREIGN_KEY_CHECKS=1
> >
> >
You can disable *triggers* on a table (which will disable all the FK
constraints, but not things like 'not nul' or 'unique').
> For Disable:
> update pg_class set reltriggers=0 where relname = 'TEST';
> For Enable:
> update pg_class set reltriggers = count(*) from pg_trigger where
> pg_class.oid=tgrelid and relname='TEST';
>
>
No, don't manipulate pg_* - tables. Use instead ALTER TABLE ... DISABLE TRIGGER
...
Regards, Andreas
>
> --Raghu
>
>
From: | Emi Lu <emilu(at)encs(dot)concordia(dot)ca> |
---|---|
To: | raghu ram <raghuchennuru(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Temporally disabled foreign key constraint check? |
Date: | 2011-10-21 15:49:31 |
Message-ID: | 4EA1948B.1020802@encs.concordia.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Thank you first.
I believe that upate pg_class can only be done by superuser, right?
Besides, if I need the whole schema's foreign keys to be disabled and
then enabled later.
Is there a simple command could do it? Similar to mysql's "set
FOREIGN_KEY_CHECKS = false/true"?
Emi
On 10/21/2011 11:12 AM, raghu ram wrote:
>
>
> On Fri, Oct 21, 2011 at 8:33 PM, Emi Lu <emilu(at)encs(dot)concordia(dot)ca
> <mailto:emilu(at)encs(dot)concordia(dot)ca>> wrote:
>
> Good morning,
>
>
> Is there a way to temporally disabled foreign key constraints
> something like:
>
> SET FOREIGN_KEY_CHECKS=0
>
> When population is done, will set FOREIGN_KEY_CHECKS=1
>
>
> You can disable *triggers* on a table (which will disable all the FK
> constraints, but not things like 'not nul' or 'unique').
>
> For Disable:
>
> update pg_class set reltriggers=0 where relname = 'TEST';
>
> For Enable:
>
> update pg_class set reltriggers = count(*) from pg_trigger where
> pg_class.oid=tgrelid and relname='TEST';
>
> --Raghu
--
Emi Lu, ENCS, Concordia University, Montreal H3G 1M8
emilu(at)encs(dot)concordia(dot)ca +1 514 848-2424 x5884
From: | raghu ram <raghuchennuru(at)gmail(dot)com> |
---|---|
To: | emilu(at)encs(dot)concordia(dot)ca |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Temporally disabled foreign key constraint check? |
Date: | 2011-10-22 21:49:55 |
Message-ID: | CALnrrJRmFSdee1p8Km8-2cCucSVvk8kzfEMVNBmgyHVuhCusdg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Fri, Oct 21, 2011 at 9:19 PM, Emi Lu <emilu(at)encs(dot)concordia(dot)ca> wrote:
> Thank you first.
>
> I believe that upate pg_class can only be done by superuser, right?
>
Yes,it's requires superuser privileges.
>
> Besides, if I need the whole schema's foreign keys to be disabled and then
> enabled later.
>
> Is there a simple command could do it? Similar to mysql's "set
> FOREIGN_KEY_CHECKS = false/true"?
>
>
No. The main goal for PG is to secure data integrity, and you can't switch
off this feature.
Disable the constraints:
alter table <tablename> disable trigger all;
You can use this command inside your transaction too, but don't forget to
reactivate the triggers because this change will become global after
the transaction ends!
--Raghu
> On 10/21/2011 11:12 AM, raghu ram wrote:
>
>>
>>
>> On Fri, Oct 21, 2011 at 8:33 PM, Emi Lu <emilu(at)encs(dot)concordia(dot)ca
>> <mailto:emilu(at)encs(dot)concordia(dot)**ca <emilu(at)encs(dot)concordia(dot)ca>>> wrote:
>>
>> Good morning,
>>
>>
>> Is there a way to temporally disabled foreign key constraints
>> something like:
>>
>> SET FOREIGN_KEY_CHECKS=0
>>
>> When population is done, will set FOREIGN_KEY_CHECKS=1
>>
>>
>> You can disable *triggers* on a table (which will disable all the FK
>> constraints, but not things like 'not nul' or 'unique').
>>
>> For Disable:
>>
>> update pg_class set reltriggers=0 where relname = 'TEST';
>>
>> For Enable:
>>
>> update pg_class set reltriggers = count(*) from pg_trigger where
>> pg_class.oid=tgrelid and relname='TEST';
>>
>> --Raghu
>>
>
>
> --
> Emi Lu, ENCS, Concordia University, Montreal H3G 1M8
> emilu(at)encs(dot)concordia(dot)ca +1 514 848-2424 x5884
>