Thread: Temparary disable constraint
Hi,<br /><br /> Is there a way to temporary disable a constraint (without drop and recreating it) in postgresql?<br /><br/> Thanks _______________________________________________<br />Ce message et les �ventuels documents joints peuventcontenir des informations confidentielles.<br />Au cas o� il ne vous serait pas destin�, nous vous remercions de bienvouloir le supprimer et en aviser imm�diatement l'exp�diteur. Toute utilisation de ce message non conforme � sa destination,toute diffusion ou publication, totale ou partielle et quel qu'en soit le moyen est formellement interdite.<br/>Les communications sur internet n'�tant pas s�curis�es, l'int�grit� de ce message n'est pas assur�e et lasoci�t� �mettrice ne peut �tre tenue pour responsable de son contenu.
Hubert FONGARNAND wrote: > Hi, > > Is there a way to temporary disable a constraint (without drop and > recreating it) in postgresql? Sure, try ALTER TABLE ... DISABLE TRIGGER for the constraint trigger name. -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Ok it works, but we have to write a plpgsql function that takes the table_name and the constraint name in parameters....<br/><br /> It could be useful to have a : ALTER TABLE ... DISABLE CONSTRAINT ...<br /> (as oracle does)<br/><br /> it could be good to add this to the TODO LIST...<br /><br /> Thanks<br /><br /><br /><br /> Le mardi 16janvier 2007 à 22:02 -0500, Bruce Momjian a écrit : <blockquote type="CITE"><pre> <font color="#000000">Hubert FONGARNAND wrote:</font> <font color="#000000">> Hi,</font> <font color="#000000">> </font> <font color="#000000">> Is there a way to temporary disable a constraint (without drop and</font> <font color="#000000">> recreating it) in postgresql?</font> <font color="#000000">Sure, try ALTER TABLE ... DISABLE TRIGGER for the constraint trigger</font> <font color="#000000">name.</font> </pre></blockquote> _______________________________________________<br />Ce message et les �ventuels documents joints peuventcontenir des informations confidentielles.<br />Au cas o� il ne vous serait pas destin�, nous vous remercions de bienvouloir le supprimer et en aviser imm�diatement l'exp�diteur. Toute utilisation de ce message non conforme � sa destination,toute diffusion ou publication, totale ou partielle et quel qu'en soit le moyen est formellement interdite.<br/>Les communications sur internet n'�tant pas s�curis�es, l'int�grit� de ce message n'est pas assur�e et lasoci�t� �mettrice ne peut �tre tenue pour responsable de son contenu.
Hi,
Maybe added more further things to TODO list. Enabled / disabled other objects like view/funtion. imagine a lot of views that referances a table and i wanna drop a column on this table that used by these views. Postgres doesnt allow this. First i must drop these views then drop the column on that table and then recreate these views.
Can this be resolved (like oracle does) ?
Best regards
Adnan DURSUN
ASRIN Bilişim Ltd.
ASRIN Bilişim Ltd.
----- Original Message -----From: Hubert FONGARNANDTo: Bruce MomjianSent: Wednesday, January 17, 2007 10:36 AMSubject: Re: [HACKERS] Temparary disable constraintOk it works, but we have to write a plpgsql function that takes the table_name and the constraint name in parameters....
It could be useful to have a : ALTER TABLE ... DISABLE CONSTRAINT ...
(as oracle does)
it could be good to add this to the TODO LIST...
Thanks
Le mardi 16 janvier 2007 à 22:02 -0500, Bruce Momjian a écrit :_______________________________________________Hubert FONGARNAND wrote: > Hi, > > Is there a way to temporary disable a constraint (without drop and > recreating it) in postgresql? Sure, try ALTER TABLE ... DISABLE TRIGGER for the constraint trigger name.
Ce message et les 鶥ntuels documents joints peuvent contenir des informations confidentielles.
Au cas o?ne vous serait pas destin鬠nous vous remercions de bien vouloir le supprimer et en aviser imm餩atement l'exp餩teur. Toute utilisation de ce message non conforme ࠳a destination, toute diffusion ou publication, totale ou partielle et quel qu'en soit le moyen est formellement interdite.
Les communications sur internet n'鴡nt pas s飵ris饳, l'int駲it頤e ce message n'est pas assur饠et la soci鴩 魥ttrice ne peut 괲e tenue pour responsable de son contenu.
Hubert FONGARNAND wrote: > Ok it works, but we have to write a plpgsql function that takes the > table_name and the constraint name in parameters.... > > It could be useful to have a : ALTER TABLE ... DISABLE CONSTRAINT ... > (as oracle does) > > it could be good to add this to the TODO LIST... Well, we currently only allow disabling foreign key constraints (those that have triggers). There is no way to disable a UNIQUE or NOT NULL constraint, for example, so I don't see how we can do DISABLE CONSTRAINT cleanly. --------------------------------------------------------------------------- > > Thanks > > > > Le mardi 16 janvier 2007 ? 22:02 -0500, Bruce Momjian a ?crit : > > > Hubert FONGARNAND wrote: > > > Hi, > > > > > > Is there a way to temporary disable a constraint (without drop and > > > recreating it) in postgresql? > > > > Sure, try ALTER TABLE ... DISABLE TRIGGER for the constraint trigger > > name. > > > _______________________________________________ > Ce message et les ?entuels documents joints peuvent contenir des informations confidentielles. > Au cas o?il ne vous serait pas destin? nous vous remercions de bien vouloir le supprimer et en aviser imm?iatement l'exp?iteur.Toute utilisation de ce message non conforme ?sa destination, toute diffusion ou publication, totale ou partielleet quel qu'en soit le moyen est formellement interdite. > Les communications sur internet n'?ant pas s?uris?s, l'int?rit?de ce message n'est pas assur? et la soci???ettrice ne peut?re tenue pour responsable de son contenu. -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Adnan DURSUN wrote: > Hi, Maybe added more further things to TODO list. Enabled / > disabled other objects like view/funtion. imagine a lot of > views that referances a table and i wanna drop a column on this > table that used by these views. Postgres doesnt allow this. > First i must drop these views then drop the column on that > table and then recreate these views. Can this be resolved > (like oracle does) ? Not easily, because the view are bound to the object id of the tables involved. -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Maybe you are right as technically but this behaver causes a lot of maintance problem on a database that alot of view and functions that depends on a table or a type. I think objects has a property if that object is enable or not. We hope this problem can be resolved at 8.4 release. Best Regards Adnan DURSUN ASRIN Bilisim Ltd. ----- Original Message ----- From: "Bruce Momjian" <bruce@momjian.us> To: "Adnan DURSUN" <a_dursun@hotmail.com> Cc: <pgsql-hackers@postgresql.org> Sent: Wednesday, January 17, 2007 5:49 PM Subject: Re: [HACKERS] Temparary disable constraint > Adnan DURSUN wrote: >> Hi, Maybe added more further things to TODO list. Enabled / >> disabled other objects like view/funtion. imagine a lot of >> views that referances a table and i wanna drop a column on this >> table that used by these views. Postgres doesnt allow this. >> First i must drop these views then drop the column on that >> table and then recreate these views. Can this be resolved >> (like oracle does) ? > > Not easily, because the view are bound to the object id of the tables > involved. > > -- > Bruce Momjian bruce@momjian.us > EnterpriseDB http://www.enterprisedb.com > > + If your life is a hard drive, Christ can be your backup. + > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >
Bruce Momjian wrote: > Adnan DURSUN wrote: >> Hi, Maybe added more further things to TODO list. Enabled / >> disabled other objects like view/funtion. imagine a lot of >> views that referances a table and i wanna drop a column on this >> table that used by these views. Postgres doesnt allow this. >> First i must drop these views then drop the column on that >> table and then recreate these views. Can this be resolved >> (like oracle does) ? > > Not easily, because the view are bound to the object id of the tables > involved. The trick would be I think to bind them to individual columns, so if view V doesn't mention column C then dropping C has no effect on it. That's a lot more dependencies to track of course. -- Richard Huxton Archonet Ltd
----- Original Message ----- From: "Richard Huxton" <dev@archonet.com> To: "Bruce Momjian" <bruce@momjian.us> Cc: "Adnan DURSUN" <a_dursun@hotmail.com>; <pgsql-hackers@postgresql.org> Sent: Thursday, January 18, 2007 5:57 PM Subject: Re: [HACKERS] Temparary disable constraint >> Not easily, because the view are bound to the object id of the tables >> involved. > > The trick would be I think to bind them to individual columns, so if view > V doesn't mention column C then dropping C has no effect on it. > > That's a lot more dependencies to track of course. > Is that not possible that all objects have a column that says whether object state is ok or not. When any session wants to try to execute any DML on object, then the Postgres checks that state column. If object state is not ok then Postgres raises an exception. Best Regards Adnan DURSUN ASRIN Bilisim Ltd.