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
170
171
172
173
174
175
176
177
178
|
# Test TOAST behavior in PL/pgSQL procedures with transaction control.
#
# We need to ensure that values stored in PL/pgSQL variables are free
# of external TOAST references, because those could disappear after a
# transaction is committed (leading to errors "missing chunk number
# ... for toast value ..."). The tests here do this by running VACUUM
# in a second session. Advisory locks are used to have the VACUUM
# kick in at the right time. The different "assign" steps test
# different code paths for variable assignments in PL/pgSQL.
setup
{
CREATE TABLE test1 (a int, b text);
ALTER TABLE test1 ALTER COLUMN b SET STORAGE EXTERNAL;
INSERT INTO test1 VALUES (1, repeat('foo', 2000));
CREATE TYPE test2 AS (a bigint, b text);
}
teardown
{
DROP TABLE test1;
DROP TYPE test2;
}
session s1
setup
{
SELECT pg_advisory_unlock_all();
}
# assign_simple_var()
step assign1
{
do $$
declare
x text;
begin
select test1.b into x from test1;
delete from test1;
commit;
perform pg_advisory_lock(1);
raise notice 'length(x) = %', length(x);
end;
$$;
}
# assign_simple_var()
step assign2
{
do $$
declare
x text;
begin
x := (select test1.b from test1);
delete from test1;
commit;
perform pg_advisory_lock(1);
raise notice 'length(x) = %', length(x);
end;
$$;
}
# expanded_record_set_field()
step assign3
{
do $$
declare
r record;
begin
select * into r from test1;
r.b := (select test1.b from test1);
delete from test1;
commit;
perform pg_advisory_lock(1);
raise notice 'length(r) = %', length(r::text);
end;
$$;
}
# expanded_record_set_fields()
step assign4
{
do $$
declare
r test2;
begin
select * into r from test1;
delete from test1;
commit;
perform pg_advisory_lock(1);
raise notice 'length(r) = %', length(r::text);
end;
$$;
}
# expanded_record_set_tuple()
step assign5
{
do $$
declare
r record;
begin
for r in select test1.b from test1 loop
null;
end loop;
delete from test1;
commit;
perform pg_advisory_lock(1);
raise notice 'length(r) = %', length(r::text);
end;
$$;
}
# FOR loop must not hold any fetched-but-not-detoasted values across commit
step assign6
{
do $$
declare
r record;
begin
insert into test1 values (2, repeat('bar', 3000));
insert into test1 values (3, repeat('baz', 4000));
for r in select test1.b from test1 loop
delete from test1;
commit;
perform pg_advisory_lock(1);
raise notice 'length(r) = %', length(r::text);
end loop;
end;
$$;
}
# Check that the results of a query can be detoasted just after committing
# (there's no interaction with VACUUM here)
step "fetch-after-commit"
{
do $$
declare
r record;
t text;
begin
insert into test1 values (2, repeat('bar', 3000));
insert into test1 values (3, repeat('baz', 4000));
for r in select test1.a from test1 loop
commit;
select b into t from test1 where a = r.a;
raise notice 'length(t) = %', length(t);
end loop;
end;
$$;
}
session s2
setup
{
SELECT pg_advisory_unlock_all();
}
step lock
{
SELECT pg_advisory_lock(1);
}
step vacuum
{
VACUUM test1;
}
step unlock
{
SELECT pg_advisory_unlock(1);
}
permutation lock assign1 vacuum unlock
permutation lock assign2 vacuum unlock
permutation lock assign3 vacuum unlock
permutation lock assign4 vacuum unlock
permutation lock assign5 vacuum unlock
permutation lock assign6 vacuum unlock
permutation "fetch-after-commit"
|
