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