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
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
|
# Test that pruning and vacuuming pay attention to concurrent sessions
# in the right way. For normal relations that means that rows cannot
# be pruned away if there's an older snapshot, in contrast to that
# temporary tables should nearly always be prunable.
#
# NB: Think hard before adding a test showing that rows in permanent
# tables get pruned - it's quite likely that it'd be racy, e.g. due to
# an autovacuum worker holding a snapshot.
setup {
CREATE OR REPLACE FUNCTION explain_json(p_query text)
RETURNS json
LANGUAGE plpgsql AS $$
DECLARE
v_ret json;
BEGIN
EXECUTE p_query INTO STRICT v_ret;
RETURN v_ret;
END;$$;
}
teardown {
DROP FUNCTION explain_json(text);
}
session lifeline
# Start a transaction, force a snapshot to be held
step ll_start
{
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT 1;
}
step ll_commit { COMMIT; }
session pruner
setup
{
SET enable_seqscan = false;
SET enable_indexscan = false;
SET enable_bitmapscan = false;
}
step pruner_create_temp
{
CREATE TEMPORARY TABLE horizons_tst (data int unique) WITH (autovacuum_enabled = off);
INSERT INTO horizons_tst(data) VALUES(1),(2);
}
step pruner_create_perm
{
CREATE TABLE horizons_tst (data int unique) WITH (autovacuum_enabled = off);
INSERT INTO horizons_tst(data) VALUES(1),(2);
}
# Temp tables cannot be dropped in the teardown, so just always do so
# as part of the permutation
step pruner_drop
{
DROP TABLE horizons_tst;
}
step pruner_delete
{
DELETE FROM horizons_tst;
}
step pruner_begin { BEGIN; }
step pruner_commit { COMMIT; }
step pruner_vacuum
{
VACUUM horizons_tst;
}
# Show the heap fetches of an ordered index-only-scan (other plans
# have been forbidden above) - that tells us how many non-killed leaf
# entries there are.
step pruner_query
{
SELECT explain_json($$
EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
}
# Verify that the query plan still is an IOS
step pruner_query_plan
{
EXPLAIN (COSTS OFF) SELECT * FROM horizons_tst ORDER BY data;
}
# Show that with a permanent relation deleted rows cannot be pruned
# away if there's a concurrent session still seeing the rows.
permutation
pruner_create_perm
ll_start
pruner_query_plan
# Run query that could do pruning twice, first has chance to prune,
# second would not perform heap fetches if first query did.
pruner_query
pruner_query
pruner_delete
pruner_query
pruner_query
ll_commit
pruner_drop
# Show that with a temporary relation deleted rows can be pruned away,
# even if there's a concurrent session with a snapshot from before the
# deletion. That's safe because the session with the older snapshot
# cannot access the temporary table.
permutation
pruner_create_temp
ll_start
pruner_query_plan
pruner_query
pruner_query
pruner_delete
pruner_query
pruner_query
ll_commit
pruner_drop
# Verify that pruning in temporary relations doesn't remove rows still
# visible in the current session
permutation
pruner_create_temp
ll_start
pruner_query
pruner_query
pruner_begin
pruner_delete
pruner_query
pruner_query
ll_commit
pruner_commit
pruner_drop
# Show that vacuum cannot remove deleted rows still visible to another
# session's snapshot, when accessing a permanent table.
permutation
pruner_create_perm
ll_start
pruner_query
pruner_query
pruner_delete
pruner_vacuum
pruner_query
pruner_query
ll_commit
pruner_drop
# Show that vacuum can remove deleted rows still visible to another
# session's snapshot, when accessing a temporary table.
permutation
pruner_create_temp
ll_start
pruner_query
pruner_query
pruner_delete
pruner_vacuum
pruner_query
pruner_query
ll_commit
pruner_drop
|
