Date: 2013-08-08 10:27:52 +0200
From: @hannesmuehleisen
To: SQL devs <>
Version: -- development
CC: akkaran046, barrysingh103, @njnes

Last updated: 2020-03-12 09:38:02 +0100

Comment 18981

Date: 2013-08-08 10:27:52 +0200
From: @hannesmuehleisen

User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/29.0.1547.41 Safari/537.36
Build Identifier:

Truncating tables is an everyday operation. Ideally, if all rows are removed (and certainly if the table is dropped), the related files on disk should also be removed. However, this seems to be not the case. In effect, there is currently no way of truncating a table without using the following workaround:

create table sometable_copy as select * from sometable with no data;
drop table sometable;
create table sometable as select * from sometable_copy with no data;

Reproducible: Always

Steps to Reproduce:

1. create and start new database
2. create table
3. copy data into table
4. delete from table
5. restart DB
6. observe BAT files as reported by storage(), they are still present

Actual Results:

BAT files are still present and at their original size

Expected Results:

BAT files would be either gone or empty

monetdb create sstest
monetdb release sstest
mclient sstest -s "create table starships(class string, speed int, flux int);"
mclient sstest -s "copy into sys.starships from '/export/scratch2/hannes/starships/starships.csv.noheader' using delimiters ',','\r\n';"

mclient sstest -s "select "location","count" from storage() where "table"='starships';"
+----------+----------+
| location | count |
+==========+==========+
| 04/453 | 68399658 |
| 04/460 | 68399658 |
| 04/461 | 68399658 |
+----------+----------+
3 tuples
ls sstest/bat/04/460.tail -lh
-rw------- 1 hannes da 261M Aug 8 10:14 sstest/bat/04/460.tail

okay, files are there and big, now let's delete the rows
mclient sstest -s "delete from starships;"
mclient sstest -s "select "location","count" from storage() where "table"='starships';"
+----------+-------+
| location | count |
+==========+=======+
| 04/453 | 0 |
| 04/460 | 0 |
| 04/461 | 0 |
+----------+-------+
3 tuples

file remains with original size
ls sstest/bat/04/460.tail -lh
-rw------- 1 hannes da 261M Aug 8 10:14 sstest/bat/04/460.tail

drop the table

mclient sstest -s "drop table starships;"
mclient sstest -s "select "location","count" from storage() where "table"='starships';"
+----------+-------+
| location | count |
+==========+=======+
+----------+-------+
0 tuples

file still remains
ls sstest/bat/04/460.tail -lh
-rw------- 1 hannes da 261M Aug 8 10:14 sstest/bat/04/460.tail

also remains after db is stopped
monetdb stop sstest
ls sstest/bat/04/460.tail -lh
-rw------- 1 hannes da 261M Aug 8 10:14 sstest/bat/04/460.tail

file is removed after db is restarted
monetdb start sstest
ls sstest/bat/04/460.tail -lh
ls: cannot access sstest/bat/04/460.tail: No such file or directory

now let's see if the file also is removed after only a DELETE FROM table
create, start, create table and copy into as above

mclient sstest -s "select "location","count" from storage() where "table"='starships';"
+----------+----------+
| location | count |
+==========+==========+
| 04/453 | 68399658 |
| 04/460 | 68399658 |
| 04/461 | 68399658 |
+----------+----------+
3 tuples

ls sstest/bat/04/460.tail -lh
-rw------- 1 hannes da 261M Aug 8 10:20 sstest/bat/04/460.tail

okay

mclient sstest -s "select "location","count" from storage() where "table"='starships';"
+----------+-------+
| location | count |
+==========+=======+
| 04/453 | 0 |
| 04/460 | 0 |
| 04/461 | 0 |
+----------+-------+
3 tuples

file again remains
ls sstest/bat/04/460.tail -lh
-rw------- 1 hannes da 261M Aug 8 10:20 sstest/bat/04/460.tail

also after stopping the server
monetdb stop sstest
ls sstest/bat/04/460.tail -lh
-rw------- 1 hannes da 261M Aug 8 10:20 sstest/bat/04/460.tail

restart server
monetdb start sstest
mclient sstest -s "select "location","count" from storage() where "table"='starships';"
+----------+-------+
| location | count |
+==========+=======+
| 04/453 | 0 |
| 04/460 | 0 |
| 04/461 | 0 |
+----------+-------+
3 tuples

file is still there with original size
ls sstest/bat/04/460.tail -lh
-rw------- 1 hannes da 261M Aug 8 10:20 sstest/bat/04/460.tail

Comment 19060

Date: 2013-08-23 13:25:14 +0200
From: @njnes

seems fixed after truncating of cached heaps. Were can I find the test files?

Comment 19061

Date: 2013-08-23 13:29:41 +0200
From: @hannesmuehleisen

CSV file is at http://homepages.cwi.nl/~hannes/starships.csv.noheader.bz2

Comment 19064

Date: 2013-08-23 16:17:26 +0200
From: @njnes

added a test in benchmarks/tpch/fileleak. This shows indeed that the current HEAPshrink fixes this.

Comment 26768

Date: 2018-12-31 07:14:12 +0100
From: martin garix <>

Download mozilla firefox latest version,this is batter search browser then more,i am sure you felling very well https://foxdownload.org i have giving the 4 star this game,now looking here,and using the one of the simple method.