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
Last activity: Sep 3, 2024, 11:50 AM