[postgis-users] Query crashes Postgres?

[Luca Bertoncello]

Hi list!

After about one months I got all Europe's data from OSM imported.
Now I needed to create some help tables, so I fired these queries:

CREATE TABLE city_admin_boundaries AS
SELECT
NEXTVAL('city_admin_boundaries_seq') AS id,
planet_osm_polygon.way,
planet_osm_polygon.admin_level,
planet_osm_point.name,
planet_osm_point.place,
CASE
WHEN (planet_osm_point.tags->'population' ~ '^[0-9]{1,8}$') THEN
(planet_osm_point.tags->'population')::INTEGER ELSE 0
END as population,
(ST_Area(ST_Transform(planet_osm_polygon.way, 4326)::geography) /
1000000) AS km2
FROM planet_osm_polygon
JOIN (
SELECT name, MAX(admin_level) AS al
FROM planet_osm_polygon
WHERE boundary = 'administrative' AND admin_level IN ('4', '6', '8')
AND osm_id < 0 GROUP BY name
) size USING(name)
JOIN planet_osm_point USING (name)
WHERE planet_osm_polygon.boundary = 'administrative' AND
planet_osm_polygon.admin_level = size.al AND
(
(
planet_osm_polygon.admin_level IN ('6', '8') AND
planet_osm_point.place IN ('city', 'town')
) OR
(
planet_osm_polygon.admin_level = '4' AND
planet_osm_point.place = 'city'
)
) AND
planet_osm_polygon.osm_id < 0;

CREATE TABLE city_boundaries AS
SELECT NEXTVAL('city_boundaries_seq') AS id,

ST_CollectionExtract(unnest(ST_ClusterWithin(planet_osm_polygon.way,
200)), 3)::geometry(MultiPolygon, 3857) as way
FROM planet_osm_polygon, city_admin_boundaries
WHERE landuse IN ('residential', 'retail', 'retail;residential',
'commercial', 'school', 'university', 'industrial',
'asphalt', 'cemetery', 'civic', 'civic_admin',
'concrete_surface', 'construction', 'education',
'educational', 'institutional', 'village') AND
ST_Within(planet_osm_polygon.way, city_admin_boundaries.way)
AND
(
(city_admin_boundaries.admin_level = '4' AND km2 >= 100)
OR
(
population > 18000 AND
(
(city_admin_boundaries.admin_level = '6' AND km2 >=
50) OR
(city_admin_boundaries.admin_level = '8' AND km2 >=
25)
)
) OR
(
population > 10000 AND city_admin_boundaries.admin_level
= '8' AND km2 >= 100
)
);
ALTER TABLE city_boundaries OWNER TO _renderd;

DELETE FROM city_boundaries WHERE ST_Area(ST_Buffer(way, -50)) < 50;

UPDATE city_boundaries SET way =
ST_Makevalid(ST_Multi(ST_Buffer(ST_Buffer(way, 300, 'join=miter'), -300,
'join=miter')));

The table were created successfully (13828 rows the first, 108385 the
seconds). The "DELETE" worked successfully, too, but on the UPDATE the
process crashes...

In PostgresSQL log I find:

2021-01-27 07:06:02.165 CET [582] LOG: Serverprozess (PID 10391) wurde
von Signal 9 beendet: Getötet
2021-01-27 07:06:02.165 CET [582] DETAIL: Der fehlgeschlagene Prozess
führte aus: UPDATE city_boundaries SET way =
ST_Makevalid(ST_Multi(ST_Buffer(ST_Buffer(way, 300, 'join=miter'), -300,
'join=miter')));
2021-01-27 07:06:02.178 CET [582] LOG: aktive Serverprozesse werden
abgebrochen
2021-01-27 07:06:02.179 CET [10238] postgres@gis WARNUNG: Verbindung
wird abgebrochen wegen Absturz eines anderen Serverprozesses
2021-01-27 07:06:02.179 CET [10238] postgres@gis DETAIL: Der Postmaster
hat diesen Serverprozess angewiesen, die aktuelle Transaktion
zurückzurollen und die Sitzung zu beenden, weil ein anderer
Serverprozess abnormal beendet wurde und möglicherweise das Shared
Memory verfälscht hat.
2021-01-27 07:06:02.179 CET [10238] postgres@gis TIPP: In einem Moment
sollten Sie wieder mit der Datenbank verbinden und Ihren Befehl
wiederholen können.
2021-01-27 07:06:02.180 CET [10234] WARNUNG: Verbindung wird
abgebrochen wegen Absturz eines anderen Serverprozesses
2021-01-27 07:06:02.180 CET [10234] DETAIL: Der Postmaster hat diesen
Serverprozess angewiesen, die aktuelle Transaktion zurückzurollen und
die Sitzung zu beenden, weil ein anderer Serverprozess abnormal beendet
wurde und möglicherweise das Shared Memory verfälscht hat.
2021-01-27 07:06:02.180 CET [10234] TIPP: In einem Moment sollten Sie
wieder mit der Datenbank verbinden und Ihren Befehl wiederholen können.
2021-01-27 07:06:02.997 CET [582] LOG: alle Serverprozesse beendet;
initialisiere neu
2021-01-27 07:06:03.208 CET [10410] LOG: Datenbanksystem wurde
unterbrochen; letzte bekannte Aktion am 2021-01-27 07:01:27 CET
2021-01-27 07:06:03.208 CET [10411] postgres@gis FATAL: das
Datenbanksystem ist im Wiederherstellungsmodus
2021-01-27 07:06:03.786 CET [10410] LOG: Datenbanksystem wurde nicht
richtig heruntergefahren; automatische Wiederherstellung läuft
2021-01-27 07:06:03.937 CET [10410] LOG: Redo beginnt bei 11F/22E1D520
2021-01-27 07:06:04.317 CET [10410] LOG: ungültige Datensatzlänge bei
11F/24124828: 24 erwartet, 0 erhalten
2021-01-27 07:06:04.317 CET [10410] LOG: Redo fertig bei 11F/241247F0
2021-01-27 07:06:05.597 CET [582] LOG: Datenbanksystem ist bereit, um
Verbindungen anzunehmen

