1-- 2-- FOREIGN_KEY_TO_REFERENCE_TABLE 3-- 4 5CREATE SCHEMA fkey_reference_table; 6SET search_path TO 'fkey_reference_table'; 7SET citus.shard_replication_factor TO 1; 8SET citus.shard_count TO 8; 9SET citus.next_shard_id TO 7000000; 10SET citus.next_placement_id TO 7000000; 11 12SET client_min_messages TO ERROR; 13 14CREATE TYPE foreign_details AS (name text, relid text, refd_relid text); 15 16CREATE VIEW table_fkeys_in_workers AS 17SELECT 18(json_populate_record(NULL::foreign_details, 19 json_array_elements_text((run_command_on_workers( $$ 20 SELECT 21 COALESCE(json_agg(row_to_json(d)), '[]'::json) 22 FROM 23 ( 24 SELECT 25 distinct name, 26 relid::regclass::text, 27 refd_relid::regclass::text 28 FROM 29 table_fkey_cols 30 WHERE 31 "schema" = 'fkey_reference_table' 32 ) 33 d $$ )).RESULT::json )::json )).* ; 34 35CREATE TABLE referenced_table(id int UNIQUE, test_column int); 36SELECT create_reference_table('referenced_table'); 37 38-- we still do not support update/delete operations through foreign constraints if the foreign key includes the distribution column 39-- All should fail 40CREATE TABLE referencing_table(id int, ref_id int); 41SELECT create_distributed_table('referencing_table', 'ref_id'); 42ALTER TABLE referencing_table ADD CONSTRAINT fkey_ref FOREIGN KEY(ref_id) REFERENCES referenced_table(id) ON DELETE SET NULL; 43DROP TABLE referencing_table; 44 45CREATE TABLE referencing_table(id int, ref_id int, FOREIGN KEY(ref_id) REFERENCES referenced_table(id) ON DELETE SET NULL); 46SELECT create_distributed_table('referencing_table', 'ref_id'); 47DROP TABLE referencing_table; 48 49CREATE TABLE referencing_table(id int, ref_id int); 50SELECT create_distributed_table('referencing_table', 'ref_id'); 51ALTER TABLE referencing_table ADD CONSTRAINT fkey_ref FOREIGN KEY(ref_id) REFERENCES referenced_table(id) ON DELETE SET DEFAULT; 52DROP TABLE referencing_table; 53 54CREATE TABLE referencing_table(id int, ref_id int, FOREIGN KEY(ref_id) REFERENCES referenced_table(id) ON DELETE SET DEFAULT); 55SELECT create_distributed_table('referencing_table', 'ref_id'); 56DROP TABLE referencing_table; 57 58CREATE TABLE referencing_table(id int, ref_id int); 59SELECT create_distributed_table('referencing_table', 'ref_id'); 60ALTER TABLE referencing_table ADD CONSTRAINT fkey_ref FOREIGN KEY(ref_id) REFERENCES referenced_table(id) ON UPDATE SET NULL; 61DROP TABLE referencing_table; 62 63BEGIN; 64 CREATE TABLE referencing_table(id int, ref_id int, FOREIGN KEY(ref_id) REFERENCES referenced_table(id) ON UPDATE SET NULL); 65 SELECT create_distributed_table('referencing_table', 'ref_id'); 66ROLLBACK; 67 68-- try with multiple columns including the distribution column 69DROP TABLE referenced_table; 70CREATE TABLE referenced_table(id int, test_column int, PRIMARY KEY(id, test_column)); 71SELECT create_reference_table('referenced_table'); 72 73CREATE TABLE referencing_table(id int, ref_id int); 74SELECT create_distributed_table('referencing_table', 'ref_id'); 75ALTER TABLE referencing_table ADD CONSTRAINT fkey_ref FOREIGN KEY(id, ref_id) REFERENCES referenced_table(id, test_column) ON UPDATE SET DEFAULT; 76DROP TABLE referencing_table; 77 78CREATE TABLE referencing_table(id int, ref_id int, FOREIGN KEY(id, ref_id) REFERENCES referenced_table(id, test_column) ON UPDATE SET DEFAULT); 79SELECT create_distributed_table('referencing_table', 'ref_id'); 80DROP TABLE referencing_table; 81 82CREATE TABLE referencing_table(id int, ref_id int); 83SELECT create_distributed_table('referencing_table', 'ref_id'); 84ALTER TABLE referencing_table ADD CONSTRAINT fkey_ref FOREIGN KEY(id, ref_id) REFERENCES referenced_table(id, test_column) ON UPDATE CASCADE; 85DROP TABLE referencing_table; 86 87BEGIN; 88 CREATE TABLE referencing_table(id int, ref_id int, FOREIGN KEY(id, ref_id) REFERENCES referenced_table(id, test_column) ON UPDATE CASCADE); 89 SELECT create_distributed_table('referencing_table', 'ref_id'); 90ROLLBACK; 91 92-- all of the above is supported if the foreign key does not include distribution column 93DROP TABLE referenced_table; 94CREATE TABLE referenced_table(id int, test_column int, PRIMARY KEY(id)); 95SELECT create_reference_table('referenced_table'); 96 97CREATE TABLE referencing_table(id int, ref_id int); 98SELECT create_distributed_table('referencing_table', 'ref_id'); 99ALTER TABLE referencing_table ADD CONSTRAINT fkey_ref FOREIGN KEY(id) REFERENCES referenced_table(id) ON DELETE SET NULL; 100SELECT COUNT(*) FROM table_fkeys_in_workers WHERE relid LIKE 'fkey_reference_table.%' AND refd_relid LIKE 'fkey_reference_table.%'; 101DROP TABLE referencing_table; 102 103CREATE TABLE referencing_table(id int, ref_id int, FOREIGN KEY(id) REFERENCES referenced_table(id) ON DELETE SET NULL); 104SELECT create_distributed_table('referencing_table', 'ref_id'); 105SELECT COUNT(*) FROM table_fkeys_in_workers WHERE relid LIKE 'fkey_reference_table.%' AND refd_relid LIKE 'fkey_reference_table.%'; 106DROP TABLE referencing_table; 107 108CREATE TABLE referencing_table(id int, ref_id int); 109SELECT create_distributed_table('referencing_table', 'ref_id'); 110ALTER TABLE referencing_table ADD CONSTRAINT fkey_ref FOREIGN KEY(id) REFERENCES referenced_table(id) ON DELETE SET DEFAULT; 111SELECT COUNT(*) FROM table_fkeys_in_workers WHERE relid LIKE 'fkey_reference_table.%' AND refd_relid LIKE 'fkey_reference_table.%'; 112DROP TABLE referencing_table; 113 114BEGIN; 115 CREATE TABLE referencing_table(id int, ref_id int, FOREIGN KEY(id) REFERENCES referenced_table(id) ON DELETE SET DEFAULT); 116 SELECT create_distributed_table('referencing_table', 'ref_id'); 117COMMIT; 118SELECT COUNT(*) FROM table_fkeys_in_workers WHERE relid LIKE 'fkey_reference_table.%' AND refd_relid LIKE 'fkey_reference_table.%'; 119DROP TABLE referencing_table; 120 121CREATE TABLE referencing_table(id int, ref_id int); 122SELECT create_distributed_table('referencing_table', 'ref_id'); 123ALTER TABLE referencing_table ADD CONSTRAINT fkey_ref FOREIGN KEY(id) REFERENCES referenced_table(id) ON UPDATE SET NULL; 124SELECT COUNT(*) FROM table_fkeys_in_workers WHERE relid LIKE 'fkey_reference_table.%' AND refd_relid LIKE 'fkey_reference_table.%'; 125DROP TABLE referencing_table; 126 127CREATE TABLE referencing_table(id int, ref_id int); 128SELECT create_distributed_table('referencing_table', 'ref_id'); 129ALTER TABLE referencing_table ADD CONSTRAINT fkey_ref FOREIGN KEY(id) REFERENCES referenced_table(id) ON UPDATE SET DEFAULT; 130SELECT COUNT(*) FROM table_fkeys_in_workers WHERE relid LIKE 'fkey_reference_table.%' AND refd_relid LIKE 'fkey_reference_table.%'; 131DROP TABLE referencing_table; 132 133CREATE TABLE referencing_table(id int, ref_id int); 134SELECT create_distributed_table('referencing_table', 'ref_id'); 135ALTER TABLE referencing_table ADD CONSTRAINT fkey_ref FOREIGN KEY(id) REFERENCES referenced_table(id) ON UPDATE CASCADE; 136SELECT COUNT(*) FROM table_fkeys_in_workers WHERE relid LIKE 'fkey_reference_table.%' AND refd_relid LIKE 'fkey_reference_table.%'; 137DROP TABLE referencing_table; 138 139-- check if we can add the foreign key while adding the column 140CREATE TABLE referencing_table(id int, ref_id int); 141SELECT create_distributed_table('referencing_table', 'ref_id'); 142ALTER TABLE referencing_table ADD COLUMN referencing int REFERENCES referenced_table(id) ON UPDATE CASCADE; 143SELECT COUNT(*) FROM table_fkeys_in_workers WHERE relid LIKE 'fkey_reference_table.%' AND refd_relid LIKE 'fkey_reference_table.%'; 144DROP TABLE referencing_table; 145 146-- foreign keys are only supported when the replication factor = 1 147SET citus.shard_replication_factor TO 2; 148CREATE TABLE referencing_table(id int, ref_id int); 149SELECT create_distributed_table('referencing_table', 'ref_id'); 150ALTER TABLE referencing_table ADD CONSTRAINT fkey_ref FOREIGN KEY (id) REFERENCES referenced_table(id); 151SELECT COUNT(*) FROM table_fkeys_in_workers WHERE relid LIKE 'fkey_reference_table.%' AND refd_relid LIKE 'fkey_reference_table.%'; 152DROP TABLE referencing_table; 153 154-- should fail when we add the column as well 155CREATE TABLE referencing_table(id int, ref_id int); 156SELECT create_distributed_table('referencing_table', 'ref_id'); 157ALTER TABLE referencing_table ADD COLUMN referencing_col int REFERENCES referenced_table(id) ON DELETE SET NULL; 158SELECT COUNT(*) FROM table_fkeys_in_workers WHERE relid LIKE 'fkey_reference_table.%' AND refd_relid LIKE 'fkey_reference_table.%'; 159DROP TABLE referencing_table; 160SET citus.shard_replication_factor TO 1; 161 162-- simple create_distributed_table should work in/out transactions on tables with foreign key to reference tables 163CREATE TABLE referencing_table(id int, ref_id int, FOREIGN KEY (id) REFERENCES referenced_table(id)); 164SELECT create_distributed_table('referencing_table', 'ref_id'); 165SELECT COUNT(*) FROM table_fkeys_in_workers WHERE relid LIKE 'fkey_reference_table.%' AND refd_relid LIKE 'fkey_reference_table.%'; 166DROP TABLE referencing_table; 167DROP TABLE referenced_table; 168 169BEGIN; 170 CREATE TABLE referenced_table(id int, test_column int, PRIMARY KEY(id)); 171 SELECT create_reference_table('referenced_table'); 172 CREATE TABLE referencing_table(id int, ref_id int, FOREIGN KEY (id) REFERENCES referenced_table(id)); 173 SELECT create_distributed_table('referencing_table', 'ref_id'); 174COMMIT; 175SELECT COUNT(*) FROM table_fkeys_in_workers WHERE relid LIKE 'fkey_reference_table.%' AND refd_relid LIKE 'fkey_reference_table.%'; 176DROP TABLE referencing_table; 177 178-- foreign keys are supported either in between distributed tables including the 179-- distribution column or from distributed tables to reference tables. 180CREATE TABLE referencing_table(id int, ref_id int); 181SELECT create_distributed_table('referencing_table', 'ref_id', 'append'); 182ALTER TABLE referencing_table ADD CONSTRAINT fkey_ref FOREIGN KEY (id) REFERENCES referenced_table(id); 183SELECT * FROM table_fkeys_in_workers WHERE name LIKE 'fkey_ref%' ORDER BY 1,2,3; 184DROP TABLE referencing_table; 185 186CREATE TABLE referencing_table(id int, ref_id int); 187SELECT create_distributed_table('referencing_table', 'ref_id', 'range'); 188ALTER TABLE referencing_table ADD CONSTRAINT fkey_ref FOREIGN KEY (id) REFERENCES referenced_table(id); 189SELECT * FROM table_fkeys_in_workers WHERE name LIKE 'fkey_ref%' ORDER BY 1,2,3; 190DROP TABLE referencing_table; 191DROP TABLE referenced_table; 192 193-- test foreign constraint with correct conditions 194CREATE TABLE referenced_table(id int UNIQUE, test_column int, PRIMARY KEY(id, test_column)); 195CREATE TABLE referencing_table(id int, ref_id int); 196SELECT create_reference_table('referenced_table'); 197SELECT create_distributed_table('referencing_table', 'id'); 198ALTER TABLE referencing_table ADD CONSTRAINT fkey_ref FOREIGN KEY (ref_id) REFERENCES referenced_table(id); 199 200 201-- test inserts 202-- test insert to referencing table while there is NO corresponding value in referenced table 203INSERT INTO referencing_table VALUES(1, 1); 204 205-- test insert to referencing while there is corresponding value in referenced table 206INSERT INTO referenced_table SELECT x, x from generate_series(1,1000) as f(x); 207INSERT INTO referencing_table SELECT x, x from generate_series(1,500) as f(x); 208 209 210-- test deletes 211-- test delete from referenced table while there is corresponding value in referencing table 212DELETE FROM referenced_table WHERE id > 3; 213 214-- test delete from referenced table while there is NO corresponding value in referencing table 215DELETE FROM referenced_table WHERE id = 501; 216 217-- test cascading truncate 218TRUNCATE referenced_table CASCADE; 219SELECT count(*) FROM referencing_table; 220 221-- drop table for next tests 222DROP TABLE referencing_table; 223DROP TABLE referenced_table; 224 225-- self referencing foreign key on reference tables are allowed 226-- TODO try create_reference_table with already created foreign key. 227CREATE TABLE referenced_table(id int, test_column int, PRIMARY KEY(id)); 228CREATE TABLE referencing_table(id int, ref_id int); 229SELECT create_reference_table('referenced_table'); 230SELECT create_reference_table('referencing_table'); 231-- self referencing foreign key 232ALTER TABLE referenced_table ADD CONSTRAINT fkey_ref FOREIGN KEY (test_column) REFERENCES referenced_table(id); 233-- foreign Keys from reference table to reference table are allowed 234ALTER TABLE referencing_table ADD CONSTRAINT fkey_ref FOREIGN KEY(id) REFERENCES referenced_table(id) ON UPDATE CASCADE; 235 236DROP TABLE referencing_table; 237DROP TABLE referenced_table; 238 239-- cascades on delete with different schemas 240CREATE SCHEMA referenced_schema; 241CREATE SCHEMA referencing_schema; 242CREATE TABLE referenced_schema.referenced_table(id int UNIQUE, test_column int, PRIMARY KEY(id, test_column)); 243CREATE TABLE referencing_schema.referencing_table(id int, ref_id int); 244SELECT create_reference_table('referenced_schema.referenced_table'); 245SELECT create_distributed_table('referencing_schema.referencing_table', 'id'); 246ALTER TABLE referencing_schema.referencing_table ADD CONSTRAINT fkey_ref FOREIGN KEY (ref_id) REFERENCES referenced_schema.referenced_table(id) ON DELETE CASCADE; 247 248INSERT INTO referenced_schema.referenced_table SELECT x, x from generate_series(1,1000) as f(x); 249INSERT INTO referencing_schema.referencing_table SELECT x, x from generate_series(1,1000) as f(x); 250 251DELETE FROM referenced_schema.referenced_table WHERE id > 800; 252SELECT count(*) FROM referencing_schema.referencing_table; 253 254DROP SCHEMA referenced_schema CASCADE; 255DROP SCHEMA referencing_schema CASCADE; 256 257-- on delete set update cascades properly 258CREATE TABLE referenced_table(test_column int, test_column2 int, PRIMARY KEY(test_column)); 259CREATE TABLE referencing_table(id int, ref_id int DEFAULT 1); 260SELECT create_reference_table('referenced_table'); 261SELECT create_distributed_table('referencing_table', 'id'); 262ALTER TABLE referencing_table ADD CONSTRAINT fkey_ref FOREIGN KEY (ref_id) REFERENCES referenced_table(test_column) ON DELETE SET DEFAULT; 263 264INSERT INTO referenced_table SELECT x, x FROM generate_series(1,1000) AS f(x); 265INSERT INTO referencing_table SELECT x, x FROM generate_series(1,1000) AS f(x); 266 267DELETE FROM referenced_table WHERE test_column > 800; 268SELECT count(*) FROM referencing_table WHERE ref_id = 1; 269 270DROP TABLE referencing_table; 271DROP TABLE referenced_table; 272 273-- foreign key as composite key 274CREATE TYPE fkey_reference_table.composite AS (key1 int, key2 int); 275 276CREATE TABLE referenced_table(test_column composite, PRIMARY KEY(test_column)); 277CREATE TABLE referencing_table(id int, referencing_composite composite); 278SELECT create_reference_table('referenced_table'); 279SELECT create_distributed_table('referencing_table', 'id'); 280ALTER TABLE referencing_table ADD CONSTRAINT fkey_ref FOREIGN KEY (referencing_composite) REFERENCES referenced_table(test_column) ON DELETE CASCADE; 281 282INSERT INTO referenced_table SELECT (x+1, x+1)::composite FROM generate_series(1,1000) AS f(x); 283INSERT INTO referencing_table SELECT x, (x+1, x+1)::composite FROM generate_series(1,1000) AS f(x); 284 285DELETE FROM referenced_table WHERE (test_column).key1 > 900; 286SELECT count(*) FROM referencing_table; 287 288DROP TABLE referenced_table CASCADE; 289DROP TABLE referencing_table CASCADE; 290 291-- In the following test, we'll use a SERIAL column as the referenced column 292-- in the foreign constraint. We'll first show that and insert on non-serial 293-- column successfully inserts into the serial and referenced column. 294-- Accordingly, the inserts into the referencing table which references to the 295-- serial column will be successful. 296CREATE TABLE referenced_table(test_column SERIAL PRIMARY KEY, test_column2 int); 297CREATE TABLE referencing_table(id int, ref_id int); 298SELECT create_reference_table('referenced_table'); 299SELECT create_distributed_table('referencing_table', 'id'); 300ALTER TABLE referencing_table ADD CONSTRAINT fkey_ref FOREIGN KEY (ref_id) REFERENCES referenced_table(test_column) ON DELETE CASCADE; 301 302INSERT INTO referenced_table(test_column2) SELECT x FROM generate_series(1,1000) AS f(x); 303INSERT INTO referencing_table SELECT x, x FROM generate_series(1,1000) AS f(x); 304 305DELETE FROM referenced_table WHERE test_column2 > 10; 306SELECT count(*) FROM referencing_table; 307 308DROP TABLE referenced_table CASCADE; 309DROP TABLE referencing_table CASCADE; 310 311-- In the following test, we'll use a SERIAL column as the referencing column 312-- in the foreign constraint. We'll first show that the values that exist 313-- in the referenced tables are successfully generated by the serial column 314-- and inserted to the distributed table. However, if the values that are generated 315-- by serial column do not exist on the referenced table, the query fails. 316CREATE TABLE referenced_table(test_column int PRIMARY KEY, test_column2 int); 317CREATE TABLE referencing_table(id int, ref_id SERIAL); 318SELECT create_reference_table('referenced_table'); 319SELECT create_distributed_table('referencing_table', 'id'); 320ALTER TABLE referencing_table ADD CONSTRAINT fkey_ref FOREIGN KEY (ref_id) REFERENCES referenced_table(test_column) ON DELETE CASCADE; 321 322INSERT INTO referenced_table SELECT x,x FROM generate_series(1,1000) AS f(x); 323-- Success for existing inserts 324INSERT INTO referencing_table(id) SELECT x FROM generate_series(1,1000) AS f(x); 325-- Fails for non existing value inserts (serial is already incremented) 326INSERT INTO referencing_table(id) SELECT x FROM generate_series(1,10) AS f(x); 327 328DROP TABLE referenced_table CASCADE; 329DROP TABLE referencing_table CASCADE; 330 331-- In the following test, we'll use a SERIAL column as the referencing column 332-- and referenced columns in a foreign constraint. We'll first show that the 333-- the inserts into referenced column will successfully generate and insert 334-- data into serial column. Then, we will be successfully insert the same amount 335-- of data into referencing table. However, if the values that are generated 336-- by serial column do not exist on the referenced table, the query fails. 337CREATE TABLE referenced_table(test_column SERIAL PRIMARY KEY, test_column2 int); 338CREATE TABLE referencing_table(id int, ref_id SERIAL); 339SELECT create_reference_table('referenced_table'); 340SELECT create_distributed_table('referencing_table', 'id'); 341ALTER TABLE referencing_table ADD CONSTRAINT fkey_ref FOREIGN KEY (ref_id) REFERENCES referenced_table(test_column) ON DELETE CASCADE; 342 343INSERT INTO referenced_table(test_column2) SELECT x FROM generate_series(1,1000) AS f(x); 344-- Success for existing values 345INSERT INTO referencing_table(id) SELECT x FROM generate_series(1,1000) AS f(x); 346-- Fails for non existing value inserts (serial is already incremented) 347INSERT INTO referencing_table(id) SELECT x FROM generate_series(1,10) AS f(x); 348 349DROP TABLE referenced_table CASCADE; 350DROP TABLE referencing_table CASCADE; 351-- In the following test, we use a volatile function in the referencing 352-- column in a foreign constraint. We show that if the data exists in the 353-- referenced table, we can successfully use volatile functions with 354-- foreign constraints. 355CREATE TABLE referenced_table(test_column int, test_column2 int, PRIMARY KEY(test_column)); 356CREATE TABLE referencing_table(id int, ref_id int DEFAULT -1); 357SELECT create_reference_table('referenced_table'); 358SELECT create_distributed_table('referencing_table', 'id'); 359ALTER TABLE referencing_table ADD CONSTRAINT fkey_ref FOREIGN KEY (ref_id) REFERENCES referenced_table(test_column) ON DELETE SET DEFAULT; 360 361INSERT INTO referenced_table SELECT x, x FROM generate_series(0,1000) AS f(x); 362INSERT INTO referencing_table SELECT x,(random()*1000)::int FROM generate_series(0,1000) AS f(x); 363 364DROP TABLE referenced_table CASCADE; 365DROP TABLE referencing_table CASCADE; 366 367-- In the following tests, we create a foreign constraint with 368-- ON UPDATE CASCADE and see if it works properly with cascading upsert 369CREATE TABLE referenced_table(test_column int, test_column2 int, PRIMARY KEY(test_column)); 370CREATE TABLE referencing_table(id int, ref_id int DEFAULT -1); 371SELECT create_reference_table('referenced_table'); 372SELECT create_distributed_table('referencing_table', 'id'); 373ALTER TABLE referencing_table ADD CONSTRAINT fkey_ref FOREIGN KEY (ref_id) REFERENCES referenced_table(test_column) ON UPDATE CASCADE; 374 375INSERT INTO referenced_table SELECT x, x FROM generate_series(0,1000) AS f(x); 376INSERT INTO referencing_table SELECT x, x FROM generate_series(0,1000) AS f(x); 377 378INSERT INTO referenced_table VALUES (1,2), (2,3), (3,4), (4,5) 379ON CONFLICT (test_column) 380DO UPDATE 381 SET test_column = -1 * EXCLUDED.test_column; 382 383SELECT * FROM referencing_table WHERE ref_id < 0 ORDER BY 1; 384 385DROP TABLE referenced_table CASCADE; 386DROP TABLE referencing_table CASCADE; 387 388-- create_distributed_table should fail for tables with data if fkey exists to reference table 389CREATE TABLE referenced_table(test_column int, test_column2 int, PRIMARY KEY(test_column)); 390CREATE TABLE referencing_table(id int, ref_id int DEFAULT -1, FOREIGN KEY (ref_id) REFERENCES referenced_table(test_column) ON UPDATE CASCADE); 391INSERT INTO referenced_table VALUES (1,1), (2,2), (3,3); 392INSERT INTO referencing_table VALUES (1,1), (2,2), (3,3); 393SELECT create_reference_table('referenced_table'); 394 395DROP TABLE referenced_table CASCADE; 396DROP TABLE referencing_table CASCADE; 397 398-- Chained references 399-- In the following test, we create foreign keys from one column in a distributed 400-- table to two reference tables. We expect to see that even if a data exist in 401-- one reference table, it is not going to be inserted in to referencing table 402-- because of lack of the key in the other table. Data can only be inserted into 403-- referencing table if it exists in both referenced tables. 404-- Additionally, delete or update in one referenced table should cascade properly. 405CREATE TABLE referenced_table(test_column int, test_column2 int, PRIMARY KEY(test_column)); 406CREATE TABLE referenced_table2(test_column int, test_column2 int, PRIMARY KEY(test_column2)); 407CREATE TABLE referencing_table(id int, ref_id int); 408SELECT create_reference_table('referenced_table'); 409SELECT create_reference_table('referenced_table2'); 410SELECT create_distributed_table('referencing_table', 'id'); 411ALTER TABLE referencing_table ADD CONSTRAINT fkey_ref FOREIGN KEY (id) REFERENCES referenced_table(test_column) ON DELETE CASCADE; 412ALTER TABLE referencing_table ADD CONSTRAINT foreign_key_2 FOREIGN KEY (id) REFERENCES referenced_table2(test_column2) ON DELETE CASCADE; 413 414SELECT COUNT(*) FROM table_fkeys_in_workers WHERE relid LIKE 'fkey_reference_table.%' AND refd_relid LIKE 'fkey_reference_table.%'; 415 416INSERT INTO referenced_table SELECT x, x+1 FROM generate_series(0,1000) AS f(x); 417INSERT INTO referenced_table2 SELECT x, x+1 FROM generate_series(500,1500) AS f(x); 418-- should fail 419INSERT INTO referencing_table SELECT x, x+1 FROM generate_series(0,1500) AS f(x); 420-- should fail 421INSERT INTO referencing_table SELECT x, x+1 FROM generate_series(0,400) AS f(x); 422-- should fail 423INSERT INTO referencing_table SELECT x, x+1 FROM generate_series(1000,1400) AS f(x); 424-- should succeed 425INSERT INTO referencing_table SELECT x, x+1 FROM generate_series(600,900) AS f(x); 426 427SELECT count(*) FROM referencing_table; 428DELETE FROM referenced_table WHERE test_column < 700; 429SELECT count(*) FROM referencing_table; 430DELETE FROM referenced_table2 WHERE test_column2 > 800; 431SELECT count(*) FROM referencing_table; 432 433DROP TABLE referenced_table CASCADE; 434DROP TABLE referenced_table2 CASCADE; 435DROP TABLE referencing_table CASCADE; 436 437-- check if the above fkeys are created with create_distributed_table 438CREATE TABLE referenced_table(test_column int, test_column2 int, PRIMARY KEY(test_column)); 439CREATE TABLE referenced_table2(test_column int, test_column2 int, PRIMARY KEY(test_column2)); 440CREATE TABLE referencing_table(id int, ref_id int, FOREIGN KEY (id) REFERENCES referenced_table(test_column) ON DELETE CASCADE, FOREIGN KEY (id) REFERENCES referenced_table2(test_column2) ON DELETE CASCADE); 441SELECT create_reference_table('referenced_table'); 442SELECT create_reference_table('referenced_table2'); 443SELECT create_distributed_table('referencing_table', 'id'); 444 445SELECT count(*) FROM table_fkeys_in_workers WHERE relid LIKE 'fkey_reference_table.%' AND refd_relid LIKE 'fkey_reference_table.%'; 446 447\set VERBOSITY terse 448DROP TABLE referenced_table CASCADE; 449DROP TABLE referenced_table2 CASCADE; 450DROP TABLE referencing_table CASCADE; 451 452-- In the following test, we create foreign keys from two columns in a distributed 453-- table to two reference tables separately. We expect to see that even if a data 454-- exist in one reference table for one column, it is not going to be inserted in 455-- to referencing table because the other constraint doesn't hold. Data can only 456-- be inserted into referencing table if both columns exist in respective columns 457-- in referenced tables. 458-- Additionally, delete or update in one referenced table should cascade properly. 459CREATE TABLE referenced_table(test_column int, test_column2 int, PRIMARY KEY(test_column)); 460CREATE TABLE referenced_table2(test_column int, test_column2 int, PRIMARY KEY(test_column2)); 461CREATE TABLE referencing_table(id int, ref_id int); 462SELECT create_reference_table('referenced_table'); 463SELECT create_reference_table('referenced_table2'); 464SELECT create_distributed_table('referencing_table', 'id'); 465 466BEGIN; 467 ALTER TABLE referencing_table ADD CONSTRAINT fkey_ref FOREIGN KEY (id) REFERENCES referenced_table(test_column) ON DELETE CASCADE; 468 ALTER TABLE referencing_table ADD CONSTRAINT foreign_key_2 FOREIGN KEY (ref_id) REFERENCES referenced_table2(test_column2) ON DELETE CASCADE; 469COMMIT; 470 471SELECT COUNT(*) FROM table_fkeys_in_workers WHERE relid LIKE 'fkey_reference_table.%' AND refd_relid LIKE 'fkey_reference_table.%'; 472 473INSERT INTO referenced_table SELECT x, x+1 FROM generate_series(0,1000) AS f(x); 474INSERT INTO referenced_table2 SELECT x, x+1 FROM generate_series(500,1500) AS f(x); 475-- should fail 476INSERT INTO referencing_table SELECT x, x+1 FROM generate_series(0,1500) AS f(x); 477-- should fail 478INSERT INTO referencing_table SELECT x, x+1 FROM generate_series(0,400) AS f(x); 479-- should fail 480INSERT INTO referencing_table SELECT x, x+1 FROM generate_series(1000,1400) AS f(x); 481-- should succeed 482INSERT INTO referencing_table SELECT x, x+501 FROM generate_series(0,1000) AS f(x); 483 484SELECT count(*) FROM referencing_table; 485DELETE FROM referenced_table WHERE test_column < 700; 486SELECT count(*) FROM referencing_table; 487DELETE FROM referenced_table2 WHERE test_column2 > 800; 488SELECT count(*) FROM referencing_table; 489 490DROP TABLE referenced_table CASCADE; 491DROP TABLE referenced_table2 CASCADE; 492DROP TABLE referencing_table CASCADE; 493 494-- check if the above fkeys are created when create_distributed_table is used for 1 foreign key and alter table for the other 495CREATE TABLE referenced_table(test_column int, test_column2 int, PRIMARY KEY(test_column)); 496CREATE TABLE referenced_table2(test_column int, test_column2 int, PRIMARY KEY(test_column2)); 497CREATE TABLE referencing_table(id int, ref_id int, FOREIGN KEY (id) REFERENCES referenced_table(test_column) ON DELETE CASCADE); 498 499BEGIN; 500 SELECT create_reference_table('referenced_table'); 501 SELECT create_reference_table('referenced_table2'); 502 SELECT create_distributed_table('referencing_table', 'id'); 503 ALTER TABLE referencing_table ADD CONSTRAINT foreign_key_2 FOREIGN KEY (ref_id) REFERENCES referenced_table2(test_column2) ON DELETE CASCADE; 504COMMIT; 505 506SELECT count(*) FROM table_fkeys_in_workers WHERE relid LIKE 'fkey_reference_table.%' AND refd_relid LIKE 'fkey_reference_table.%'; 507 508DROP TABLE referenced_table CASCADE; 509DROP TABLE referenced_table2 CASCADE; 510DROP TABLE referencing_table CASCADE; 511\set VERBOSITY default 512 513 514-- two distributed tables are referencing to one reference table and 515-- in the same time the distributed table 2 is referencing to 516-- distributed table 1. Thus, we have a triangular 517-- distributed table 1 has a foreign key from the distribution column to reference table 518-- distributed table 2 has a foreign key from a non-distribution column to reference table 519-- distributed table 2 has a foreign key to distributed table 1 on the distribution column 520-- We show that inserts into distributed table 2 will fail if the data does not exist in distributed table 1 521-- Delete from reference table cascades to both of the distributed tables properly 522CREATE TABLE referenced_table(test_column int, test_column2 int UNIQUE, PRIMARY KEY(test_column)); 523CREATE TABLE referencing_table(id int PRIMARY KEY, ref_id int); 524CREATE TABLE referencing_table2(id int, ref_id int); 525SELECT create_reference_table('referenced_table'); 526SELECT create_distributed_table('referencing_table', 'id'); 527SELECT create_distributed_table('referencing_table2', 'id'); 528BEGIN; 529SET LOCAL citus.multi_shard_modify_mode TO 'sequential'; 530ALTER TABLE referencing_table ADD CONSTRAINT fkey_ref FOREIGN KEY (id) REFERENCES referenced_table(test_column) ON DELETE CASCADE; 531ALTER TABLE referencing_table2 ADD CONSTRAINT fkey_ref FOREIGN KEY (ref_id) REFERENCES referenced_table(test_column2) ON DELETE CASCADE; 532ALTER TABLE referencing_table2 ADD CONSTRAINT fkey_ref_to_dist FOREIGN KEY (id) REFERENCES referencing_table(id) ON DELETE CASCADE; 533COMMIT; 534 535SELECT COUNT(*) FROM table_fkeys_in_workers WHERE relid LIKE 'fkey_reference_table.%' AND refd_relid LIKE 'fkey_reference_table.%'; 536 537INSERT INTO referenced_table SELECT x, x+1 FROM generate_series(0,1000) AS f(x); 538-- should fail 539INSERT INTO referencing_table2 SELECT x, x+1 FROM generate_series(0,100) AS f(x); 540-- should succeed 541INSERT INTO referencing_table SELECT x, x+1 FROM generate_series(0,400) AS f(x); 542-- should fail 543INSERT INTO referencing_table2 SELECT x, x+1 FROM generate_series(200,500) AS f(x); 544-- should succeed 545INSERT INTO referencing_table2 SELECT x, x+1 FROM generate_series(0,300) AS f(x); 546 547DELETE FROM referenced_table WHERE test_column < 200; 548SELECT count(*) FROM referencing_table; 549SELECT count(*) FROM referencing_table2; 550DELETE FROM referencing_table WHERE id > 200; 551SELECT count(*) FROM referencing_table2; 552 553\set VERBOSITY terse 554DROP TABLE referenced_table CASCADE; 555DROP TABLE referencing_table CASCADE; 556DROP TABLE referencing_table2 CASCADE; 557\set VERBOSITY default 558 559-- Check if the above fkeys are created with create_distributed_table 560CREATE TABLE referenced_table(test_column int, test_column2 int UNIQUE, PRIMARY KEY(test_column)); 561SELECT create_reference_table('referenced_table'); 562 563SELECT count(*) FROM table_fkeys_in_workers WHERE relid LIKE 'fkey_reference_table.%' AND refd_relid LIKE 'fkey_reference_table.%'; 564 565\set VERBOSITY terse 566DROP TABLE referenced_table CASCADE; 567\set VERBOSITY default 568 569-- In this test we have a chained relationship in form of 570-- distributed table (referencing_referencing_table) has a foreign key with two columns 571-- to another distributed table (referencing_table) 572-- referencing_table has another foreign key with 2 columns to referenced_table. 573-- We will show that a cascading delete on referenced_table reaches to referencing_referencing_table. 574CREATE TABLE referenced_table(test_column int, test_column2 int, PRIMARY KEY(test_column, test_column2)); 575CREATE TABLE referencing_table(id int, ref_id int, ref_id2 int, PRIMARY KEY(id, ref_id)); 576CREATE TABLE referencing_referencing_table(id int, ref_id int, FOREIGN KEY (id, ref_id) REFERENCES referencing_table(id, ref_id) ON DELETE CASCADE); 577SELECT create_reference_table('referenced_table'); 578SELECT create_distributed_table('referencing_table', 'id'); 579SELECT create_distributed_table('referencing_referencing_table', 'id'); 580ALTER TABLE referencing_table ADD CONSTRAINT fkey_ref FOREIGN KEY (ref_id, ref_id2) REFERENCES referenced_table(test_column, test_column2) ON DELETE CASCADE; 581 582SELECT COUNT(*) FROM table_fkeys_in_workers WHERE relid LIKE 'fkey_reference_table.referencing%'; 583 584INSERT INTO referenced_table SELECT x, x+1 FROM generate_series(1,1000) AS f(x); 585INSERT INTO referencing_table SELECT x, x+1, x+2 FROM generate_series(1,999) AS f(x); 586INSERT INTO referencing_referencing_table SELECT x, x+1 FROM generate_series(1,999) AS f(x); 587 588DELETE FROM referenced_table WHERE test_column > 800; 589SELECT max(ref_id) FROM referencing_referencing_table; 590 591DROP TABLE referenced_table CASCADE; 592DROP TABLE referencing_table CASCADE; 593DROP TABLE referencing_referencing_table; 594 595BEGIN; 596 CREATE TABLE test_table_1(id int PRIMARY KEY); 597 SELECT create_reference_table('test_table_1'); 598 599 CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int); 600 601 ALTER TABLE test_table_2 ADD CONSTRAINT c_check FOREIGN KEY (value_1) REFERENCES test_table_1(id); 602 603 DROP TABLE test_table_1, test_table_2; 604COMMIT; 605 606BEGIN; 607 CREATE TABLE test_table_1(id int PRIMARY KEY, value_1 int); 608 609 CREATE TABLE test_table_2(id int PRIMARY KEY); 610 SELECT create_reference_table('test_table_2'); 611 612 ALTER TABLE test_table_1 ADD CONSTRAINT c_check FOREIGN KEY (value_1) REFERENCES test_table_2(id); 613 614 DROP TABLE test_table_2 CASCADE; 615ROLLBACK; 616 617BEGIN; 618 619 CREATE TABLE test_table_1(id int PRIMARY KEY); 620 INSERT INTO test_table_1 SELECT i FROM generate_series(0,100) i; 621 622 CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id)); 623 INSERT INTO test_table_2 SELECT i, i FROM generate_series(0,100) i; 624 625 SELECT create_reference_table('test_table_1'); 626 DROP TABLE test_table_2, test_table_1; 627COMMIT; 628 629-- make sure that other DDLs/DMLs also work fine 630BEGIN; 631 CREATE TABLE test_table_1(id int PRIMARY KEY); 632 CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id)); 633 634 SELECT create_reference_table('test_table_1'); 635 SELECT create_distributed_table('test_table_2', 'id'); 636 637 CREATE INDEX i1 ON test_table_1(id); 638 ALTER TABLE test_table_2 ADD CONSTRAINT check_val CHECK (id > 0); 639 640 DROP TABLE test_table_2, test_table_1; 641COMMIT; 642 643-- The following tests check if the DDLs affecting foreign keys work as expected 644-- check if we can drop the foreign constraint 645CREATE TABLE test_table_1(id int PRIMARY KEY); 646CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id)); 647 648SELECT create_reference_table('test_table_1'); 649SELECT create_distributed_table('test_table_2', 'id'); 650SELECT count(*) FROM table_fkeys_in_workers WHERE relid LIKE 'fkey_reference_table.%' AND refd_relid LIKE 'fkey_reference_table.%'; 651 652ALTER TABLE test_table_2 DROP CONSTRAINT test_table_2_value_1_fkey; 653SELECT count(*) FROM table_fkeys_in_workers WHERE relid LIKE 'fkey_reference_table.%' AND refd_relid LIKE 'fkey_reference_table.%'; 654 655DROP TABLE test_table_1, test_table_2; 656 657-- check if we can drop the foreign constraint in a transaction right after ADD CONSTRAINT 658BEGIN; 659 CREATE TABLE test_table_1(id int PRIMARY KEY); 660 CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int); 661 662 SELECT create_reference_table('test_table_1'); 663 SELECT create_distributed_table('test_table_2', 'id'); 664 665 ALTER TABLE test_table_2 ADD CONSTRAINT foreign_key FOREIGN KEY(value_1) REFERENCES test_table_1(id); 666 ALTER TABLE test_table_2 DROP CONSTRAINT test_table_2_value_1_fkey; 667COMMIT; 668 669SELECT count(*) FROM table_fkeys_in_workers WHERE relid LIKE 'fkey_reference_table.%' AND refd_relid LIKE 'fkey_reference_table.%'; 670DROP TABLE test_table_1, test_table_2; 671 672-- check if we can drop the primary key which cascades to the foreign key 673CREATE TABLE test_table_1(id int PRIMARY KEY); 674CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id)); 675 676SELECT create_reference_table('test_table_1'); 677SELECT create_distributed_table('test_table_2', 'id'); 678 679ALTER TABLE test_table_1 DROP CONSTRAINT test_table_1_pkey CASCADE; 680SELECT count(*) FROM table_fkeys_in_workers WHERE relid LIKE 'fkey_reference_table.%' AND refd_relid LIKE 'fkey_reference_table.%'; 681DROP TABLE test_table_1, test_table_2; 682 683-- check if we can drop the primary key which cascades to the foreign key in a transaction block 684BEGIN; 685 CREATE TABLE test_table_1(id int PRIMARY KEY); 686 CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id)); 687 688 SELECT create_reference_table('test_table_1'); 689 SELECT create_distributed_table('test_table_2', 'id'); 690 691 ALTER TABLE test_table_1 DROP CONSTRAINT test_table_1_pkey CASCADE; 692COMMIT; 693 694SELECT count(*) FROM table_fkeys_in_workers WHERE relid LIKE 'fkey_reference_table.%' AND refd_relid LIKE 'fkey_reference_table.%'; 695DROP TABLE test_table_1, test_table_2; 696 697-- check if we can drop the column which foreign key is referencing from 698CREATE TABLE test_table_1(id int PRIMARY KEY, id2 int); 699CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id)); 700 701SELECT create_reference_table('test_table_1'); 702SELECT create_distributed_table('test_table_2', 'id'); 703 704ALTER TABLE test_table_2 DROP COLUMN value_1; 705SELECT count(*) FROM table_fkeys_in_workers WHERE relid LIKE 'fkey_reference_table.%' AND refd_relid LIKE 'fkey_reference_table.%'; 706DROP TABLE test_table_1, test_table_2; 707 708-- check if we can drop the column which foreign key is referencing from in a transaction block 709CREATE TABLE test_table_1(id int PRIMARY KEY, id2 int); 710CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id)); 711 712BEGIN; 713 SELECT create_reference_table('test_table_1'); 714 SELECT create_distributed_table('test_table_2', 'id'); 715 716 ALTER TABLE test_table_2 DROP COLUMN value_1; 717COMMIT; 718SELECT count(*) FROM table_fkeys_in_workers WHERE relid LIKE 'fkey_reference_table.%' AND refd_relid LIKE 'fkey_reference_table.%'; 719DROP TABLE test_table_1, test_table_2; 720 721-- check if we can drop the column which foreign key is referencing to 722CREATE TABLE test_table_1(id int PRIMARY KEY, id2 int); 723CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id)); 724 725SELECT create_reference_table('test_table_1'); 726SELECT create_distributed_table('test_table_2', 'id'); 727 728ALTER TABLE test_table_1 DROP COLUMN id CASCADE; 729SELECT count(*) FROM table_fkeys_in_workers WHERE relid LIKE 'fkey_reference_table.%' AND refd_relid LIKE 'fkey_reference_table.%'; 730DROP TABLE test_table_1, test_table_2; 731 732-- check if we can drop the column which foreign key is referencing from in a transaction block 733CREATE TABLE test_table_1(id int PRIMARY KEY, id2 int); 734CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id)); 735 736BEGIN; 737 SELECT create_reference_table('test_table_1'); 738 SELECT create_distributed_table('test_table_2', 'id'); 739 740 ALTER TABLE test_table_1 DROP COLUMN id CASCADE; 741COMMIT; 742SELECT count(*) FROM table_fkeys_in_workers WHERE relid LIKE 'fkey_reference_table.%' AND refd_relid LIKE 'fkey_reference_table.%'; 743DROP TABLE test_table_1, test_table_2; 744 745-- check if we can alter the column type which foreign key is referencing to 746CREATE TABLE test_table_1(id int PRIMARY KEY, id2 int); 747CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id)); 748 749SELECT create_reference_table('test_table_1'); 750SELECT create_distributed_table('test_table_2', 'id'); 751INSERT INTO test_table_1 VALUES (1,1), (2,2), (3,3); 752INSERT INTO test_table_2 VALUES (1,1), (2,2), (3,3); 753 754-- should succeed 755ALTER TABLE test_table_2 ALTER COLUMN value_1 SET DATA TYPE bigint; 756ALTER TABLE test_table_1 ALTER COLUMN id SET DATA TYPE bigint; 757 758INSERT INTO test_table_1 VALUES (2147483648,4); 759INSERT INTO test_table_2 VALUES (4,2147483648); 760-- should fail since there is a bigint out of integer range > (2^32 - 1) 761ALTER TABLE test_table_2 ALTER COLUMN value_1 SET DATA TYPE int; 762SELECT count(*) FROM table_fkeys_in_workers WHERE relid LIKE 'fkey_reference_table.%' AND refd_relid LIKE 'fkey_reference_table.%'; 763DROP TABLE test_table_1 CASCADE; 764DROP TABLE test_table_2; 765 766-- check if we can alter the column type and drop it which foreign key is referencing to in a transaction block 767CREATE TABLE test_table_1(id int PRIMARY KEY); 768CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id)); 769 770BEGIN; 771 SELECT create_reference_table('test_table_1'); 772 SELECT create_distributed_table('test_table_2', 'id'); 773 774 ALTER TABLE test_table_2 ALTER COLUMN value_1 SET DATA TYPE bigint; 775 ALTER TABLE test_table_1 DROP COLUMN id CASCADE; 776COMMIT; 777 778SELECT count(*) FROM table_fkeys_in_workers WHERE relid LIKE 'fkey_reference_table.%' AND refd_relid LIKE 'fkey_reference_table.%'; 779DROP TABLE test_table_1, test_table_2; 780 781-- check if we can TRUNCATE the referenced table 782CREATE TABLE test_table_1(id int PRIMARY KEY); 783CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id)); 784SELECT create_reference_table('test_table_1'); 785SELECT create_distributed_table('test_table_2', 'id'); 786 787INSERT INTO test_table_1 VALUES (1),(2),(3); 788INSERT INTO test_table_2 VALUES (1,1),(2,2),(3,3); 789TRUNCATE test_table_1 CASCADE; 790 791SELECT * FROM test_table_2; 792DROP TABLE test_table_1, test_table_2; 793 794-- check if we can TRUNCATE the referenced table in a transaction 795CREATE TABLE test_table_1(id int PRIMARY KEY); 796CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id)); 797SELECT create_reference_table('test_table_1'); 798SELECT create_distributed_table('test_table_2', 'id'); 799 800INSERT INTO test_table_1 VALUES (1),(2),(3); 801INSERT INTO test_table_2 VALUES (1,1),(2,2),(3,3); 802 803BEGIN; 804 TRUNCATE test_table_1 CASCADE; 805COMMIT; 806SELECT * FROM test_table_2; 807DROP TABLE test_table_1, test_table_2; 808 809-- check if we can TRUNCATE the referenced table in a transaction after inserts 810CREATE TABLE test_table_1(id int PRIMARY KEY); 811CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id)); 812 813BEGIN; 814 SELECT create_reference_table('test_table_1'); 815 SELECT create_distributed_table('test_table_2', 'id'); 816 817 INSERT INTO test_table_1 VALUES (1),(2),(3); 818 INSERT INTO test_table_2 VALUES (1,1),(2,2),(3,3); 819 TRUNCATE test_table_1 CASCADE; 820COMMIT; 821 822SELECT * FROM test_table_2; 823DROP TABLE test_table_1, test_table_2; 824 825-- check if we can TRUNCATE the referencing table 826CREATE TABLE test_table_1(id int PRIMARY KEY); 827CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id)); 828SELECT create_reference_table('test_table_1'); 829SELECT create_distributed_table('test_table_2', 'id'); 830 831INSERT INTO test_table_1 VALUES (1),(2),(3); 832INSERT INTO test_table_2 VALUES (1,1),(2,2),(3,3); 833TRUNCATE test_table_2 CASCADE; 834 835SELECT * FROM test_table_2; 836SELECT * FROM test_table_1; 837DROP TABLE test_table_1, test_table_2; 838 839-- check if we can TRUNCATE the referencing table in a transaction 840CREATE TABLE test_table_1(id int PRIMARY KEY); 841CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id)); 842SELECT create_reference_table('test_table_1'); 843SELECT create_distributed_table('test_table_2', 'id'); 844 845INSERT INTO test_table_1 VALUES (1),(2),(3); 846INSERT INTO test_table_2 VALUES (1,1),(2,2),(3,3); 847BEGIN; 848 TRUNCATE test_table_2 CASCADE; 849COMMIT; 850 851SELECT * FROM test_table_2; 852SELECT * FROM test_table_1; 853DROP TABLE test_table_1, test_table_2; 854 855-- check if we successfuly set multi_shard_modify_mode to sequential after sequentially running DDLs 856-- in transaction since the upcoming DDLs need to run sequentially. 857CREATE TABLE test_table_1(id int PRIMARY KEY); 858CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int); 859CREATE TABLE test_table_3(id int PRIMARY KEY, value_1 int); 860SELECT create_reference_table('test_table_1'); 861SELECT create_distributed_table('test_table_2', 'id'); 862SELECT create_distributed_table('test_table_3', 'id'); 863BEGIN; 864 ALTER TABLE test_table_2 ADD CONSTRAINT fkey FOREIGN KEY (value_1) REFERENCES test_table_1(id); 865 ALTER TABLE test_table_3 ADD COLUMN test_column int; 866 ALTER TABLE test_table_1 DROP COLUMN id CASCADE; 867 ALTER TABLE test_table_1 ADD COLUMN id int; 868COMMIT; 869DROP TABLE test_table_1, test_table_2, test_table_3; 870-- NOTE: Postgres does not support foreign keys on partitioned tables currently. 871-- However, we can create foreign keys to/from the partitions themselves. 872-- The following tests chech if we create the foreign constraints in partitions properly. 873CREATE TABLE referenced_table(id int PRIMARY KEY, test_column int); 874CREATE TABLE referencing_table(id int, value_1 int) PARTITION BY RANGE (value_1); 875CREATE TABLE referencing_table_0 PARTITION OF referencing_table FOR VALUES FROM (0) TO (2); 876CREATE TABLE referencing_table_2 PARTITION OF referencing_table FOR VALUES FROM (2) TO (4); 877CREATE TABLE referencing_table_4 PARTITION OF referencing_table FOR VALUES FROM (4) TO (6); 878 879-- partitioned tables are not supported as reference tables 880select create_reference_table('referencing_table'); 881 882-- partitioned tables are supported as hash distributed table 883SELECT create_reference_table('referenced_table'); 884SELECT create_distributed_table('referencing_table', 'id'); 885 886-- add foreign constraints in between partitions 887ALTER TABLE referencing_table_0 ADD CONSTRAINT pkey PRIMARY KEY (id); 888ALTER TABLE referencing_table_4 ADD CONSTRAINT fkey FOREIGN KEY (id) REFERENCES referencing_table_0; 889-- add foreign constraint from a partition to reference table 890ALTER TABLE referencing_table_4 ADD CONSTRAINT fkey_to_ref FOREIGN KEY (value_1) REFERENCES referenced_table; 891-- should fail since the data will flow to partitioning_test_4 and it has a foreign constraint to partitioning_test_0 on id column 892INSERT INTO referencing_table VALUES (0, 5); 893-- should succeed on partitioning_test_0 894INSERT INTO referencing_table VALUES (0, 1); 895SELECT * FROM referencing_table; 896-- should fail since partitioning_test_4 has foreign constraint to referenced_table on value_1 column 897INSERT INTO referencing_table VALUES (0, 5); 898INSERT INTO referenced_table VALUES(5,5); 899-- should succeed since both of the foreign constraints are positive 900INSERT INTO referencing_table VALUES (0, 5); 901 902-- TRUNCATE should work in any way 903TRUNCATE referencing_table, referenced_table; 904TRUNCATE referenced_table, referencing_table; 905 906BEGIN; 907 TRUNCATE referencing_table, referenced_table; 908 ALTER TABLE referencing_table ADD COLUMN x INT; 909 SELECT * FROM referencing_table; 910ROLLBACK; 911 912BEGIN; 913 TRUNCATE referenced_table, referencing_table; 914 ALTER TABLE referencing_table ADD COLUMN x INT; 915 SELECT * FROM referencing_table; 916ROLLBACK; 917 918DROP TABLE referenced_table CASCADE; 919DROP TABLE referencing_table; 920 921-- tests specific to an edgecase in citus 8.x where it was possible to create foreign keys 922-- in between colocation groups due to a bug of foreign key to reference tables 923CREATE TABLE t1 (a int PRIMARY KEY, b text); 924CREATE TABLE t2 (a bigint PRIMARY KEY, b text); 925CREATE TABLE r1 (a int PRIMARY KEY, b text); 926 927SELECT create_distributed_table('t1', 'a'); 928SELECT create_distributed_table('t2', 'a'); 929SELECT create_reference_table('r1'); 930 931-- this always fails as it should be 932ALTER TABLE t1 ADD CONSTRAINT c1 FOREIGN KEY (a) REFERENCES t2(a); 933 934-- after we create a foreign key to the reference table, that has a lower order by name, 935-- we would have been able to create a FK to non-colocated tables 936ALTER TABLE t1 ADD CONSTRAINT c2 FOREIGN KEY (a) REFERENCES r1(a); 937ALTER TABLE t1 ADD CONSTRAINT c3 FOREIGN KEY (a) REFERENCES t2(a); 938 939 940DROP SCHEMA fkey_reference_table CASCADE; 941SET search_path TO DEFAULT; 942RESET client_min_messages; 943