[PostGIS] #5983: Data corruption in topology.topoelement and topology.topogeometry after upgrade to 3.6.0

[PostGIS]

#5983: Data corruption in topology.topoelement and topology.topogeometry after
upgrade to 3.6.0
-----------------------+---------------------------
  Reporter:  packi     |      Owner:  robe
      Type:  defect    |     Status:  new
  Priority:  blocker   |  Milestone:  PostGIS 3.6.1
 Component:  topology  |    Version:  3.6.x
Resolution:            |   Keywords:
-----------------------+---------------------------
Comment (by robe):

 Okay I think this would fix the broken topogeometries but probably very
 slow


 {{{
 CREATE EXTENSION postgis_topology VERSION "3.4.2";
 CREATE OR REPLACE FUNCTION topology.hex_to_int(hexVal varchar) RETURNS
 numeric(1000) AS $$
 DECLARE
     intVal numeric(1000) := 0;
     hexLength integer;
     i integer;
     hexDigit varchar;
 BEGIN
     hexLength := length(hexVal);

     FOR i IN 1..hexLength
     LOOP
         hexDigit := substr(hexVal, hexLength - i + 1, 1);
         intVal :=
             intVal +
             CASE
                 WHEN hexDigit BETWEEN '0' AND '9' THEN CAST(hexDigit AS
 numeric(1000))
                 WHEN upper(hexDigit) BETWEEN 'A' AND 'F' THEN
 CAST(ascii(upper(hexDigit)) - 55 AS numeric(1000))
             END *
             CAST(16 AS numeric(1000)) ^ CAST(i - 1 AS numeric(1000));
     END LOOP;

     RETURN intVal;
 END;
 $$
 LANGUAGE 'plpgsql' IMMUTABLE STRICT;

 CREATE OR REPLACE FUNCTION topology.fix_topogeometry(param_topo
 topogeometry) RETURNS topogeometry AS
 $$
 BEGIN
         IF geometrytype(param_topo) != 'UNEXPECTED' THEN -- it's okay
 return unchanged
                 RETURN param_topo;
         ELSE
                 RETURN (param_topo.topology_id, param_topo.layer_id,
 topology.hex_to_int(right(lpad(to_hex(param_topo.id), 16,
 '0'),8))::bigint, topology.hex_to_int(left(lpad(to_hex(param_topo.id), 16,
 '0'),8))::bigint)::topogeometry;
         END IF;
 END
 $$ language plpgsql;

 }}}

 I tested with a set of mix of bad and good topogeometries upgrading from
 postgis_topology 3.4.2 to 3.6.0


 {{{
 CREATE EXTENSION postgis_topology VERSION '3.4.2';

 DROP SCHEMA IF EXISTS  tiger_topo CASCADE;
 SELECT topology.CreateTopology('tiger_topo', 4326, 0.00001);

 DROP TABLE IF EXISTS topo_states;
 CREATE TABLE topo_states (stusps char(2));

 SELECT topology.AddTopoGeometryColumn('tiger_topo', 'public',
 'topo_states', 'topo', 'POLYGON');

 INSERT INTO topo_states(stusps, topo)
 SELECT stusps, ToTopoGeom(ST_SetSRID(the_geom,4326), 'tiger_topo', 1)
 FROM tiger_link.state WHERE stusps IN('MA','DC','RI','NH', 'NY');
 }}}

 {{{
 -- not broken
 SELECT stusps, geometrytype(topo) FROM topo_states;
 }}}


 {{{
  stusps | geometrytype |   topo
 --------+--------------+-----------
  RI     | MULTIPOLYGON | (1,1,1,3)
  NH     | MULTIPOLYGON | (1,1,2,3)
  NY     | MULTIPOLYGON | (1,1,3,3)
  MA     | MULTIPOLYGON | (1,1,4,3)
  DC     | MULTIPOLYGON | (1,1,5,3)
 }}}



 {{{
 ALTER EXTENSION postgis_topology UPDATE TO "3.6.0";
 -- all broken
 SELECT stusps, geometrytype(topo), topo FROM topo_states;

 }}}


 {{{
  stusps | geometrytype |            topo
 --------+--------------+----------------------------
  RI     | UNEXPECTED   | (1,1,12884901889,19626)
  NH     | UNEXPECTED   | (1,1,12884901890,16676265)
  NY     | UNEXPECTED   | (1,1,12884901891,16676265)
  MA     | UNEXPECTED   | (1,1,12884901892,16676265)
  DC     | UNEXPECTED   | (1,1,12884901893,16676265)
 (5 rows)
 }}}



 {{{
 INSERT INTO topo_states(stusps, topo)
 SELECT stusps, ToTopoGeom(ST_SetSRID(the_geom,4326), 'tiger_topo', 1)
 FROM tiger_link.state WHERE stusps IN('DE');

 -- new one not broken
 SELECT stusps, geometrytype(topo), topo FROM topo_states;

 }}}


 {{{
 stusps | geometrytype |            topo
 --------+--------------+----------------------------
  RI     | UNEXPECTED   | (1,1,12884901889,19626)
  NH     | UNEXPECTED   | (1,1,12884901890,16676265)
  NY     | UNEXPECTED   | (1,1,12884901891,16676265)
  MA     | UNEXPECTED   | (1,1,12884901892,16676265)
  DC     | UNEXPECTED   | (1,1,12884901893,16676265)
  DE     | MULTIPOLYGON | (1,1,6,3)
 (6 rows)

 }}}



 {{{
 UPDATE topo_states SET topo = topology.fix_topogeometry(topo)  WHERE
 geometrytype(topo) = 'UNEXPECTED';
 -- all good
 SELECT stusps, geometrytype(topo), topo FROM topo_states;

 }}}


 {{{
 stusps | geometrytype |   topo
 --------+--------------+-----------
  DE     | MULTIPOLYGON | (1,1,6,3)
  RI     | MULTIPOLYGON | (1,1,1,3)
  NH     | MULTIPOLYGON | (1,1,2,3)
  NY     | MULTIPOLYGON | (1,1,3,3)
  MA     | MULTIPOLYGON | (1,1,4,3)
  DC     | MULTIPOLYGON | (1,1,5,3)
 (6 rows)
 }}}
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5983#comment:17>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.