Of course, I didn't killed the query...
I think, the problem could be "ungültige Datensatzlänge bei
11F/24124828: 24 erwartet, 0 erhalten" (invalid data length...).

The same query fired on a test server with just data from Saxony,
Brandenburg and Czeck Republic worked fine.
Could someone help me to solve the problem?

Thanks
Luca Bertoncello
(luca...@lucabert.de)
_______________________________________________
postgis-users mailing list
postgi...@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

[Regina Obe]

Would help if you can provide versions of things

SELECT postgis_full_version(), version();

To confirm you are running latest micro of everything.

> Signal 9 beendet: Get tet
> 2021-01-27 07:06:02.165 CET [582] DETAIL: Der fehlgeschlagene Prozess f hrte

> aus: UPDATE city_boundaries SET way =
> ST_Makevalid(ST_Multi(ST_Buffer(ST_Buffer(way, 300, 'join=miter'), -300,
> 'join=miter')));
> 2021-01-27 07:06:02.178 CET [582] LOG: aktive Serverprozesse werden
> abgebrochen
> 2021-01-27 07:06:02.179 CET [10238] postgres@gis WARNUNG: Verbindung
> wird abgebrochen wegen Absturz eines anderen Serverprozesses
> 2021-01-27 07:06:02.179 CET [10238] postgres@gis DETAIL: Der Postmaster

> hat diesen Serverprozess angewiesen, die aktuelle Transaktion zur ckzurollen

> und die Sitzung zu beenden, weil ein anderer Serverprozess abnormal

> beendet wurde und m glicherweise das Shared Memory verf lscht hat.

> 2021-01-27 07:06:02.179 CET [10238] postgres@gis TIPP: In einem Moment
> sollten Sie wieder mit der Datenbank verbinden und Ihren Befehl

> wiederholen k nnen.

> 2021-01-27 07:06:02.180 CET [10234] WARNUNG: Verbindung wird
> abgebrochen wegen Absturz eines anderen Serverprozesses
> 2021-01-27 07:06:02.180 CET [10234] DETAIL: Der Postmaster hat diesen

> Serverprozess angewiesen, die aktuelle Transaktion zur ckzurollen und die

> Sitzung zu beenden, weil ein anderer Serverprozess abnormal beendet

> wurde und m glicherweise das Shared Memory verf lscht hat.

> 2021-01-27 07:06:02.180 CET [10234] TIPP: In einem Moment sollten Sie

> wieder mit der Datenbank verbinden und Ihren Befehl wiederholen k nnen.

> 2021-01-27 07:06:02.997 CET [582] LOG: alle Serverprozesse beendet;
> initialisiere neu
> 2021-01-27 07:06:03.208 CET [10410] LOG: Datenbanksystem wurde
> unterbrochen; letzte bekannte Aktion am 2021-01-27 07:01:27 CET
> 2021-01-27 07:06:03.208 CET [10411] postgres@gis FATAL: das
> Datenbanksystem ist im Wiederherstellungsmodus
> 2021-01-27 07:06:03.786 CET [10410] LOG: Datenbanksystem wurde nicht

> richtig heruntergefahren; automatische Wiederherstellung l uft

> 2021-01-27 07:06:03.937 CET [10410] LOG: Redo beginnt bei 11F/22E1D520

> 2021-01-27 07:06:04.317 CET [10410] LOG: ung ltige Datensatzl nge bei

> 11F/24124828: 24 erwartet, 0 erhalten
> 2021-01-27 07:06:04.317 CET [10410] LOG: Redo fertig bei 11F/241247F0
> 2021-01-27 07:06:05.597 CET [582] LOG: Datenbanksystem ist bereit, um
> Verbindungen anzunehmen
>
> Of course, I didn't killed the query...

> I think, the problem could be "ung ltige Datensatzl nge bei

[Luca Bertoncello]

Am 27.01.2021 10:10, schrieb Regina Obe:

