R5RtxWbv

1\unset ON_ERROR_STOP
2\set VERBOSITY verbose
3
4CREATE TABLE IF NOT EXISTS foo (
5    id BIGSERIAL PRIMARY KEY,
6    lim INTEGER DEFAULT 5 NOT NULL,
7    CHECK(lim >= 0)
8);
9
10CREATE TABLE IF NOT EXISTS bar (
11    foo_id BIGINT REFERENCES foo
12);
13
14CREATE OR REPLACE FUNCTION limit_foos_in_bar()
15RETURNS TRIGGER
16LANGUAGE plpgsql
17AS $$
18DECLARE
19BEGIN
20    IF NOT EXISTS (SELECT 1 FROM pg_catalog.pg_settings WHERE "name"='transaction_isolation' AND setting='serializable') THEN
21        RAISE EXCEPTION 'SERIALIZABLE isolation is required for writes to bar';
22    END IF;
23    IF (SELECT count(*) > foo.lim FROM bar JOIN foo ON foo_id = NEW.foo_id AND NEW.foo_id = foo.id) THEN
24        RAISE 'You have used up all your foos.'
25            USING
26                ERRCODE = 'integrity_constraint_violation',
27                HINT = 'Time to restock!';
28    END IF;
29    RETURN NEW;
30END;
31$$;
32
33DROP TRIGGER IF EXISTS limit_foos_in_bar ON bar;
34
35CREATE TRIGGER limit_foos_in_bar
36    AFTER INSERT OR UPDATE ON bar
37    FOR EACH ROW
38        EXECUTE PROCEDURE limit_foos_in_bar();
39
40INSERT INTO foo(lim) VALUES (3);
41
42CREATE OR REPLACE FUNCTION check_bars_from_foo()
43RETURNS TRIGGER
44LANGUAGE plpgsql
45AS $$
46BEGIN
47    IF NOT EXISTS (SELECT 1 FROM pg_catalog.pg_settings WHERE "name"='transaction_isolation' AND setting='serializable') THEN
48        RAISE EXCEPTION 'SERIALIZABLE isolation is required for writes to foo';
49    END IF;
50    IF (SELECT count(*) > NEW.lim FROM bar WHERE NEW.id = foo_id) THEN
51        RAISE 'There are already more bars than %', NEW.lim
52            USING
53                ERRCODE = 'integrity_constraint_violation',
54                HINT = 'Consider deleting some bars';
55    END IF;
56    RETURN NEW;
57END;
58$$;
59
60DROP TRIGGER IF EXISTS check_bars_from_foo ON foo;
61
62CREATE TRIGGER check_bars_from_foo
63    AFTER UPDATE OF lim ON foo
64    FOR EACH ROW
65        EXECUTE PROCEDURE check_bars_from_foo();
66
67BEGIN ISOLATION LEVEL READ COMMITTED;
68    INSERT INTO bar VALUES(1); /* Fails because wrong transaction isolation level */
69ROLLBACK;
70
71BEGIN ISOLATION LEVEL SERIALIZABLE;
72    INSERT INTO bar VALUES (1),(1),(1); /* Succeeds */
73    INSERT INTO bar VALUES (1);         /* Fails because wrong count */
74ROLLBACK;
75
76BEGIN ISOLATION LEVEL SERIALIZABLE;
77    SET transaction_isolation = 'serializable';
78    INSERT INTO bar VALUES (1),(1),(1); /* Succeeds */
79    UPDATE foo SET lim=2; /* Fails */
80ROLLBACK;
81
82DROP TABLE bar, foo;
83DROP FUNCTION check_bars_from_foo();
84DROP FUNCTION limit_foos_in_bar();