Re: [HACKERS] ENABLE/DISABLE CONSTRAINT NAME
On 10/9/13 1:10 PM, Robert Haas wrote: On Tue, Sep 24, 2013 at 10:40 PM, Peter Eisentraut pete...@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 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. -- Jim C. Nasby, Data Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ENABLE/DISABLE CONSTRAINT NAME
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ENABLE/DISABLE CONSTRAINT NAME
On Tue, Sep 24, 2013 at 10:40 PM, Peter Eisentraut pete...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ENABLE/DISABLE CONSTRAINT NAME
--On 13. September 2013 20:17:19 -0400 Robert Haas robertmh...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ENABLE/DISABLE CONSTRAINT NAME
On Tue, Sep 24, 2013 at 5:58 AM, Bernd Helmle maili...@oopsware.de wrote: --On 13. September 2013 20:17:19 -0400 Robert Haas robertmh...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ENABLE/DISABLE CONSTRAINT NAME
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ENABLE/DISABLE CONSTRAINT NAME
On Thu, Sep 12, 2013 at 10:03 PM, wangs...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ENABLE/DISABLE CONSTRAINT NAME
On 9/11/13 1:09 AM, wangs...@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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ENABLE/DISABLE CONSTRAINT NAME
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(a14)); 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ENABLE/DISABLE CONSTRAINT NAME
于 2013-09-09 20:54, Peter Eisentraut 回复: On 9/3/13 3:13 AM, wangs...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ENABLE/DISABLE CONSTRAINT NAME
On 9/3/13 3:13 AM, wangs...@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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ENABLE/DISABLE CONSTRAINT NAME
On Thu, Sep 5, 2013 at 12:27 PM, wangs...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ENABLE/DISABLE CONSTRAINT NAME
On Tue, Sep 3, 2013 at 3:13 AM, wangs...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ENABLE/DISABLE CONSTRAINT NAME
于 2013-09-05 01:56, Robert Haas 回复: On Tue, Sep 3, 2013 at 3:13 AM, wangs...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ENABLE/DISABLE CONSTRAINT NAME
于 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ENABLE/DISABLE CONSTRAINT NAME
On Fri, 2013-08-30 at 09:57 +0800, wangs...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ENABLE/DISABLE CONSTRAINT NAME
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ENABLE/DISABLE CONSTRAINT NAME
于 2013-08-30 21:27, Tom Lane 回复: wangs...@highgo.com.cn writes: 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. Tom Lane t...@sss.pgh.pa.us writes: Uh ... why not just drop the constraint, and re-add it later if you want it again? Thanks for your reply. If you drop the constraint,you must record the sql of the constraint. ENABLE/DISABLE just turn off or trun on that.The sql2008 support this. And, Oracle,DB2,SQL Server,MySQL all support this feature, new users ever used Oracle are accustomed to use, besides, this feature benefits data migration, so we have enough reasons to add this feature. This seems like adding a lot of mechanism (and possible bugs) for a rather marginal use-case. regards, tom lane I changed the pg_constraint system table , the ConstrCheck struct, the CreateTrigger function, the CreateConstraintEntry function and some grammars. I have passed the pgtest,and this may has some bugs. I refer to the validation feature to do this feature. The validation feature only works while adding constraint, my work is a supplement to the validation feature. If possible, I would like to merge the two features together. For all above, I wrote this letter to community, to let more people to talk about this and correct possible bugs. Wang Shuo HighGo Software Co.,Ltd. September 1, 2013 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ENABLE/DISABLE CONSTRAINT NAME
wangs...@highgo.com.cn writes: 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. Uh ... why not just drop the constraint, and re-add it later if you want it again? This seems like adding a lot of mechanism (and possible bugs) for a rather marginal use-case. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ENABLE/DISABLE CONSTRAINT NAME
Uh ... why not just drop the constraint, and re-add it later if you want it again? My 0.02€ : maybe because you must keep track of the constraint details to do so, this it is significantly more error prone than disable / enable when the bookkeeping is done by the system and if everything is in a transaction... If the ENABLE is automatically done on the next COMMIT, that would be even better. This seems like adding a lot of mechanism (and possible bugs) for a rather marginal use-case. That is possible! -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
