[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):

 I still need to test this some more but this is a WIP of a function.

 I'm going to focus on creating upgrade tests for damaged topogeoms and
 then try to fix with this function and will write a companion function to
 check damaged topogeometries.


 {{{
 CREATE OR REPLACE FUNCTION topology.fix_topogeometry_column(topo_schema
 name, topo_table name, topo_column name) RETURNS text AS
 $$
 DECLARE var_sql text; var_row_count bigint; result text;
 BEGIN
         -- if topogeometry is bigint, then fix damaged integer, need to
 upgrade to bigint
         IF EXISTS ( SELECT 1
                 FROM  pg_catalog.pg_type
                         join pg_class on pg_class.oid = pg_type.typrelid
                         join pg_catalog.pg_attribute AS pga  on
 pga.attrelid = pg_class.oid
                         join pg_type as pg_attr_type on pg_attr_type.oid =
 pga.atttypid
 WHERE pg_type.typname::regtype::text = 'topogeometry' AND pga.attname =
 'id'
                         AND
                 pg_type.typnamespace::regnamespace::text = 'topology' AND
 pga.atttypid::regtype::text = 'bigint' ) THEN
          var_sql =  format('UPDATE %1$I.%2$I
                 SET
            %3$I = (
              (%3$I).topology_id,
             (%3$I).layer_id,
              (%3$I).id & 0xFFFFFFFF,
              (%3$I).id >> 32
            )::topogeometry
          WHERE (  (%3$I).id & 0xFFFFFFFF ) <> (%3$I).id OR  (  (%3$I).id
 >> 32 ) = (%3$I).type  ', topo_schema, topo_table, topo_column);

          EXECUTE var_sql;
          GET DIAGNOSTICS var_row_count = ROW_COUNT;
          result = format('%s rows updated for %s.%s.%s column to bigint id
 type', var_row_count, topo_schema, topo_table, topo_column);
   ELSE --we are coming from bigint and going back to integer
         var_sql =  format('UPDATE %1$I.%2$I
                 SET
            %3$I = (
              (%3$I).topology_id,
             (%3$I).layer_id,
              (%3$I).id,
              l.feature_type
            )::topogeometry
            FROM topology.layer AS l
          WHERE  l.topology_id = (%3$I).topology_id AND l.layer_id =
 (%3$I).layer_id AND (%3$I).type <> l.feature_type  ', topo_schema,
 topo_table, topo_column);
          EXECUTE var_sql;
          GET DIAGNOSTICS var_row_count = ROW_COUNT;
          result = format('%s rows updated for %s.%s.%s column back to
 integer id type', var_row_count, topo_schema, topo_table, topo_column);
   END IF;
   RETURN result;
 END
 $$ language plpgsql;
 }}}


 Use case, we would run across all layers in the database and it will fix
 broken rows


 {{{
 SELECT topology.fix_topogeometry_column(schema_name, table_name,
 feature_column)
 FROM topology.layer;
 }}}
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5983#comment:24>
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.