Crash on aurora postgres topology add_polygon

[robe]

The performance suite is failing on aurora postgres in a couple of cases.

In the topology case it crashes

​https://git.osgeo.org/gitea/postgis/postgis-performance

POSTGIS="3.1.5 0" [EXTENSION] PGSQL="130" GEOS="3.9.1dev-CAPI-1.14.1" PROJ="6.2.1" LIBXML="2.9.9" LIBJSON="0.12.99" LIBPROTOBUF="1.3.0" WAGYU="0.5.0 (Internal)"

[02:59:23] pgtap/topology/add_polygon.sql .......
1..3
16
psql:pgtap/topology/add_polygon.sql:13: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
psql:pgtap/topology/add_polygon.sql:13: fatal: connection to server was lost
Dubious, test returned 2 (wstat 512, 0x200)
Failed 3/3 subtests

The add_linestring was okay except for exceeding time limits

data

CREATE EXTENSION postgis_tiger_geocoder CASCADE;
CREATE EXTENSION topology;

Load data with psql from Where state boundaries are the ones in - ​http://postgis.net/extra/test-data/tiger_national.sql.bz2

Test

SELECT topology.CreateTopology('topoperf', 4269) ;
CREATE TABLE topoperf.polygon
AS SELECT (ST_Dump(geom)).geom AS g FROM (SELECT the_geom AS geom FROM tiger.state) AS f;

SELECT topology.TopoGeo_addPolygon('topoperf', g)
  FROM topoperf.polygon;

I tested on RDS running and that passed fine including the timing

POSTGIS="3.1.4 ded6c34" [EXTENSION] PGSQL="140" GEOS="3.9.1-CAPI-1.14.2" PROJ="8.0.1" LIBXML="2.9.1" LIBJSON="0.15" LIBPROTOBUF="1.3.2" WAGYU="0.5.0 (Internal)" TOPOLOGY

I'm going to isolate further and check other systems.

[robe]

I should add, to rule out 3.1.5 as the culprit, I built on my mingw 3.1.5 and tested against 3.1.5, and that worked fine. So I'm suspecting it's my sizing of aurora instance I screwed up or something about the aurora special storage structure causing the issue.

[robe]

Okay further investigation, it is not crashing at the building of topology it seems but instead on the ROLLBACK.

Taking out the commit/rollback on my test Aurora, it completes in 2 hrs. On RDS it completes in about 10-11 minutes. Working with Amazon engineer to troubleshoot the issue further.

So this actually works, my mistake:

SELECT topology.CreateTopology('topoperf', 4269) ;
CREATE TABLE topoperf.polygon
AS SELECT (ST_Dump(geom)).geom AS g FROM (SELECT the_geom AS geom FROM tiger.state) AS f;

SELECT topology.TopoGeo_addPolygon('topoperf', g)
  FROM topoperf.polygon;

But the test is really doing more like below which crashes

BEGIN TRANSACTION;
SELECT topology.CreateTopology('topoperf', 4269) ;
CREATE TABLE topoperf.polygon
AS SELECT (ST_Dump(geom)).geom AS g FROM (SELECT the_geom AS geom FROM tiger.state) AS f;

SELECT topology.TopoGeo_addPolygon('topoperf', g)
  FROM topoperf.polygon;

ROLLBACK;

So I am guessing it is how the Aurora storage engine works (since that is what makes it a fork the plumbing under the storage engine) something it doesn't like. I think I have just one reader node in addition to the writer, where as RDS has no extra nodes to contend with.