/
githubmirror
/
postgres
Обзор
Документация
Войти
/
githubmirror
/
postgres
Код
Запросы
0
Пакеты
0
Релизы
0
Аналитика
Безопасность
master
src/test/isolation/specs/eval-plan-qual-trigger.spec
410 строк
15 KB
Dean Rasheed
Fix RETURNING OLD with BEFORE UPDATE trigger and concurrent update.
08 июл 2026, 22:46
08 июл 2026, 22:46
8e0c252
Код
Авторство
О чём код?
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 *, data = old.data AS check_old; } 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 *, new.data = old.data || '-ups2' AS check_old_and_new; } 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 *, new.data = old.data || '-ups2' AS check_old_and_new; } 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 *, new.data = old.data || '-upserts2' AS check_old_and_new; } 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 commits, 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 commits, 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