Thread: ENABLE/DISABLE CONSTRAINT NAME
Hi hackers,
In order to achieve enable/disable constraint name，I made a few
modifications to the code.
First, someone used to build the constraints while building
table. Then inserting data must follow a certain order.
And people usually like to insert the data but not affected by
foreign keys or check.
Second, the check or the foreign key constraint will waste much
time while inserting the data into the table.
Due to the above reasons，I realized this command.
I add a field named 'conenabled' to pg_constraint, identifying
whether a constraint is enable or not;
I enable or disable a foreign key constraint, by enable or
disable the triggers of the foreign key;
Our database will depend on the value of 'conenabled' to use the
check constrint or not;
I think the internal trigger's naming can be changed,and the
function ATExecValidateConstraint can be changed too,
but I think that together we can discuss, to decide what to do.
Now,we can do those:
Syntax:
alter table disable constraint <constraint_name> ;
alter table enable constraint <constraint_name> [ no valid ];
alter table add constraint table_constriant [ disable ];
CREATE TABLE aa
(
a1 INT CHECK(a1>4),
a2 INT
);
ALTER TABLE aa ADD CONSTRAINT aa_a2_check CHECK(a2>10) DISABLE;
INSERT INTO aa VALUES (10,1);
DELETE FROM aa;
ALTER TABLE aa DROP CONSTRAINT aa_a2_check;
ALTER TABLE aa DISABLE CONSTRAINT aa_a1_check;
INSERT INTO aa VALUES (5,2);
UPDATE aa SET a1=2 WHERE a2=2;
INSERT INTO aa VALUES (1,1);
ALTER TABLE aa ENABLE CONSTRAINT aa_a1_check NOT VALID; //don't
validate the data
DELETE FROM aa;
ALTER TABLE aa DISABLE CONSTRAINT aa_a1_check;
ALTER TABLE aa ENABLE CONSTRAINT aa_a1_check; //validate the data
CREATE TABLE bb
(
b1 INT PRIMARY KEY,
b2 INT
);
CREATE TABLE cc
(
c1 INT REFERENCES bb(b1),
c2 INT
);
ALTER TABLE cc DISABLE CONSTRAINT cc_c1_fkey;
INSERT INTO cc VALUES (1,1);
INSERT INTO bb VALUES (2,2);
INSERT INTO cc VALUES (2,2);
UPDATE cc SET c1=1 WHERE c2=2;
ALTER TABLE cc ENABLE CONSTRAINT cc_c1_fkey NOT VALID; //don't
validate the data
ALTER TABLE cc DISABLE CONSTRAINT cc_c1_fkey;
DELETE FROM cc;
ALTER TABLE cc ENABLE CONSTRAINT cc_c1_fkey; //validate the data
I packed a patch about this modification.This is my first time to send
the patch,
I hope you give me some advice.
Best Regards!
Yours,
Wang Shuo
HighGo Software Co.,Ltd.
August 26, 2013
Attachment
On Fri, 2013-08-30 at 09:57 +0800, wangshuo@highgo.com.cn wrote: > Hi hackers, > > In order to achieve enable/disable constraint name，I made a few > modifications to the code. > > First, someone used to build the constraints while building > table. Then inserting data must follow a certain order. > And people usually like to insert the data but not affected by > foreign keys or check. Is there any semantic difference between marking a constraint as DISABLED and simply dropping it? Or does it just make it easier to re-add it later? Regards,Jeff Davis
Jeff Davis-8 wrote > Is there any semantic difference between marking a constraint as > DISABLED and simply dropping it? Or does it just make it easier to > re-add it later? I cannot answer the question but if there is none then the main concern I'd have is capturing "meta-information" about WHY such a constraint has been disabled instead of dropped. I guess this whole feature extends from the trigger disable feature that already exists. Given we have the one adding this seems symmetrical... I cannot really see using either feature on a production system (if following best practices) but I can imagine where they could both be helpful during development. Note with this usage pattern the meta-information about "why" becomes considerably less important. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/ENABLE-DISABLE-CONSTRAINT-NAME-tp5769136p5769337.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
于 2013-09-03 08:15, David Johnston 回复:
> Jeff Davis-8 wrote
>> Is there any semantic difference between marking a constraint as
>> DISABLED and simply dropping it? Or does it just make it easier to
>> re-add it later?
>
David Johnston wrote:
> I cannot answer the question but if there is none then the main
> concern I'd
> have is capturing "meta-information" about WHY such a constraint has
> been
> disabled instead of dropped.
Drop/build and disable/enable constraint has no fundamental difference,
and could achieve the same purpose.What I do also more convenient for
the user.
Recording the disabled constraints is easier than recoding all the
constrains.
What's more, a lot of people ever asked about turing off constraint and
The sql2008 support this.So I think it's necessary in some ways.
> I guess this whole feature extends from the trigger disable feature
> that
> already exists. Given we have the one adding this seems
> symmetrical...
>
> I cannot really see using either feature on a production system (if
> following best practices) but I can imagine where they could both be
> helpful
> during development. Note with this usage pattern the
> meta-information about
> "why" becomes considerably less important.
>
> David J.
Wang Shuo HighGo Software Co.,Ltd. September 3, 2013
On Tue, Sep 3, 2013 at 3:13 AM, <wangshuo@highgo.com.cn> wrote: > 于 2013-09-03 08:15, David Johnston 回复: > >> Jeff Davis-8 wrote >>> >>> Is there any semantic difference between marking a constraint as >>> DISABLED and simply dropping it? Or does it just make it easier to >>> re-add it later? >> >> > David Johnston wrote: >> >> I cannot answer the question but if there is none then the main concern >> I'd >> have is capturing "meta-information" about WHY such a constraint has been >> disabled instead of dropped. > > > Drop/build and disable/enable constraint has no fundamental difference, > and could achieve the same purpose.What I do also more convenient for the > user. > Recording the disabled constraints is easier than recoding all the > constrains. > What's more, a lot of people ever asked about turing off constraint and > The sql2008 support this.So I think it's necessary in some ways. Please add your patch to the upcoming CommitFest so we don't forget about it. https://commitfest.postgresql.org/action/commitfest_view/open Please see also https://wiki.postgresql.org/wiki/Submitting_a_Patch -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
于 2013-09-05 01:56, Robert Haas 回复: > On Tue, Sep 3, 2013 at 3:13 AM, <wangshuo@highgo.com.cn> wrote: >> 于 2013-09-03 08:15, David Johnston 回复: >> >>> Jeff Davis-8 wrote >>>> >>>> Is there any semantic difference between marking a constraint as >>>> DISABLED and simply dropping it? Or does it just make it easier to >>>> re-add it later? >>> >>> >> David Johnston wrote: >>> >>> I cannot answer the question but if there is none then the main >>> concern >>> I'd >>> have is capturing "meta-information" about WHY such a constraint >>> has been >>> disabled instead of dropped. >> >> >> Drop/build and disable/enable constraint has no fundamental >> difference, >> and could achieve the same purpose.What I do also more convenient >> for the >> user. >> Recording the disabled constraints is easier than recoding all the >> constrains. >> What's more, a lot of people ever asked about turing off constraint >> and >> The sql2008 support this.So I think it's necessary in some ways. > > Please add your patch to the upcoming CommitFest so we don't forget > about it. > > https://commitfest.postgresql.org/action/commitfest_view/open > > Please see also https://wiki.postgresql.org/wiki/Submitting_a_Patch > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company I had committed the patch to the Server Features (https://commitfest.postgresql.org/action/commitfest_view/open). Is this right ? If not, please give me more advice,thanks ! Wang Shuo HighGo Software Co.,Ltd. September 5, 2013
On Thu, Sep 5, 2013 at 12:27 PM, <wangshuo@highgo.com.cn> wrote: > I had committed the patch to the Server Features > (https://commitfest.postgresql.org/action/commitfest_view/open). > Is this right ? If not, please give me more advice,thanks ! Yes this category is fine don't worry. -- Michael
On 9/3/13 3:13 AM, wangshuo@highgo.com.cn wrote: > Drop/build and disable/enable constraint has no fundamental difference, > and could achieve the same purpose.What I do also more convenient for > the user. > Recording the disabled constraints is easier than recoding all the > constrains. Note that other schema objects can depend on the existence of constraints. For example, the validity of a view might depend on the existence of a primary key constraint. What would you do with the view if the primary key constraint is temporarily disabled? > What's more, a lot of people ever asked about turing off constraint and > The sql2008 support this.So I think it's necessary in some ways. I don't see this in the SQL standard. There is [NOT] ENFORCED, but that's something different. Implementing that instead might actually address the above concern.
于 2013-09-09 20:54, Peter Eisentraut 回复:
> On 9/3/13 3:13 AM, wangshuo@highgo.com.cn wrote:
>> Drop/build and disable/enable constraint has no fundamental
>> difference,
>> and could achieve the same purpose.What I do also more convenient
>> for
>> the user.
>> Recording the disabled constraints is easier than recoding all the
>> constrains.
>
Peter Eisentraut wrote:
> Note that other schema objects can depend on the existence of
> constraints. For example, the validity of a view might depend on the
> existence of a primary key constraint. What would you do with the
> view
> if the primary key constraint is temporarily disabled?
>
Thanks for your reply. I could't clearly understand your opinion, could you give me more
information or example？
>> What's more, a lot of people ever asked about turing off constraint
>> and
>> The sql2008 support this.So I think it's necessary in some ways.
>
> I don't see this in the SQL standard. There is [NOT] ENFORCED, but
> that's something different. Implementing that instead might actually
> address the above concern.
You are right. I had checked the SQL standard. There is not
ENABLE/DISABLE.
Sorry.
I misunderstood the former discussion about the constraint and the SQL
standard.
Thanks ,again.
Wang Shuo HighGo Software Co.,Ltd. September 11, 2013
On 9/11/13 1:09 AM, wangshuo@highgo.com.cn wrote: > Peter Eisentraut wrote: >> Note that other schema objects can depend on the existence of >> constraints. For example, the validity of a view might depend on the >> existence of a primary key constraint. What would you do with the view >> if the primary key constraint is temporarily disabled? >> > > Thanks for your reply. > I could't clearly understand your opinion, could you give me more > information or example？ => create table test1 (a int constraint pk primary key, b text); => create view test2 as select a, b from test1 group by a; => alter table test1 drop constraint pk; ERROR: 2BP01: cannot drop constraint pk on table test1 because other objects depend on it DETAIL: view test2 depends on constraint pk on table test1 HINT: Use DROP ... CASCADE to drop the dependent objects too. (This has to do with whether ungrouped columns are allowed in the select list when the presence of constraints ensures well-defined results.) When trying to drop the constraint, the choice is to abort the drop or to drop dependent objects. When you are talking about enabling/disabling the constraint, it's not clear what to do.
On 09/13/2013 05:23, Peter Eisentraut wrote:
> => create table test1 (a int constraint pk primary key, b text);
> => create view test2 as select a, b from test1 group by a;
> => alter table test1 drop constraint pk;
> ERROR: 2BP01: cannot drop constraint pk on table test1 because other
> objects depend on it
> DETAIL: view test2 depends on constraint pk on table test1
> HINT: Use DROP ... CASCADE to drop the dependent objects too.
>
> (This has to do with whether ungrouped columns are allowed in the
> select
> list when the presence of constraints ensures well-defined results.)
>
> When trying to drop the constraint, the choice is to abort the drop
> or
> to drop dependent objects. When you are talking about
> enabling/disabling the constraint, it's not clear what to do.
Thanks for your reply.
First, I had said that I I only made a few modifications to the check
and the
foreign key constraint, and did nothing with primary key constraint.
On 08/30/2013 02:03 PM, I wrote:
>Due to the above reasons，I realized this command.
>
>I add a field named 'conenabled' to pg_constraint, identifying whether
> a constraint is enable or not;
>I enable or disable a foreign key constraint, by enable or disable the
> triggers of the foreign key;
>Our database will depend on the value of 'conenabled' to use the check
> constrint or not;
In the alter_table.sgml, I wrote:
>This form enables or disables a foreign key or check constraint.
Second, I tested the check and the foreign key constraint as your test
above.
And no error found, as fellow:
postgres=# create table a1 (a1 int check(a1>4));
CREATE TABLE
postgres=# create view a11 as select * from a1;
CREATE VIEW
postgres=# alter table a1 disable constraint a1_a1_check;
ALTER TABLE
postgres=# insert into a1 values (3);
INSERT 0 1
postgres=# select * from a11; a1
---- 3
(1 row)
postgres=# alter table a1 drop constraint a1_a1_check;
ALTER TABLE
postgres=# create table bb(b1 int primary key);
CREATE TABLE
postgres=# create table cc(c1 int references bb(b1));
CREATE TABLE
postgres=# create view c11 as select * from cc;
CREATE VIEW
postgres=# alter table cc disable constraint cc_c1_fkey;
ALTER TABLE
postgres=# insert into cc values (1);
INSERT 0 1
postgres=# select * from c11; c1
---- 1
(1 row)
postgres=# alter table cc drop constraint cc_c1_fkey;
ALTER TABLE
Wang Shuo HighGo Software Co.,Ltd. September 13, 2013
On Thu, Sep 12, 2013 at 10:03 PM, <wangshuo@highgo.com.cn> wrote: > Second, I tested the check and the foreign key constraint as your test > above. > And no error found, as fellow: You're missing the point. Peter wasn't worried that your patch throws an error; he's concerned about the fact that it doesn't. In PostgreSQL, you can only create the following view because test1 has a primary key over column a: => create table test1 (a int constraint pk primary key, b text); => create view test2 as select a, b from test1 group by a; => alter table test1 drop constraint pk; The reason that, if the primary key weren't there, it would be ambiguous which row should be returned as among multiple values where a is equal and b is not. If you can disable the constraint, then you can create precisely that problem. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
--On 13. September 2013 20:17:19 -0400 Robert Haas <robertmhaas@gmail.com> wrote: > You're missing the point. Peter wasn't worried that your patch throws > an error; he's concerned about the fact that it doesn't. > > In PostgreSQL, you can only create the following view because test1 > has a primary key over column a: > > => create table test1 (a int constraint pk primary key, b text); > => create view test2 as select a, b from test1 group by a; > => alter table test1 drop constraint pk; > > The reason that, if the primary key weren't there, it would be > ambiguous which row should be returned as among multiple values where > a is equal and b is not. If you can disable the constraint, then you > can create precisely that problem. Hmm not sure i understand this argument either: this patch doesn't allow disabling a primary key. It only supports FKs and CHECK constraints explicitly. -- Thanks Bernd
On Tue, Sep 24, 2013 at 5:58 AM, Bernd Helmle <mailings@oopsware.de> wrote: > --On 13. September 2013 20:17:19 -0400 Robert Haas <robertmhaas@gmail.com> > wrote: >> You're missing the point. Peter wasn't worried that your patch throws >> an error; he's concerned about the fact that it doesn't. >> >> In PostgreSQL, you can only create the following view because test1 >> has a primary key over column a: >> >> => create table test1 (a int constraint pk primary key, b text); >> => create view test2 as select a, b from test1 group by a; >> => alter table test1 drop constraint pk; >> >> The reason that, if the primary key weren't there, it would be >> ambiguous which row should be returned as among multiple values where >> a is equal and b is not. If you can disable the constraint, then you >> can create precisely that problem. > > Hmm not sure i understand this argument either: this patch doesn't allow > disabling a primary key. It only supports FKs and CHECK constraints > explicitly. Well, that is certainly one way of skating around the specific concern Peter raised. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Tue, 2013-09-24 at 11:58 +0200, Bernd Helmle wrote: > Hmm not sure i understand this argument either: this patch doesn't > allow disabling a primary key. It only supports FKs and CHECK > constraints explicitly. Well, as soon as the patch for cataloging not-null constraints as check constraints is available, it will be possible to create views that depend functionally on check constraints. Then you'll have the same problem there. It's also not clear why this patch only supports foreign keys and check constraints. Maybe that's what was convenient to implement, but it's not a principled solution to the general issue that constraints can be involved in dependencies.
On Tue, Sep 24, 2013 at 10:40 PM, Peter Eisentraut <peter_e@gmx.net> wrote: > On Tue, 2013-09-24 at 11:58 +0200, Bernd Helmle wrote: >> Hmm not sure i understand this argument either: this patch doesn't >> allow disabling a primary key. It only supports FKs and CHECK >> constraints explicitly. > > Well, as soon as the patch for cataloging not-null constraints as check > constraints is available, it will be possible to create views that > depend functionally on check constraints. Then you'll have the same > problem there. > > It's also not clear why this patch only supports foreign keys and check > constraints. Maybe that's what was convenient to implement, but it's > not a principled solution to the general issue that constraints can be > involved in dependencies. I agree with these concerns, as well as those raised by Tom Lane and Fabien COELHO, and I think they indicate that we shouldn't accept this patch. So I'm marking this as Rejected. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 10/9/13 1:10 PM, Robert Haas wrote: > On Tue, Sep 24, 2013 at 10:40 PM, Peter Eisentraut <peter_e@gmx.net> wrote: >> On Tue, 2013-09-24 at 11:58 +0200, Bernd Helmle wrote: >>> Hmm not sure i understand this argument either: this patch doesn't >>> allow disabling a primary key. It only supports FKs and CHECK >>> constraints explicitly. >> >> Well, as soon as the patch for cataloging not-null constraints as check >> constraints is available, it will be possible to create views that >> depend functionally on check constraints. Then you'll have the same >> problem there. >> >> It's also not clear why this patch only supports foreign keys and check >> constraints. Maybe that's what was convenient to implement, but it's >> not a principled solution to the general issue that constraints can be >> involved in dependencies. > > I agree with these concerns, as well as those raised by Tom Lane and > Fabien COELHO, and I think they indicate that we shouldn't accept this > patch. So I'm marking this as Rejected. I see a use case for disabling FKs and CHECKS but not PKs or UNIQUE constraints: FKs and CHECKS don't depend on additionalstate information (namely an index), so it's easy to just disable them temporarily and then re-enable them. Thesame isn't true about a PK or UNIQUE constraint. Of course we could decide to do something more complex to handle disabling PK/UNIQUE... though at that point it'd be betterto just allow temporarily disabling any index. But I think there's an argument to be made for that being beyond thescope of disabling "simple" constraints... it's a pretty high bar to set that we won't accept a patch that disables simpleconstraints but not those involving indexes. -- Jim C. Nasby, Data Architect jim@nasby.net 512.569.9461 (cell) http://jim.nasby.net
On 2013-10-10 02:10, Robert Haas wrote:
> I agree with these concerns, as well as those raised by Tom Lane and
> Fabien COELHO, and I think they indicate that we shouldn't accept
> this
> patch. So I'm marking this as Rejected.
On 2013-10-11 06:48, Jim Nasby wrote:
>I see a use case for disabling FKs and CHECKS but not PKs or UNIQUE
> constraints: FKs and CHECKS don't depend on additional state
> information (namely an index), so >it's easy to just disable them
> temporarily and then re-enable them. The same isn't true about a PK or
> UNIQUE constraint.
>
>Of course we could decide to do something more complex to handle
> disabling PK/UNIQUE... though at that point it'd be better to just
> allow temporarily disabling >any index. But I think there's an argument
> to be made for that being beyond the scope of disabling "simple"
> constraints... it's a pretty high bar to set that we ?>won't accept a
> patch that disables simple constraints but not those involving indexes.
Thanks for your reply.
I found my patch's weakness.I think the DISABLE/ENABLE patch is
necessary.
I will pack a new patch for all the constraints to commit.
Thanks again.
Yours, Wang Shuo HighGo Software Co.,Ltd. October 11, 2013