Message-ID: <5520a74bc59f5aed4b29772b04c28d58566013a2.camel@cybertec.at>
Subject: Re: Even if I have a lot of free space PgSQL returns "ERROR: could
 not extend file - No space left on device"
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Nature Conservation Geovista Space
 <conservation.by.geovista.space@gmail.com>,
 pgsql-novice@lists.postgresql.org
Date: Fri, 08 Sep 2023 19:51:15 +0200
In-Reply-To: 
 <CA+=_Eg-GYE9BfdQoXkevSv9GkrMbRzsYTDnMoYa_zzQoBQbe6Q@mail.gmail.com>
References: 
	<CA+=_Eg-GYE9BfdQoXkevSv9GkrMbRzsYTDnMoYa_zzQoBQbe6Q@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.48.4 (3.48.4-1.fc38) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/5520a74bc59f5aed4b29772b04c28d58566013a2.camel%40cybertec.at>
Precedence: bulk

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
> > =C2=A0
> > 2023-09-08 02:11:23.745 BST [328594] postgres@database ERROR:=C2=A0 cou=
ld not extend file "base/16388/7985375.1020": No space left on device
> > 2023-09-08 02:11:23.745 BST [328594] postgres@database HINT:=C2=A0 Chec=
k free disk space.
> > =C2=A0
> > 2023-09-08 02:11:23.745 BST [328594] postgres@database=C2=A0 STATEMENT:=
=C2=A0 CREATE TABLE _gaul_administrative_subdivided100 AS (
> > =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0 SELECT *, st_subdivide(geom,100) AS geom_subdivided10=
0=20
> > =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 FROM =
gaul_administrative=20
> > =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 );
>=20
> It seems that it is not a problem of space. [df shows there is space ever=
ywhere]

"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 the=
y are gone again after
the statement has failed.

Set "temp_file_limit" to avoid that kind of problem.

Yours,
Laurenz Albe