Message-ID: <753193ede36dad1b9da0755e17d69f4980bb3333.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: Tue, 12 Sep 2023 14:48:02 +0200
In-Reply-To: 
 <CA+=_Eg8px00BgKanc7D8eh2yDY62HmgqOzvAN7-=FspBR-fjNQ@mail.gmail.com>
References: 
	<CA+=_Eg-GYE9BfdQoXkevSv9GkrMbRzsYTDnMoYa_zzQoBQbe6Q@mail.gmail.com>
	 <5520a74bc59f5aed4b29772b04c28d58566013a2.camel@cybertec.at>
	 <CAB+Nuk87tNEkApQOoSzw1u4vtnKPgksaKLv1t5-w49JkCkotmA@mail.gmail.com>
	 <d447afcf8a6aa65c09d3d132a90989548641e141.camel@cybertec.at>
	 <CA+=_Eg8px00BgKanc7D8eh2yDY62HmgqOzvAN7-=FspBR-fjNQ@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/753193ede36dad1b9da0755e17d69f4980bb3333.camel%40cybertec.at>
Precedence: bulk

On Tue, 2023-09-12 at 13:12 +0100, Nature Conservation Geovista Space wrote=
:
> On Sat, 9 Sept 2023 at 03:54, Laurenz Albe <laurenz.albe@cybertec.at> wro=
te:
> > On Fri, 2023-09-08 at 16:01 -0400, bruno da silva wrote:
> > > Hello. Does temp_file_limt also applies to reindex?
> >=20
> > To the extent that REINDEX creates temporary files, yes.
> > It will limit the size of temporary files created when the
> > rows are sorted.
> >=20
> > It does *not* limit the size of the data files for the new
> > index.
>
> 1 - At the moment my postgresql.conf is by default with no limit   #temp_=
file_limit =3D -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" ar=
e
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 =3D '100kB'", and every temp file above that siz=
e will be logged.

Yours,
Laurenz Albe