1#include "catch.hpp"
2#include "duckdb/common/file_system.hpp"
3#include "test_helpers.hpp"
4#include "duckdb/storage/storage_info.hpp"
5
6using namespace duckdb;
7using namespace std;
8
9TEST_CASE("Test scanning a table and computing an aggregate over a table that exceeds buffer manager size",
10 "[storage][.]") {
11 unique_ptr<MaterializedQueryResult> result;
12 auto storage_database = TestCreatePath("storage_test");
13 auto config = GetTestConfig();
14
15 // set the maximum memory to 10MB
16 config->maximum_memory = 10000000;
17
18 int64_t expected_sum;
19 Value sum;
20 // make sure the database does not exist
21 DeleteDatabase(storage_database);
22 {
23 // create a database and insert values
24 DuckDB db(storage_database, config.get());
25 Connection con(db);
26 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER);"));
27 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 22), (13, 22), (12, 21), (NULL, NULL)"));
28 uint64_t table_size = 2 * 4 * sizeof(int);
29 uint64_t desired_size = 10 * config->maximum_memory;
30 expected_sum = 11 + 12 + 13 + 22 + 22 + 21;
31 // grow the table until it exceeds 100MB
32 while (table_size < desired_size) {
33 REQUIRE_NO_FAIL(con.Query("INSERT INTO test SELECT * FROM test"));
34 table_size *= 2;
35 expected_sum *= 2;
36 }
37 sum = Value::BIGINT(expected_sum);
38 // compute the sum
39 result = con.Query("SELECT SUM(a) + SUM(b) FROM test");
40 REQUIRE(CHECK_COLUMN(result, 0, {sum}));
41 }
42 for (idx_t i = 0; i < 2; i++) {
43 DuckDB db(storage_database, config.get());
44 Connection con(db);
45 result = con.Query("SELECT SUM(a) + SUM(b) FROM test");
46 REQUIRE(CHECK_COLUMN(result, 0, {sum}));
47 }
48 DeleteDatabase(storage_database);
49}
50
51TEST_CASE("Test storing a big string that exceeds buffer manager size", "[storage][.]") {
52 unique_ptr<MaterializedQueryResult> result;
53 auto storage_database = TestCreatePath("storage_test");
54 auto config = GetTestConfig();
55
56 uint64_t string_length = 64;
57 uint64_t desired_size = 10000000; // desired size is 10MB
58 uint64_t iteration = 2;
59 // make sure the database does not exist
60 DeleteDatabase(storage_database);
61 {
62 // create a database and insert the big string
63 DuckDB db(storage_database, config.get());
64 Connection con(db);
65 string big_string = string(string_length, 'a');
66 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a VARCHAR, j BIGINT);"));
67 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES ('" + big_string + "', 1)"));
68 while (string_length < desired_size) {
69 REQUIRE_NO_FAIL(con.Query("INSERT INTO test SELECT a||a||a||a||a||a||a||a||a||a, " + to_string(iteration) +
70 " FROM test"));
71 REQUIRE_NO_FAIL(con.Query("DELETE FROM test WHERE j=" + to_string(iteration - 1)));
72 iteration++;
73 string_length *= 10;
74 }
75
76 // check the length
77 result = con.Query("SELECT LENGTH(a) FROM test");
78 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(string_length)}));
79 result = con.Query("SELECT j FROM test");
80 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(iteration - 1)}));
81 }
82 {
83 DuckDB db(storage_database, config.get());
84 Connection con(db);
85 result = con.Query("SELECT LENGTH(a) FROM test");
86 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(string_length)}));
87 result = con.Query("SELECT j FROM test");
88 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(iteration - 1)}));
89 }
90 // now reload the database, but this time with a max memory of 5MB
91 {
92 config->maximum_memory = 5000000;
93 DuckDB db(storage_database, config.get());
94 Connection con(db);
95 // we can still select the integer
96 result = con.Query("SELECT j FROM test");
97 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(iteration - 1)}));
98 // however the string is too big to fit in our buffer manager
99 REQUIRE_FAIL(con.Query("SELECT LENGTH(a) FROM test"));
100 }
101 {
102 // reloading with a bigger limit again makes it work
103 config->maximum_memory = (idx_t)-1;
104 DuckDB db(storage_database, config.get());
105 Connection con(db);
106 result = con.Query("SELECT LENGTH(a) FROM test");
107 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(string_length)}));
108 result = con.Query("SELECT j FROM test");
109 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(iteration - 1)}));
110 }
111 DeleteDatabase(storage_database);
112}
113
114TEST_CASE("Test appending and checkpointing a table that exceeds buffer manager size", "[storage][.]") {
115 unique_ptr<MaterializedQueryResult> result;
116 auto storage_database = TestCreatePath("storage_test");
117 auto config = GetTestConfig();
118
119 // maximum memory is 10MB
120 config->maximum_memory = 10000000;
121
122 // create a table of size 10 times the buffer pool size
123 uint64_t size = 0, size_a, sum_a, sum_b;
124 uint64_t table_size = 100000000 / sizeof(int32_t);
125 // make sure the database does not exist
126 DeleteDatabase(storage_database);
127 {
128 // create a database and insert the big string
129 DuckDB db(storage_database, config.get());
130 Connection con(db);
131 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER);"));
132 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (1, 10), (2, 20), (3, 30), (NULL, NULL)"));
133 size_a = 3;
134 sum_a = 1 + 2 + 3;
135 sum_b = 10 + 20 + 30;
136 for (size = 4; size < table_size; size *= 2) {
137 REQUIRE_NO_FAIL(con.Query("INSERT INTO test SELECT * FROM test"));
138 size_a *= 2;
139 sum_a *= 2;
140 sum_b *= 2;
141 }
142
143 // check the aggregate statistics of the table
144 result = con.Query("SELECT COUNT(*), COUNT(a), SUM(a), SUM(b) FROM test");
145 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(size)}));
146 REQUIRE(CHECK_COLUMN(result, 1, {Value::BIGINT(size_a)}));
147 REQUIRE(CHECK_COLUMN(result, 2, {Value::BIGINT(sum_a)}));
148 REQUIRE(CHECK_COLUMN(result, 3, {Value::BIGINT(sum_b)}));
149 }
150 for (idx_t i = 0; i < 2; i++) {
151 // reload the table and checkpoint, still with a 10MB limit
152 DuckDB db(storage_database, config.get());
153 Connection con(db);
154
155 result = con.Query("SELECT COUNT(*), COUNT(a), SUM(a), SUM(b) FROM test");
156 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(size)}));
157 REQUIRE(CHECK_COLUMN(result, 1, {Value::BIGINT(size_a)}));
158 REQUIRE(CHECK_COLUMN(result, 2, {Value::BIGINT(sum_a)}));
159 REQUIRE(CHECK_COLUMN(result, 3, {Value::BIGINT(sum_b)}));
160 }
161 DeleteDatabase(storage_database);
162}
163
164TEST_CASE("Modifying the buffer manager limit at runtime for an in-memory database", "[storage][.]") {
165 unique_ptr<MaterializedQueryResult> result;
166
167 DuckDB db(nullptr);
168 Connection con(db);
169
170 // initialize an in-memory database of size 10MB
171 uint64_t table_size = (1000 * 1000) / sizeof(int);
172
173 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER);"));
174 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (1), (2), (3), (NULL)"));
175
176 idx_t not_null_size = 3;
177 idx_t size = 4;
178 idx_t sum = 6;
179 for (; size < table_size; size *= 2) {
180 REQUIRE_NO_FAIL(con.Query("INSERT INTO test SELECT * FROM test"));
181 not_null_size *= 2;
182 sum *= 2;
183 }
184
185 result = con.Query("SELECT COUNT(*), COUNT(a), SUM(a) FROM test");
186 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(size)}));
187 REQUIRE(CHECK_COLUMN(result, 1, {Value::BIGINT(not_null_size)}));
188 REQUIRE(CHECK_COLUMN(result, 2, {Value::BIGINT(sum)}));
189
190 // we can set the memory limit to 1GB
191 REQUIRE_NO_FAIL(con.Query("PRAGMA memory_limit='1GB'"));
192 // but we cannot set it below 10MB
193 REQUIRE_FAIL(con.Query("PRAGMA memory_limit='1MB'"));
194
195 // if we make room by dropping the table, we can set it to 1MB though
196 REQUIRE_NO_FAIL(con.Query("DROP TABLE test"));
197 REQUIRE_NO_FAIL(con.Query("PRAGMA memory_limit='1MB'"));
198
199 // also test that large strings are properly deleted
200 // reset the memory limit
201 REQUIRE_NO_FAIL(con.Query("PRAGMA memory_limit=-1"));
202
203 // create a table with a large string (10MB)
204 uint64_t string_length = 64;
205 uint64_t desired_size = 10000000; // desired size is 10MB
206 uint64_t iteration = 2;
207
208 string big_string = string(string_length, 'a');
209 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a VARCHAR, j BIGINT);"));
210 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES ('" + big_string + "', 1)"));
211 while (string_length < desired_size) {
212 REQUIRE_NO_FAIL(
213 con.Query("INSERT INTO test SELECT a||a||a||a||a||a||a||a||a||a, " + to_string(iteration) + " FROM test"));
214 REQUIRE_NO_FAIL(con.Query("DELETE FROM test WHERE j=" + to_string(iteration - 1)));
215 iteration++;
216 string_length *= 10;
217 }
218
219 // now we cannot set the memory limit to 1MB again
220 REQUIRE_FAIL(con.Query("PRAGMA memory_limit='1MB'"));
221 // but dropping the table allows us to set the memory limit to 1MB again
222 REQUIRE_NO_FAIL(con.Query("DROP TABLE test"));
223 REQUIRE_NO_FAIL(con.Query("PRAGMA memory_limit='1MB'"));
224}
225