DropTopology: ERROR: update or delete on table "topology" violates foreign key constraint "layer_topology_id_fkey" on table "layer"

[strk]

xxx=# select * from topology.layer;
 topology_id | layer_id | schema_name |  table_name   | feature_column | feature_type | level | child_id 
-------------+----------+-------------+---------------+----------------+--------------+-------+----------
          17 |        1 | features    | land_parcels  | feature        |            3 |     0 |         
          17 |        2 | features    | traffic_signs | feature        |            1 |     0 |         
          17 |        3 | features    | city_streets  | feature        |            2 |     0 |         
(3 rows)

xxx=# select * from topology.topology;
 id |   name    | srid | precision | hasz 
----+-----------+------+-----------+------
  1 | tt        |    0 |         0 | f
 17 | city_data | 4326 |         0 | f
(2 rows)
xxx=# select droptopology('city_data');
NOTICE:  Dropping all layers from topology 'city_data' (17)
NOTICE:  sequence "topogeo_s_1" does not exist
NOTICE:  A record in city_data.relation still references layer 1
NOTICE:  sequence "topogeo_s_2" does not exist
NOTICE:  A record in city_data.relation still references layer 2
NOTICE:  sequence "topogeo_s_3" does not exist
NOTICE:  A record in city_data.relation still references layer 3
ERROR:  update or delete on table "topology" violates foreign key constraint "layer_topology_id_fkey" on table "layer"
DETAIL:  Key (id)=(17) is still referenced from table "layer".
CONTEXT:  SQL statement "DELETE FROM topology.topology WHERE id = $1"
xxx=# select version();
                                                            version                                                             
--------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 13.5 (Ubuntu 13.5-0ubuntu0.21.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 10.3.0-1ubuntu1) 10.3.0, 64-bit
(1 row)
xxx=# select postgis_full_version();
                                                                                      postgis_full_version                                                                                      
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="3.3.0dev 3.2.0-656-g1ba6bccae" [EXTENSION] PGSQL="130" GEOS="3.11.0dev-CAPI-1.16.0" PROJ="7.2.1" LIBXML="2.9.10" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" TOPOLOGY
(1 row)

}}}

[strk]

It's odd to read NOTICE: Dropping all layers from topology 'city_data' (17) and then DETAIL: Key (id)=(17) is still referenced from table "layer".

[strk]

It looks like the culprit is DropTopoGeometryColumn silently failing to drop the record from topology.layer:

xxx=# select * from topology.layer;
 topology_id | layer_id | schema_name |  table_name   | feature_column | feature_type | level | child_id 
-------------+----------+-------------+---------------+----------------+--------------+-------+----------
          17 |        1 | features    | land_parcels  | feature        |            3 |     0 |         
          17 |        2 | features    | traffic_signs | feature        |            1 |     0 |         
          17 |        3 | features    | city_streets  | feature        |            2 |     0 |         
(3 rows)

xxx=# SELECT topology.DropTopoGeometryColumn('features', 'land_parcels', 'feature');
NOTICE:  sequence "topogeo_s_1" does not exist
NOTICE:  A record in city_data.relation still references layer 1
             droptopogeometrycolumn              
-------------------------------------------------
 Layer 1 (features.land_parcels.feature) dropped
(1 row)

xxx=# select * from topology.layer;
 topology_id | layer_id | schema_name |  table_name   | feature_column | feature_type | level | child_id 
-------------+----------+-------------+---------------+----------------+--------------+-------+----------
          17 |        1 | features    | land_parcels  | feature        |            3 |     0 |         
          17 |        2 | features    | traffic_signs | feature        |            1 |     0 |         
          17 |        3 | features    | city_streets  | feature        |            2 |     0 |         
(3 rows)

[strk]

PostGIS Version 3.2.2dev (16d19f0a5b9915166a9f52b75c426b06c78e7c49) is also affected

[strk]

The layer table (features.land_parcels) does NOT exist, which may explain why the code stops earlier.

[strk]

Branches 3.1 and 3.0 also affected, probably even older ones.

[strk]

Got it: the NOTICE should probably be an EXCEPTION in that it *prevents* deleting from topology.layer if records still exist in the relation table:

xxx=# delete from topology.layer where topology_id = 17 and layer_id = 1;
NOTICE:  A record in city_data.relation still references layer 1
DELETE 0

[strk]

The reason why relation table is not cleaner up is because both relation cleanup and sequence removal are in the same sub-transaction, and the absence of the sequence rollsback the relation table cleanup:

xxx=# SELECT topology.DropTopoGeometryColumn('features', 'land_parcels', 'feature');
WARNING:  Executing: DELETE FROM city_data.relation WHERE layer_id = $1
WARNING:  $1 is city_data
WARNING:  Executing: DROP SEQUENCE city_data.topogeo_s_1
WARNING:  $1 is city_data
WARNING:  $2 is 1
NOTICE:  sequence "topogeo_s_1" does not exist

[strk]

DROP SEQUENCE IF EXISTS is supported since PostreSQL 8.2 so we could use that: ​https://www.postgresql.org/docs/8.2/sql-dropsequence.html

[Sandro Santilli <strk@…>]

In bb3842b/git:

Allow dropping topologies with missing layer topogeom sequences

Closes #5118 in master branch
Includes regression test