[PostGIS] #6016: Data corruption in topoelement after upgrade to PostGIS 3.6

[PostGIS]

#6016: Data corruption in topoelement after upgrade to PostGIS 3.6
---------------------+---------------------------
 Reporter:  robe     |      Owner:  pramsey
     Type:  defect   |     Status:  new
 Priority:  medium   |  Milestone:  PostGIS 3.7.0
Component:  postgis  |    Version:  3.6.x
 Keywords:           |
---------------------+---------------------------
 This ticket is split from #5983
 Our integration-tests caught an error when upgrading from PostGIS 3.5 to
 3.6 with a very simple topology.topoelement.

 We're calling `SELECT public.postgis_extensions_upgrade();` like the
 documentation suggests but it ends up either erroring out with:

 `ERROR:  column "t" of table "postgis_test" contains values that violate
 the new constraint` (the offending line is in `postgis_topology--ANY--
 3.6.0.sql`: `ALTER DOMAIN topology.topoelement ADD CONSTRAINT type_range
 CHECK ( VALUE[2] > 0 );`)

 or, worse, silently "corrupting" the data when accessing single members of
 the `topoelement`. It still returns the right values when accessing the
 whole element.

 The problem seems to be that we alter the type of the domain to be
 bigint[] in the catalog which seems to work fine to access the whole
 element, but access to single members yields it returning a negative value
 which means the check will fail for some values.

 Steps to reproduce:

 1. Create fresh PG17 instance.
 2. Execute:
 {{{
 CREATE EXTENSION postgis VERSION '3.5.0';
 CREATE EXTENSION postgis_topology VERSION '3.5.0';

 SELECT version();
 SELECT postgis_version();

 CREATE TABLE postgis_test (t topology.topoelement);
 INSERT INTO postgis_test VALUES (ARRAY[1,2]);
 SELECT t[1], t[2] FROM postgis_test;

 ALTER EXTENSION postgis UPDATE;
 SELECT postgis_extensions_upgrade();
 SELECT t[1], t[2] FROM postgis_test;
 SELECT * FROM postgis_test;


 SELECT version();
 SELECT postgis_version();
 }}}
 Output:
 {{{
 CREATE EXTENSION
 CREATE EXTENSION
                                                  version
 ----------------------------------------------------------------------------------------------------------
  PostgreSQL 17.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 14.2.1
 20240912 (Red Hat 14.2.1-3), 64-bit
 (1 row)

             postgis_version
 ---------------------------------------
  3.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
 (1 row)

 CREATE TABLE
 INSERT 0 1
  t | t
 ---+---
  1 | 2
 (1 row)

 ALTER EXTENSION
 NOTICE:  Updating extension postgis 3.6.0
 NOTICE:  Updating extension postgis_topology 3.5.0
 INFO:  Upgraded topoelement from integer[] to bigint[]
 INFO:  Upgraded topoelementarray from integer[][] to bigint[][]
 INFO:  Modified topology added useslargeids(BOOLEAN)
 INFO:  Upgraded topogeometry.id from integer to bigint
 INFO:  Upgraded getfaceedges_returntype.edge from integer to bigint
 INFO:  Upgraded validatetopology_returntype.id1 from integer to bigint
 INFO:  Upgraded validatetopology_returntype.id2 from integer to bigint
                              postgis_extensions_upgrade
 ------------------------------------------------------------------------------------
  Upgrade to version 3.6.0 completed, run SELECT postgis_full_version();
 for details
 (1 row)

      t      | t
 ------------+---
  8589934593 | 0
 (1 row)

    t
 -------
  {1,2}
 (1 row)

                                                  version
 ----------------------------------------------------------------------------------------------------------
  PostgreSQL 17.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 14.2.1
 20240912 (Red Hat 14.2.1-3), 64-bit
 (1 row)

             postgis_version
 ---------------------------------------
  3.6 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
 (1 row)

 postgres=#
 }}}

 Our automated reproduction case where we can reproduce the constraint
 error every time involves upgrading from PG 16 to 17 but this should also
 illustrate the problem.
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/6016>
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.