/
githubmirror
/
postgres
Обзор
Документация
Войти
/
githubmirror
/
postgres
Код
Запросы
0
Пакеты
0
Релизы
0
Аналитика
Безопасность
master
src/test/isolation/specs/ri-trigger.spec
53 строки
1 KB
Tom Lane
Allow non-quoted identifiers as isolation test session/step names.
24 июн 2021, 01:41
24 июн 2021, 01:41
a443c1b
Код
Авторство
О чём код?
# RI Trigger test # # Test trigger-based referential integrity enforcement. # # Any overlap between the transactions must cause a serialization failure. setup { CREATE TABLE parent (parent_id SERIAL NOT NULL PRIMARY KEY); CREATE TABLE child (child_id SERIAL NOT NULL PRIMARY KEY, parent_id INTEGER NOT NULL); CREATE FUNCTION ri_parent() RETURNS TRIGGER LANGUAGE PLPGSQL AS $body$ BEGIN PERFORM TRUE FROM child WHERE parent_id = OLD.parent_id; IF FOUND THEN RAISE SQLSTATE '23503' USING MESSAGE = 'child row exists'; END IF; IF TG_OP = 'DELETE' THEN RETURN OLD; END IF; RETURN NEW; END; $body$; CREATE TRIGGER ri_parent BEFORE UPDATE OR DELETE ON parent FOR EACH ROW EXECUTE PROCEDURE ri_parent(); CREATE FUNCTION ri_child() RETURNS TRIGGER LANGUAGE PLPGSQL AS $body$ BEGIN PERFORM TRUE FROM parent WHERE parent_id = NEW.parent_id; IF NOT FOUND THEN RAISE SQLSTATE '23503' USING MESSAGE = 'parent row missing'; END IF; RETURN NEW; END; $body$; CREATE TRIGGER ri_child BEFORE INSERT OR UPDATE ON child FOR EACH ROW EXECUTE PROCEDURE ri_child(); INSERT INTO parent VALUES(0); } teardown { DROP TABLE parent, child; DROP FUNCTION ri_parent(); DROP FUNCTION ri_child(); } session s1 setup { BEGIN ISOLATION LEVEL SERIALIZABLE; } step wxry1 { INSERT INTO child (parent_id) VALUES (0); } step c1 { COMMIT; } session s2 setup { BEGIN ISOLATION LEVEL SERIALIZABLE; } step r2 { SELECT TRUE; } step wyrx2 { DELETE FROM parent WHERE parent_id = 0; } step c2 { COMMIT; }