1# ALTER TABLE - Add foreign keys with concurrent reads 2# 3# ADD CONSTRAINT uses ShareRowExclusiveLock so we mix writes with it 4# to see what works or waits. 5 6setup 7{ 8 CREATE TABLE a (i int PRIMARY KEY); 9 CREATE TABLE b (a_id int); 10 INSERT INTO a VALUES (0), (1), (2), (3); 11 INSERT INTO b SELECT generate_series(1,1000) % 4; 12} 13 14teardown 15{ 16 DROP TABLE a, b; 17} 18 19session s1 20step s1a { BEGIN; } 21step s1b { ALTER TABLE b ADD CONSTRAINT bfk FOREIGN KEY (a_id) REFERENCES a (i) NOT VALID; } 22step s1c { COMMIT; } 23 24session s2 25step s2a { BEGIN; } 26step s2b { SELECT * FROM a WHERE i = 1 LIMIT 1 FOR UPDATE; } 27step s2c { SELECT * FROM b WHERE a_id = 3 LIMIT 1 FOR UPDATE; } 28step s2d { INSERT INTO b VALUES (0); } 29step s2e { INSERT INTO a VALUES (4); } 30step s2f { COMMIT; } 31 32permutation s1a s1b s1c s2a s2b s2c s2d s2e s2f 33permutation s1a s1b s2a s1c s2b s2c s2d s2e s2f 34permutation s1a s1b s2a s2b s1c s2c s2d s2e s2f 35permutation s1a s1b s2a s2b s2c s1c s2d s2e s2f 36permutation s1a s1b s2a s2b s2c s2d s1c s2e s2f 37permutation s1a s2a s1b s1c s2b s2c s2d s2e s2f 38permutation s1a s2a s1b s2b s1c s2c s2d s2e s2f 39permutation s1a s2a s1b s2b s2c s1c s2d s2e s2f 40permutation s1a s2a s1b s2b s2c s2d s1c s2e s2f 41permutation s1a s2a s2b s1b s1c s2c s2d s2e s2f 42permutation s1a s2a s2b s1b s2c s1c s2d s2e s2f 43permutation s1a s2a s2b s1b s2c s2d s1c s2e s2f 44permutation s1a s2a s2b s2c s1b s1c s2d s2e s2f 45permutation s1a s2a s2b s2c s1b s2d s1c s2e s2f 46permutation s1a s2a s2b s2c s2d s1b s2e s2f s1c 47permutation s1a s2a s2b s2c s2d s2e s1b s2f s1c 48permutation s1a s2a s2b s2c s2d s2e s2f s1b s1c 49permutation s2a s1a s1b s1c s2b s2c s2d s2e s2f 50permutation s2a s1a s1b s2b s1c s2c s2d s2e s2f 51permutation s2a s1a s1b s2b s2c s1c s2d s2e s2f 52permutation s2a s1a s1b s2b s2c s2d s1c s2e s2f 53permutation s2a s1a s2b s1b s1c s2c s2d s2e s2f 54permutation s2a s1a s2b s1b s2c s1c s2d s2e s2f 55permutation s2a s1a s2b s1b s2c s2d s1c s2e s2f 56permutation s2a s1a s2b s2c s1b s1c s2d s2e s2f 57permutation s2a s1a s2b s2c s1b s2d s1c s2e s2f 58permutation s2a s1a s2b s2c s2d s1b s2e s2f s1c 59permutation s2a s1a s2b s2c s2d s2e s1b s2f s1c 60permutation s2a s1a s2b s2c s2d s2e s2f s1b s1c 61permutation s2a s2b s1a s1b s1c s2c s2d s2e s2f 62permutation s2a s2b s1a s1b s2c s1c s2d s2e s2f 63permutation s2a s2b s1a s1b s2c s2d s1c s2e s2f 64permutation s2a s2b s1a s2c s1b s1c s2d s2e s2f 65permutation s2a s2b s1a s2c s1b s2d s1c s2e s2f 66permutation s2a s2b s1a s2c s2d s1b s2e s2f s1c 67permutation s2a s2b s1a s2c s2d s2e s1b s2f s1c 68permutation s2a s2b s1a s2c s2d s2e s2f s1b s1c 69permutation s2a s2b s2c s1a s1b s1c s2d s2e s2f 70permutation s2a s2b s2c s1a s1b s2d s1c s2e s2f 71permutation s2a s2b s2c s1a s2d s1b s2e s2f s1c 72permutation s2a s2b s2c s1a s2d s2e s1b s2f s1c 73permutation s2a s2b s2c s1a s2d s2e s2f s1b s1c 74permutation s2a s2b s2c s2d s1a s1b s2e s2f s1c 75permutation s2a s2b s2c s2d s1a s2e s1b s2f s1c 76permutation s2a s2b s2c s2d s1a s2e s2f s1b s1c 77permutation s2a s2b s2c s2d s2e s1a s1b s2f s1c 78permutation s2a s2b s2c s2d s2e s1a s2f s1b s1c 79permutation s2a s2b s2c s2d s2e s2f s1a s1b s1c 80