diff options
Diffstat (limited to 'src/test/isolation/specs/eval-plan-qual-trigger.spec')
| -rw-r--r-- | src/test/isolation/specs/eval-plan-qual-trigger.spec | 410 |
1 files changed, 410 insertions, 0 deletions
diff --git a/src/test/isolation/specs/eval-plan-qual-trigger.spec b/src/test/isolation/specs/eval-plan-qual-trigger.spec new file mode 100644 index 0000000..b512edd --- /dev/null +++ b/src/test/isolation/specs/eval-plan-qual-trigger.spec @@ -0,0 +1,410 @@ +setup +{ + CREATE TABLE trigtest(key text primary key, data text); + + CREATE FUNCTION noisy_oper(p_comment text, p_a anynonarray, p_op text, p_b anynonarray) + RETURNS bool LANGUAGE plpgsql AS $body$ + DECLARE + r bool; + BEGIN + EXECUTE format('SELECT $1 %s $2', p_op) INTO r USING p_a, p_b; + RAISE NOTICE '%: % % % % %: %', p_comment, pg_typeof(p_a), p_a, p_op, pg_typeof(p_b), p_b, r; + RETURN r; + END;$body$; + + CREATE FUNCTION trig_report() RETURNS TRIGGER LANGUAGE plpgsql AS $body$ + DECLARE + r_new text; + r_old text; + r_ret record; + BEGIN + -- In older releases it wasn't allowed to reference OLD/NEW + -- when not applicable for TG_WHEN + IF TG_OP = 'INSERT' THEN + r_old = NULL; + r_new = NEW; + r_ret = NEW; + ELSIF TG_OP = 'DELETE' THEN + r_old = OLD; + r_new = NULL; + r_ret = OLD; + ELSIF TG_OP = 'UPDATE' THEN + r_old = OLD; + r_new = NEW; + r_ret = NEW; + END IF; + + IF TG_WHEN = 'AFTER' THEN + r_ret = NULL; + END IF; + + RAISE NOTICE 'trigger: name %; when: %; lev: %s; op: %; old: % new: %', + TG_NAME, TG_WHEN, TG_LEVEL, TG_OP, r_old, r_new; + + RETURN r_ret; + END; + $body$; +} + +teardown +{ + DROP TABLE trigtest; + DROP FUNCTION noisy_oper(text, anynonarray, text, anynonarray); + DROP FUNCTION trig_report(); +} + + +session s0 +step s0_rep { SELECT * FROM trigtest ORDER BY key, data } + +session s1 +#setup { } +step s1_b_rc { BEGIN ISOLATION LEVEL READ COMMITTED; SELECT 1; } +step s1_b_rr { BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT 1; } +step s1_c { COMMIT; } +step s1_r { ROLLBACK; } +step s1_trig_rep_b_i { CREATE TRIGGER rep_b_i BEFORE INSERT ON trigtest FOR EACH ROW EXECUTE PROCEDURE trig_report(); } +step s1_trig_rep_a_i { CREATE TRIGGER rep_a_i AFTER INSERT ON trigtest FOR EACH ROW EXECUTE PROCEDURE trig_report(); } +step s1_trig_rep_b_u { CREATE TRIGGER rep_b_u BEFORE UPDATE ON trigtest FOR EACH ROW EXECUTE PROCEDURE trig_report(); } +step s1_trig_rep_a_u { CREATE TRIGGER rep_a_u AFTER UPDATE ON trigtest FOR EACH ROW EXECUTE PROCEDURE trig_report(); } +step s1_trig_rep_b_d { CREATE TRIGGER rep_b_d BEFORE DELETE ON trigtest FOR EACH ROW EXECUTE PROCEDURE trig_report(); } +step s1_trig_rep_a_d { CREATE TRIGGER rep_a_d AFTER DELETE ON trigtest FOR EACH ROW EXECUTE PROCEDURE trig_report(); } +step s1_ins_a { INSERT INTO trigtest VALUES ('key-a', 'val-a-s1') RETURNING *; } +step s1_ins_b { INSERT INTO trigtest VALUES ('key-b', 'val-b-s1') RETURNING *; } +step s1_ins_c { INSERT INTO trigtest VALUES ('key-c', 'val-c-s1') RETURNING *; } +step s1_del_a { + DELETE FROM trigtest + WHERE + noisy_oper('upd', key, '=', 'key-a') AND + noisy_oper('upk', data, '<>', 'mismatch') + RETURNING * +} +step s1_del_b { + DELETE FROM trigtest + WHERE + noisy_oper('upd', key, '=', 'key-b') AND + noisy_oper('upk', data, '<>', 'mismatch') + RETURNING * +} +step s1_upd_a_data { + UPDATE trigtest SET data = data || '-ups1' + WHERE + noisy_oper('upd', key, '=', 'key-a') AND + noisy_oper('upk', data, '<>', 'mismatch') + RETURNING *; +} +step s1_upd_b_data { + UPDATE trigtest SET data = data || '-ups1' + WHERE + noisy_oper('upd', key, '=', 'key-b') AND + noisy_oper('upk', data, '<>', 'mismatch') + RETURNING *; +} +step s1_upd_a_tob { + UPDATE trigtest SET key = 'key-b', data = data || '-tobs1' + WHERE + noisy_oper('upk', key, '=', 'key-a') AND + noisy_oper('upk', data, '<>', 'mismatch') + RETURNING *; +} + +session s2 +#setup { } +step s2_b_rc { BEGIN ISOLATION LEVEL READ COMMITTED; SELECT 1; } +step s2_b_rr { BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT 1; } +step s2_c { COMMIT; } +step s2_r { ROLLBACK; } +step s2_ins_a { INSERT INTO trigtest VALUES ('key-a', 'val-a-s2') RETURNING *; } +step s2_del_a { + DELETE FROM trigtest + WHERE + noisy_oper('upd', key, '=', 'key-a') AND + noisy_oper('upk', data, '<>', 'mismatch') + RETURNING * +} +step s2_upd_a_data { + UPDATE trigtest SET data = data || '-ups2' + WHERE + noisy_oper('upd', key, '=', 'key-a') AND + noisy_oper('upk', data, '<>', 'mismatch') + RETURNING *; +} +step s2_upd_b_data { + UPDATE trigtest SET data = data || '-ups2' + WHERE + noisy_oper('upd', key, '=', 'key-b') AND + noisy_oper('upk', data, '<>', 'mismatch') + RETURNING *; +} +step s2_upd_all_data { + UPDATE trigtest SET data = data || '-ups2' + WHERE + noisy_oper('upd', key, '<>', 'mismatch') AND + noisy_oper('upk', data, '<>', 'mismatch') + RETURNING *; +} +step s2_upsert_a_data { + INSERT INTO trigtest VALUES ('key-a', 'val-a-upss2') + ON CONFLICT (key) + DO UPDATE SET data = trigtest.data || '-upserts2' + WHERE + noisy_oper('upd', trigtest.key, '=', 'key-a') AND + noisy_oper('upk', trigtest.data, '<>', 'mismatch') + RETURNING *; +} + +session s3 +#setup { } +step s3_b_rc { BEGIN ISOLATION LEVEL READ COMMITTED; SELECT 1; } +step s3_c { COMMIT; } +step s3_r { ROLLBACK; } +step s3_del_a { + DELETE FROM trigtest + WHERE + noisy_oper('upd', key, '=', 'key-a') AND + noisy_oper('upk', data, '<>', 'mismatch') + RETURNING * +} +step s3_upd_a_data { + UPDATE trigtest SET data = data || '-ups3' + WHERE + noisy_oper('upd', key, '=', 'key-a') AND + noisy_oper('upk', data, '<>', 'mismatch') + RETURNING *; +} + +### base case verifying that triggers see performed modifications +# s1 updates, s1 commits, s2 updates +permutation s1_trig_rep_b_u s1_trig_rep_a_u + s1_ins_a s1_ins_b s1_b_rc s2_b_rc + s1_upd_a_data s1_c s2_upd_a_data s2_c + s0_rep +# s1 updates, s1 rolls back, s2 updates +permutation s1_trig_rep_b_u s1_trig_rep_a_u + s1_ins_a s1_ins_b s1_b_rc s2_b_rc + s1_upd_a_data s1_r s2_upd_a_data s2_c + s0_rep +# s1 updates, s1 commits back, s2 deletes +permutation s1_trig_rep_b_d s1_trig_rep_b_u s1_trig_rep_a_d s1_trig_rep_a_u + s1_ins_a s1_ins_b s1_b_rc s2_b_rc + s1_upd_a_data s1_c s2_del_a s2_c + s0_rep +# s1 updates, s1 rolls back back, s2 deletes +permutation s1_trig_rep_b_d s1_trig_rep_b_u s1_trig_rep_a_d s1_trig_rep_a_u + s1_ins_a s1_ins_b s1_b_rc s2_b_rc + s1_upd_a_data s1_r s2_del_a s2_c + s0_rep + +### Verify EPQ is performed if necessary, and skipped if transaction rolled back +# s1 updates, s2 updates, s1 commits, EPQ +permutation s1_trig_rep_b_u s1_trig_rep_a_u + s1_ins_a s1_ins_b s1_b_rc s2_b_rc + s1_upd_a_data s2_upd_a_data s1_c s2_c + s0_rep +# s1 updates, s2 updates, s1 rolls back, no EPQ +permutation s1_trig_rep_b_u s1_trig_rep_a_u + s1_ins_a s1_ins_b s1_b_rc s2_b_rc + s1_upd_a_data s2_upd_a_data s1_r s2_c + s0_rep +# s1 updates, s2 deletes, s1 commits, EPQ +permutation s1_trig_rep_b_d s1_trig_rep_b_u s1_trig_rep_a_d s1_trig_rep_a_u + s1_ins_a s1_ins_b s1_b_rc s2_b_rc + s1_upd_a_data s2_upd_a_data s1_c s2_c + s0_rep +# s1 updates, s2 deletes, s1 rolls back, no EPQ +permutation s1_trig_rep_b_d s1_trig_rep_b_u s1_trig_rep_a_d s1_trig_rep_a_u + s1_ins_a s1_ins_b s1_b_rc s2_b_rc + s1_upd_a_data s2_upd_a_data s1_r s2_c + s0_rep +# s1 deletes, s2 updates, s1 commits, EPQ +permutation s1_trig_rep_b_d s1_trig_rep_b_u s1_trig_rep_a_d s1_trig_rep_a_u + s1_ins_a s1_ins_b s1_b_rc s2_b_rc + s1_del_a s2_upd_a_data s1_c s2_c + s0_rep +# s1 deletes, s2 updates, s1 rolls back, no EPQ +permutation s1_trig_rep_b_d s1_trig_rep_b_u s1_trig_rep_a_d s1_trig_rep_a_u + s1_ins_a s1_ins_b s1_b_rc s2_b_rc + s1_del_a s2_upd_a_data s1_r s2_c + s0_rep +# s1 inserts, s2 inserts, s1 commits, s2 inserts, unique conflict +permutation s1_trig_rep_b_i s1_trig_rep_b_d s1_trig_rep_a_i s1_trig_rep_a_d + s1_b_rc s2_b_rc + s1_ins_a s2_ins_a s1_c s2_c + s0_rep +# s1 inserts, s2 inserts, s1 rolls back, s2 inserts, no unique conflict +permutation s1_trig_rep_b_i s1_trig_rep_b_d s1_trig_rep_a_i s1_trig_rep_a_d + s1_b_rc s2_b_rc + s1_ins_a s2_ins_a s1_r s2_c + s0_rep +# s1 updates, s2 upserts, s1 commits, EPQ +permutation s1_trig_rep_b_i s1_trig_rep_b_d s1_trig_rep_b_u s1_trig_rep_a_i s1_trig_rep_a_d s1_trig_rep_a_u + s1_ins_a s1_ins_b s1_b_rc s2_b_rc + s1_upd_a_data s2_upsert_a_data s1_c s2_c + s0_rep +# s1 updates, s2 upserts, s1 rolls back, no EPQ +permutation s1_trig_rep_b_i s1_trig_rep_b_d s1_trig_rep_b_u s1_trig_rep_a_i s1_trig_rep_a_d s1_trig_rep_a_u + s1_ins_a s1_ins_b s1_b_rc s2_b_rc + s1_upd_a_data s2_upsert_a_data s1_c s2_c + s0_rep +# s1 inserts, s2 upserts, s1 commits +permutation s1_trig_rep_b_i s1_trig_rep_b_d s1_trig_rep_b_u s1_trig_rep_a_i s1_trig_rep_a_d s1_trig_rep_a_u + s1_b_rc s2_b_rc + s1_ins_a s2_upsert_a_data s1_c s2_c + s0_rep +# s1 inserts, s2 upserts, s1 rolls back +permutation s1_trig_rep_b_i s1_trig_rep_b_d s1_trig_rep_b_u s1_trig_rep_a_i s1_trig_rep_a_d s1_trig_rep_a_u + s1_b_rc s2_b_rc + s1_ins_a s2_upsert_a_data s1_r s2_c + s0_rep +# s1 inserts, s2 upserts, s1 updates, s1 commits, EPQ +permutation s1_trig_rep_b_i s1_trig_rep_b_d s1_trig_rep_b_u s1_trig_rep_a_i s1_trig_rep_a_d s1_trig_rep_a_u + s1_b_rc s2_b_rc + s1_ins_a s1_upd_a_data s2_upsert_a_data s1_c s2_c + s0_rep +# s1 inserts, s2 upserts, s1 updates, s1 rolls back, no EPQ +permutation s1_trig_rep_b_i s1_trig_rep_b_d s1_trig_rep_b_u s1_trig_rep_a_i s1_trig_rep_a_d s1_trig_rep_a_u + s1_b_rc s2_b_rc + s1_ins_a s1_upd_a_data s2_upsert_a_data s1_r s2_c + s0_rep + +### Verify EPQ is performed if necessary, and skipped if transaction rolled back, +### just without before triggers (for comparison, no additional row locks) +# s1 updates, s2 updates, s1 commits, EPQ +permutation s1_trig_rep_a_u + s1_ins_a s1_ins_b s1_b_rc s2_b_rc + s1_upd_a_data s2_upd_a_data s1_c s2_c + s0_rep +# s1 updates, s2 updates, s1 rolls back, no EPQ +permutation s1_trig_rep_a_u + s1_ins_a s1_ins_b s1_b_rc s2_b_rc + s1_upd_a_data s2_upd_a_data s1_r s2_c + s0_rep +# s1 updates, s2 deletes, s1 commits, EPQ +permutation s1_trig_rep_a_d s1_trig_rep_a_u + s1_ins_a s1_ins_b s1_b_rc s2_b_rc + s1_upd_a_data s2_del_a s1_c s2_c + s0_rep +# s1 updates, s2 deletes, s1 rolls back, no EPQ +permutation s1_trig_rep_a_d s1_trig_rep_a_u + s1_ins_a s1_ins_b s1_b_rc s2_b_rc + s1_upd_a_data s2_del_a s1_r s2_c + s0_rep +# s1 deletes, s2 updates, s1 commits, EPQ +permutation s1_trig_rep_a_d s1_trig_rep_a_u + s1_ins_a s1_ins_b s1_b_rc s2_b_rc + s1_del_a s2_upd_a_data s1_c s2_c + s0_rep +# s1 deletes, s2 updates, s1 rolls back, no EPQ +permutation s1_trig_rep_a_d s1_trig_rep_a_u + s1_ins_a s1_ins_b s1_b_rc s2_b_rc + s1_del_a s2_upd_a_data s1_r s2_c + s0_rep +# s1 deletes, s2 deletes, s1 commits, EPQ +permutation s1_trig_rep_a_d + s1_ins_a s1_ins_b s1_b_rc s2_b_rc + s1_del_a s2_del_a s1_c s2_c + s0_rep +# s1 deletes, s2 deletes, s1 rolls back, no EPQ +permutation s1_trig_rep_a_d + s1_ins_a s1_ins_b s1_b_rc s2_b_rc + s1_del_a s2_del_a s1_r s2_c + s0_rep + +### Verify that an update affecting a row that has been +### updated/deleted to not match the where clause anymore works +### correctly +# s1 updates to different key, s2 updates old key, s1 commits, EPQ failure should lead to no update +permutation s1_trig_rep_b_u s1_trig_rep_a_u + s1_ins_a s1_ins_c s1_b_rc s2_b_rc + s1_upd_a_tob s2_upd_a_data s1_c s2_c + s0_rep +# s1 updates to different key, s2 updates old key, s1 rolls back, no EPQ failure +permutation s1_trig_rep_b_u s1_trig_rep_a_u + s1_ins_a s1_ins_c s1_b_rc s2_b_rc + s1_upd_a_tob s2_upd_a_data s1_r s2_c + s0_rep +# s1 updates to different key, s2 updates new key, s1 commits, s2 will +# not see tuple with new key and not block +permutation s1_trig_rep_b_u s1_trig_rep_a_u + s1_ins_a s1_ins_c s1_b_rc s2_b_rc + s1_upd_a_tob s2_upd_b_data s1_c s2_c + s0_rep +# s1 updates to different key, s2 updates all keys, s1 commits, s2, +# will not see tuple with old key, but block on old, and then follow +# the chain +permutation s1_trig_rep_b_u s1_trig_rep_a_u + s1_ins_a s1_ins_c s1_b_rc s2_b_rc + s1_upd_a_tob s2_upd_all_data s1_c s2_c + s0_rep +# s1 deletes, s2 updates, s1 committs, EPQ failure should lead to no update +permutation s1_trig_rep_b_d s1_trig_rep_b_u s1_trig_rep_a_d s1_trig_rep_a_u + s1_ins_a s1_ins_c s1_b_rc s2_b_rc + s1_del_a s2_upd_a_data s1_c s2_c + s0_rep +# s1 deletes, s2 updates, s1 rolls back, no EPQ failure +permutation s1_trig_rep_b_d s1_trig_rep_b_u s1_trig_rep_a_d s1_trig_rep_a_u + s1_ins_a s1_ins_c s1_b_rc s2_b_rc + s1_del_a s2_upd_a_data s1_r s2_c + s0_rep +# s1 deletes, s2 deletes, s1 committs, EPQ failure should lead to no delete +permutation s1_trig_rep_b_d s1_trig_rep_a_d + s1_ins_a s1_ins_c s1_b_rc s2_b_rc + s1_del_a s2_del_a s1_c s2_c + s0_rep +# s1 deletes, s2 deletes, s1 rolls back, no EPQ failure +permutation s1_trig_rep_b_d s1_trig_rep_a_d + s1_ins_a s1_ins_c s1_b_rc s2_b_rc + s1_del_a s2_del_a s1_r s2_c + s0_rep + +### Verify EPQ with more than two participants works +## XXX: Disable tests, there is some potential for instability here that's not yet fully understood +## s1 updates, s2 updates, s3 updates, s1 commits, s2 EPQ, s2 commits, s3 EPQ +#permutation s1_trig_rep_b_u s1_trig_rep_a_u +# s1_ins_a s1_ins_b s1_b_rc s2_b_rc s3_b_rc +# s1_upd_a_data s2_upd_a_data s3_upd_a_data s1_c s2_c s3_c +# s0_rep +## s1 updates, s2 updates, s3 updates, s1 commits, s2 EPQ, s2 rolls back, s3 EPQ +#permutation s1_trig_rep_b_u s1_trig_rep_a_u +# s1_ins_a s1_ins_b s1_b_rc s2_b_rc s3_b_rc +# s1_upd_a_data s2_upd_a_data s3_upd_a_data s1_c s2_r s3_c +# s0_rep +## s1 updates, s3 updates, s2 upserts, s1 updates, s1 commits, s3 EPQ, s3 deletes, s3 commits, s2 inserts without EPQ recheck +#permutation s1_trig_rep_b_i s1_trig_rep_b_d s1_trig_rep_b_u s1_trig_rep_a_i s1_trig_rep_a_d s1_trig_rep_a_u +# s1_ins_a s1_b_rc s2_b_rc s3_b_rc +# s1_upd_a_data s3_upd_a_data s2_upsert_a_data s1_upd_a_data s1_c s3_del_a s3_c s2_c +# s0_rep +## s1 updates, s3 updates, s2 upserts, s1 updates, s1 commits, s3 EPQ, s3 deletes, s3 rolls back, s2 EPQ +#permutation s1_trig_rep_b_i s1_trig_rep_b_d s1_trig_rep_b_u s1_trig_rep_a_i s1_trig_rep_a_d s1_trig_rep_a_u +# s1_ins_a s1_b_rc s2_b_rc s3_b_rc +# s1_upd_a_data s3_upd_a_data s2_upsert_a_data s1_upd_a_data s1_c s3_del_a s3_r s2_c +# s0_rep + +### Document that EPQ doesn't "leap" onto a tuple that would match after blocking +# s1 inserts a, s1 updates b, s2 updates b, s1 deletes b, s1 updates a to b, s1 commits, s2 EPQ finds tuple deleted +permutation s1_trig_rep_b_i s1_trig_rep_b_d s1_trig_rep_b_u s1_trig_rep_a_i s1_trig_rep_a_d s1_trig_rep_a_u + s1_ins_b s1_b_rc s2_b_rc + s1_ins_a s1_upd_b_data s2_upd_b_data s1_del_b s1_upd_a_tob s1_c s2_c + s0_rep + +### Triggers for EPQ detect serialization failures +# s1 updates, s2 updates, s1 commits, serialization failure +permutation s1_trig_rep_b_u s1_trig_rep_a_u + s1_ins_a s1_ins_b s1_b_rr s2_b_rr + s1_upd_a_data s2_upd_a_data s1_c s2_c + s0_rep +# s1 updates, s2 updates, s1 rolls back, s2 succeeds +permutation s1_trig_rep_b_u s1_trig_rep_a_u + s1_ins_a s1_ins_b s1_b_rr s2_b_rr + s1_upd_a_data s2_upd_a_data s1_r s2_c + s0_rep +# s1 deletes, s2 updates, s1 commits, serialization failure +permutation s1_trig_rep_b_d s1_trig_rep_b_u s1_trig_rep_a_d s1_trig_rep_a_u + s1_ins_a s1_ins_b s1_b_rr s2_b_rr + s1_del_a s2_upd_a_data s1_c s2_c + s0_rep +# s1 deletes, s2 updates, s1 rolls back, s2 succeeds +permutation s1_trig_rep_b_d s1_trig_rep_b_u s1_trig_rep_a_d s1_trig_rep_a_u + s1_ins_a s1_ins_b s1_b_rr s2_b_rr + s1_del_a s2_upd_a_data s1_r s2_c + s0_rep |