[PostGIS] #5983: Data corruption in topology.topogeometry after upgrade to 3.6.0 (was: Data corruption in topology.topoelement and topology.topogeometry after upgrade to 3.6.0)
PostGIS
trac at osgeo.org
Wed Nov 12 18:18:49 PST 2025
#5983: Data corruption in topology.topogeometry after upgrade to 3.6.0
-----------------------+---------------------------
Reporter: packi | Owner: robe
Type: defect | Status: closed
Priority: blocker | Milestone: PostGIS 3.6.1
Component: topology | Version: 3.6.x
Resolution: fixed | Keywords:
-----------------------+---------------------------
Changes (by robe):
* resolution: => fixed
* status: new => closed
* summary:
Data corruption in topology.topoelement and topology.topogeometry
after upgrade to 3.6.0
=> Data corruption in topology.topogeometry after upgrade to 3.6.0
Old description:
> 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.
New description:
This ticket now just addresses topology.topogeometry though it started as
issue discussing topoelement corruption which is related.
topoelement issue is now on #6016
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.
--
Comment:
The patch I put in place only addresses the topogeometry corruption and is
more of a workaround that requires the user to run:
{{{
SELECT topology.FixCorruptTopoGeometryColumn(schema_name, table_name,
feature_column)
FROM topology.layer;
}}}
The topoelement part is continued on #6016 - topology.topoelement.
I think it's rare that people store topoelements in tables, so less of an
issue.
--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5983#comment:33>
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.
More information about the postgis-tickets
mailing list