Thread: BUG #15512: Creating index on 119 M files table generates error[could not determine size of temporary file "0"]
BUG #15512: Creating index on 119 M files table generates error[could not determine size of temporary file "0"]
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 15512 Logged by: Joan Sánchez Sabé Email address: joan@sanchezsabe.com PostgreSQL version: 11.1 Operating system: Windows 10 Description: This sequence of SQL statements will produce an error "could not determine size of temporary file "0"". It is reproducible, and happens on PostgreSQL 11.0 and 11.1 systems. Steps to reproduce: ------------------ /** -- Script takes about 36 min on Windows 10, CPU intel core i7 @ 2.6 GHz, 8 GB RAM, SSD storage -- Installer: https://www.enterprisedb.com/downloads/postgres-postgresql-downloads -- PostgreSQL parameters: default from install ## Most common parameters... shared_buffers = 128MB #huge_pages = try #temp_buffers = 8MB #max_prepared_transactions = 0 #work_mem = 4MB #maintenance_work_mem = 64MB #autovacuum_work_mem = -1 #max_stack_depth = 2MB dynamic_shared_memory_type = window #effective_io_concurrency = 0 #max_worker_processes = 8 #max_parallel_maintenance_workers = 2 #max_parallel_workers_per_gather = 2 #parallel_leader_participation = on #max_parallel_workers = 8 **/ CREATE DATABASE test WITH TEMPLATE = template1 ENCODING = 'UTF8' CONNECTION LIMIT = -1; CREATE EXTENSION pgcrypto; CREATE SEQUENCE items_key_seq; -- Create table with just one text column, that will contain text (actually base64 chars) CREATE TABLE items ( key text NOT NULL DEFAULT encode(digest('salt' || nextval('items_key_seq'::regclass), 'md5'), 'base64') ) ; -- Fill table with "a lot" of data. A smaller amount didn't show the problem in our tests. INSERT INTO items SELECT /* Nothing, actually. We just use the default value */ FROM generate_series(0, 119000100) ; -- Add a UNIQUE constraint... that will implicitly create a unique index ALTER TABLE items ADD CONSTRAINT items_unique_key UNIQUE (key); /** After some 20 minutes, the following error appears on the PostgreSQL log: 2018-11-15 12:32:02.841 CET [12200] ERROR: could not determine size of temporary file "0" 2018-11-15 12:32:02.841 CET [12200] SENTENCIA: ALTER TABLE items ADD CONSTRAINT items_unique_key UNIQUE (key); 2018-11-15 12:32:03.127 CET [9072] LOG: could not rmdir directory "base/pgsql_tmp/pgsql_tmp12200.2.sharedfileset": Directory not empty NOTE: pgAdmin4 may NOT show the error; but it's on the log, and the constraint isn't in place. **/ -- This also produces the same error CREATE UNIQUE INDEX ON items(key); /* 2018-11-15 13:01:40.532 CET [12200] ERROR: could not determine size of temporary file "0" 2018-11-15 13:01:40.532 CET [12200] SENTENCIA: CREATE UNIQUE INDEX ON items(key); 2018-11-15 13:01:40.775 CET [12200] LOG: could not rmdir directory "base/pgsql_tmp/pgsql_tmp12200.5.sharedfileset": Directory not empty */ -- The same thing happens when you have an emptied table, and insert data DELETE FROM items ; CREATE UNIQUE INDEX ON items(key); INSERT INTO items SELECT FROM generate_series(0, 119000100) ; /* 2018-11-15 13:41:51.567 CET [12200] ERROR: could not determine size of temporary file "0" 2018-11-15 13:41:51.567 CET [12200] SENTENCIA: DELETE FROM items ; CREATE UNIQUE INDEX ON items(key); INSERT INTO items SELECT FROM generate_series(0, 119000100) ; 2018-11-15 13:41:51.839 CET [11508] LOG: could not rmdir directory "base/pgsql_tmp/pgsql_tmp12200.8.sharedfileset": Directory not empty 2018-11-15 13:41:51.841 CET [11508] LOG: could not rmdir directory "base/pgsql_tmp/pgsql_tmp12200.7.sharedfileset": Directory not empty */ -- But it doesn't happen if you VACUUM first: DELETE FROM items ; CREATE UNIQUE INDEX ON items(key); VACUUM FULL ANALYZE items ; INSERT INTO items SELECT FROM generate_series(0, 119000100) ; /* Works ok */ EXPLAIN SELECT min(key) FROM items ; -- Uses index
Re: BUG #15512: Creating index on 119 M files table generates error[could not determine size of temporary file "0"]
From
Thomas Munro
Date:
On Tue, Nov 20, 2018 at 12:06 AM PG Bug reporting form <noreply@postgresql.org> wrote: > Bug reference: 15512 > Logged by: Joan Sánchez Sabé > Email address: joan@sanchezsabe.com > PostgreSQL version: 11.1 > Operating system: Windows 10 > Description: > > This sequence of SQL statements will produce an error "could not determine > size of temporary file "0"". It is reproducible, and happens on PostgreSQL > 11.0 and 11.1 systems. Hello Joan, Thanks for the report. This was reported also here: https://www.postgresql.org/message-id/flat/15460-b6db80de822fa0ad%40postgresql.org https://www.postgresql.org/message-id/flat/CAHDGBJP_GsESbTt4P3FZA8kMUKuYxjg57XHF7NRBoKnR%3DCAR-g%40mail.gmail.com It is fixed in REL_11_STABLE and master, scheduled for release in 11.2 (though I am still waiting for confirmation from someone who can actually test the fix on Windows). In the meantime you can work around the problem with: SET max_parallel_maintenance_workers = 0 -- Thomas Munro http://www.enterprisedb.com