Sample Header Ad - 728x90

Temporary relation files not removed after sessions ends - Single PostgreSQL instance PostgreSQL 13

0 votes
0 answers
43 views
We are facing default temporary tablespace disk space full issue due to lots of below temporary relations files created but not removed after ending session
1.1G    t756_1536213694.409
1.1G    t756_1536213694.410
1.1G    t756_1536213694.411
1.1G    t756_1536213694.412
1.1G    t756_1536213694.413
1.1G    t756_1536213694.414
Please note: - these files are not in pgsql_tmp <-- this is used for temporary files like sorting etc. these file are created in temp_tablespace location which default tablespace currenlty.
spin=# show temp_tablespaces ;
 temp_tablespaces
------------------

(1 row)
I tried to get the backend id from below query but no luck as process id is changing every time when execute the below query .
spin=# SELECT bid, pg_stat_get_backend_pid(bid) AS pid FROM pg_stat_get_backend_idset() bid where bid='756';
 bid |  pid
-----+-------
 756 | 41533
(1 row) 
 spin=# SELECT bid, pg_stat_get_backend_pid(bid) AS pid FROM pg_stat_get_backend_idset() bid where bid='756';
 bid |  pid
-----+-------
 756 | 86639
(1 row)
Questions: 1. Can we remove directly ?
           2. are these files are removed once db restart ? 
           3. How WE CAN find which session created these temp relations which is not get removed once session ends ? 
thanks for your help.
Asked by Adam Mulla (143 rep)
Sep 2, 2024, 12:32 PM
Last activity: Sep 3, 2024, 11:50 AM