1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4#include <chrono>
5#include <thread>
6
7using namespace duckdb;
8using namespace std;
9
10TEST_CASE("Schema dependencies", "[catalog]") {
11 unique_ptr<QueryResult> result;
12 DuckDB db(nullptr);
13 Connection con(db);
14 Connection con2(db);
15
16 // single schema and dependencies
17 REQUIRE_NO_FAIL(con.Query("CREATE SCHEMA s1"));
18 REQUIRE_NO_FAIL(con.Query("CREATE TABLE s1.integers(i INTEGER)"));
19 REQUIRE_NO_FAIL(con.Query("SELECT * FROM s1.integers"));
20 // can't drop: dependency
21 REQUIRE_FAIL(con.Query("DROP SCHEMA s1"));
22 REQUIRE_NO_FAIL(con.Query("SELECT * FROM s1.integers"));
23 // we can drop with cascade though
24 REQUIRE_NO_FAIL(con.Query("DROP SCHEMA s1 CASCADE"));
25 // this also drops the table
26 REQUIRE_FAIL(con.Query("SELECT * FROM s1.integers"));
27
28 // schemas and dependencies
29 // create a schema and a table inside the schema
30 REQUIRE_NO_FAIL(con.Query("CREATE SCHEMA s1"));
31 REQUIRE_NO_FAIL(con.Query("CREATE TABLE s1.integers(i INTEGER)"));
32
33 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
34 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION"));
35 // drop the table in con1
36 REQUIRE_NO_FAIL(con.Query("DROP TABLE s1.integers"));
37 // we can't drop the schema from con2 because the table still exists for con2!
38 REQUIRE_FAIL(con2.Query("DROP SCHEMA s1"));
39 // now rollback the table drop
40 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
41 REQUIRE_NO_FAIL(con2.Query("ROLLBACK"));
42 // the table exists again
43 REQUIRE_NO_FAIL(con.Query("SELECT * FROM s1.integers"));
44 // try again, but this time we commit
45 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
46 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION"));
47 // drop the schema entirely now
48 REQUIRE_NO_FAIL(con.Query("DROP SCHEMA s1 CASCADE"));
49 // we can still query the table from con2
50 REQUIRE_NO_FAIL(con2.Query("SELECT * FROM s1.integers"));
51 // even after we commit
52 REQUIRE_NO_FAIL(con.Query("COMMIT"));
53 REQUIRE_NO_FAIL(con2.Query("SELECT * FROM s1.integers"));
54 // however if we end the transaction in con2 the schema is gone
55 REQUIRE_NO_FAIL(con2.Query("ROLLBACK"));
56 REQUIRE_FAIL(con2.Query("CREATE TABLE s1.dummy(i INTEGER)"));
57}
58
59TEST_CASE("Prepared statement dependencies dependencies", "[catalog]") {
60 unique_ptr<QueryResult> result;
61 DuckDB db(nullptr);
62 Connection con(db);
63 Connection con2(db);
64
65 // prepared statements and dependencies
66 // dependency on a bound table
67 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
68 REQUIRE_NO_FAIL(con2.Query("PREPARE v AS SELECT * FROM integers"));
69 REQUIRE_NO_FAIL(con2.Query("EXECUTE v"));
70 // cannot drop table now
71 REQUIRE_FAIL(con.Query("DROP TABLE integers"));
72 // but CASCADE drop should work
73 REQUIRE_NO_FAIL(con.Query("DROP TABLE integers CASCADE"));
74 // after the cascade drop the prepared statement is invalidated
75 REQUIRE_FAIL(con2.Query("EXECUTE v"));
76
77 // dependency on a sequence for prepare
78 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq"));
79 REQUIRE_NO_FAIL(con2.Query("PREPARE v AS SELECT nextval('seq')"));
80 result = con2.Query("EXECUTE v");
81 REQUIRE(CHECK_COLUMN(result, 0, {1}));
82 // cannot drop sequence now
83 REQUIRE_FAIL(con.Query("DROP SEQUENCE seq"));
84 // check that the prepared statement still works
85 result = con2.Query("EXECUTE v");
86 REQUIRE(CHECK_COLUMN(result, 0, {2}));
87 // cascade drop
88 REQUIRE_NO_FAIL(con.Query("DROP SEQUENCE seq CASCADE"));
89 // after the cascade drop the prepared statement is invalidated
90 REQUIRE_FAIL(con2.Query("EXECUTE v"));
91}
92
93TEST_CASE("Default values and dependencies", "[catalog]") {
94 unique_ptr<QueryResult> result;
95 DuckDB db(nullptr);
96 Connection con(db);
97 Connection con2(db);
98
99 // dependency on a sequence in a default value
100 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq"));
101 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER DEFAULT nextval('seq'), j INTEGER)"));
102 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers (j) VALUES (1), (1), (1), (1), (1)"));
103
104 result = con2.Query("SELECT SUM(i) FROM integers");
105 REQUIRE(CHECK_COLUMN(result, 0, {15}));
106 // we can't drop the sequence: the table depends on it
107 REQUIRE_FAIL(con.Query("DROP SEQUENCE seq"));
108 // cascade drop works
109 REQUIRE_NO_FAIL(con.Query("DROP SEQUENCE seq CASCADE"));
110 // but it also drops the table
111 REQUIRE_FAIL(con.Query("SELECT * FROM integers"));
112
113 // dependency on multiple sequences in default value
114 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq"));
115 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq1"));
116 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq2"));
117 REQUIRE_NO_FAIL(con.Query(
118 "CREATE TABLE integers(i INTEGER DEFAULT nextval('seq' || CAST(nextval('seq') AS VARCHAR)), j INTEGER)"));
119
120 // seq1 exists, so the result of the first default value is 1
121 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers (j) VALUES (1)"));
122 // we can drop seq1 and seq2: the dependency is not fixed
123 REQUIRE_NO_FAIL(con.Query("DROP SEQUENCE seq1"));
124 REQUIRE_NO_FAIL(con.Query("DROP SEQUENCE seq2"));
125 // seq2 does not exist after this drop, so another insert fails
126 REQUIRE_FAIL(con.Query("INSERT INTO integers (j) VALUES (1)"));
127 // table is now [1, 1]: query it
128 result = con.Query("SELECT SUM(i) FROM integers");
129 REQUIRE(CHECK_COLUMN(result, 0, {1}));
130 // we can't drop seq however: the dependency is fixed
131 REQUIRE_FAIL(con.Query("DROP SEQUENCE seq"));
132 // need to do a cascading drop
133 REQUIRE_NO_FAIL(con.Query("DROP SEQUENCE seq CASCADE"));
134 // now the table is gone
135 REQUIRE_FAIL(con.Query("SELECT * FROM integers"));
136}
137
138TEST_CASE("Prepare dependencies and transactions", "[catalog]") {
139 unique_ptr<QueryResult> result;
140 DuckDB db(nullptr);
141 Connection con(db);
142 Connection con2(db);
143
144 // case one: prepared statement is created outside of transaction and committed
145 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
146 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3), (4), (5)"));
147 REQUIRE_NO_FAIL(con2.Query("PREPARE v AS SELECT SUM(i) FROM integers"));
148
149 // begin a transaction in con2
150 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION"));
151 // now drop the table in con, with a cascading drop
152 REQUIRE_NO_FAIL(con.Query("DROP TABLE integers CASCADE"));
153 // we can still execute v in con2
154 result = con2.Query("EXECUTE v");
155 REQUIRE(CHECK_COLUMN(result, 0, {15}));
156 // if we try to drop integers we get a conflict though
157 REQUIRE_FAIL(con2.Query("DROP TABLE integers CASCADE"));
158 // now we rollback
159 REQUIRE_NO_FAIL(con2.Query("ROLLBACK"));
160 // now we can't use the prepared statement anymore
161 REQUIRE_FAIL(con2.Query("EXECUTE v"));
162
163 // case two: prepared statement is created inside transaction
164 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
165 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3), (4), (5)"));
166
167 // begin a transaction and create a prepared statement
168 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION"));
169 REQUIRE_NO_FAIL(con2.Query("PREPARE v AS SELECT SUM(i) FROM integers"));
170
171 // integers has a dependency: we can't drop it
172 REQUIRE_FAIL(con.Query("DROP TABLE integers"));
173 // now we can't drop integers even with cascade, because the dependency is not yet committed, this creates a write
174 // conflict on attempting to drop the dependency
175 REQUIRE_FAIL(con.Query("DROP TABLE integers CASCADE"));
176
177 // use the prepared statement
178 result = con2.Query("EXECUTE v");
179 REQUIRE(CHECK_COLUMN(result, 0, {15}));
180 // and commit
181 REQUIRE_NO_FAIL(con2.Query("COMMIT"));
182
183 // now we can commit the table
184 REQUIRE_NO_FAIL(con.Query("DROP TABLE integers CASCADE"));
185
186 // case three: prepared statement is created inside transaction, and then rolled back
187 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
188 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3), (4), (5)"));
189
190 // begin a transaction and create a prepared statement
191 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION"));
192 REQUIRE_NO_FAIL(con2.Query("PREPARE v AS SELECT SUM(i) FROM integers"));
193 // integers has a dependency: we can't drop it
194 REQUIRE_FAIL(con.Query("DROP TABLE integers"));
195 // rollback the prepared statement
196 REQUIRE_NO_FAIL(con2.Query("ROLLBACK"));
197 // depedency was rolled back: now we can drop it
198 REQUIRE_NO_FAIL(con.Query("DROP TABLE integers"));
199
200 // case four: deallocate happens inside transaction
201 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
202 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3), (4), (5)"));
203 REQUIRE_NO_FAIL(con2.Query("PREPARE v AS SELECT SUM(i) FROM integers"));
204
205 // deallocate v inside transaction
206 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION"));
207 REQUIRE_NO_FAIL(con2.Query("DEALLOCATE v"));
208
209 // we still can't drop integers because the dependency is still there
210 REQUIRE_FAIL(con.Query("DROP TABLE integers"));
211 // cascade gives a concurrency conflict
212 REQUIRE_FAIL(con.Query("DROP TABLE integers CASCADE"));
213 // now rollback the deallocation
214 REQUIRE_NO_FAIL(con2.Query("ROLLBACK"));
215 // still can't drop the table
216 REQUIRE_FAIL(con.Query("DROP TABLE integers"));
217 // we can use the prepared statement again
218 result = con2.Query("EXECUTE v");
219 REQUIRE(CHECK_COLUMN(result, 0, {15}));
220 // now do the same as before, but commit the transaction this time
221 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION"));
222 REQUIRE_NO_FAIL(con2.Query("DEALLOCATE v"));
223 // can't drop yet: not yet committed
224 REQUIRE_FAIL(con.Query("DROP TABLE integers"));
225 REQUIRE_FAIL(con.Query("DROP TABLE integers CASCADE"));
226 REQUIRE_NO_FAIL(con2.Query("COMMIT"));
227 // after committing we can drop
228 REQUIRE_NO_FAIL(con.Query("DROP TABLE integers"));
229}
230
231TEST_CASE("Test prepare dependencies with multiple connections", "[catalog]") {
232 unique_ptr<QueryResult> result;
233 DuckDB db(nullptr);
234 auto con = make_unique<Connection>(db);
235 auto con2 = make_unique<Connection>(db);
236 auto con3 = make_unique<Connection>(db);
237
238 // simple prepare: begin transaction before the second client calls PREPARE
239 REQUIRE_NO_FAIL(con->Query("CREATE TABLE integers(i INTEGER)"));
240 // open a transaction in con2, this forces the prepared statement to be kept around until this transaction is closed
241 REQUIRE_NO_FAIL(con2->Query("BEGIN TRANSACTION"));
242 // we prepare a statement in con
243 REQUIRE_NO_FAIL(con->Query("PREPARE s1 AS SELECT * FROM integers"));
244 // now we drop con while the second client still has an active transaction
245 con.reset();
246 // now commit the transaction in the second client
247 REQUIRE_NO_FAIL(con2->Query("COMMIT"));
248
249 con = make_unique<Connection>(db);
250 // three transactions
251 // open a transaction in con2, this forces the prepared statement to be kept around until this transaction is closed
252 REQUIRE_NO_FAIL(con2->Query("BEGIN TRANSACTION"));
253 // create a prepare, this creates a dependency from s1 -> integers
254 REQUIRE_NO_FAIL(con->Query("PREPARE s1 AS SELECT * FROM integers"));
255 // drop the client
256 con.reset();
257 // now begin a transaction in con3
258 REQUIRE_NO_FAIL(con3->Query("BEGIN TRANSACTION"));
259 // drop the table integers with cascade, this should drop s1 as well
260 REQUIRE_NO_FAIL(con3->Query("DROP TABLE integers CASCADE"));
261 REQUIRE_NO_FAIL(con2->Query("COMMIT"));
262 REQUIRE_NO_FAIL(con3->Query("COMMIT"));
263}
264
265#define REPETITIONS 100
266#define SEQTHREADS 10
267volatile bool finished = false;
268
269static void RunQueryUntilSuccess(Connection &con, string query) {
270 while (true) {
271 auto result = con.Query(query);
272 if (result->success) {
273 break;
274 }
275 }
276}
277
278static void create_drop_table(DuckDB *db) {
279 Connection con(*db);
280
281 while (!finished) {
282 // printf("[TABLE] Create table\n");
283 // create the table: this should never fail
284 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
285 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
286 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3), (4), (5)"));
287 REQUIRE_NO_FAIL(con.Query("COMMIT"));
288 // now wait a bit
289 this_thread::sleep_for(chrono::milliseconds(20));
290 // printf("[TABLE] Drop table\n");
291 // perform a cascade drop of the table
292 // this can fail if a thread is still busy preparing a statement
293 RunQueryUntilSuccess(con, "DROP TABLE integers CASCADE");
294 }
295}
296
297static void create_use_prepared_statement(DuckDB *db) {
298 Connection con(*db);
299 unique_ptr<QueryResult> result;
300
301 for (int i = 0; i < REPETITIONS; i++) {
302 // printf("[PREPARE] Prepare statement\n");
303 RunQueryUntilSuccess(con, "PREPARE s1 AS SELECT SUM(i) FROM integers");
304 // printf("[PREPARE] Query prepare\n");
305 while (true) {
306 // execute the prepared statement until the prepared statement is dropped because of the CASCADE in another
307 // thread
308 result = con.Query("EXECUTE s1");
309 if (!result->success) {
310 break;
311 } else {
312 REQUIRE(CHECK_COLUMN(result, 0, {15}));
313 }
314 }
315 }
316}
317
318TEST_CASE("Test parallel dependencies in multiple connections", "[catalog][.]") {
319 DuckDB db(nullptr);
320
321 // in this test we create and drop a table in one thread (with CASCADE drop)
322 // in the other thread, we create a prepared statement and execute it
323 // the prepared statement depends on the table
324 // hence when the CASCADE drop is executed the prepared statement also needs to be dropped
325
326 thread table_thread = thread(create_drop_table, &db);
327 thread seq_threads[SEQTHREADS];
328 for (int i = 0; i < SEQTHREADS; i++) {
329 seq_threads[i] = thread(create_use_prepared_statement, &db);
330 }
331 for (int i = 0; i < SEQTHREADS; i++) {
332 seq_threads[i].join();
333 }
334 finished = true;
335 table_thread.join();
336}
337
338static void create_drop_schema(DuckDB *db) {
339 Connection con(*db);
340
341 while (!finished) {
342 // create the schema: this should never fail
343 REQUIRE_NO_FAIL(con.Query("CREATE SCHEMA s1"));
344 // now wait a bit
345 this_thread::sleep_for(chrono::milliseconds(20));
346 // perform a cascade drop of the schema
347 // this can fail if a thread is still busy creating something inside the schema
348 RunQueryUntilSuccess(con, "DROP SCHEMA s1 CASCADE");
349 }
350}
351
352static void create_use_table_view(DuckDB *db, int threadnr) {
353 Connection con(*db);
354 unique_ptr<QueryResult> result;
355 string tname = "integers" + to_string(threadnr);
356 string vname = "v" + to_string(threadnr);
357
358 for (int i = 0; i < REPETITIONS; i++) {
359 RunQueryUntilSuccess(con, "CREATE TABLE s1." + tname + "(i INTEGER)");
360 con.Query("INSERT INTO s1." + tname + " VALUES (1), (2), (3), (4), (5)");
361 RunQueryUntilSuccess(con, "CREATE VIEW s1." + vname + " AS SELECT 42");
362 while (true) {
363 result = con.Query("SELECT SUM(i) FROM s1." + tname);
364 if (!result->success) {
365 break;
366 } else {
367 REQUIRE(CHECK_COLUMN(result, 0, {15}));
368 }
369 result = con.Query("SELECT * FROM s1." + vname);
370 if (!result->success) {
371 break;
372 } else {
373 REQUIRE(CHECK_COLUMN(result, 0, {42}));
374 }
375 }
376 }
377}
378TEST_CASE("Test parallel dependencies with schemas and tables", "[catalog][.]") {
379 DuckDB db(nullptr);
380 // FIXME: this test crashes
381 return;
382
383 // in this test we create and drop a schema in one thread (with CASCADE drop)
384 // in other threads, we create tables and views and query those tables and views
385
386 thread table_thread = thread(create_drop_schema, &db);
387 thread seq_threads[SEQTHREADS];
388 for (int i = 0; i < SEQTHREADS; i++) {
389 seq_threads[i] = thread(create_use_table_view, &db, i);
390 }
391 for (int i = 0; i < SEQTHREADS; i++) {
392 seq_threads[i].join();
393 }
394 finished = true;
395 table_thread.join();
396}
397