Subject: Even if I have a lot of free space PgSQL returns "ERROR: could not extend file - No space left on device"

From: Nature Conservation Geovista Space <[email-masked]>
Date: Fri, 8 Sep 2023 12:22:58 +0100

Dear Pg-users,

I am coming back to Postgres/PostGIS after a few years. I am dealing with a
big database with a lot of geometries and too many vertices.

After hours running a query to Subdivide, I get this Postgres error

*2023-09-08 02:11:23.745 BST [328594] postgres@database ERROR:  could not
extend file "base/16388/7985375.1020": No space left on device 2023-09-08
02:11:23.745 BST [328594] postgres@database HINT:  Check free disk space.*

2023-09-08 02:11:23.745 BST [328594] postgres@database  STATEMENT:  CREATE
TABLE _gaul_administrative_subdivided100 AS (
                SELECT *, st_subdivide(geom,100) AS geom_subdivided100
                        FROM gaul_administrative
        );

*2023-09-08 02:15:38.251 BST [313729] LOG:  checkpoint complete: wrote
81956 buffers (1.6%); 0 WAL file(s) added, 0 removed, 608 recycled;
write=269.414 s, sync=0.001 s, total=269.634 s; sync files=1, longest=0.001
s, average=0.001 s; distance=9962549 kB, estimate=9980351 kB;
lsn=291/BF46ABE8, redo lsn=291/A0FB7D98*

It seems that it is not a problem of space.

Command *df -h* returns:

Filesystem      Size  Used Avail Use% Mounted on
tmpfs           6.3G  1.1M  6.3G   1% /run
/dev/sda        1.3T  164G  1.1T  14% /
tmpfs            32G  3.2M   32G   1% /dev/shm
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs           6.3G  4.0K  6.3G   1% /run/user/1000

Command *df -ih* returns:

Filesystem     Inodes IUsed IFree IUse% Mounted on
tmpfs            7.9M   724  7.9M    1% /run
/dev/sda          80M  179K   80M    1% /
tmpfs            7.9M     4  7.9M    1% /dev/shm
tmpfs            7.9M     3  7.9M    1% /run/lock
tmpfs            1.6M    28  1.6M    1% /run/user/1000

I suppose it is an issue with temporary table, here my present
configuration in *postgresql.conf*

#temp_tablespaces = ''                  # a list of tablespace names,
'' uses
                                        # only default tablespace

#temp_file_limit = -1                   # limits per-process temp file
space
                                        # in kilobytes, or -1 for no limit

What do you suggest?

cheers,

Enzopolo

---

From: Laurenz Albe <[email-masked]>
Date: Fri, 08 Sep 2023 19:51:15 +0200

On Fri, 2023-09-08 at 12:22 +0100, Nature Conservation Geovista Space wrote:
> > 2023-09-08 02:11:23.745 BST [328594] postgres@database ERROR:  could not extend file "base/16388/7985375.1020": No space left on device
> > 2023-09-08 02:11:23.745 BST [328594] postgres@database HINT:  Check free disk space.
> >  
> > 2023-09-08 02:11:23.745 BST [328594] postgres@database  STATEMENT:  CREATE TABLE _gaul_administrative_subdivided100 AS (
> It seems that it is not a problem of space. [df shows there is space everywhere]

"After running a query for hours" makes it appear like you run out of disk space with
temporary files.  These only exist during the lifetime of the query, so they are gone again after
the statement has failed.

Set "temp_file_limit" to avoid that kind of problem.

Yours,
Laurenz Albe

---

From: bruno da silva <[email-masked]>
Date: Fri, 8 Sep 2023 16:01:03 -0400

Hello. Does temp_file_limt also applies to reindex?

Thanks

On Fri, Sept 8, 2023, 3:54 p.m. Laurenz Albe <[email-masked]>
wrote:

> > > 2023-09-08 02:11:23.745 BST [328594] postgres@database ERROR:  could
> not extend file "base/16388/7985375.1020": No space left on device
> > > 2023-09-08 02:11:23.745 BST [328594] postgres@database  STATEMENT:
> CREATE TABLE _gaul_administrative_subdivided100 AS (

---

From: Laurenz Albe <[email-masked]>
Date: Sat, 09 Sep 2023 04:54:24 +0200

> Hello. Does temp_file_limt also applies to reindex?

To the extent that REINDEX creates temporary files, yes.
It will limit the size of temporary files created when the
rows are sorted.

It does *not* limit the size of the data files for the new
index.

Yours,
Laurenz Albe

---

From: Nature Conservation Geovista Space <[email-masked]>
Date: Tue, 12 Sep 2023 13:12:37 +0100

Hi Laurenz,

Thanks a lot for your kind support.

1 - At the moment my *postgresql.conf *is by default with no limit
*#temp_file_limit
= -1*
Maybe I just have to uncomment it so that this rule really applies. Let's
see this evening if your suggestion fix it.

2 - Do you think it could come from another param not set up correctly?

3 - Can I try to watch out the log or size of the temporary table created
after my query failed so that I can investigate what happened?

Cheers,

Vincent

---

From: Laurenz Albe <[email-masked]>
Date: Tue, 12 Sep 2023 14:48:02 +0200

On Tue, 2023-09-12 at 13:12 +0100, Nature Conservation Geovista Space wrote:
> 1 - At the moment my postgresql.conf is by default with no limit   #temp_file_limit = -1
> Maybe I just have to uncomment it so that this rule really applies. Let's see this evening if
> your suggestion fix it.

"-1" meanse "no limit", so removing the comment won't do anything.
You also have to change the value.

> 2 - Do you think it could come from another param not set up correctly?

"work_mem" influences the amount of temp files required.  But if your query
needs enough temp files to fill your disk, higher settings of "work_mem" are
unlikely to fix that.

> 3 - Can I try to watch out the log or size of the temporary table created after my query failed
>     so that I can investigate what happened?

Sure.  Set "log_temp_files = '100kB'", and every temp file above that size will be logged.

Yours,
Laurenz Albe