Error could not write to tuplestore temporary file no space left on device

I ended up with a table storing a network topology as follows:

create table topology.network_graph(
node_from_id varchar(50) not null,
node_to_id varchar(50) not null
PRIMARY KEY (node_from_id, node_to_id)
);

The expected output data in something like this, where all the sub-graphs starting from the node «A» are listed:

Now I try to find the paths between the nodes, starting at a specific node using this query:

WITH RECURSIVE network_nodes AS (
    select 
    node_from_id,       
    node_to_id,
    0 as hop_count,
    ,ARRAY[node_from_id::varchar(50), node_to_id::varchar(50)] AS "path"
    from topology.network_graph
    where  node_from_id = '_9EB23E6C4C824441BB5F75616DEB8DA7' --Set this node as the starting element
    
    union
    
    select  
    nn.node_from_id,        
    ng.node_to_id,
    nn.hop_count + 1 as hop_count,
    , (nn."path" || ARRAY[ng.node_to_id])::varchar(50)[]  AS "path" 
    from topology.network_graph as ng
    inner join network_nodes as nn
    on ng.node_from_id = nn.node_to_id
    and ng.node_to_id != ALL(nn."path")
)
select node_from_id, node_to_id, hop_count, "path"
from network_nodes 
order by node_from_id, node_to_id, hop_count  ;

The query runs several minutes before throwing the error:

could not write to tuplestore temporary file: No space left on device
recursive query postgresql

The topology.network_graph has 2148 records and during the query execution the base/pgsql_tmp directory grows bis some GBs. It seems I have an infinite loop.

Can someone find what could be wrong?

In this blog post I am going to simulate a potential Denial of Service attack by hammering the postgresql database with temporary table that will exhaust the PostgreSQL PGDATA directory.

Basically by default PostgreSQL doesn’t have a “dedicated” Temporary tablespace.

show temp_tablespaces ;

So, if you create a “temporary” table this table will be stored under base directory under $PGDATA directory (which is the main PostgreSQL directory).

To simulate, I will create an account called “emad_usr” under a database called “emad” with no really high permissions as you will see:

create user emad_usr with password ’emad_123′;

 GRANT CONNECT ON DATABASE emad TO emad_usr;

 // I will connect using the account and will create a temporary table

 psql -h localhost -U emad_usr  -d emad

 create temporary table tempo as select * from generate_series(1,1000000000000000000000000000000000000);

This will eventually lead to fully filling the PGDATA directory and will cause the service to be down !

And the following error will be thrown:

ERROR: could not write to tuplestore temporary file: No space left on device

Methods to protect against this type of Denial of Service Attack:

1. The first method to avoid such attack you need to define a dedicated temporary tablespacein a directory different than $PGDATA directory:

mkdir /var/lib/postgresql/tmp_tbs

psql

create tablespace temp_tablespc location ‘/var/lib/postgresql/tmp_tbs’;

alter system set temp_tablespaces = ‘temp_tablespc’;

select pg_reload_conf();

 show temp_tablespaces ;

To confirm the setup, a temporary table will be created:

create temporary table tempo as select * from generate_series(1,100);

select pg_relation_filepath(‘tempo’);

2. Second method to protect against Denial of Service Attack is to set a limitation of the temp file sizethrough the parameter “temp_file_limit” :

show temp_file_limit;

By default -1 means unlimited

set temp_file_limit=’4MB’;

If somebody attempts to create a very large temporary table an error will be thrown:

create temporary table tempo as select * from generate_series(1,1000000000000000000000000000000000000);

ERROR:  temporary file size exceeds temp_file_limit (4096kB)

3. A third way is revoking create temporary tables from a database level , please note that this might have an impact if the application requires sorting or temp file operations:

psql

REVOKE TEMPORARY ON DATABASE emad FROM PUBLIC;

psql -h localhost -U emad_usr  -d emad

emad=> create temporary table tempo as select * from generate_series(1,10);

ERROR:  permission denied to create temporary tables in database “emad”

1- How do the internals of postgres work that cause this message to occur in the first place?

When joining two very large data sets, one strategy PostgreSQL can use is to scan one table and hash its join columns. Then scan the other table and compare the hashes of the join columns to hashes calculated for the second table.

See https://en.wikipedia.org/wiki/Hash_join

It works really well when you’re joining a small table against a much larger one, especially where no indexes suit the join conditions.

PostgreSQL can use a hash join strategy for bigger tables where it writes the hash table partitions out to disk first. This is presumably what you got.

What is the hash-join in reference to the creation of the temporary file that the db creates?

Hash tables. I don’t really understand this question.

2- How can I predict and prevent errors like this (related to performance and memory) occurring in the future?

Have more spare disk space for temporary storage. It doesn’t have to be in the main tablespace; see the temp_tablespaces setting.

As far as I know PostgreSQL doesn’t have the ability to estimate hash table sizes and decide the hash table will be too big for the available disk space.

Описание ошибки:
На сервере ошибка возникла в обоих вариантах одновременно в двух базах. Только первый текст ошибки — для файловой базы, а второй — для серверной. Каталог файловой базы данных и папки СУБД сервеной базы находились на одном фиизческом диске.

Найденные решения:

Ошибка записи файла базы данных (возможно, нет места на диске) ‘К<каталог_базы>’/1Cv8tmp.1CD

Ошибка СУБД:
ERROR: could not write block 41 of temporary file: No space left on device

Формулировка текста ошибки на русском языке проявила себя быстрее в файловой базе. По формулировке ошибки в принципе понятно, что не просто возможно, а на самом деле место на жестком диске закончилось. Т.е. информирование в ошибке о недостатке физического пространства, как в русской, так и в английской формулировке «No space left on device» (На устройстве нет свободного места) — говорит об первом, что нужно сделать — проверить количество свободной памяти на жестком диске, где размещена база(ы).

Оцените, помогло ли Вам предоставленное описание решения ошибки?

© www.azhur-c.ru 2014-2020. Все права защищены. Использование текстов и изображений с данной страницы без письменного разрешения владельца запрещено. При использовании материалов с данной страницы обязательно указание ссылки на данную страницу.

06-02-2020

Журавлев А.С.
(Сайт azhur-c.ru)