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
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
|
# Tests for the EvalPlanQual mechanism
#
# EvalPlanQual is used in READ COMMITTED isolation level to attempt to
# re-execute UPDATE and DELETE operations against rows that were updated
# by some concurrent transaction.
setup
{
CREATE TABLE accounts (accountid text PRIMARY KEY, balance numeric not null);
INSERT INTO accounts VALUES ('checking', 600), ('savings', 600);
CREATE TABLE accounts_ext (accountid text PRIMARY KEY, balance numeric not null, other text);
INSERT INTO accounts_ext VALUES ('checking', 600, 'other'), ('savings', 700, null);
ALTER TABLE accounts_ext ADD COLUMN newcol int DEFAULT 42;
ALTER TABLE accounts_ext ADD COLUMN newcol2 text DEFAULT NULL;
CREATE TABLE p (a int, b int, c int);
CREATE TABLE c1 () INHERITS (p);
CREATE TABLE c2 () INHERITS (p);
CREATE TABLE c3 () INHERITS (p);
INSERT INTO c1 SELECT 0, a / 3, a % 3 FROM generate_series(0, 9) a;
INSERT INTO c2 SELECT 1, a / 3, a % 3 FROM generate_series(0, 9) a;
INSERT INTO c3 SELECT 2, a / 3, a % 3 FROM generate_series(0, 9) a;
CREATE TABLE table_a (id integer, value text);
CREATE TABLE table_b (id integer, value text);
INSERT INTO table_a VALUES (1, 'tableAValue');
INSERT INTO table_b VALUES (1, 'tableBValue');
CREATE TABLE jointest AS SELECT generate_series(1,10) AS id, 0 AS data;
CREATE INDEX ON jointest(id);
CREATE TABLE parttbl (a int) PARTITION BY LIST (a);
CREATE TABLE parttbl1 PARTITION OF parttbl FOR VALUES IN (1);
INSERT INTO parttbl VALUES (1);
}
teardown
{
DROP TABLE accounts;
DROP TABLE accounts_ext;
DROP TABLE p CASCADE;
DROP TABLE table_a, table_b, jointest;
DROP TABLE parttbl;
}
session "s1"
setup { BEGIN ISOLATION LEVEL READ COMMITTED; }
# wx1 then wx2 checks the basic case of re-fetching up-to-date values
step "wx1" { UPDATE accounts SET balance = balance - 200 WHERE accountid = 'checking'; }
# wy1 then wy2 checks the case where quals pass then fail
step "wy1" { UPDATE accounts SET balance = balance + 500 WHERE accountid = 'checking'; }
# upsert tests are to check writable-CTE cases
step "upsert1" {
WITH upsert AS
(UPDATE accounts SET balance = balance + 500
WHERE accountid = 'savings'
RETURNING accountid)
INSERT INTO accounts SELECT 'savings', 500
WHERE NOT EXISTS (SELECT 1 FROM upsert);
}
# tests with table p check inheritance cases:
# readp1/writep1/readp2 tests a bug where nodeLockRows did the wrong thing
# when the first updated tuple was in a non-first child table.
# writep2/returningp1 tests a memory allocation issue
step "readp1" { SELECT tableoid::regclass, ctid, * FROM p WHERE b IN (0, 1) AND c = 0 FOR UPDATE; }
step "writep1" { UPDATE p SET b = -1 WHERE a = 1 AND b = 1 AND c = 0; }
step "writep2" { UPDATE p SET b = -b WHERE a = 1 AND c = 0; }
step "c1" { COMMIT; }
# these tests are meant to exercise EvalPlanQualFetchRowMarks,
# ie, handling non-locked tables in an EvalPlanQual recheck
step "partiallock" {
SELECT * FROM accounts a1, accounts a2
WHERE a1.accountid = a2.accountid
FOR UPDATE OF a1;
}
step "lockwithvalues" {
SELECT * FROM accounts a1, (values('checking'),('savings')) v(id)
WHERE a1.accountid = v.id
FOR UPDATE OF a1;
}
step "partiallock_ext" {
SELECT * FROM accounts_ext a1, accounts_ext a2
WHERE a1.accountid = a2.accountid
FOR UPDATE OF a1;
}
# these tests exercise EvalPlanQual with a SubLink sub-select (which should be
# unaffected by any EPQ recheck behavior in the outer query); cf bug #14034
step "updateforss" {
UPDATE table_a SET value = 'newTableAValue' WHERE id = 1;
UPDATE table_b SET value = 'newTableBValue' WHERE id = 1;
}
# these tests exercise EvalPlanQual with conditional InitPlans which
# have not been executed prior to the EPQ
step "updateforcip" {
UPDATE table_a SET value = NULL WHERE id = 1;
}
# these tests exercise mark/restore during EPQ recheck, cf bug #15032
step "selectjoinforupdate" {
set enable_nestloop to 0;
set enable_hashjoin to 0;
set enable_seqscan to 0;
explain (costs off)
select * from jointest a join jointest b on a.id=b.id for update;
select * from jointest a join jointest b on a.id=b.id for update;
}
# test for EPQ on a partitioned result table
step "simplepartupdate" {
update parttbl set a = a;
}
session "s2"
setup { BEGIN ISOLATION LEVEL READ COMMITTED; }
step "wx2" { UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking'; }
step "wy2" { UPDATE accounts SET balance = balance + 1000 WHERE accountid = 'checking' AND balance < 1000; }
step "upsert2" {
WITH upsert AS
(UPDATE accounts SET balance = balance + 1234
WHERE accountid = 'savings'
RETURNING accountid)
INSERT INTO accounts SELECT 'savings', 1234
WHERE NOT EXISTS (SELECT 1 FROM upsert);
}
step "wx2_ext" { UPDATE accounts_ext SET balance = balance + 450; }
step "readp2" { SELECT tableoid::regclass, ctid, * FROM p WHERE b IN (0, 1) AND c = 0 FOR UPDATE; }
step "returningp1" {
WITH u AS ( UPDATE p SET b = b WHERE a > 0 RETURNING * )
SELECT * FROM u;
}
step "readforss" {
SELECT ta.id AS ta_id, ta.value AS ta_value,
(SELECT ROW(tb.id, tb.value)
FROM table_b tb WHERE ta.id = tb.id) AS tb_row
FROM table_a ta
WHERE ta.id = 1 FOR UPDATE OF ta;
}
step "updateforcip2" {
UPDATE table_a SET value = COALESCE(value, (SELECT text 'newValue')) WHERE id = 1;
}
step "updateforcip3" {
WITH d(val) AS (SELECT text 'newValue' FROM generate_series(1,1))
UPDATE table_a SET value = COALESCE(value, (SELECT val FROM d)) WHERE id = 1;
}
step "wrtwcte" { UPDATE table_a SET value = 'tableAValue2' WHERE id = 1; }
step "wrjt" { UPDATE jointest SET data = 42 WHERE id = 7; }
step "complexpartupdate" {
with u as (update parttbl set a = a returning parttbl.*)
update parttbl set a = u.a from u;
}
step "c2" { COMMIT; }
session "s3"
setup { BEGIN ISOLATION LEVEL READ COMMITTED; }
step "read" { SELECT * FROM accounts ORDER BY accountid; }
step "read_ext" { SELECT * FROM accounts_ext ORDER BY accountid; }
step "read_a" { SELECT * FROM table_a ORDER BY id; }
# this test exercises EvalPlanQual with a CTE, cf bug #14328
step "readwcte" {
WITH
cte1 AS (
SELECT id FROM table_b WHERE value = 'tableBValue'
),
cte2 AS (
SELECT * FROM table_a
WHERE id = (SELECT id FROM cte1)
FOR UPDATE
)
SELECT * FROM cte2;
}
# this test exercises a different CTE misbehavior, cf bug #14870
step "multireadwcte" {
WITH updated AS (
UPDATE table_a SET value = 'tableAValue3' WHERE id = 1 RETURNING id
)
SELECT (SELECT id FROM updated) AS subid, * FROM updated;
}
teardown { COMMIT; }
permutation "wx1" "wx2" "c1" "c2" "read"
permutation "wy1" "wy2" "c1" "c2" "read"
permutation "upsert1" "upsert2" "c1" "c2" "read"
permutation "readp1" "writep1" "readp2" "c1" "c2"
permutation "writep2" "returningp1" "c1" "c2"
permutation "wx2" "partiallock" "c2" "c1" "read"
permutation "wx2" "lockwithvalues" "c2" "c1" "read"
permutation "wx2_ext" "partiallock_ext" "c2" "c1" "read_ext"
permutation "updateforss" "readforss" "c1" "c2"
permutation "updateforcip" "updateforcip2" "c1" "c2" "read_a"
permutation "updateforcip" "updateforcip3" "c1" "c2" "read_a"
permutation "wrtwcte" "readwcte" "c1" "c2"
permutation "wrjt" "selectjoinforupdate" "c2" "c1"
permutation "wrtwcte" "multireadwcte" "c1" "c2"
permutation "simplepartupdate" "complexpartupdate" "c1" "c2"
|
