1#include "catch.hpp"
2#include "test_helpers.hpp"
3#include "duckdb/storage/storage_info.hpp"
4
5using namespace duckdb;
6using namespace std;
7
8TEST_CASE("Test multiple versions of the same data", "[transactions]") {
9 unique_ptr<QueryResult> result;
10 DuckDB db(nullptr);
11 Connection con(db), con2(db);
12 con.EnableQueryVerification();
13
14 // initialize the database
15 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER);"));
16 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3);"));
17
18 // we can query the database using both connections
19 result = con.Query("SELECT SUM(i) FROM integers");
20 REQUIRE(CHECK_COLUMN(result, 0, {6}));
21 result = con2.Query("SELECT SUM(i) FROM integers");
22 REQUIRE(CHECK_COLUMN(result, 0, {6}));
23
24 // now update the database in connection 1
25 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION;"));
26 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET i=5 WHERE i=1;"));
27 result = con.Query("SELECT SUM(i) FROM integers");
28 REQUIRE(CHECK_COLUMN(result, 0, {10}));
29 // con 2 still has the same result
30 result = con2.Query("SELECT SUM(i) FROM integers");
31 REQUIRE(CHECK_COLUMN(result, 0, {6}));
32
33 // we can update the same data point again in con 1
34 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET i=10 WHERE i=5;"));
35 result = con.Query("SELECT SUM(i) FROM integers");
36 REQUIRE(CHECK_COLUMN(result, 0, {15}));
37 // con 2 still has the same result
38 result = con2.Query("SELECT SUM(i) FROM integers");
39 REQUIRE(CHECK_COLUMN(result, 0, {6}));
40
41 // now delete it
42 REQUIRE_NO_FAIL(con.Query("DELETE FROM integers WHERE i>5;"));
43 result = con.Query("SELECT SUM(i) FROM integers");
44 REQUIRE(CHECK_COLUMN(result, 0, {5}));
45 // con 2 still has the same result
46 result = con2.Query("SELECT SUM(i) FROM integers");
47 REQUIRE(CHECK_COLUMN(result, 0, {6}));
48
49 // insert some new data again
50 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2)"));
51 result = con.Query("SELECT SUM(i) FROM integers");
52 REQUIRE(CHECK_COLUMN(result, 0, {8}));
53 // con 2 still has the same result
54 result = con2.Query("SELECT SUM(i) FROM integers");
55 REQUIRE(CHECK_COLUMN(result, 0, {6}));
56
57 // now commit
58 REQUIRE_NO_FAIL(con.Query("COMMIT"));
59
60 // con 2 now has the updated results
61 result = con2.Query("SELECT SUM(i) FROM integers");
62 REQUIRE(CHECK_COLUMN(result, 0, {8}));
63}
64
65TEST_CASE("Test multiple versions of the same data with a data set that exceeds a single block", "[transactions]") {
66 unique_ptr<QueryResult> result;
67 DuckDB db(nullptr);
68 Connection con(db), con2(db);
69 con.EnableQueryVerification();
70
71 // set up the database
72 uint64_t integer_count = 2 * (Storage::BLOCK_SIZE / sizeof(int32_t));
73 uint64_t current_count = 4;
74 uint64_t expected_sum = 10;
75 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER);"));
76 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3), (4);"));
77 while (current_count < integer_count) {
78 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers SELECT * FROM integers"));
79 current_count *= 2;
80 expected_sum *= 2;
81 }
82 // verify the count and sum
83 result = con.Query("SELECT COUNT(*) FROM integers");
84 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(current_count)}));
85 result = con.Query("SELECT SUM(i) FROM integers");
86 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(expected_sum)}));
87
88 for (idx_t i = 1; i <= 4; i++) {
89 // now delete some tuples
90 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION;"));
91 REQUIRE_NO_FAIL(con.Query("DELETE FROM integers WHERE i=" + to_string(i)));
92
93 // check the updated count and sum
94 result = con.Query("SELECT COUNT(*) FROM integers");
95 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(current_count - (current_count / 4))}));
96 result = con.Query("SELECT SUM(i) FROM integers");
97 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(expected_sum - i * (current_count / 4))}));
98
99 // con2 still has the same count and sum
100 result = con2.Query("SELECT COUNT(*) FROM integers");
101 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(current_count)}));
102 result = con2.Query("SELECT SUM(i) FROM integers");
103 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(expected_sum)}));
104
105 // rollback
106 REQUIRE_NO_FAIL(con.Query("ROLLBACK;"));
107
108 // now the count and sum are back to normal
109 result = con.Query("SELECT COUNT(*) FROM integers");
110 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(current_count)}));
111 result = con.Query("SELECT SUM(i) FROM integers");
112 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(expected_sum)}));
113 }
114}
115