PostgreSQL/PostGIS : Error No space left on device SQL state: 53100

Question

I want to compare a geoprocessing done in QGIS and PostGIS. It involves a union of two large polygonal vector layers.The processing in QGIS tooks 1.5 hours on a well-equipped workstation (32 GB of RAM). I was never able to complete the processing on PostGIS.

Here is the tested SQL query:

create table public.union as
select st_union(ST_Forece2D(mos.geom), ST_Forece2D(bd_topo_bat.geom))
from public.mos, public.bd_topo_bat

Here is the error message:

ERROR: could not extend file "base/19370/76968.57" with FileFallocate(): No space left on device HINT: Check available disk space. ERROR: Could not extend file "base/19370/76968.57" with FileFallocate(): No space left on device SQL state: 53100

The error message is related to a problem with the temporary log file. In the postgresql.conf file, I edited the file by setting the value of log_temp_files = 10240. The default value was -1. I took this action based on this post: https://stackoverflow.com/questions/508 ... on-device

• Ian Turton: You need to get a bigger disc
• Bera: Your query is cross joining the tables so you are creating mos row count * topo row count features. What are you trying to do, the PostGIS equivalent of QGIS Dissolve, or Merge?
• user35117: Thanks so much for your replies. i try to do the union Qgis Geoprocessing : docs.qgis.org/3.40/en/docs/user_manual/processing_algs/qgis/…
• Bera: There is no PostGIS equivalent of that Union... See for example: gis.stackexchange.com/questions/179533/… . Do you need the attributes or are you ok with only the geometries?
• user35117: Thnaks again for your help @Bera i need the attributes and geometries

Answer 1

As some commenters are saying, you are creating a many to many join. This is blowing the lid off the memory of Postgres. QGIS is not quite doing the equivalent process.

Test your query first with LIMIT 10 or WHERE id = 123456 in PGAdmin, in the results window, for the GEOM column, there will be a button to view the results in the built in geometry viewer. See if your polygons overlay each other.