Duckdb: 0.2.1 memory_limit does not apply to WAL flush

[wizzard0]

STR:

.open 1.db
pragma memory_limit='1GB';
create table stars1 as select * from read_csv_auto('star2002-full.csv');
create table stars2 as select * from read_csv_auto('star2002-full.csv');
.open 1.db

peak memory consumption is just over 5G during second .open
dataset from https://sdm.lbl.gov/fastbit/data/samples.html

likely worth to investigate before #49 too

[Mytherin]

The problem here is that the pragma only applies to the first database, calling .open restarts the database, and thus removes the pragma. In principle the memory limit should be respected during the checkpoint. A fix here could be to "remember" options in the shell between calls to .open, but a more long-term fix is indeed to enable checkpointing during run-time and incremental checkpointing.

[hannesmuehleisen]

And I'd like to keep shell.c unmodified so it can be easily replaced ^^ #931

[wizzard0]

The problem here is that the pragma only applies to the first database, calling .open restarts the database

well sure, but there's no way to insert the pragma before checkpointing :) i'm fine with hoops and workarounds, but when there's no way to limit RAM at all it's a problem

[Mytherin]

Indeed you are correct, in the localstoragerework2 branch I have set the default memory limit to 80% of system memory. That should solve this problem in most use cases. To really solve it we need to allow other clients (besides the C++ one) to start the database with non-default settings.

[arjenpdevries]

Mark, is there a brief instruction on how to achieve this (setting a lower memory limit)? I would like to let some students look into IR collection ranking with DuckDB, but if they already run in trouble loading the tables, it will be hell to supervise :-)

[Mytherin]

This problem should only occur when re-loading a database after it has been saved on disk, and it should only be a problem if the data is bigger than the available memory. Working only in an in-memory database, or working with data smaller than 1GB~ shouldn't be a problem. Otherwise you will need to wait for my PR, which should hopefully be around this week.