Обсуждение: 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

> On 8 Sep 2023, at 13:25, Nature Conservation Geovista Space <conservation.by.geovista.space@gmail.com> 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
toomany vertices. 

So a lot of geometry going on then… That seems important in this case.

> 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
>         );

That _looks_ like a query that could blow through space rather quickly.

How large is gaul_administrative in GB? In rows? How many subdivisions does this generate per row on average? How many
bytesare those subdivisions on average? 

Multiply those numbers, and you get a fair indication of how much space that table requires. Does that fit in 1.1TB?


Frankly, I don’t see the point of repeating the geom column in that table after dividing it up, aren’t you just wasting
spacethere? The original is still available in the source table, after all. And what about the other columns that you
cloneinto this new table? 

My suggestion would be to minimise that with an operation like the above. This would be one of those situations where
I’dintroduce a (bigint) surrogate key and use that to link the two tables together, even though I’m a natural key
person.

Something like this (I had to make a few assumptions about your source table):

CREATE TABLE _gaul_administrative_subdivided100 AS (
    id bigint NOT NULL,
    geomid bigserial NOT NULL,
    geom_subdivided100 geometry NOT NULL
);

-- If you care about referential integrity here
ALTER TABLE _gaul_administrative_subdivided100
    ADD FOREIGN KEY (id) REFERENCES gaul_administrative (id)
     ON DELETE CASCADE
     ON UPDATE RESTRICT;

INSERT INTO _gaul_administrative_subdivided100 (id, geom_subdivided100)
    SELECT id, st_subdivide(geom,100)
      FROM gaul_administrative;


(…)

> It seems that it is not a problem of space.

I wouldn’t be too sure of that, but I don’t have the numbers.

> 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

Is this after the error and after PG finished rolling back?
What does this show while that query is going on?

If gaul_administrative takes up a large part of those 164G, then you probably don’t have enough space for a 10x
multiplicationin size from the original table to the new table. And that happening looks entirely possible from the
informationyou provided. 

Regards,

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.