My nightly regression tests for OBSD failed for i386 and sparc. Attached
is the regression.diff, I don't know what to make of it.. Looks like
problems w/ foreign keys.
...
parallel group (5 tests): portals_p2 rules select_views alter_table
foreign_key
select_views ... ok
alter_table ... FAILED
portals_p2 ... ok
rules ... ok
foreign_key ... FAILED
parallel group (3 tests): limit temp plpgsql
...
b. palmer, [EMAIL PROTECTED]
pgp: www.crimelabs.net/bpalmer.pgp5
*** ./expected/alter_table.out Thu May 10 05:02:48 2001
--- ./results/alter_table.out Thu May 10 05:09:16 2001
***************
*** 347,372 ****
CREATE TABLE FKTABLE (ftest1 cidr, ftest2 datetime);
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable;
NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN
KEY check(s)
! ERROR: Unable to identify an operator '=' for types 'cidr' and 'int4'
! You will have to retype this query using an explicit cast
-- Again, so should this...
DROP TABLE FKTABLE;
CREATE TABLE FKTABLE (ftest1 cidr, ftest2 datetime);
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable(ptest1,
ptest2);
NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN
KEY check(s)
! ERROR: Unable to identify an operator '=' for types 'cidr' and 'int4'
! You will have to retype this query using an explicit cast
-- This fails because we mixed up the column ordering
DROP TABLE FKTABLE;
CREATE TABLE FKTABLE (ftest1 int, ftest2 text);
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable(ptest2,
ptest1);
NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN
KEY check(s)
! ERROR: Unable to identify an operator '=' for types 'int4' and 'text'
! You will have to retype this query using an explicit cast
-- As does this...
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1) references pktable(ptest1,
ptest2);
NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN
KEY check(s)
! ERROR: Unable to identify an operator '=' for types 'text' and 'int4'
! You will have to retype this query using an explicit cast
DROP TABLE FKTABLE;
DROP TABLE PKTABLE;
--- 347,369 ----
CREATE TABLE FKTABLE (ftest1 cidr, ftest2 datetime);
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable;
NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN
KEY check(s)
! ERROR: RelationClearRelation: relation 144096 deleted while still in use
-- Again, so should this...
DROP TABLE FKTABLE;
CREATE TABLE FKTABLE (ftest1 cidr, ftest2 datetime);
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable(ptest1,
ptest2);
NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN
KEY check(s)
! ERROR: Relation 'pktable' does not exist
-- This fails because we mixed up the column ordering
DROP TABLE FKTABLE;
CREATE TABLE FKTABLE (ftest1 int, ftest2 text);
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable(ptest2,
ptest1);
NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN
KEY check(s)
! ERROR: UNIQUE constraint matching given keys for referenced table "pktable" not
found
-- As does this...
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1) references pktable(ptest1,
ptest2);
NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN
KEY check(s)
! ERROR: RelationClearRelation: relation 144277 deleted while still in use
DROP TABLE FKTABLE;
+ ERROR: table "fktable" does not exist
DROP TABLE PKTABLE;
======================================================================
*** ./expected/foreign_key.out Thu May 10 05:02:48 2001
--- ./results/foreign_key.out Thu May 10 05:09:18 2001
***************
*** 574,697 ****
-- set default update / set null delete
CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY
KEY(ptest1, ptest2, ptest3) );
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for
table 'pktable'
CREATE TABLE FKTABLE ( ftest1 int DEFAULT 0, ftest2 int DEFAULT -1, ftest3 int,
ftest4 int, CONSTRAINT constrname3
FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE
ON DELETE SET NULL ON UPDATE SET DEFAULT);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
-- Insert Primary Key values
INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1');
INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2');
INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3');
INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4');
INSERT INTO PKTABLE VALUES (2, -1, 5, 'test5');
-- Insert Foreign Key values
INSERT INTO FKTABLE VALUES (1, 2, 3, 1);
INSERT INTO FKTABLE VALUES (2, 3, 4, 1);
INSERT INTO FKTABLE VALUES (2, 4, 5, 1);
INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2);
INSERT INTO FKTABLE VALUES (2, NULL, 3, 3);
INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4);
INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5);
-- Insert a failed values
INSERT INTO FKTABLE VALUES (1, 2, 7, 6);
! ERROR: constrname3 referential integrity violation - key referenced from fktable
not found in pktable
-- Show FKTABLE
SELECT * from FKTABLE;
! ftest1 | ftest2 | ftest3 | ftest4
! --------+--------+--------+--------
! 1 | 2 | 3 | 1
! 2 | 3 | 4 | 1
! 2 | 4 | 5 | 1
! | 2 | 3 | 2
! 2 | | 3 | 3
! | 2 | 7 | 4
! | 3 | 4 | 5
! (7 rows)
-- Try to update something that will fail
UPDATE PKTABLE set ptest2=5 where ptest2=2;
! ERROR: constrname3 referential integrity violation - key referenced from fktable
not found in pktable
-- Try to update something that will set default
UPDATE PKTABLE set ptest1=0, ptest2=5, ptest3=10 where ptest2=2;
UPDATE PKTABLE set ptest2=10 where ptest2=4;
-- Try to update something that should not set default
UPDATE PKTABLE set ptest2=2 WHERE ptest2=3 and ptest1=1;
-- Show PKTABLE and FKTABLE
SELECT * from PKTABLE;
! ptest1 | ptest2 | ptest3 | ptest4
! --------+--------+--------+--------
! 2 | 3 | 4 | test3
! 2 | -1 | 5 | test5
! 0 | 5 | 10 | test1
! 2 | 10 | 5 | test4
! 1 | 2 | 3 | test2
! (5 rows)
!
SELECT * from FKTABLE;
! ftest1 | ftest2 | ftest3 | ftest4
! --------+--------+--------+--------
! 2 | 3 | 4 | 1
! | 2 | 3 | 2
! 2 | | 3 | 3
! | 2 | 7 | 4
! | 3 | 4 | 5
! 0 | -1 | | 1
! 2 | -1 | 5 | 1
! (7 rows)
!
-- Try to delete something that should set null
DELETE FROM PKTABLE where ptest1=2 and ptest2=3 and ptest3=4;
-- Show PKTABLE and FKTABLE
SELECT * from PKTABLE;
! ptest1 | ptest2 | ptest3 | ptest4
! --------+--------+--------+--------
! 2 | -1 | 5 | test5
! 0 | 5 | 10 | test1
! 2 | 10 | 5 | test4
! 1 | 2 | 3 | test2
! (4 rows)
!
SELECT * from FKTABLE;
! ftest1 | ftest2 | ftest3 | ftest4
! --------+--------+--------+--------
! | 2 | 3 | 2
! 2 | | 3 | 3
! | 2 | 7 | 4
! | 3 | 4 | 5
! 0 | -1 | | 1
! 2 | -1 | 5 | 1
! | | | 1
! (7 rows)
!
-- Try to delete something that should not set null
DELETE FROM PKTABLE where ptest2=5;
-- Show PKTABLE and FKTABLE
SELECT * from PKTABLE;
! ptest1 | ptest2 | ptest3 | ptest4
! --------+--------+--------+--------
! 2 | -1 | 5 | test5
! 2 | 10 | 5 | test4
! 1 | 2 | 3 | test2
! (3 rows)
!
SELECT * from FKTABLE;
! ftest1 | ftest2 | ftest3 | ftest4
! --------+--------+--------+--------
! | 2 | 3 | 2
! 2 | | 3 | 3
! | 2 | 7 | 4
! | 3 | 4 | 5
! 0 | -1 | | 1
! 2 | -1 | 5 | 1
! | | | 1
! (7 rows)
!
DROP TABLE FKTABLE;
! NOTICE: DROP TABLE implicitly drops referential integrity trigger from table
"pktable"
! NOTICE: DROP TABLE implicitly drops referential integrity trigger from table
"pktable"
DROP TABLE PKTABLE;
CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY);
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for
table 'pktable'
CREATE TABLE FKTABLE_FAIL1 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest2)
REFERENCES PKTABLE);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
ERROR: columns referenced in foreign key constraint not found.
--- 574,660 ----
-- set default update / set null delete
CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY
KEY(ptest1, ptest2, ptest3) );
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for
table 'pktable'
+ ERROR: Cannot insert a duplicate key into unique index pg_class_relname_index
CREATE TABLE FKTABLE ( ftest1 int DEFAULT 0, ftest2 int DEFAULT -1, ftest3 int,
ftest4 int, CONSTRAINT constrname3
FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE
ON DELETE SET NULL ON UPDATE SET DEFAULT);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
+ NOTICE: Illegal FOREIGN KEY definition REFERENCES "pktable"
+ ERROR: number of key attributes in referenced table must be equal to foreign key
-- Insert Primary Key values
INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1');
+ ERROR: INSERT has more expressions than target columns
INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2');
+ ERROR: INSERT has more expressions than target columns
INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3');
+ ERROR: INSERT has more expressions than target columns
INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4');
+ ERROR: INSERT has more expressions than target columns
INSERT INTO PKTABLE VALUES (2, -1, 5, 'test5');
+ ERROR: INSERT has more expressions than target columns
-- Insert Foreign Key values
INSERT INTO FKTABLE VALUES (1, 2, 3, 1);
+ ERROR: Relation 'fktable' does not exist
INSERT INTO FKTABLE VALUES (2, 3, 4, 1);
+ ERROR: Relation 'fktable' does not exist
INSERT INTO FKTABLE VALUES (2, 4, 5, 1);
+ ERROR: Relation 'fktable' does not exist
INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2);
+ ERROR: Relation 'fktable' does not exist
INSERT INTO FKTABLE VALUES (2, NULL, 3, 3);
+ ERROR: INSERT has more expressions than target columns
INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4);
+ ERROR: INSERT has more expressions than target columns
INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5);
+ ERROR: INSERT has more expressions than target columns
-- Insert a failed values
INSERT INTO FKTABLE VALUES (1, 2, 7, 6);
! ERROR: INSERT has more expressions than target columns
-- Show FKTABLE
SELECT * from FKTABLE;
! ftest1
! --------
! (0 rows)
-- Try to update something that will fail
UPDATE PKTABLE set ptest2=5 where ptest2=2;
! ERROR: RelationClearRelation: relation 143823 deleted while still in use
-- Try to update something that will set default
UPDATE PKTABLE set ptest1=0, ptest2=5, ptest3=10 where ptest2=2;
+ ERROR: Relation 'pktable' does not exist
UPDATE PKTABLE set ptest2=10 where ptest2=4;
+ ERROR: Relation 'pktable' does not exist
-- Try to update something that should not set default
UPDATE PKTABLE set ptest2=2 WHERE ptest2=3 and ptest1=1;
+ ERROR: Relation 'pktable' does not exist
-- Show PKTABLE and FKTABLE
SELECT * from PKTABLE;
! ERROR: Relation 'pktable' does not exist
SELECT * from FKTABLE;
! ERROR: RelationClearRelation: relation 144013 deleted while still in use
-- Try to delete something that should set null
DELETE FROM PKTABLE where ptest1=2 and ptest2=3 and ptest3=4;
+ ERROR: Relation 'pktable' does not exist
-- Show PKTABLE and FKTABLE
SELECT * from PKTABLE;
! ERROR: Relation 'pktable' does not exist
SELECT * from FKTABLE;
! ERROR: Relation 'fktable' does not exist
-- Try to delete something that should not set null
DELETE FROM PKTABLE where ptest2=5;
+ ERROR: Relation 'pktable' does not exist
-- Show PKTABLE and FKTABLE
SELECT * from PKTABLE;
! ERROR: Relation 'pktable' does not exist
SELECT * from FKTABLE;
! ERROR: Relation 'fktable' does not exist
DROP TABLE FKTABLE;
! ERROR: table "fktable" does not exist
DROP TABLE PKTABLE;
+ ERROR: table "pktable" does not exist
CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY);
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for
table 'pktable'
+ ERROR: Cannot insert a duplicate key into unique index pg_class_relname_index
CREATE TABLE FKTABLE_FAIL1 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest2)
REFERENCES PKTABLE);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
ERROR: columns referenced in foreign key constraint not found.
***************
*** 733,748 ****
-- because varchar=int does exist
CREATE TABLE FKTABLE (ftest1 varchar REFERENCES pktable);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
DROP TABLE FKTABLE;
- NOTICE: DROP TABLE implicitly drops referential integrity trigger from table
"pktable"
- NOTICE: DROP TABLE implicitly drops referential integrity trigger from table
"pktable"
-- As should this
CREATE TABLE FKTABLE (ftest1 varchar REFERENCES pktable(ptest1));
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
DROP TABLE FKTABLE;
! NOTICE: DROP TABLE implicitly drops referential integrity trigger from table
"pktable"
! NOTICE: DROP TABLE implicitly drops referential integrity trigger from table
"pktable"
DROP TABLE PKTABLE;
-- Two columns, two tables
CREATE TABLE PKTABLE (ptest1 int, ptest2 text, PRIMARY KEY(ptest1, ptest2));
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for
table 'pktable'
--- 696,711 ----
-- because varchar=int does exist
CREATE TABLE FKTABLE (ftest1 varchar REFERENCES pktable);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
+ ERROR: Relation 'fktable' already exists
DROP TABLE FKTABLE;
-- As should this
CREATE TABLE FKTABLE (ftest1 varchar REFERENCES pktable(ptest1));
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
+ ERROR: RelationClearRelation: relation 144234 deleted while still in use
DROP TABLE FKTABLE;
! ERROR: table "fktable" does not exist
DROP TABLE PKTABLE;
+ ERROR: table "pktable" does not exist
-- Two columns, two tables
CREATE TABLE PKTABLE (ptest1 int, ptest2 text, PRIMARY KEY(ptest1, ptest2));
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for
table 'pktable'
======================================================================
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