Hi

> Would help if you can provide versions of things
>
> SELECT postgis_full_version(), version();
>
> To confirm you are running latest micro of everything.

POSTGIS="2.5.1 r17027" [EXTENSION] PGSQL="110" GEOS="3.7.1-CAPI-1.11.1
27a5e771" PROJ="Rel. 5.2.0, September 15th, 2018" GDAL="GDAL 2.4.0,
released 2018/12/14" LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.3.1"
RASTER | PostgreSQL 11.9 (Debian 11.9-0+deb10u1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

I think, it could be a memory problem, since if I start the query
limiting the rows, it works...
The PC has 16GB RAM.

[Giuseppe Broccolo]

Hi Luca,

From what I understand from the logs you attached, the most likely scenario is a failure at OS level, which made the Postmaster process crash, probably

stopped by the OS itself with an OOM kill.

This is compatible with what is said here (the postmaster process received a Signal 9, a forced kill):

2021-01-27 07:06:02.165 CET [582] LOG:  Serverprozess (PID 10391) wurde
von Signal 9 beendet: Getötet

The child process which effectively triggered the issue is the backend which was running the update:

2021-01-27 07:06:02.165 CET [582] DETAIL:  Der fehlgeschlagene Prozess
führte aus: UPDATE city_boundaries SET way =
ST_Makevalid(ST_Multi(ST_Buffer(ST_Buffer(way, 300, 'join=miter'), -300,
'join=miter')));

For the following reason (memory corruption):

2021-01-27 07:06:02.179 CET [10238] postgres@gis DETAIL:  Der Postmaster
hat diesen Serverprozess angewiesen, die aktuelle Transaktion
zurückzurollen und die Sitzung zu beenden, weil ein anderer
Serverprozess abnormal beendet wurde und möglicherweise das Shared
Memory verfälscht hat.

This made the PostgreSQL server restart in recovery mode:

2021-01-27 07:06:03.208 CET [10411] postgres@gis FATAL:  das
Datenbanksystem ist im Wiederherstellungsmodus

In the recovery mode, it started using the WALs available on the local storage to recover from the crash:

2021-01-27 07:06:03.937 CET [10410] LOG:  Redo beginnt bei 11F/22E1D520
2021-01-27 07:06:04.317 CET [10410] LOG:  ungültige Datensatzlänge bei
11F/24124828: 24 erwartet, 0 erhalten
2021-01-27 07:06:04.317 CET [10410] LOG:  Redo fertig bei 11F/241247F0

There's effectively a "warning" message about an issue in applying a specific location of the WALs (11F/24124828),

but I would avoid to focus for the moment on this, because it's a symptom of the real issue that made PostgreSQL crash,

not the origin. It was then able to properly restart:

2021-01-27 07:06:05.597 CET [582] LOG:  Datenbanksystem ist bereit, um
Verbindungen anzunehmen

To have a confirmation about the source of the issue, I'd suggest to have a look at system logs (syslog, dmesg, ...). I am

expecting some entry related to an OOM kill performed by the OS.

If this is confirmed, then the solution would be to check the configuration of PostgreSQL, OS, hardware (specially RAM resources), etc.

Also the fact that the transaction worked properly on a reduced dataset makes me think of a problem related to the size/complexity of the dataset,

with an undersized configuration of the whole DB server.

Hope it helps,

Giuseppe.

[Luca Bertoncello]

Am 27.01.2021 10:41, schrieb Giuseppe Broccolo:

Ciao Giuseppe!

> Also the fact that the transaction worked properly on a reduced
> dataset makes me think of a problem related to the size/complexity of
> the dataset,
> with an undersized configuration of the whole DB server.

I tried now to add a SWAP file. I know, this is not the best, but right
now I cannot add more RAM to the PC...
It seems to help, since it runs and didn't crash yet...

[Giuseppe Broccolo]

Ciao Luca,

Il giorno mer 27 gen 2021 alle ore 11:15 Luca Bertoncello <luca...@lucabert.de> ha scritto:

I tried now to add a SWAP file. I know, this is not the best, but right
now I cannot add more RAM to the PC...
It seems to help, since it runs and didn't crash yet...

Yeah, I was pretty sure it was due to PostgreSQL trying to ask the OS for more resources than it was

actually able to provide.

Yep, adding/increasing the SWAP is not elegant, because you are actually materialising the execution of the OS

processes,but if this does the job you need to do...it's fine!

Giuseppe.

[Luca Bertoncello]

Am 27.01.2021 13:08, schrieb Giuseppe Broccolo:

> Yeah, I was pretty sure it was due to PostgreSQL trying to ask the OS
> for more resources than it was
> actually able to provide.

I use a PC with Debian 10 and Postgres from Repository.
The PC has 4 cores and 16 GB RAM.

> Yep, adding/increasing the SWAP is not elegant, because you are
> actually materialising the execution of the OS
> processes,but if this does the job you need to do...it's fine!

Unfortunately, I cannot add RAM right now...
So, SWAP is slow, but if it helps, then it's OK...

Ciao