ENABLE/DISABLE CONSTRAINT NAME

From: wangshuo(at)highgo(dot)com(dot)cn
To: <pgsql-hackers(at)postgresql(dot)org>
Subject: ENABLE/DISABLE CONSTRAINT NAME
Date: 2013-08-30 06:03:09
Message-ID: 346433adf2d0bd1056c201cb5f372e49@highgo.com.cn
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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 30, 2013

Attachment Content-Type Size
constraint_enabledordisable.patch text/x-diff 42.7 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Fujii Masao 2013-08-30 06:03:39 Re: Compression of full-page-writes
Previous Message Fujii Masao 2013-08-30 06:02:09 Re: Compression of full-page-writes