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

On 9/8/23 04:25, Nature Conservation Geovista Space wrote:
> 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.

Postgres version?

PostGIS version?

OS and version?

Is this a physical machine or a virtual one?

Hosted or local?

'Hardware' specifications?

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

How does CREATE TABLE enter into the query?

What is the query?

> 
>     *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
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com