[PostGIS] #5983: Potential data corruption in topology.topoelement after upgrade to 3.6.0
PostGIS
trac at osgeo.org
Wed Sep 10 03:05:27 PDT 2025
#5983: Potential data corruption in topology.topoelement after upgrade to 3.6.0
----------------------+---------------------------
Reporter: packi | Owner: strk
Type: defect | Status: new
Priority: critical | Milestone: PostGIS 3.6.1
Component: topology | Version: 3.6.x
Keywords: |
----------------------+---------------------------
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/5983>
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