-
Notifications
You must be signed in to change notification settings - Fork 0
/
test.sql
136 lines (107 loc) · 3.37 KB
/
test.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
BEGIN;
CREATE SCHEMA test_pg_rtc;
SET search_path TO test_pg_rtc;
SET client_min_messages TO WARNING;
\set ON_ERROR_STOP on
-- Load the library.
\ir ./pg-rtc-up.sql
-- Initial fixture.
CREATE TABLE parent(id bigint PRIMARY KEY);
INSERT INTO parent(id)
SELECT id FROM generate_series(0, 10) id;
CREATE FUNCTION parent_trigger_delete() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE
arr bigint[];
BEGIN
UPDATE child
SET parent_ids = array_remove(child.parent_ids, old_rows.id)
FROM old_rows
WHERE
child.parent_ids && ARRAY[old_rows.id]
AND old_rows.id = 1;
RETURN NULL;
END;
$$;
CREATE TRIGGER parent_trigger_delete AFTER DELETE ON parent
REFERENCING OLD TABLE AS old_rows
FOR EACH STATEMENT EXECUTE PROCEDURE parent_trigger_delete();
CREATE TABLE child(id bigint PRIMARY KEY, parent_id bigint, parent_ids bigint[]);
INSERT INTO child(id, parent_id, parent_ids)
SELECT id, id % 10, ARRAY[id % 10, null, id % 4]
FROM generate_series(0, 999) id;
-- Add counters.
SELECT rtc_create('child', 'parent_id', 'parent', true) \gset
SELECT rtc_create('child', 'parent_ids', 'parent', true) \gset
-- Force differential triggers to fire and modify the counters.
INSERT INTO child(id, parent_id, parent_ids)
SELECT id, id % 10, ARRAY[id % 10, null, id % 4]
FROM generate_series(1000, 1999) id;
UPDATE child
SET
parent_id = (id + 3) % 10,
parent_ids = ARRAY[(id + 3) % 10, null, (id + 3) % 4]
WHERE id IN(SELECT id FROM generate_series(500, 999) id);
UPDATE child
SET
parent_id = 1,
parent_ids = ARRAY[1, 2]
WHERE id = 1999;
DELETE FROM parent WHERE id = 1;
DELETE FROM child
WHERE id IN(SELECT id FROM generate_series(1500, 1999) id);
-- Test for single-value FKs.
\echo ''
\echo 'Counters for some of parent rows referred by single-value FK (parent_id):'
WITH data AS (
SELECT
parent.id,
cnt AS rtc_cnt,
(SELECT count(1) FROM child WHERE parent_id = parent.id) AS real_cnt,
cnt = (SELECT count(1) FROM child WHERE parent_id = parent.id) AS correct
FROM parent
LEFT JOIN child_rtc_parent_id ON child_rtc_parent_id.id = parent.id
)
SELECT * FROM data
ORDER BY correct, id
LIMIT 5;
DO $$
BEGIN
PERFORM parent.id
FROM parent
LEFT JOIN child_rtc_parent_id ON child_rtc_parent_id.id = parent.id
WHERE cnt <> (SELECT count(1) FROM child WHERE parent_id = parent.id);
IF FOUND THEN
RAISE 'Mismatches found, see above.';
END IF;
END;
$$ LANGUAGE plpgsql;
-- Test for array-value FK.
\echo 'Counters for some of parent rows referred by ARRAY-value FK (parent_ids):'
WITH data AS (
SELECT
parent.id,
cnt AS rtc_cnt,
(SELECT count(1) FROM child WHERE parent_ids && ARRAY[parent.id]) AS real_cnt,
cnt = (SELECT count(1) FROM child WHERE parent_ids && ARRAY[parent.id]) AS correct
FROM parent
LEFT JOIN child_rtc_parent_ids ON child_rtc_parent_ids.id = parent.id
)
SELECT * FROM data
ORDER BY correct, id
LIMIT 5;
DO $$
BEGIN
PERFORM parent.id
FROM parent
LEFT JOIN child_rtc_parent_ids ON child_rtc_parent_ids.id = parent.id
WHERE cnt <> (SELECT count(1) FROM child WHERE parent_ids && ARRAY[parent.id]);
IF FOUND THEN
RAISE 'Mismatches found, see above.';
END IF;
END;
$$ LANGUAGE plpgsql;
-- Test that cleanup works.
SELECT rtc_drop('child', 'parent_id') \gset
SELECT rtc_drop('child', 'parent_ids') \gset
\ir ./pg-rtc-down.sql
ROLLBACK;