\c table_name_test_db;
set dolphin.lower_case_table_names TO 0;
--
-- FOREIGN KEY
--
-- MATCH FULL
--
-- First test, check and cascade
--
CREATE TABLE PKTABLE ( ptest1 int PRIMARY KEY, ptest2 text ) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
CREATE TABLE FKTABLE ( ftest1 int REFERENCES PKTABLE MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE, ftest2 int ) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
-- Insert test data into PKTABLE
INSERT INTO PKTABLE VALUES (1, 'Test1');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (1, 'Test1');
^
INSERT INTO PKTABLE VALUES (2, 'Test2');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (2, 'Test2');
^
INSERT INTO PKTABLE VALUES (3, 'Test3');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (3, 'Test3');
^
INSERT INTO PKTABLE VALUES (4, 'Test4');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (4, 'Test4');
^
INSERT INTO PKTABLE VALUES (5, 'Test5');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (5, 'Test5');
^
-- Insert successful rows into FK TABLE
INSERT INTO FKTABLE VALUES (1, 2);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (1, 2);
^
INSERT INTO FKTABLE VALUES (2, 3);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (2, 3);
^
INSERT INTO FKTABLE VALUES (3, 4);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (3, 4);
^
INSERT INTO FKTABLE VALUES (NULL, 1);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (NULL, 1);
^
-- Insert a failed row into FK TABLE
INSERT INTO FKTABLE VALUES (100, 2);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (100, 2);
^
-- Check FKTABLE
SELECT * FROM FKTABLE ORDER BY 1, 2;
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: SELECT * FROM FKTABLE ORDER BY 1, 2;
^
-- Delete a row from PK TABLE
DELETE FROM PKTABLE WHERE ptest1=1;
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: DELETE FROM PKTABLE WHERE ptest1=1;
^
-- Check FKTABLE for removal of matched row
SELECT * FROM FKTABLE ORDER BY 1, 2;
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: SELECT * FROM FKTABLE ORDER BY 1, 2;
^
-- Update a row from PK TABLE
UPDATE PKTABLE SET ptest1=1 WHERE ptest1=2;
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: UPDATE PKTABLE SET ptest1=1 WHERE ptest1=2;
^
-- Check FKTABLE for update of matched row
SELECT * FROM FKTABLE ORDER BY 1, 2;
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: SELECT * FROM FKTABLE ORDER BY 1, 2;
^
DROP TABLE FKTABLE;
ERROR: table "FKTABLE" does not exist
DROP TABLE PKTABLE;
ERROR: table "PKTABLE" does not exist
--
-- check set NULL and table constraint on multiple columns
--
CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 text, PRIMARY KEY(ptest1, ptest2) ) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, CONSTRAINT constrname FOREIGN KEY(ftest1, ftest2)
REFERENCES PKTABLE MATCH FULL ON DELETE SET NULL ON UPDATE SET NULL) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
-- Test comments
COMMENT ON CONSTRAINT constrname_wrong ON FKTABLE IS 'fk constraint comment';
ERROR: relation "fktable" does not exist
COMMENT ON CONSTRAINT constrname ON FKTABLE IS 'fk constraint comment';
ERROR: relation "fktable" does not exist
COMMENT ON CONSTRAINT constrname ON FKTABLE IS NULL;
ERROR: relation "fktable" does not exist
-- Insert test data into PKTABLE
INSERT INTO PKTABLE VALUES (1, 2, 'Test1');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (1, 2, 'Test1');
^
INSERT INTO PKTABLE VALUES (1, 3, 'Test1-2');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (1, 3, 'Test1-2');
^
INSERT INTO PKTABLE VALUES (2, 4, 'Test2');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (2, 4, 'Test2');
^
INSERT INTO PKTABLE VALUES (3, 6, 'Test3');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (3, 6, 'Test3');
^
INSERT INTO PKTABLE VALUES (4, 8, 'Test4');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (4, 8, 'Test4');
^
INSERT INTO PKTABLE VALUES (5, 10, 'Test5');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (5, 10, 'Test5');
^
-- Insert successful rows into FK TABLE
INSERT INTO FKTABLE VALUES (1, 2, 4);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (1, 2, 4);
^
INSERT INTO FKTABLE VALUES (1, 3, 5);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (1, 3, 5);
^
INSERT INTO FKTABLE VALUES (2, 4, 8);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (2, 4, 8);
^
INSERT INTO FKTABLE VALUES (3, 6, 12);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (3, 6, 12);
^
INSERT INTO FKTABLE VALUES (NULL, NULL, 0);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (NULL, NULL, 0);
^
-- Insert failed rows into FK TABLE
INSERT INTO FKTABLE VALUES (100, 2, 4);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (100, 2, 4);
^
INSERT INTO FKTABLE VALUES (2, 2, 4);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (2, 2, 4);
^
INSERT INTO FKTABLE VALUES (NULL, 2, 4);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (NULL, 2, 4);
^
INSERT INTO FKTABLE VALUES (1, NULL, 4);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (1, NULL, 4);
^
-- Check FKTABLE
SELECT * FROM FKTABLE ORDER BY 1, 2, 3;
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: SELECT * FROM FKTABLE ORDER BY 1, 2, 3;
^
-- Delete a row from PK TABLE
DELETE FROM PKTABLE WHERE ptest1=1 and ptest2=2;
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: DELETE FROM PKTABLE WHERE ptest1=1 and ptest2=2;
^
-- Check FKTABLE for removal of matched row
SELECT * FROM FKTABLE ORDER BY 1, 2, 3;
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: SELECT * FROM FKTABLE ORDER BY 1, 2, 3;
^
-- Delete another row from PK TABLE
DELETE FROM PKTABLE WHERE ptest1=5 and ptest2=10;
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: DELETE FROM PKTABLE WHERE ptest1=5 and ptest2=10;
^
-- Check FKTABLE (should be no change)
SELECT * FROM FKTABLE ORDER BY 1, 2, 3;
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: SELECT * FROM FKTABLE ORDER BY 1, 2, 3;
^
-- Update a row from PK TABLE
UPDATE PKTABLE SET ptest1=1 WHERE ptest1=2;
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: UPDATE PKTABLE SET ptest1=1 WHERE ptest1=2;
^
-- Check FKTABLE for update of matched row
SELECT * FROM FKTABLE ORDER BY 1, 2, 3;
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: SELECT * FROM FKTABLE ORDER BY 1, 2, 3;
^
-- Try altering the column type where foreign keys are involved
ALTER TABLE PKTABLE ALTER COLUMN ptest1 TYPE bigint;
ERROR: relation "PKTABLE" does not exist
ALTER TABLE FKTABLE ALTER COLUMN ftest1 TYPE bigint;
ERROR: relation "FKTABLE" does not exist
SELECT * FROM PKTABLE ORDER BY 1, 2, 3;
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: SELECT * FROM PKTABLE ORDER BY 1, 2, 3;
^
SELECT * FROM FKTABLE ORDER BY 1, 2, 3;
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: SELECT * FROM FKTABLE ORDER BY 1, 2, 3;
^
DROP TABLE PKTABLE CASCADE;
ERROR: table "PKTABLE" does not exist
DROP TABLE FKTABLE;
ERROR: table "FKTABLE" does not exist
--
-- check set default and table constraint on multiple columns
--
CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 text, PRIMARY KEY(ptest1, ptest2) ) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
CREATE TABLE FKTABLE ( ftest1 int DEFAULT -1, ftest2 int DEFAULT -2, ftest3 int, CONSTRAINT constrname2 FOREIGN KEY(ftest1, ftest2)
REFERENCES PKTABLE MATCH FULL ON DELETE SET DEFAULT ON UPDATE SET DEFAULT);
ERROR: relation "PKTABLE" does not exist
-- Insert a value in PKTABLE for default
INSERT INTO PKTABLE VALUES (-1, -2, 'The Default!');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (-1, -2, 'The Default!');
^
-- Insert test data into PKTABLE
INSERT INTO PKTABLE VALUES (1, 2, 'Test1');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (1, 2, 'Test1');
^
INSERT INTO PKTABLE VALUES (1, 3, 'Test1-2');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (1, 3, 'Test1-2');
^
INSERT INTO PKTABLE VALUES (2, 4, 'Test2');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (2, 4, 'Test2');
^
INSERT INTO PKTABLE VALUES (3, 6, 'Test3');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (3, 6, 'Test3');
^
INSERT INTO PKTABLE VALUES (4, 8, 'Test4');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (4, 8, 'Test4');
^
INSERT INTO PKTABLE VALUES (5, 10, 'Test5');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (5, 10, 'Test5');
^
-- Insert successful rows into FK TABLE
INSERT INTO FKTABLE VALUES (1, 2, 4);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (1, 2, 4);
^
INSERT INTO FKTABLE VALUES (1, 3, 5);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (1, 3, 5);
^
INSERT INTO FKTABLE VALUES (2, 4, 8);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (2, 4, 8);
^
INSERT INTO FKTABLE VALUES (3, 6, 12);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (3, 6, 12);
^
INSERT INTO FKTABLE VALUES (NULL, NULL, 0);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (NULL, NULL, 0);
^
-- Insert failed rows into FK TABLE
INSERT INTO FKTABLE VALUES (100, 2, 4);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (100, 2, 4);
^
INSERT INTO FKTABLE VALUES (2, 2, 4);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (2, 2, 4);
^
INSERT INTO FKTABLE VALUES (NULL, 2, 4);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (NULL, 2, 4);
^
INSERT INTO FKTABLE VALUES (1, NULL, 4);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (1, NULL, 4);
^
-- Check FKTABLE
SELECT * FROM FKTABLE ORDER BY 1, 2, 3;
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: SELECT * FROM FKTABLE ORDER BY 1, 2, 3;
^
-- Delete a row from PK TABLE
DELETE FROM PKTABLE WHERE ptest1=1 and ptest2=2;
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: DELETE FROM PKTABLE WHERE ptest1=1 and ptest2=2;
^
-- Check FKTABLE to check for removal
SELECT * FROM FKTABLE ORDER BY 1, 2, 3;
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: SELECT * FROM FKTABLE ORDER BY 1, 2, 3;
^
-- Delete another row from PK TABLE
DELETE FROM PKTABLE WHERE ptest1=5 and ptest2=10;
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: DELETE FROM PKTABLE WHERE ptest1=5 and ptest2=10;
^
-- Check FKTABLE (should be no change)
SELECT * FROM FKTABLE ORDER BY 1, 2, 3;
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: SELECT * FROM FKTABLE ORDER BY 1, 2, 3;
^
-- Update a row from PK TABLE
UPDATE PKTABLE SET ptest1=1 WHERE ptest1=2;
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: UPDATE PKTABLE SET ptest1=1 WHERE ptest1=2;
^
-- Check FKTABLE for update of matched row
SELECT * FROM FKTABLE ORDER BY 1, 2, 3;
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: SELECT * FROM FKTABLE ORDER BY 1, 2, 3;
^
-- this should fail for lack of CASCADE
DROP TABLE PKTABLE;
ERROR: table "PKTABLE" does not exist
DROP TABLE PKTABLE CASCADE;
ERROR: table "PKTABLE" does not exist
DROP TABLE FKTABLE;
ERROR: table "FKTABLE" does not exist
--
-- First test, check with no on delete or on update
--
CREATE TABLE PKTABLE ( ptest1 int PRIMARY KEY, ptest2 text ) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
CREATE TABLE FKTABLE ( ftest1 int REFERENCES PKTABLE MATCH FULL, ftest2 int ) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
-- Insert test data into PKTABLE
INSERT INTO PKTABLE VALUES (1, 'Test1');
ERROR: INSERT has more expressions than target columns
LINE 1: INSERT INTO PKTABLE VALUES (1, 'Test1');
^
INSERT INTO PKTABLE VALUES (2, 'Test2');
ERROR: INSERT has more expressions than target columns
LINE 1: INSERT INTO PKTABLE VALUES (2, 'Test2');
^
INSERT INTO PKTABLE VALUES (3, 'Test3');
ERROR: INSERT has more expressions than target columns
LINE 1: INSERT INTO PKTABLE VALUES (3, 'Test3');
^
INSERT INTO PKTABLE VALUES (4, 'Test4');
ERROR: INSERT has more expressions than target columns
LINE 1: INSERT INTO PKTABLE VALUES (4, 'Test4');
^
INSERT INTO PKTABLE VALUES (5, 'Test5');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (5, 'Test5');
^
-- Insert successful rows into FK TABLE
INSERT INTO FKTABLE VALUES (1, 2);
ERROR: INSERT has more expressions than target columns
LINE 1: INSERT INTO FKTABLE VALUES (1, 2);
^
INSERT INTO FKTABLE VALUES (2, 3);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (2, 3);
^
INSERT INTO FKTABLE VALUES (3, 4);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (3, 4);
^
INSERT INTO FKTABLE VALUES (NULL, 1);
ERROR: timestamp out of range
CONTEXT: referenced column: ftest2
-- Insert a failed row into FK TABLE
INSERT INTO FKTABLE VALUES (100, 2);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (100, 2);
^
-- Check FKTABLE
SELECT * FROM FKTABLE ORDER BY 1, 2;
ftest1 | ftest2
--------+--------
(0 rows)
-- Check PKTABLE
SELECT * FROM PKTABLE ORDER BY 1, 2;
ptest1 | ptest2
--------+--------
(0 rows)
-- Delete a row from PK TABLE (should fail)
DELETE FROM PKTABLE WHERE ptest1=1;
-- Delete a row from PK TABLE (should succeed)
DELETE FROM PKTABLE WHERE ptest1=5;
-- Check PKTABLE for deletes
SELECT * FROM PKTABLE ORDER BY 1, 2;
ptest1 | ptest2
--------+--------
(0 rows)
-- Update a row from PK TABLE (should fail)
UPDATE PKTABLE SET ptest1=0 WHERE ptest1=2;
-- Update a row from PK TABLE (should succeed)
UPDATE PKTABLE SET ptest1=0 WHERE ptest1=4;
-- Check PKTABLE for updates
SELECT * FROM PKTABLE ORDER BY 1, 2;
ptest1 | ptest2
--------+--------
(0 rows)
DROP TABLE FKTABLE;
ERROR: table "FKTABLE" does not exist
DROP TABLE PKTABLE;
-- MATCH unspecified
-- Base test restricting update/delete
CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) ) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int, CONSTRAINT constrname3
FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
-- Insert Primary Key values
INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1');
^
INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2');
^
INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3');
^
INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4');
^
-- Insert Foreign Key values
INSERT INTO FKTABLE VALUES (1, 2, 3, 1);
ERROR: INSERT has more expressions than target columns
LINE 1: INSERT INTO FKTABLE VALUES (1, 2, 3, 1);
^
INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2);
ERROR: INSERT has more expressions than target columns
LINE 1: INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2);
^
INSERT INTO FKTABLE VALUES (2, NULL, 3, 3);
ERROR: INSERT has more expressions than target columns
LINE 1: INSERT INTO FKTABLE VALUES (2, NULL, 3, 3);
^
INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4);
ERROR: INSERT has more expressions than target columns
LINE 1: INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4);
^
INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5);
ERROR: INSERT has more expressions than target columns
LINE 1: INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5);
^
-- Insert a failed values
INSERT INTO FKTABLE VALUES (1, 2, 7, 6);
ERROR: INSERT has more expressions than target columns
LINE 1: INSERT INTO FKTABLE VALUES (1, 2, 7, 6);
^
-- Show FKTABLE
SELECT * from FKTABLE ORDER BY 1, 2, 3,4;
ERROR: ORDER BY position 3 is not in select list
LINE 1: SELECT * from FKTABLE ORDER BY 1, 2, 3,4;
^
-- Try to update something that should fail
UPDATE PKTABLE set ptest2=5 where ptest2=2;
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: UPDATE PKTABLE set ptest2=5 where ptest2=2;
^
-- Try to update something that should succeed
UPDATE PKTABLE set ptest1=1 WHERE ptest2=3;
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: UPDATE PKTABLE set ptest1=1 WHERE ptest2=3;
^
-- Try to delete something that should fail
DELETE FROM PKTABLE where ptest1=1 and ptest2=2 and ptest3=3;
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: DELETE FROM PKTABLE where ptest1=1 and ptest2=2 and ptest3=3...
^
-- Try to delete something that should work
DELETE FROM PKTABLE where ptest1=2;
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: DELETE FROM PKTABLE where ptest1=2;
^
-- Show PKTABLE and FKTABLE
SELECT * from PKTABLE ORDER BY 1, 2, 3,4;
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: SELECT * from PKTABLE ORDER BY 1, 2, 3,4;
^
SELECT * from FKTABLE ORDER BY 1, 2, 3,4;
ERROR: ORDER BY position 3 is not in select list
LINE 1: SELECT * from FKTABLE ORDER BY 1, 2, 3,4;
^
DROP TABLE FKTABLE;
DROP TABLE PKTABLE;
ERROR: table "PKTABLE" does not exist
-- cascade update/delete
CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) ) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int, CONSTRAINT constrname3
FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE
ON DELETE CASCADE ON UPDATE CASCADE);
ERROR: relation "PKTABLE" does not exist
-- Insert Primary Key values
INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1');
^
INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2');
^
INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3');
^
INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4');
^
-- Insert Foreign Key values
INSERT INTO FKTABLE VALUES (1, 2, 3, 1);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (1, 2, 3, 1);
^
INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2);
^
INSERT INTO FKTABLE VALUES (2, NULL, 3, 3);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (2, NULL, 3, 3);
^
INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4);
^
INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5);
^
-- Insert a failed values
INSERT INTO FKTABLE VALUES (1, 2, 7, 6);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (1, 2, 7, 6);
^
-- Show FKTABLE
SELECT * from FKTABLE ORDER BY 1, 2, 3,4;
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: SELECT * from FKTABLE ORDER BY 1, 2, 3,4;
^
-- Try to update something that will cascade
UPDATE PKTABLE set ptest2=5 where ptest2=2;
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: UPDATE PKTABLE set ptest2=5 where ptest2=2;
^
-- Try to update something that should not cascade
UPDATE PKTABLE set ptest1=1 WHERE ptest2=3;
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: UPDATE PKTABLE set ptest1=1 WHERE ptest2=3;
^
-- Show PKTABLE and FKTABLE
SELECT * from PKTABLE ORDER BY 1, 2, 3,4;
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: SELECT * from PKTABLE ORDER BY 1, 2, 3,4;
^
SELECT * from FKTABLE ORDER BY 1, 2, 3,4;
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: SELECT * from FKTABLE ORDER BY 1, 2, 3,4;
^
-- Try to delete something that should cascade
DELETE FROM PKTABLE where ptest1=1 and ptest2=5 and ptest3=3;
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: DELETE FROM PKTABLE where ptest1=1 and ptest2=5 and ptest3=3...
^
-- Show PKTABLE and FKTABLE
SELECT * from PKTABLE ORDER BY 1, 2, 3,4;
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: SELECT * from PKTABLE ORDER BY 1, 2, 3,4;
^
SELECT * from FKTABLE ORDER BY 1, 2, 3,4;
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: SELECT * from FKTABLE ORDER BY 1, 2, 3,4;
^
-- Try to delete something that should not have a cascade
DELETE FROM PKTABLE where ptest1=2;
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: DELETE FROM PKTABLE where ptest1=2;
^
-- Show PKTABLE and FKTABLE
SELECT * from PKTABLE ORDER BY 1, 2, 3,4;
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: SELECT * from PKTABLE ORDER BY 1, 2, 3,4;
^
SELECT * from FKTABLE ORDER BY 1, 2, 3,4;
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: SELECT * from FKTABLE ORDER BY 1, 2, 3,4;
^
DROP TABLE FKTABLE;
ERROR: table "FKTABLE" does not exist
DROP TABLE PKTABLE;
ERROR: table "PKTABLE" does not exist
-- set null update / set default 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, ftest3 int, ftest4 int, CONSTRAINT constrname3
FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE
ON DELETE SET DEFAULT ON UPDATE SET NULL);
-- 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 Foreign Key values
INSERT INTO FKTABLE VALUES (1, 2, 3, 1);
INSERT INTO FKTABLE VALUES (2, 3, 4, 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: insert or update on table "FKTABLE" violates foreign key constraint "constrname3"
DETAIL: Key (ftest1, ftest2, ftest3)=(1, 2, 7) is not present in table "PKTABLE".
-- Show FKTABLE
SELECT * from FKTABLE ORDER BY 1, 2, 3,4;
ftest1 | ftest2 | ftest3 | ftest4
--------+--------+--------+--------
1 | 2 | 3 | 1
2 | 3 | 4 | 1
2 | | 3 | 3
| 2 | 3 | 2
| 2 | 7 | 4
| 3 | 4 | 5
(6 rows)
-- Try to update something that will set null
UPDATE PKTABLE set ptest2=5 where ptest2=2;
-- Try to update something that should not set null
UPDATE PKTABLE set ptest2=2 WHERE ptest2=3 and ptest1=1;
-- Show PKTABLE and FKTABLE
SELECT * from PKTABLE ORDER BY 1, 2, 3,4;
ptest1 | ptest2 | ptest3 | ptest4
--------+--------+--------+--------
1 | 2 | 3 | test2
1 | 5 | 3 | test1
2 | 3 | 4 | test3
2 | 4 | 5 | test4
(4 rows)
SELECT * from FKTABLE ORDER BY 1, 2, 3,4;
ftest1 | ftest2 | ftest3 | ftest4
--------+--------+--------+--------
1 | | 3 | 1
2 | 3 | 4 | 1
2 | | 3 | 3
| 2 | 3 | 2
| 2 | 7 | 4
| 3 | 4 | 5
(6 rows)
-- Try to delete something that should set default
DELETE FROM PKTABLE where ptest1=2 and ptest2=3 and ptest3=4;
-- Show PKTABLE and FKTABLE
SELECT * from PKTABLE ORDER BY 1, 2, 3,4;
ptest1 | ptest2 | ptest3 | ptest4
--------+--------+--------+--------
1 | 2 | 3 | test2
1 | 5 | 3 | test1
2 | 4 | 5 | test4
(3 rows)
SELECT * from FKTABLE ORDER BY 1, 2, 3,4;
ftest1 | ftest2 | ftest3 | ftest4
--------+--------+--------+--------
0 | | | 1
1 | | 3 | 1
2 | | 3 | 3
| 2 | 3 | 2
| 2 | 7 | 4
| 3 | 4 | 5
(6 rows)
-- Try to delete something that should not set default
DELETE FROM PKTABLE where ptest2=5;
-- Show PKTABLE and FKTABLE
SELECT * from PKTABLE ORDER BY 1, 2, 3,4;
ptest1 | ptest2 | ptest3 | ptest4
--------+--------+--------+--------
1 | 2 | 3 | test2
2 | 4 | 5 | test4
(2 rows)
SELECT * from FKTABLE ORDER BY 1, 2, 3,4;
ftest1 | ftest2 | ftest3 | ftest4
--------+--------+--------+--------
0 | | | 1
1 | | 3 | 1
2 | | 3 | 3
| 2 | 3 | 2
| 2 | 7 | 4
| 3 | 4 | 5
(6 rows)
DROP TABLE FKTABLE;
DROP TABLE PKTABLE;
-- set default update / set null delete
CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) ) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
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) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
-- Insert Primary Key values
INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1');
^
INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2');
^
INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3');
^
INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4');
^
INSERT INTO PKTABLE VALUES (2, -1, 5, 'test5');
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES (2, -1, 5, 'test5');
^
-- Insert Foreign Key values
INSERT INTO FKTABLE VALUES (1, 2, 3, 1);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (1, 2, 3, 1);
^
INSERT INTO FKTABLE VALUES (2, 3, 4, 1);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (2, 3, 4, 1);
^
INSERT INTO FKTABLE VALUES (2, 4, 5, 1);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (2, 4, 5, 1);
^
INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2);
^
INSERT INTO FKTABLE VALUES (2, NULL, 3, 3);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (2, NULL, 3, 3);
^
INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4);
^
INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5);
^
-- Insert a failed values
INSERT INTO FKTABLE VALUES (1, 2, 7, 6);
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES (1, 2, 7, 6);
^
-- Show FKTABLE
SELECT * from FKTABLE ORDER BY 1, 2, 3,4;
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: SELECT * from FKTABLE ORDER BY 1, 2, 3,4;
^
-- Try to update something that will fail
UPDATE PKTABLE set ptest2=5 where ptest2=2;
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: UPDATE PKTABLE set ptest2=5 where ptest2=2;
^
-- 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 on datanode1
LINE 1: UPDATE PKTABLE set ptest1=0, ptest2=5, ptest3=10 where ptest...
^
UPDATE PKTABLE set ptest2=10 where ptest2=4;
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: 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;
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: UPDATE PKTABLE set ptest2=2 WHERE ptest2=3 and ptest1=1;
^
-- Show PKTABLE and FKTABLE
SELECT * from PKTABLE ORDER BY 1, 2, 3,4;
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: SELECT * from PKTABLE ORDER BY 1, 2, 3,4;
^
SELECT * from FKTABLE ORDER BY 1, 2, 3,4;
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: SELECT * from FKTABLE ORDER BY 1, 2, 3,4;
^
-- 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 on datanode1
LINE 1: DELETE FROM PKTABLE where ptest1=2 and ptest2=3 and ptest3=4...
^
-- Show PKTABLE and FKTABLE
SELECT * from PKTABLE ORDER BY 1, 2, 3,4;
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: SELECT * from PKTABLE ORDER BY 1, 2, 3,4;
^
SELECT * from FKTABLE ORDER BY 1, 2, 3,4;
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: SELECT * from FKTABLE ORDER BY 1, 2, 3,4;
^
-- Try to delete something that should not set null
DELETE FROM PKTABLE where ptest2=5;
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: DELETE FROM PKTABLE where ptest2=5;
^
-- Show PKTABLE and FKTABLE
SELECT * from PKTABLE ORDER BY 1, 2, 3,4;
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: SELECT * from PKTABLE ORDER BY 1, 2, 3,4;
^
SELECT * from FKTABLE ORDER BY 1, 2, 3,4;
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: SELECT * from FKTABLE ORDER BY 1, 2, 3,4;
^
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"
CREATE TABLE FKTABLE_FAIL1 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest2) REFERENCES PKTABLE);
ERROR: column "ftest2" referenced in foreign key constraint does not exist
CREATE TABLE FKTABLE_FAIL2 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest1) REFERENCES PKTABLE(ptest2));
ERROR: column "ptest2" referenced in foreign key constraint does not exist
DROP TABLE FKTABLE_FAIL1;
ERROR: table "FKTABLE_FAIL1" does not exist
DROP TABLE FKTABLE_FAIL2;
ERROR: table "FKTABLE_FAIL2" does not exist
DROP TABLE PKTABLE;
-- Test for referencing column number smaller than referenced constraint
CREATE TABLE PKTABLE (ptest1 int, ptest2 int, UNIQUE(ptest1, ptest2));
NOTICE: CREATE TABLE / UNIQUE will create implicit index "PKTABLE_ptest1_ptest2_key" for table "PKTABLE"
CREATE TABLE FKTABLE_FAIL1 (ftest1 int REFERENCES pktable(ptest1));
ERROR: relation "pktable" does not exist
DROP TABLE FKTABLE_FAIL1;
ERROR: table "FKTABLE_FAIL1" does not exist
DROP TABLE PKTABLE;
--
-- Tests for mismatched types
--
-- Basic one column, two table setup
CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
INSERT INTO PKTABLE VALUES(42);
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES(42);
^
-- This next should fail, because int=inet does not exist
CREATE TABLE FKTABLE (ftest1 inet REFERENCES pktable);
ERROR: relation "pktable" does not exist
-- This should also fail for the same reason, but here we
-- give the column name
CREATE TABLE FKTABLE (ftest1 inet REFERENCES pktable(ptest1)) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
-- This should succeed, even though they are different types,
-- because int=int8 exists and is a member of the integer opfamily
CREATE TABLE FKTABLE (ftest1 int8 REFERENCES pktable) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
-- Check it actually works
INSERT INTO FKTABLE VALUES(42); -- should succeed
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES(42);
^
INSERT INTO FKTABLE VALUES(43); -- should fail
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES(43);
^
UPDATE FKTABLE SET ftest1 = ftest1; -- should succeed
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: UPDATE FKTABLE SET ftest1 = ftest1;
^
UPDATE FKTABLE SET ftest1 = ftest1 + 1; -- should fail
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: UPDATE FKTABLE SET ftest1 = ftest1 + 1;
^
DROP TABLE FKTABLE;
ERROR: table "FKTABLE" does not exist
-- This should fail, because we'd have to cast numeric to int which is
-- not an implicit coercion (or use numeric=numeric, but that's not part
-- of the integer opfamily)
CREATE TABLE FKTABLE (ftest1 numeric REFERENCES pktable) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
DROP TABLE PKTABLE;
ERROR: table "PKTABLE" does not exist
-- On the other hand, this should work because int implicitly promotes to
-- numeric, and we allow promotion on the FK side
CREATE TABLE PKTABLE (ptest1 numeric PRIMARY KEY) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
INSERT INTO PKTABLE VALUES(42);
ERROR: relation "PKTABLE" does not exist on datanode1
LINE 1: INSERT INTO PKTABLE VALUES(42);
^
CREATE TABLE FKTABLE (ftest1 int REFERENCES pktable) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
-- Check it actually works
INSERT INTO FKTABLE VALUES(42); -- should succeed
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES(42);
^
INSERT INTO FKTABLE VALUES(43); -- should fail
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: INSERT INTO FKTABLE VALUES(43);
^
UPDATE FKTABLE SET ftest1 = ftest1; -- should succeed
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: UPDATE FKTABLE SET ftest1 = ftest1;
^
UPDATE FKTABLE SET ftest1 = ftest1 + 1; -- should fail
ERROR: relation "FKTABLE" does not exist on datanode1
LINE 1: UPDATE FKTABLE SET ftest1 = ftest1 + 1;
^
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 inet, PRIMARY KEY(ptest1, ptest2)) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
-- This should fail, because we just chose really odd types
CREATE TABLE FKTABLE (ftest1 cidr, ftest2 timestamp, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
-- Again, so should this...
CREATE TABLE FKTABLE (ftest1 cidr, ftest2 timestamp, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable(ptest1, ptest2)) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
-- This fails because we mixed up the column ordering
CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest2, ftest1) REFERENCES pktable) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
-- As does this...
CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest2, ftest1) REFERENCES pktable(ptest1, ptest2)) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
-- And again..
CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable(ptest2, ptest1)) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
-- This works...
CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest2, ftest1) REFERENCES pktable(ptest2, ptest1)) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
DROP TABLE FKTABLE;
ERROR: table "FKTABLE" does not exist
-- As does this
CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable(ptest1, ptest2)) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
DROP TABLE FKTABLE;
ERROR: table "FKTABLE" does not exist
DROP TABLE PKTABLE;
ERROR: table "PKTABLE" does not exist
-- Two columns, same table
-- Make sure this still works...
CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest3,
ptest4) REFERENCES pktable(ptest1, ptest2));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "PKTABLE_pkey" for table "PKTABLE"
ERROR: relation "pktable" does not exist
DROP TABLE PKTABLE;
ERROR: table "PKTABLE" does not exist
-- And this,
CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest3,
ptest4) REFERENCES pktable);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "PKTABLE_pkey" for table "PKTABLE"
ERROR: relation "pktable" does not exist
DROP TABLE PKTABLE;
ERROR: table "PKTABLE" does not exist
-- This shouldn't (mixed up columns)
CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest3,
ptest4) REFERENCES pktable(ptest2, ptest1));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "PKTABLE_pkey" for table "PKTABLE"
ERROR: relation "pktable" does not exist
-- Nor should this... (same reason, we have 4,3 referencing 1,2 which mismatches types
CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest4,
ptest3) REFERENCES pktable(ptest1, ptest2));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "PKTABLE_pkey" for table "PKTABLE"
ERROR: relation "pktable" does not exist
-- Not this one either... Same as the last one except we didn't defined the columns being referenced.
CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest4,
ptest3) REFERENCES pktable);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "PKTABLE_pkey" for table "PKTABLE"
ERROR: relation "pktable" does not exist
--
-- Now some cases with inheritance
-- Basic 2 table case: 1 column of matching types.
create table pktable_base (base1 int not null) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
create table pktable (ptest1 int, primary key(base1), unique(base1, ptest1)) inherits (pktable_base) DISTRIBUTE BY REPLICATION;
ERROR: CREATE TABLE ... INHERITS is not yet supported.
create table fktable (ftest1 int references pktable(base1));
ERROR: relation "pktable" does not exist
-- now some ins, upd, del
insert into pktable(base1) values (1);
ERROR: relation "pktable" does not exist on datanode1
LINE 1: insert into pktable(base1) values (1);
^
insert into pktable(base1) values (2);
ERROR: relation "pktable" does not exist on datanode1
LINE 1: insert into pktable(base1) values (2);
^
-- let's insert a non-existent fktable value
insert into fktable(ftest1) values (3);
ERROR: relation "fktable" does not exist on datanode1
LINE 1: insert into fktable(ftest1) values (3);
^
-- let's make a valid row for that
insert into pktable(base1) values (3);
ERROR: relation "pktable" does not exist on datanode1
LINE 1: insert into pktable(base1) values (3);
^
insert into fktable(ftest1) values (3);
ERROR: relation "fktable" does not exist on datanode1
LINE 1: insert into fktable(ftest1) values (3);
^
-- let's try removing a row that should fail from pktable
delete from pktable where base1>2;
ERROR: relation "pktable" does not exist on datanode1
LINE 1: delete from pktable where base1>2;
^
-- okay, let's try updating all of the base1 values to *4
-- which should fail.
update pktable set base1=base1*4;
ERROR: relation "pktable" does not exist on datanode1
LINE 1: update pktable set base1=base1*4;
^
-- okay, let's try an update that should work.
update pktable set base1=base1*4 where base1<3;
ERROR: relation "pktable" does not exist on datanode1
LINE 1: update pktable set base1=base1*4 where base1<3;
^
-- and a delete that should work
delete from pktable where base1>3;
ERROR: relation "pktable" does not exist on datanode1
LINE 1: delete from pktable where base1>3;
^
-- cleanup
drop table fktable;
ERROR: table "fktable" does not exist
delete from pktable;
ERROR: relation "pktable" does not exist on datanode1
LINE 1: delete from pktable;
^
-- Now 2 columns 2 tables, matching types
create table fktable (ftest1 int, ftest2 int, foreign key(ftest1, ftest2) references pktable(base1, ptest1));
ERROR: relation "pktable" does not exist
-- now some ins, upd, del
insert into pktable(base1, ptest1) values (1, 1);
ERROR: relation "pktable" does not exist on datanode1
LINE 1: insert into pktable(base1, ptest1) values (1, 1);
^
insert into pktable(base1, ptest1) values (2, 2);
ERROR: relation "pktable" does not exist on datanode1
LINE 1: insert into pktable(base1, ptest1) values (2, 2);
^
-- let's insert a non-existent fktable value
insert into fktable(ftest1, ftest2) values (3, 1);
ERROR: relation "fktable" does not exist on datanode1
LINE 1: insert into fktable(ftest1, ftest2) values (3, 1);
^
-- let's make a valid row for that
insert into pktable(base1,ptest1) values (3, 1);
ERROR: relation "pktable" does not exist on datanode1
LINE 1: insert into pktable(base1,ptest1) values (3, 1);
^
insert into fktable(ftest1, ftest2) values (3, 1);
ERROR: relation "fktable" does not exist on datanode1
LINE 1: insert into fktable(ftest1, ftest2) values (3, 1);
^
-- let's try removing a row that should fail from pktable
delete from pktable where base1>2;
ERROR: relation "pktable" does not exist on datanode1
LINE 1: delete from pktable where base1>2;
^
-- okay, let's try updating all of the base1 values to *4
-- which should fail.
update pktable set base1=base1*4;
ERROR: relation "pktable" does not exist on datanode1
LINE 1: update pktable set base1=base1*4;
^
-- okay, let's try an update that should work.
update pktable set base1=base1*4 where base1<3;
ERROR: relation "pktable" does not exist on datanode1
LINE 1: update pktable set base1=base1*4 where base1<3;
^
-- and a delete that should work
delete from pktable where base1>3;
ERROR: relation "pktable" does not exist on datanode1
LINE 1: delete from pktable where base1>3;
^
-- cleanup
drop table fktable;
ERROR: table "fktable" does not exist
drop table pktable;
ERROR: table "pktable" does not exist
drop table pktable_base;
ERROR: table "pktable_base" does not exist
-- Now we'll do one all in 1 table with 2 columns of matching types
create table pktable_base(base1 int not null, base2 int) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
create table pktable(ptest1 int, ptest2 int, primary key(base1, ptest1), foreign key(base2, ptest2) references
pktable(base1, ptest1)) inherits (pktable_base) DISTRIBUTE BY REPLICATION;
ERROR: CREATE TABLE ... INHERITS is not yet supported.
insert into pktable (base1, ptest1, base2, ptest2) values (1, 1, 1, 1);
ERROR: relation "pktable" does not exist on datanode1
LINE 1: insert into pktable (base1, ptest1, base2, ptest2) values (1...
^
insert into pktable (base1, ptest1, base2, ptest2) values (2, 1, 1, 1);
ERROR: relation "pktable" does not exist on datanode1
LINE 1: insert into pktable (base1, ptest1, base2, ptest2) values (2...
^
insert into pktable (base1, ptest1, base2, ptest2) values (2, 2, 2, 1);
ERROR: relation "pktable" does not exist on datanode1
LINE 1: insert into pktable (base1, ptest1, base2, ptest2) values (2...
^
insert into pktable (base1, ptest1, base2, ptest2) values (1, 3, 2, 2);
ERROR: relation "pktable" does not exist on datanode1
LINE 1: insert into pktable (base1, ptest1, base2, ptest2) values (1...
^
-- fails (3,2) isn't in base1, ptest1
insert into pktable (base1, ptest1, base2, ptest2) values (2, 3, 3, 2);
ERROR: relation "pktable" does not exist on datanode1
LINE 1: insert into pktable (base1, ptest1, base2, ptest2) values (2...
^
-- fails (2,2) is being referenced
delete from pktable where base1=2;
ERROR: relation "pktable" does not exist on datanode1
LINE 1: delete from pktable where base1=2;
^
-- fails (1,1) is being referenced (twice)
update pktable set base1=3 where base1=1;
ERROR: relation "pktable" does not exist on datanode1
LINE 1: update pktable set base1=3 where base1=1;
^
-- this sequence of two deletes will work, since after the first there will be no (2,*) references
delete from pktable where base2=2;
ERROR: relation "pktable" does not exist on datanode1
LINE 1: delete from pktable where base2=2;
^
delete from pktable where base1=2;
ERROR: relation "pktable" does not exist on datanode1
LINE 1: delete from pktable where base1=2;
^
drop table pktable;
ERROR: table "pktable" does not exist
drop table pktable_base;
ERROR: table "pktable_base" does not exist
-- 2 columns (2 tables), mismatched types
create table pktable_base(base1 int not null) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
create table pktable(ptest1 inet, primary key(base1, ptest1)) inherits (pktable_base);
ERROR: CREATE TABLE ... INHERITS is not yet supported.
-- just generally bad types (with and without column references on the referenced table)
create table fktable(ftest1 cidr, ftest2 int[], foreign key (ftest1, ftest2) references pktable);
ERROR: relation "pktable" does not exist
create table fktable(ftest1 cidr, ftest2 int[], foreign key (ftest1, ftest2) references pktable(base1, ptest1)) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
-- let's mix up which columns reference which
create table fktable(ftest1 int, ftest2 inet, foreign key(ftest2, ftest1) references pktable) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
create table fktable(ftest1 int, ftest2 inet, foreign key(ftest2, ftest1) references pktable(base1, ptest1)) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
create table fktable(ftest1 int, ftest2 inet, foreign key(ftest1, ftest2) references pktable(ptest1, base1))distribute by hash(ftest1);
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
drop table pktable;
ERROR: table "pktable" does not exist
drop table pktable_base;
ERROR: table "pktable_base" does not exist
-- 2 columns (1 table), mismatched types
create table pktable_base(base1 int not null, base2 int);
create table pktable(ptest1 inet, ptest2 inet[], primary key(base1, ptest1), foreign key(base2, ptest2) references
pktable(base1, ptest1)) inherits (pktable_base);
ERROR: CREATE TABLE ... INHERITS is not yet supported.
create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(base2, ptest2) references
pktable(ptest1, base1)) inherits (pktable_base);
ERROR: CREATE TABLE ... INHERITS is not yet supported.
create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(ptest2, base2) references
pktable(base1, ptest1)) inherits (pktable_base);
ERROR: CREATE TABLE ... INHERITS is not yet supported.
create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(ptest2, base2) references
pktable(base1, ptest1)) inherits (pktable_base);
ERROR: CREATE TABLE ... INHERITS is not yet supported.
drop table pktable;
ERROR: table "pktable" does not exist
drop table pktable_base;
--
-- Deferrable constraints
-- (right now, only FOREIGN KEY constraints can be deferred)
--
-- deferrable, explicitly deferred
CREATE TABLE pktable (
id INT4 PRIMARY KEY,
other INT4
) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
CREATE TABLE fktable (
id INT4 PRIMARY KEY,
fk INT4 REFERENCES pktable DEFERRABLE
) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
-- default to immediate: should fail
INSERT INTO fktable VALUES (5, 10);
ERROR: relation "fktable" does not exist on datanode1
LINE 1: INSERT INTO fktable VALUES (5, 10);
^
-- explicitly defer the constraint
START TRANSACTION;
SET CONSTRAINTS ALL DEFERRED;
INSERT INTO fktable VALUES (10, 15);
ERROR: relation "fktable" does not exist on datanode1
LINE 1: INSERT INTO fktable VALUES (10, 15);
^
INSERT INTO pktable VALUES (15, 0); -- make the FK insert valid
ERROR: current transaction is aborted, commands ignored until end of transaction block, firstChar[Q]
COMMIT;
DROP TABLE fktable, pktable;
ERROR: table "fktable" does not exist
-- deferrable, initially deferred
CREATE TABLE pktable (
id INT4 PRIMARY KEY,
other INT4
) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
CREATE TABLE fktable (
id INT4 PRIMARY KEY,
fk INT4 REFERENCES pktable DEFERRABLE INITIALLY DEFERRED
) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
-- default to deferred, should succeed
START TRANSACTION;
INSERT INTO fktable VALUES (100, 200);
ERROR: relation "fktable" does not exist on datanode1
LINE 1: INSERT INTO fktable VALUES (100, 200);
^
INSERT INTO pktable VALUES (200, 500); -- make the FK insert valid
ERROR: current transaction is aborted, commands ignored until end of transaction block, firstChar[Q]
COMMIT;
-- default to deferred, explicitly make immediate
START TRANSACTION;
SET CONSTRAINTS ALL IMMEDIATE;
-- should fail
INSERT INTO fktable VALUES (500, 1000);
ERROR: relation "fktable" does not exist on datanode1
LINE 1: INSERT INTO fktable VALUES (500, 1000);
^
COMMIT;
DROP TABLE fktable, pktable;
ERROR: table "fktable" does not exist
-- tricky behavior: according to SQL99, if a deferred constraint is set
-- to 'immediate' mode, it should be checked for validity *immediately*,
-- not when the current transaction commits (i.e. the mode change applies
-- retroactively)
CREATE TABLE pktable (
id INT4 PRIMARY KEY,
other INT4
) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
CREATE TABLE fktable (
id INT4 PRIMARY KEY,
fk INT4 REFERENCES pktable DEFERRABLE
) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
START TRANSACTION;
SET CONSTRAINTS ALL DEFERRED;
-- should succeed, for now
INSERT INTO fktable VALUES (1000, 2000);
ERROR: relation "fktable" does not exist on datanode1
LINE 1: INSERT INTO fktable VALUES (1000, 2000);
^
-- should cause transaction abort, due to preceding error
SET CONSTRAINTS ALL IMMEDIATE;
ERROR: current transaction is aborted, commands ignored until end of transaction block, firstChar[Q]
INSERT INTO pktable VALUES (2000, 3); -- too late
ERROR: current transaction is aborted, commands ignored until end of transaction block, firstChar[Q]
COMMIT;
DROP TABLE fktable, pktable;
ERROR: table "fktable" does not exist
-- deferrable, initially deferred
CREATE TABLE pktable (
id INT4 PRIMARY KEY,
other INT4
) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
CREATE TABLE fktable (
id INT4 PRIMARY KEY,
fk INT4 REFERENCES pktable DEFERRABLE INITIALLY DEFERRED
) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
START TRANSACTION;
-- no error here
INSERT INTO fktable VALUES (100, 200);
ERROR: relation "fktable" does not exist on datanode1
LINE 1: INSERT INTO fktable VALUES (100, 200);
^
-- error here on commit
COMMIT;
DROP TABLE pktable, fktable;
ERROR: table "pktable" does not exist
-- test notice about expensive referential integrity checks,
-- where the index cannot be used because of type incompatibilities.
-- Enforce use of COMMIT instead of 2PC for temporary objects
CREATE TEMP TABLE pktable (
id1 INT4 PRIMARY KEY,
id2 VARCHAR(4) UNIQUE,
id3 REAL UNIQUE,
UNIQUE(id1, id2, id3)
) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
CREATE TEMP TABLE fktable (
x1 INT4 REFERENCES pktable(id1),
x2 VARCHAR(4) REFERENCES pktable(id2),
x3 REAL REFERENCES pktable(id3),
x4 TEXT,
x5 INT2
) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
-- check individual constraints with alter table.
-- should fail
-- varchar does not promote to real
ALTER TABLE fktable ADD CONSTRAINT fk_2_3
FOREIGN KEY (x2) REFERENCES pktable(id3);
ERROR: relation "fktable" does not exist
-- nor to int4
ALTER TABLE fktable ADD CONSTRAINT fk_2_1
FOREIGN KEY (x2) REFERENCES pktable(id1);
ERROR: relation "fktable" does not exist
-- real does not promote to int4
ALTER TABLE fktable ADD CONSTRAINT fk_3_1
FOREIGN KEY (x3) REFERENCES pktable(id1);
ERROR: relation "fktable" does not exist
-- int4 does not promote to text
ALTER TABLE fktable ADD CONSTRAINT fk_1_2
FOREIGN KEY (x1) REFERENCES pktable(id2);
ERROR: relation "fktable" does not exist
-- should succeed
-- int4 promotes to real
ALTER TABLE fktable ADD CONSTRAINT fk_1_3
FOREIGN KEY (x1) REFERENCES pktable(id3);
ERROR: relation "fktable" does not exist
-- text is compatible with varchar
ALTER TABLE fktable ADD CONSTRAINT fk_4_2
FOREIGN KEY (x4) REFERENCES pktable(id2);
ERROR: relation "fktable" does not exist
-- int2 is part of integer opfamily as of 8.0
ALTER TABLE fktable ADD CONSTRAINT fk_5_1
FOREIGN KEY (x5) REFERENCES pktable(id1);
ERROR: relation "fktable" does not exist
-- check multikey cases, especially out-of-order column lists
-- these should work
ALTER TABLE fktable ADD CONSTRAINT fk_123_123
FOREIGN KEY (x1,x2,x3) REFERENCES pktable(id1,id2,id3);
ERROR: relation "fktable" does not exist
ALTER TABLE fktable ADD CONSTRAINT fk_213_213
FOREIGN KEY (x2,x1,x3) REFERENCES pktable(id2,id1,id3);
ERROR: relation "fktable" does not exist
ALTER TABLE fktable ADD CONSTRAINT fk_253_213
FOREIGN KEY (x2,x5,x3) REFERENCES pktable(id2,id1,id3);
ERROR: relation "fktable" does not exist
-- these should fail
ALTER TABLE fktable ADD CONSTRAINT fk_123_231
FOREIGN KEY (x1,x2,x3) REFERENCES pktable(id2,id3,id1);
ERROR: relation "fktable" does not exist
ALTER TABLE fktable ADD CONSTRAINT fk_241_132
FOREIGN KEY (x2,x4,x1) REFERENCES pktable(id1,id3,id2);
ERROR: relation "fktable" does not exist
DROP TABLE pktable, fktable;
ERROR: table "pktable" does not exist
-- test a tricky case: we can elide firing the FK check trigger during
-- an UPDATE if the UPDATE did not change the foreign key
-- field. However, we can't do this if our transaction was the one that
-- created the updated row and the trigger is deferred, since our UPDATE
-- will have invalidated the original newly-inserted tuple, and therefore
-- cause the on-INSERT RI trigger not to be fired.
CREATE TEMP TABLE pktable (
id int primary key,
other int
) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
CREATE TEMP TABLE fktable (
id int primary key,
fk int references pktable deferrable initially deferred
) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
INSERT INTO pktable VALUES (5, 10);
ERROR: relation "pktable" does not exist on datanode1
LINE 1: INSERT INTO pktable VALUES (5, 10);
^
START TRANSACTION;
-- doesn't match PK, but no error yet
INSERT INTO fktable VALUES (0, 20);
ERROR: relation "fktable" does not exist on datanode1
LINE 1: INSERT INTO fktable VALUES (0, 20);
^
-- don't change FK
UPDATE fktable SET id = id + 1;
ERROR: current transaction is aborted, commands ignored until end of transaction block, firstChar[Q]
-- should catch error from initial INSERT
COMMIT;
-- check same case when insert is in a different subtransaction than update
START TRANSACTION;
-- doesn't match PK, but no error yet
INSERT INTO fktable VALUES (0, 20);
ERROR: relation "fktable" does not exist on datanode1
LINE 1: INSERT INTO fktable VALUES (0, 20);
^
-- UPDATE will be in a subxact
SAVEPOINT savept1;
ERROR: current transaction is aborted, commands ignored until end of transaction block, firstChar[Q]
-- don't change FK
UPDATE fktable SET id = id + 1;
ERROR: current transaction is aborted, commands ignored until end of transaction block, firstChar[Q]
-- should catch error from initial INSERT
COMMIT;
START TRANSACTION;
-- INSERT will be in a subxact
SAVEPOINT savept1;
-- doesn't match PK, but no error yet
INSERT INTO fktable VALUES (0, 20);
ERROR: relation "fktable" does not exist on datanode1
LINE 1: INSERT INTO fktable VALUES (0, 20);
^
RELEASE SAVEPOINT savept1;
ERROR: current transaction is aborted, commands ignored until end of transaction block, firstChar[Q]
-- don't change FK
UPDATE fktable SET id = id + 1;
ERROR: current transaction is aborted, commands ignored until end of transaction block, firstChar[Q]
-- should catch error from initial INSERT
COMMIT;
START TRANSACTION;
-- doesn't match PK, but no error yet
INSERT INTO fktable VALUES (0, 20);
ERROR: relation "fktable" does not exist on datanode1
LINE 1: INSERT INTO fktable VALUES (0, 20);
^
-- UPDATE will be in a subxact
SAVEPOINT savept1;
ERROR: current transaction is aborted, commands ignored until end of transaction block, firstChar[Q]
-- don't change FK
UPDATE fktable SET id = id + 1;
ERROR: current transaction is aborted, commands ignored until end of transaction block, firstChar[Q]
-- Roll back the UPDATE
ROLLBACK TO savept1;
ERROR: no such savepoint
-- should catch error from initial INSERT
COMMIT;
-- test order of firing of FK triggers when several RI-induced changes need to
-- be made to the same row. This was broken by subtransaction-related
-- changes in 8.0.
CREATE TEMP TABLE users (
id INT PRIMARY KEY,
name VARCHAR NOT NULL
) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
INSERT INTO users VALUES (1, 'Jozko');
ERROR: relation "users" does not exist on datanode1
LINE 1: INSERT INTO users VALUES (1, 'Jozko');
^
INSERT INTO users VALUES (2, 'Ferko');
ERROR: relation "users" does not exist on datanode1
LINE 1: INSERT INTO users VALUES (2, 'Ferko');
^
INSERT INTO users VALUES (3, 'Samko');
ERROR: relation "users" does not exist on datanode1
LINE 1: INSERT INTO users VALUES (3, 'Samko');
^
CREATE TEMP TABLE tasks (
id INT PRIMARY KEY,
owner INT REFERENCES users ON UPDATE CASCADE ON DELETE SET NULL,
worker INT REFERENCES users ON UPDATE CASCADE ON DELETE SET NULL,
checked_by INT REFERENCES users ON UPDATE CASCADE ON DELETE SET NULL
) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
INSERT INTO tasks VALUES (1,1,NULL,NULL);
ERROR: relation "tasks" does not exist on datanode1
LINE 1: INSERT INTO tasks VALUES (1,1,NULL,NULL);
^
INSERT INTO tasks VALUES (2,2,2,NULL);
ERROR: relation "tasks" does not exist on datanode1
LINE 1: INSERT INTO tasks VALUES (2,2,2,NULL);
^
INSERT INTO tasks VALUES (3,3,3,3);
ERROR: relation "tasks" does not exist on datanode1
LINE 1: INSERT INTO tasks VALUES (3,3,3,3);
^
SELECT * FROM tasks ORDER BY 1, 2, 3,4;
ERROR: relation "tasks" does not exist on datanode1
LINE 1: SELECT * FROM tasks ORDER BY 1, 2, 3,4;
^
UPDATE users SET id = 4 WHERE id = 3;
ERROR: relation "users" does not exist on datanode1
LINE 1: UPDATE users SET id = 4 WHERE id = 3;
^
SELECT * FROM tasks ORDER BY 1, 2, 3,4;
ERROR: relation "tasks" does not exist on datanode1
LINE 1: SELECT * FROM tasks ORDER BY 1, 2, 3,4;
^
DELETE FROM users WHERE id = 4;
ERROR: relation "users" does not exist on datanode1
LINE 1: DELETE FROM users WHERE id = 4;
^
SELECT * FROM tasks ORDER BY 1, 2, 3,4;
ERROR: relation "tasks" does not exist on datanode1
LINE 1: SELECT * FROM tasks ORDER BY 1, 2, 3,4;
^
-- could fail with only 2 changes to make, if row was already updated
START TRANSACTION;
UPDATE tasks set id=id WHERE id=2;
ERROR: relation "tasks" does not exist on datanode1
LINE 1: UPDATE tasks set id=id WHERE id=2;
^
SELECT * FROM tasks ORDER BY 1, 2, 3,4;
ERROR: current transaction is aborted, commands ignored until end of transaction block, firstChar[Q]
DELETE FROM users WHERE id = 2;
ERROR: current transaction is aborted, commands ignored until end of transaction block, firstChar[Q]
SELECT * FROM tasks ORDER BY 1, 2, 3,4;
ERROR: current transaction is aborted, commands ignored until end of transaction block, firstChar[Q]
COMMIT;
--
-- Test self-referential FK with CASCADE (bug #6268)
--
create temp table selfref (
a int primary key,
b int,
foreign key (b) references selfref (a)
on update cascade on delete cascade
) DISTRIBUTE BY REPLICATION;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
insert into selfref (a, b)
values
(0, 0),
(1, 1);
ERROR: relation "selfref" does not exist on datanode1
LINE 1: insert into selfref (a, b)
^
start transaction;
update selfref set a = 123 where a = 0;
ERROR: relation "selfref" does not exist on datanode1
LINE 1: update selfref set a = 123 where a = 0;
^
select a, b from selfref;
ERROR: current transaction is aborted, commands ignored until end of transaction block, firstChar[Q]
update selfref set a = 456 where a = 123;
ERROR: current transaction is aborted, commands ignored until end of transaction block, firstChar[Q]
select a, b from selfref;
ERROR: current transaction is aborted, commands ignored until end of transaction block, firstChar[Q]
commit;