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

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