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

 Replying to [comment:22 strk]:
 > Replying to [comment:19 francoisb]:
 > > FWIW, in my database (PostgreSQL 16.10, macOS), I was able to recover
 the topogeometry columns with these two steps:
 > >
 > > 1) Upgrade the topology to use the bigint feature:
 > >
 > > {{{
 > > SELECT UpgradeTopology('topo_1');
 > > }}}
 >
 > Wow, I did't even notice the existance of this function, I don't like
 the name of it :(
 > For those like me who missed it, its documentation is here:
 https://postgis.net/docs//en/UpgradeTopology.html
 >
 > I dunno why we call it an "upgrade" to get a bigger size :/
 >
 > > Note I'm not 100% sure this step is actually needed, but I add it here
 because I did it. We could test without, and perhaps remove it.
 > >
 > > 2) Recover `id` and `type` values from the bytes of corrupted `id`,
 with a query like:
 > >
 > > {{{
 > > UPDATE table_1
 > > SET
 > >   topogeom = (
 > >     (topogeom).topology_id,
 > >     (topogeom).layer_id,
 > >     (topogeom).id & 0xFFFFFFFF, -- 32 least significant bits from
 corrupted "id"
 > >     (topogeom).id >> 32         -- 32 most significant bits from
 corrupted "id"
 > >   )::topogeometry
 > > WHERE topogeom IS NOT NULL
 > > RETURNING topogeom;
 > > }}}
 >
 > Nice one, it might be time to start a PR with this code made available
 as a repair function so to provide improvements as we see them (like maybe
 check the xmin against the postgis xmin...). Would you be up for such task
 ? Then I'll be happy to test it against my existing corrupted tables as
 right now I cannot even `pg_upgrade` a cluster due to this bug,
 apparently.


 Wouldn't you end up overupdating with that.  I was thinking more like

 {{{
  UPDATE table_1
  SET
    topogeom = (
      (topogeom).topology_id,
     (topogeom).layer_id,
      (topogeom).id & 0xFFFFFFFF, -- 32 least significant bits from
 corrupted "id"
      (topogeom).id >> 32         -- 32 most significant bits from
 corrupted "id"
    )::topogeometry
  WHERE (  (topogeom).id & 0xFFFFFFFF ) <> (topogeom).id;


 }}}
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5983#comment:23>
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.