Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit774f570

Browse files
Jan WieckJan Wieck
Jan Wieck
authored and
Jan Wieck
committed
Moved the recently added test for foreign key disabled by rewrite
rule into the rule.sql since it affects the latter if run in paralell.Jan
1 parente615230 commit774f570

File tree

4 files changed

+118
-118
lines changed

4 files changed

+118
-118
lines changed

‎src/test/regress/expected/foreign_key.out

Lines changed: 0 additions & 59 deletions
Original file line numberDiff line numberDiff line change
@@ -1029,62 +1029,3 @@ INSERT INTO fktable VALUES (100, 200);
10291029
COMMIT;
10301030
ERROR: insert or update on table "fktable" violates foreign key constraint "$1"
10311031
DETAIL: Key (fk)=(200) is not present in table "pktable".
1032-
-- Check that rewrite rules splitting one INSERT into multiple
1033-
-- conditional statements does not disable FK checking.
1034-
create table rule_and_refint_t1 (
1035-
id1a integer,
1036-
id1b integer,
1037-
1038-
primary key (id1a, id1b)
1039-
);
1040-
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "rule_and_refint_t1_pkey" for table "rule_and_refint_t1"
1041-
create table rule_and_refint_t2 (
1042-
id2a integer,
1043-
id2c integer,
1044-
1045-
primary key (id2a, id2c)
1046-
);
1047-
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "rule_and_refint_t2_pkey" for table "rule_and_refint_t2"
1048-
create table rule_and_refint_t3 (
1049-
id3a integer,
1050-
id3b integer,
1051-
id3c integer,
1052-
data text,
1053-
primary key (id3a, id3b, id3c),
1054-
foreign key (id3a, id3b) references rule_and_refint_t1 (id1a, id1b),
1055-
foreign key (id3a, id3c) references rule_and_refint_t2 (id2a, id2c)
1056-
);
1057-
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "rule_and_refint_t3_pkey" for table "rule_and_refint_t3"
1058-
insert into rule_and_refint_t1 values (1, 11);
1059-
insert into rule_and_refint_t1 values (1, 12);
1060-
insert into rule_and_refint_t1 values (2, 21);
1061-
insert into rule_and_refint_t1 values (2, 22);
1062-
insert into rule_and_refint_t2 values (1, 11);
1063-
insert into rule_and_refint_t2 values (1, 12);
1064-
insert into rule_and_refint_t2 values (2, 21);
1065-
insert into rule_and_refint_t2 values (2, 22);
1066-
insert into rule_and_refint_t3 values (1, 11, 11, 'row1');
1067-
insert into rule_and_refint_t3 values (1, 11, 12, 'row2');
1068-
insert into rule_and_refint_t3 values (1, 12, 11, 'row3');
1069-
insert into rule_and_refint_t3 values (1, 12, 12, 'row4');
1070-
insert into rule_and_refint_t3 values (1, 11, 13, 'row5');
1071-
ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "$2"
1072-
DETAIL: Key (id3a,id3c)=(1,13) is not present in table "rule_and_refint_t2".
1073-
insert into rule_and_refint_t3 values (1, 13, 11, 'row6');
1074-
ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "$1"
1075-
DETAIL: Key (id3a,id3b)=(1,13) is not present in table "rule_and_refint_t1".
1076-
create rule rule_and_refint_t3_ins as on insert to rule_and_refint_t3
1077-
where (exists (select 1 from rule_and_refint_t3
1078-
where (((rule_and_refint_t3.id3a = new.id3a)
1079-
and (rule_and_refint_t3.id3b = new.id3b))
1080-
and (rule_and_refint_t3.id3c = new.id3c))))
1081-
do instead update rule_and_refint_t3 set data = new.data
1082-
where (((rule_and_refint_t3.id3a = new.id3a)
1083-
and (rule_and_refint_t3.id3b = new.id3b))
1084-
and (rule_and_refint_t3.id3c = new.id3c));
1085-
insert into rule_and_refint_t3 values (1, 11, 13, 'row7');
1086-
ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "$2"
1087-
DETAIL: Key (id3a,id3c)=(1,13) is not present in table "rule_and_refint_t2".
1088-
insert into rule_and_refint_t3 values (1, 13, 11, 'row8');
1089-
ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "$1"
1090-
DETAIL: Key (id3a,id3b)=(1,13) is not present in table "rule_and_refint_t1".

‎src/test/regress/expected/rules.out

Lines changed: 59 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1364,3 +1364,62 @@ SELECT * FROM ruletest_tbl2;
13641364
1000 | 1000
13651365
(2 rows)
13661366

