1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test index with transaction local commits", "[transactions]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db), con2(db);
11
12 // first test simple index usage
13 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER PRIMARY KEY)"));
14 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3)"));
15
16 // inserting a duplicate value fails
17 REQUIRE_FAIL(con.Query("INSERT INTO integers VALUES (1)"));
18 // inserting a non-duplicate value works
19 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (4)"));
20
21 // updating a primary key to an existing value fails
22 REQUIRE_FAIL(con.Query("UPDATE integers SET i=1 WHERE i=4"));
23 // but updating to a non-existing value works
24 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET i=5 WHERE i=4"));
25
26 // if we first delete a value, we can insert that value again
27 REQUIRE_NO_FAIL(con.Query("DELETE FROM integers WHERE i=1"));
28 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1)"));
29 REQUIRE_NO_FAIL(con.Query("DELETE FROM integers WHERE i >= 4"));
30
31 result = con.Query("SELECT COUNT(*) FROM integers");
32 REQUIRE(CHECK_COLUMN(result, 0, {3}));
33 result = con2.Query("SELECT COUNT(*) FROM integers");
34 REQUIRE(CHECK_COLUMN(result, 0, {3}));
35
36 // now test with multiple transactions
37 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
38 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION"));
39
40 // both transactions can insert the same value
41 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (4)"));
42 REQUIRE_NO_FAIL(con2.Query("INSERT INTO integers VALUES (4)"));
43
44 result = con.Query("SELECT COUNT(*) FROM integers");
45 REQUIRE(CHECK_COLUMN(result, 0, {4}));
46 result = con2.Query("SELECT COUNT(*) FROM integers");
47 REQUIRE(CHECK_COLUMN(result, 0, {4}));
48
49 // also using the index is fine
50 result = con.Query("SELECT COUNT(*) FROM integers WHERE i=1");
51 REQUIRE(CHECK_COLUMN(result, 0, {1}));
52 result = con2.Query("SELECT COUNT(*) FROM integers WHERE i=1");
53 REQUIRE(CHECK_COLUMN(result, 0, {1}));
54 result = con.Query("SELECT COUNT(*) FROM integers WHERE i=4");
55 REQUIRE(CHECK_COLUMN(result, 0, {1}));
56 result = con2.Query("SELECT COUNT(*) FROM integers WHERE i=4");
57 REQUIRE(CHECK_COLUMN(result, 0, {1}));
58
59 // conflict happens on commit
60 // we can commit con
61 REQUIRE_NO_FAIL(con.Query("COMMIT"));
62 // but then con2 fails to commit
63 REQUIRE_FAIL(con2.Query("COMMIT"));
64}
65
66TEST_CASE("Test index with transaction local updates", "[transactions]") {
67 unique_ptr<QueryResult> result;
68 DuckDB db(nullptr);
69 Connection con(db);
70
71 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER PRIMARY KEY)"));
72 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3)"));
73
74 // inserting a conflicting value directly fails
75 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
76 REQUIRE_FAIL(con.Query("INSERT INTO integers VALUES (3)"));
77 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
78
79 // we can insert a non-conflicting value, but then updating it fails
80 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
81 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (4)"));
82 result = con.Query("SELECT * FROM integers ORDER BY i");
83 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4}));
84 REQUIRE_FAIL(con.Query("UPDATE integers SET i=3 WHERE i=4"));
85 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
86
87 // we can insert a non-conflicting value, and then update it to another non-conflicting value
88 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
89 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (4)"));
90 result = con.Query("SELECT * FROM integers ORDER BY i");
91 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4}));
92 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET i=5 WHERE i=4"));
93 result = con.Query("SELECT * FROM integers ORDER BY i");
94 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 5}));
95 REQUIRE_NO_FAIL(con.Query("COMMIT"));
96
97 result = con.Query("SELECT * FROM integers ORDER BY i");
98 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 5}));
99}
100
101TEST_CASE("Test index with pending insertions", "[transactions]") {
102 unique_ptr<QueryResult> result;
103 DuckDB db(nullptr);
104 Connection con(db), con2(db);
105
106 // we can create an index with pending insertions
107 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
108 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION"));
109 REQUIRE_NO_FAIL(con2.Query("INSERT INTO integers VALUES (1), (2), (3)"));
110
111 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON integers using art(i)"));
112
113 result = con.Query("SELECT COUNT(*) FROM integers WHERE i=1");
114 REQUIRE(CHECK_COLUMN(result, 0, {0}));
115 result = con2.Query("SELECT COUNT(*) FROM integers WHERE i=1");
116 REQUIRE(CHECK_COLUMN(result, 0, {1}));
117
118 // after committing, the values are added to the index
119 REQUIRE_NO_FAIL(con2.Query("COMMIT"));
120
121 result = con.Query("SELECT COUNT(*) FROM integers WHERE i=1");
122 REQUIRE(CHECK_COLUMN(result, 0, {1}));
123 result = con2.Query("SELECT COUNT(*) FROM integers WHERE i=1");
124 REQUIRE(CHECK_COLUMN(result, 0, {1}));
125}
126
127TEST_CASE("Test index with pending updates", "[transactions]") {
128 unique_ptr<QueryResult> result;
129 DuckDB db(nullptr);
130 Connection con(db), con2(db);
131
132 // we cannot create an index with pending updates
133 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
134 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3)"));
135
136 // update a value
137 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION"));
138 REQUIRE_NO_FAIL(con2.Query("UPDATE integers SET i=4 WHERE i=1"));
139
140 // failed to create an index: pending updates
141 REQUIRE_FAIL(con.Query("CREATE INDEX i_index ON integers using art(i)"));
142
143 // now we commit
144 REQUIRE_NO_FAIL(con2.Query("COMMIT"));
145
146 // no more pending updates: creating the index works now
147 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON integers using art(i)"));
148
149 result = con.Query("SELECT COUNT(*) FROM integers WHERE i=4");
150 REQUIRE(CHECK_COLUMN(result, 0, {1}));
151 result = con2.Query("SELECT COUNT(*) FROM integers WHERE i=4");
152 REQUIRE(CHECK_COLUMN(result, 0, {1}));
153}
154
155TEST_CASE("Test index with pending deletes", "[transactions]") {
156 unique_ptr<QueryResult> result;
157 DuckDB db(nullptr);
158 Connection con(db), con2(db);
159
160 // we can create an index with pending deletes
161 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
162 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3)"));
163
164 // delete a value
165 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION"));
166 REQUIRE_NO_FAIL(con2.Query("DELETE FROM integers WHERE i=1"));
167
168 // we can create an index with pending deletes
169 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON integers using art(i)"));
170
171 // now we commit
172 REQUIRE_NO_FAIL(con2.Query("COMMIT"));
173
174 result = con.Query("SELECT COUNT(*) FROM integers WHERE i=1");
175 REQUIRE(CHECK_COLUMN(result, 0, {0}));
176 result = con2.Query("SELECT COUNT(*) FROM integers WHERE i=1");
177 REQUIRE(CHECK_COLUMN(result, 0, {0}));
178}
179
180TEST_CASE("Test index with versioned data from deletes", "[transactions]") {
181 unique_ptr<QueryResult> result;
182 DuckDB db(nullptr);
183 Connection con(db), con2(db);
184
185 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER PRIMARY KEY)"));
186 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3)"));
187
188 // local delete
189 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
190
191 // "1" exists for both transactions
192 result = con.Query("SELECT i FROM integers WHERE i=1");
193 REQUIRE(CHECK_COLUMN(result, 0, {1}));
194 result = con2.Query("SELECT i FROM integers WHERE i=1");
195 REQUIRE(CHECK_COLUMN(result, 0, {1}));
196
197 REQUIRE_NO_FAIL(con.Query("DELETE FROM integers WHERE i=1"));
198
199 // "1" only exists for con2
200 result = con.Query("SELECT i FROM integers WHERE i=1");
201 REQUIRE(CHECK_COLUMN(result, 0, {}));
202 result = con2.Query("SELECT i FROM integers WHERE i=1");
203 REQUIRE(CHECK_COLUMN(result, 0, {1}));
204
205 // rollback
206 REQUIRE_NO_FAIL(con.Query("DELETE FROM integers WHERE i=1"));
207 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
208
209 result = con.Query("SELECT i FROM integers WHERE i=1");
210 REQUIRE(CHECK_COLUMN(result, 0, {1}));
211 result = con2.Query("SELECT i FROM integers WHERE i=1");
212 REQUIRE(CHECK_COLUMN(result, 0, {1}));
213
214 // local update of primary key column
215 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
216
217 // 1 => 4
218 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET i=4 WHERE i=1"));
219
220 result = con.Query("SELECT i FROM integers WHERE i=1");
221 REQUIRE(CHECK_COLUMN(result, 0, {}));
222 result = con2.Query("SELECT i FROM integers WHERE i=1");
223 REQUIRE(CHECK_COLUMN(result, 0, {1}));
224
225 result = con.Query("SELECT i FROM integers WHERE i=4");
226 REQUIRE(CHECK_COLUMN(result, 0, {4}));
227 result = con2.Query("SELECT i FROM integers WHERE i=4");
228 REQUIRE(CHECK_COLUMN(result, 0, {}));
229
230 // delete 4
231 REQUIRE_NO_FAIL(con.Query("DELETE FROM integers WHERE i=4"));
232
233 result = con.Query("SELECT i FROM integers WHERE i=1");
234 REQUIRE(CHECK_COLUMN(result, 0, {}));
235 result = con2.Query("SELECT i FROM integers WHERE i=1");
236 REQUIRE(CHECK_COLUMN(result, 0, {1}));
237
238 result = con.Query("SELECT i FROM integers WHERE i=4");
239 REQUIRE(CHECK_COLUMN(result, 0, {}));
240 result = con2.Query("SELECT i FROM integers WHERE i=4");
241 REQUIRE(CHECK_COLUMN(result, 0, {}));
242
243 // commit
244 REQUIRE_NO_FAIL(con.Query("COMMIT"));
245
246 result = con.Query("SELECT i FROM integers WHERE i=1");
247 REQUIRE(CHECK_COLUMN(result, 0, {}));
248 result = con2.Query("SELECT i FROM integers WHERE i=1");
249 REQUIRE(CHECK_COLUMN(result, 0, {}));
250 result = con.Query("SELECT i FROM integers ORDER BY i");
251 REQUIRE(CHECK_COLUMN(result, 0, {2, 3}));
252 result = con2.Query("SELECT i FROM integers ORDER BY i");
253 REQUIRE(CHECK_COLUMN(result, 0, {2, 3}));
254}
255
256TEST_CASE("Test index with versioned data from updates in secondary columns", "[transactions]") {
257 unique_ptr<QueryResult> result;
258 DuckDB db(nullptr);
259 Connection con(db), con2(db);
260
261 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER PRIMARY KEY, j INTEGER)"));
262 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1, 1), (2, 2), (3, 3)"));
263
264 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
265 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET j=4 WHERE i=1"));
266
267 result = con.Query("SELECT j FROM integers WHERE i=1");
268 REQUIRE(CHECK_COLUMN(result, 0, {4}));
269 result = con2.Query("SELECT j FROM integers WHERE i=1");
270 REQUIRE(CHECK_COLUMN(result, 0, {1}));
271
272 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
273
274 result = con.Query("SELECT j FROM integers WHERE i=1");
275 REQUIRE(CHECK_COLUMN(result, 0, {1}));
276 result = con2.Query("SELECT j FROM integers WHERE i=1");
277 REQUIRE(CHECK_COLUMN(result, 0, {1}));
278}
279
280TEST_CASE("Test abort of update/delete", "[transactions]") {
281 unique_ptr<QueryResult> result;
282 DuckDB db(nullptr);
283 Connection con(db), con2(db);
284 con.EnableQueryVerification();
285
286 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER PRIMARY KEY, j INTEGER)"));
287 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1, 1), (2, 2), (3, 3)"));
288
289 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
290 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION"));
291
292 // insert the value "4" for both transactions
293 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (4, 4)"));
294 REQUIRE_NO_FAIL(con2.Query("INSERT INTO integers VALUES (4, 4)"));
295
296 // perform some other operations
297 REQUIRE_NO_FAIL(con2.Query("UPDATE integers SET j=j+1"));
298 REQUIRE_NO_FAIL(con2.Query("DELETE FROM integers WHERE i=2"));
299 REQUIRE_NO_FAIL(con2.Query("CREATE TABLE test(i INTEGER)"));
300
301 // commit both transactions, con2 should now fail
302 REQUIRE_NO_FAIL(con.Query("COMMIT"));
303 REQUIRE_FAIL(con2.Query("COMMIT"));
304
305 // verify that the data is (1, 1), (...), (4, 4)
306 result = con.Query("SELECT * FROM integers ORDER BY i");
307 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4}));
308 REQUIRE(CHECK_COLUMN(result, 1, {1, 2, 3, 4}));
309
310 // table test should not exist
311 REQUIRE_FAIL(con.Query("SELECT * FROM test"));
312}
313