Re: Fwd: temp_file_limit?

[Frits Jalvingh <jal@xxxxxx>]

@justin:

Ran the query again. Top shows the following processes:

   PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND                                                                                                                      
 650830 postgres  20   0 7503,2m   2,6g   2,6g R 100,0   4,2  12:46.34 postgres: jal datavault_317_prd [local] EXPLAIN                                                                              
 666141 postgres  20   0 7486,3m   2,6g   2,6g R 100,0   4,1   2:10.24 postgres: parallel worker for PID 650830

Your commands shows, during execution:

root@chatelet:/d2/var/lib/postgresql/15/main/base# du --max=2 -mx ./pgsql_tmp |sort -nr
68629 ./pgsql_tmp/pgsql_tmp650830.3.fileset
68629 ./pgsql_tmp

root@chatelet:/d2/var/lib/postgresql/15/main/base# du --max=2 -mx ./pgsql_tmp |sort -nr
194494 ./pgsql_tmp
194493 ./pgsql_tmp/pgsql_tmp650830.3.fileset

root@chatelet:/d2/var/lib/postgresql/15/main/base# du --max=2 -mx ./pgsql_tmp |sort -nr

335289 ./pgsql_tmp/pgsql_tmp650830.3.fileset
335289 ./pgsql_tmp

root@chatelet:/d2/var/lib/postgresql/15/main/base# du --max=2 -mx ./pgsql_tmp |sort -nr
412021 ./pgsql_tmp/pgsql_tmp650830.3.fileset
412021 ./pgsql_tmp

^^^ a few seconds after this last try the query aborted:

ERROR:  temporary file size exceeds temp_file_limit (104857600kB)

One possibility is that there are files in the tmpdir, which have been
unlinked, but are still opened, so their space hasn't been reclaimed.
You could check for that by running lsof -nn |grep pgsql_tmp Any deleted
files would say things like 'DEL|deleted|inode|no such'

I do not really understand what you would like me to do, and when. The disk space is growing, and it is actual files under pgsql_tmp?

Hope this tells you something, please let me know if you would like more info, and again - thanks!