1367+
-- Check that rewrite rules splitting one INSERT into multiple
1368+
-- conditional statements does not disable FK checking.
1369+
create table rule_and_refint_t1 (
1370+
id1a integer,
1371+
id1b integer,
1372+
1373+
primary key (id1a, id1b)
1374+
);
1375+
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "rule_and_refint_t1_pkey" for table "rule_and_refint_t1"
1376+
create table rule_and_refint_t2 (
1377+
id2a integer,
1378+
id2c integer,
1379+
1380+
primary key (id2a, id2c)
1381+
);
1382+
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "rule_and_refint_t2_pkey" for table "rule_and_refint_t2"
1383+
create table rule_and_refint_t3 (
1384+
id3a integer,
1385+
id3b integer,
1386+
id3c integer,
1387+
data text,
1388+
primary key (id3a, id3b, id3c),
1389+
foreign key (id3a, id3b) references rule_and_refint_t1 (id1a, id1b),
1390+
foreign key (id3a, id3c) references rule_and_refint_t2 (id2a, id2c)
1391+
);
1392+
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "rule_and_refint_t3_pkey" for table "rule_and_refint_t3"
1393+
insert into rule_and_refint_t1 values (1, 11);
1394+
insert into rule_and_refint_t1 values (1, 12);
1395+
insert into rule_and_refint_t1 values (2, 21);
1396+
insert into rule_and_refint_t1 values (2, 22);
1397+
insert into rule_and_refint_t2 values (1, 11);
1398+
insert into rule_and_refint_t2 values (1, 12);
1399+
insert into rule_and_refint_t2 values (2, 21);
1400+
insert into rule_and_refint_t2 values (2, 22);
1401+
insert into rule_and_refint_t3 values (1, 11, 11, 'row1');
1402+
insert into rule_and_refint_t3 values (1, 11, 12, 'row2');
1403+
insert into rule_and_refint_t3 values (1, 12, 11, 'row3');
1404+
insert into rule_and_refint_t3 values (1, 12, 12, 'row4');
1405+
insert into rule_and_refint_t3 values (1, 11, 13, 'row5');
1406+
ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "$2"
1407+
DETAIL: Key (id3a,id3c)=(1,13) is not present in table "rule_and_refint_t2".
1408+
insert into rule_and_refint_t3 values (1, 13, 11, 'row6');
1409+
ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "$1"
1410+
DETAIL: Key (id3a,id3b)=(1,13) is not present in table "rule_and_refint_t1".
1411+
create rule rule_and_refint_t3_ins as on insert to rule_and_refint_t3
1412+
where (exists (select 1 from rule_and_refint_t3
1413+
where (((rule_and_refint_t3.id3a = new.id3a)
1414+
and (rule_and_refint_t3.id3b = new.id3b))
1415+
and (rule_and_refint_t3.id3c = new.id3c))))
1416+
do instead update rule_and_refint_t3 set data = new.data
1417+
where (((rule_and_refint_t3.id3a = new.id3a)
1418+
and (rule_and_refint_t3.id3b = new.id3b))
1419+
and (rule_and_refint_t3.id3c = new.id3c));
1420+
insert into rule_and_refint_t3 values (1, 11, 13, 'row7');
1421+
ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "$2"
1422+
DETAIL: Key (id3a,id3c)=(1,13) is not present in table "rule_and_refint_t2".
1423+
insert into rule_and_refint_t3 values (1, 13, 11, 'row8');
1424+
ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "$1"
1425+
DETAIL: Key (id3a,id3b)=(1,13) is not present in table "rule_and_refint_t1".

‎src/test/regress/sql/foreign_key.sql

Lines changed: 0 additions & 59 deletions
Original file line numberDiff line numberDiff line change
@@ -693,62 +693,3 @@ INSERT INTO fktable VALUES (100, 200);
693693

