BUG #15512: Creating index on 119 M files table generates error[could not determine size of temporary file "0"] - Mailing list pgsql-bugs

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