Re: 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

On Fri, 2013-08-30 at 09:57 +0800, wangshuo(at)highgo(dot)com(dot)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(at)highgo(dot)com(dot)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(at)highgo(dot)com(dot)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(at)highgo(dot)com(dot)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(at)highgo(dot)com(dot)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(at)highgo(dot)com(dot)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(at)highgo(dot)com(dot)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(at)highgo(dot)com(dot)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(at)gmail(dot)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(at)oopsware(dot)de> wrote:
> --On 13. September 2013 20:17:19 -0400 Robert Haas <robertmhaas(at)gmail(dot)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(at)gmx(dot)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(at)gmx(dot)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 jim(at)nasby(dot)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