Re: Even if I have a lot of free space PgSQL returns "ERROR: could not extend file - No space left on device" - Mailing list pgsql-novice

On Fri, 2023-09-08 at 12:22 +0100, Nature Conservation Geovista Space wrote:
> 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
> >         );
>
> 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