APPENDIX · POSTGRESQL LIVE DEMO · SLIDE P3
A3. Testing DB protection: CHECK and composite FK
Click "Run"—let's see how the base intercepts a negative price and a fragment from another player
DO
BEGIN
-- 1. Баг скидки пытается записать отрицательную цену покупки (-50)
BEGIN
INSERT INTO game.purchases (player_id, item_id, price_paid) VALUES (1, 1, -50);
EXCEPTION WHEN check_violation THEN
RAISE NOTICE [CHECK OK] Отрицательная цена отбита: %, SQLERRM;
END;
-- 2. Игрок #4 НЕ играл в матче #1 — пробуем записать ему фраг в матче #1
BEGIN
INSERT INTO game.kill_log (match_id, killer_id, victim_id, weapon) VALUES (1, 4, 2, railgun);
EXCEPTION WHEN foreign_key_violation THEN
RAISE NOTICE [FK OK] Чужой игрок отбит составным FK: %, SQLERRM;
END;
END ;
SELECT COUNT(*) AS valid_kills_in_match_1 FROM game.kill_log WHERE match_id = 1;
What happened when I clicked "Run"?
• The first insert violated chk_purchases_price (price_paid >= 0) — the database threw a check_violation exception.
• The second insert attempted to write player_id = 4 to match #1 (he only played in match #2). The composite foreign key fk_kill_killer (match_id, killer_id) instantly blocked the invalid record!
• Only 3 valid kills remain in the kill_log table.
Required Component of Practice 5
Atomicity of the check
Why the DO $$ block?
In the report for Exercise 5, you similarly demonstrate 2–3 intentionally invalid INSERT / DELETE statements that your schema rejects.
An invalid row is never visible to other transactions, not even for a microsecond—it is rejected before commit.
The DO $$ ... EXCEPTION ... $$ block allows you to catch an expected error, log it in a NOTICE, and continue executing the script.