Обсуждение: Even if I have a lot of free space PgSQL returns "ERROR: could not extend file - No space left on device"
Even if I have a lot of free space PgSQL returns "ERROR: could not extend file - No space left on device"
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 ontmpfs 7.9M 724 7.9M 1% /run/dev/sda 80M 179K 80M 1% /tmpfs 7.9M 4 7.9M 1% /dev/shmtmpfs 7.9M 3 7.9M 1% /run/locktmpfs 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
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 spaceleft 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
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
On Fri, 2023-09-08 at 16:01 -0400, bruno da silva wrote: > 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
Re: Even if I have a lot of free space PgSQL returns "ERROR: could not extend file - No space left on device"
On Fri, 2023-09-08 at 16:01 -0400, bruno da silva wrote:
> 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
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> wrote: > > On Fri, 2023-09-08 at 16:01 -0400, bruno da silva wrote: > > > 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. > > 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