694694
-- error here on commit
695695
COMMIT;
696-
697-
-- Check that rewrite rules splitting one INSERT into multiple
698-
-- conditional statements does not disable FK checking.
699-
createtablerule_and_refint_t1 (
700-
id1ainteger,
701-
id1binteger,
702-
703-
primary key (id1a, id1b)
704-
);
705-
706-
createtablerule_and_refint_t2 (
707-
id2ainteger,
708-
id2cinteger,
709-
710-
primary key (id2a, id2c)
711-
);
712-
713-
createtablerule_and_refint_t3 (
714-
id3ainteger,
715-
id3binteger,
716-
id3cinteger,
717-
datatext,
718-
719-
primary key (id3a, id3b, id3c),
720-
721-
foreign key (id3a, id3b)references rule_and_refint_t1 (id1a, id1b),
722-
foreign key (id3a, id3c)references rule_and_refint_t2 (id2a, id2c)
723-
);
724-
725-
726-
insert into rule_and_refint_t1values (1,11);
727-
insert into rule_and_refint_t1values (1,12);
728-
insert into rule_and_refint_t1values (2,21);
729-
insert into rule_and_refint_t1values (2,22);
730-
731-
insert into rule_and_refint_t2values (1,11);
732-
insert into rule_and_refint_t2values (1,12);
733-
insert into rule_and_refint_t2values (2,21);
734-
insert into rule_and_refint_t2values (2,22);
735-
736-
insert into rule_and_refint_t3values (1,11,11,'row1');
737-
insert into rule_and_refint_t3values (1,11,12,'row2');
738-
insert into rule_and_refint_t3values (1,12,11,'row3');
739-
insert into rule_and_refint_t3values (1,12,12,'row4');
740-
insert into rule_and_refint_t3values (1,11,13,'row5');
741-
insert into rule_and_refint_t3values (1,13,11,'row6');
742-
743-
createrulerule_and_refint_t3_insason insert to rule_and_refint_t3
744-
where (exists (select1from rule_and_refint_t3
745-
where (((rule_and_refint_t3.id3a=new.id3a)
746-
and (rule_and_refint_t3.id3b=new.id3b))
747-
and (rule_and_refint_t3.id3c=new.id3c))))
748-
do insteadupdate rule_and_refint_t3set data=new.data
749-
where (((rule_and_refint_t3.id3a=new.id3a)
750-
and (rule_and_refint_t3.id3b=new.id3b))
751-
and (rule_and_refint_t3.id3c=new.id3c));
752-
753-
insert into rule_and_refint_t3values (1,11,13,'row7');
754-
insert into rule_and_refint_t3values (1,13,11,'row8');

‎src/test/regress/sql/rules.sql

Lines changed: 59 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -786,3 +786,62 @@ CREATE OR REPLACE RULE myrule AS ON INSERT TO ruletest_tbl
786786
INSERT INTO ruletest_tblVALUES (99,99);
787787

788788
SELECT*FROM ruletest_tbl2;
789+
790+
-- Check that rewrite rules splitting one INSERT into multiple
791+
-- conditional statements does not disable FK checking.
792+
createtablerule_and_refint_t1 (
793+
id1ainteger,
794+
id1binteger,
795+
796+
primary key (id1a, id1b)
797+
);
798+
799+
createtablerule_and_refint_t2 (
800+
id2ainteger,
801+
id2cinteger,
802+
803+
primary key (id2a, id2c)
804+
);
805+
806+
createtablerule_and_refint_t3 (
807+
id3ainteger,
808+
id3binteger,
809+
id3cinteger,
810+
datatext,
811+
812+
primary key (id3a, id3b, id3c),
813+
814+
foreign key (id3a, id3b)references rule_and_refint_t1 (id1a, id1b),
815+
foreign key (id3a, id3c)references rule_and_refint_t2 (id2a, id2c)
816+
);
817+
818+
819+
insert into rule_and_refint_t1values (1,11);
820+
insert into rule_and_refint_t1values (1,12);
821+
insert into rule_and_refint_t1values (2,21);
822+
insert into rule_and_refint_t1values (2,22);
823+
824+
insert into rule_and_refint_t2values (1,11);
825+
insert into rule_and_refint_t2values (1,12);
826+
insert into rule_and_refint_t2values (2,21);
827+
insert into rule_and_refint_t2values (2,22);
828+
829+
insert into rule_and_refint_t3values (1,11,11,'row1');
830+
insert into rule_and_refint_t3values (1,11,12,'row2');
831+
insert into rule_and_refint_t3values (1,12,11,'row3');
832+
insert into rule_and_refint_t3values (1,12,12,'row4');
833+
insert into rule_and_refint_t3values (1,11,13,'row5');
834+
insert into rule_and_refint_t3values (1,13,11,'row6');
835+
836+
createrulerule_and_refint_t3_insason insert to rule_and_refint_t3
837+
where (exists (select1from rule_and_refint_t3
838+
where (((rule_and_refint_t3.id3a=new.id3a)
839+
and (rule_and_refint_t3.id3b=new.id3b))
840+
and (rule_and_refint_t3.id3c=new.id3c))))
841+
do insteadupdate rule_and_refint_t3set data=new.data
842+
where (((rule_and_refint_t3.id3a=new.id3a)
843+
and (rule_and_refint_t3.id3b=new.id3b))
844+
and (rule_and_refint_t3.id3c=new.id3c));
845+
846+
insert into rule_and_refint_t3values (1,11,13,'row7');
847+
insert into rule_and_refint_t3values (1,13,11,'row8');

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp