[SCM] PostGIS branch stable-3.6 updated. 3.6.0-31-g6a16232cb
git at osgeo.org
git at osgeo.org
Wed Nov 12 18:02:01 PST 2025
This is an automated email from the git hooks/post-receive script. It was
generated because a ref change was pushed to the repository containing
the project "PostGIS".
The branch, stable-3.6 has been updated
via 6a16232cb57a202d0e1407fedb6f59ede0a354ec (commit)
from abf2fb9b06665ae0099acd6ccaeeb0c8bbc0f1c4 (commit)
Those revisions listed above that are new to this repository have
not appeared on any other notification email; so we list those
revisions in full, below.
- Log -----------------------------------------------------------------
commit 6a16232cb57a202d0e1407fedb6f59ede0a354ec
Author: Regina Obe <lr at pcorp.us>
Date: Wed Nov 12 18:44:10 2025 -0500
topology.FixCorruptTopoGeometryColumn to fix corruption caused by 3.6.0 upgrade
- Add function
- Add tests
- Add docs
- Add NEWS
References #5983 for PostGIS 3.6.1
diff --git a/NEWS b/NEWS
index 6fbc465ee..2a2fd08da 100644
--- a/NEWS
+++ b/NEWS
@@ -1,6 +1,16 @@
PostGIS 3.6.1
-2025/xx/xx
+2025/11/xx
+
+This version requires PostgreSQL 12-18, GEOS 3.8 or higher, and Proj 6.1+.
+To take advantage of all features, GEOS 3.14+ is needed.
+To take advantage of all SFCGAL features, SFCGAL 2.2+ is needed.
+
+If you are upgrading postgis_topology and have topogeometry columns, make sure to run
+after the upgrade to fix topogeometry corruption:
+
+SELECT topology.FixCorruptTopoGeometryColumn(schema_name, table_name, feature_column)
+ FROM topology.layer;
* Fixes *
@@ -17,6 +27,8 @@ PostGIS 3.6.1
- #5959, #5984, Prevent histogram target overflow when analysing massive tables (Darafei Praliaskouski)
- #6012, Remove memory leak from lwcircstring_from_lwpointarray (Paul Ramsey)
- #6013, [tiger_geocoder] Load Tiger 2025 data (Regina Obe)
+ - #5983, [topology] topology.FixCorruptTopoGeometryColumn
+ to fix corruption caused by 3.6.0 upgrade (Regina Obe, Francois Bonzon)
PostGIS 3.6.0
diff --git a/doc/extras_topology.xml b/doc/extras_topology.xml
index 3854a4a93..833ad2ca1 100644
--- a/doc/extras_topology.xml
+++ b/doc/extras_topology.xml
@@ -535,6 +535,57 @@ Rename a topology from <varname>topo_stage</varname> to <varname>topo_prod</varn
</refsection>
</refentry>
+ <refentry xml:id="FixCorruptTopoGeometryColumn">
+ <refnamediv>
+ <refname>FixCorruptTopoGeometryColumn</refname>
+ <refpurpose>Fixes topogeometry corruption caused by upgrade to postgis_topology 3.6.0 and higher</refpurpose>
+ </refnamediv>
+ <refsynopsisdiv>
+ <funcsynopsis>
+ <funcprototype>
+ <funcdef>text <function>FixCorruptTopoGeometryColumn</function></funcdef>
+ <paramdef><type>name </type>
+ <parameter>layerSchema</parameter></paramdef>
+ <paramdef><type>name </type>
+ <parameter>layerTable</parameter></paramdef>
+ <paramdef><type>name </type>
+ <parameter>layerColumn </parameter></paramdef>
+ </funcprototype>
+ </funcsynopsis>
+ </refsynopsisdiv>
+
+ <refsection>
+ <title>Description</title>
+
+ <para>
+When upgrading from PostGIS topology <3.6.0 to version >3.6.0+, the topogeometry column definition was changed.
+This caused corruption in topogeometries created before the upgrade. This function fixes this corruption in affected tables.
+ </para>
+
+ <!-- use this format if new function -->
+ <para role="availability" conformance="3.6.1">Availability: 3.6.1</para>
+
+ </refsection>
+
+ <refsection>
+ <title>Examples</title>
+ <para>Fix all topology columns</para>
+ <programlisting>
+SELECT topology.FixCorruptTopoGeometryColumn(schema_name, table_name, feature_column)
+ FROM topology.layer;
+ </programlisting>
+ </refsection>
+
+ <!-- Optionally add a "See Also" section -->
+ <refsection>
+ <title>See Also</title>
+
+ <para>
+ <xref linkend="UpgradeTopology"/>
+ </para>
+ </refsection>
+ </refentry>
+
<refentry xml:id="Populate_Topology_Layer">
<refnamediv>
<refname>Populate_Topology_Layer</refname>
diff --git a/topology/Makefile.in b/topology/Makefile.in
index ce7cbb350..608f5e76a 100644
--- a/topology/Makefile.in
+++ b/topology/Makefile.in
@@ -141,6 +141,7 @@ topology.sql: \
sql/manage/CreateTopology.sql.in \
sql/manage/FindLayer.sql.in \
sql/manage/FindTopology.sql.in \
+ sql/manage/FixCorruptTopoGeometryColumn.sql.in \
sql/manage/ManageHelper.sql.in \
sql/manage/MakeTopologyPrecise.sql.in \
sql/manage/TotalTopologySize.sql.in \
diff --git a/topology/sql/manage/FixCorruptTopoGeometryColumn.sql.in b/topology/sql/manage/FixCorruptTopoGeometryColumn.sql.in
new file mode 100644
index 000000000..9c1ca04ec
--- /dev/null
+++ b/topology/sql/manage/FixCorruptTopoGeometryColumn.sql.in
@@ -0,0 +1,83 @@
+-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
+--
+-- PostGIS - Spatial Types for PostgreSQL
+-- https://postgis.net
+--
+-- Copyright (C) 2025 Regina Obe <lr at pcorp.us>
+--
+-- This is free software; you can redistribute and/or modify it under
+-- the terms of the GNU General Public License. See the COPYING file.
+--
+-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
+--{
+-- FixCorruptTopGeometryColumn((layerSchema name, layerTable name, layerColumn name)
+--
+-- Needed to fix corruption of topogeometries caused by upgrade from < 3.6.0 to 3.6.0 and higher
+--
+-- Availability: 3.6.1
+CREATE OR REPLACE FUNCTION topology.FixCorruptTopoGeometryColumn(layerSchema name, layerTable name, layerColumn name)
+RETURNS text AS
+$$
+DECLARE var_sql text; var_row_count bigint; result text; var_create_index_sql text; var_drop_index_sql text;
+BEGIN
+ result = '';
+ -- if topogeometry is bigint, then fix damaged integer, need to upgrade to bigint
+ IF EXISTS ( SELECT 1
+ FROM pg_catalog.pg_type AS pg_type
+ JOIN pg_catalog.pg_class AS pg_class ON pg_class.oid = pg_type.typrelid
+ JOIN pg_catalog.pg_attribute AS pga ON pga.attrelid = pg_class.oid
+ JOIN pg_catalog.pg_type AS pg_attr_type on pg_attr_type.oid = pga.atttypid
+WHERE pg_type.typname = 'topogeometry' AND pga.attname = 'id'
+ AND
+ pg_type.typnamespace::regnamespace::text = 'topology' AND pga.atttypid::regtype::text = 'bigint' ) THEN
+
+ -- generate index scripts to create and drop indexes that are based on the column
+ IF EXISTS( SELECT 1 FROM pg_indexes WHERE schemaname = layerSchema AND tablename = layerTable AND indexdef LIKE '%(' || layerColumn || ')%' ) THEN
+ SELECT string_agg(indexdef, ';'), string_agg('DROP INDEX ' || quote_ident(schemaname) || '.' || quote_ident(indexname), ';') INTO var_create_index_sql, var_drop_index_sql
+ FROM pg_indexes
+ WHERE schemaname = layerSchema
+ AND tablename = layerTable AND indexdef LIKE ('%(' || layerColumn || ')%');
+ END IF;
+
+ IF var_drop_index_sql > '' THEN
+ EXECUTE var_drop_index_sql;
+ END IF;
+
+ -- correct any corrupt topogeometries and fix
+ var_sql = format('UPDATE %1$I.%2$I
+ SET
+ %3$I = (
+ (%3$I).topology_id,
+ (%3$I).layer_id,
+ ((%3$I).id & 4294967295 )::bigint,
+ ((%3$I).id >> 32)::integer
+ )::topology.topogeometry
+ WHERE ( (%3$I).id & 4294967295 )::bigint <> (%3$I).id OR ( (%3$I).id >> 32 )::integer = (%3$I).type ', layerSchema, layerTable, layerColumn);
+
+ EXECUTE var_sql;
+ GET DIAGNOSTICS var_row_count = ROW_COUNT;
+
+ IF var_create_index_sql > '' THEN
+ EXECUTE var_create_index_sql;
+ result = result || E'\n' || 'Recreating indexes';
+ END IF;
+ result = result || E'\n' || format('%s rows updated for %s.%s.%s column to bigint id type', var_row_count, layerSchema, layerTable, layerColumn);
+ ELSE --we are coming from bigint and going back to integer
+ var_sql = format('UPDATE %1$I.%2$I
+ SET
+ %3$I = (
+ (%3$I).topology_id,
+ (%3$I).layer_id,
+ (%3$I).id,
+ l.feature_type
+ )::topogeometry
+ FROM topology.layer AS l
+ WHERE l.topology_id = (%3$I).topology_id AND l.layer_id = (%3$I).layer_id AND (%3$I).type <> l.feature_type ', topo_schema, topo_table, topo_column);
+ EXECUTE var_sql;
+ GET DIAGNOSTICS var_row_count = ROW_COUNT;
+ result = result || format('%s rows updated for %s.%s.%s column back to integer id type', var_row_count, topo_schema, topo_table, topo_column);
+ END IF;
+ RETURN result;
+END
+$$ language plpgsql;
+--}
diff --git a/topology/test/regress/fix_topogeometry_columns.sql b/topology/test/regress/fix_topogeometry_columns.sql
new file mode 100644
index 000000000..725e36375
--- /dev/null
+++ b/topology/test/regress/fix_topogeometry_columns.sql
@@ -0,0 +1,11 @@
+set client_min_messages to WARNING;
+
+\i :top_builddir/topology/test/load_topology-4326.sql
+\i ../load_features.sql
+\i ../more_features.sql
+\i ../hierarchy.sql
+SELECT topology.FixCorruptTopoGeometryColumn(schema_name, table_name, feature_column)
+FROM topology.layer;
+
+SELECT topology.DropTopology('city_data');
+DROP SCHEMA features CASCADE;
diff --git a/topology/test/regress/fix_topogeometry_columns_expected b/topology/test/regress/fix_topogeometry_columns_expected
new file mode 100644
index 000000000..3652720d1
--- /dev/null
+++ b/topology/test/regress/fix_topogeometry_columns_expected
@@ -0,0 +1,7 @@
+0 rows updated for features.land_parcels.feature column to bigint id type
+0 rows updated for features.traffic_signs.feature column to bigint id type
+0 rows updated for features.city_streets.feature column to bigint id type
+0 rows updated for features.big_parcels.feature column to bigint id type
+0 rows updated for features.big_streets.feature column to bigint id type
+0 rows updated for features.big_signs.feature column to bigint id type
+Topology 'city_data' dropped
diff --git a/topology/test/regress/hooks/hook-after-upgrade-topology.sql b/topology/test/regress/hooks/hook-after-upgrade-topology.sql
index 248b6206f..c0c4166b6 100644
--- a/topology/test/regress/hooks/hook-after-upgrade-topology.sql
+++ b/topology/test/regress/hooks/hook-after-upgrade-topology.sql
@@ -1,6 +1,20 @@
+SELECT * FROM topology.layer;
+\d upgrade_test.feature
+-- https://trac.osgeo.org/postgis/ticket/5983
+SELECT topology.FixCorruptTopoGeometryColumn(schema_name, table_name, feature_column)
+ FROM topology.layer;
+
+\d upgrade_test.feature
-- See https://trac.osgeo.org/postgis/ticket/5102
SELECT topology.CopyTopology('upgrade_test', 'upgrade_test_copy');
+SELECT * FROM topology.layer;
+INSERT INTO upgrade_test.domain_test values (
+ '{1,2}'::topology.topoelement,
+ '{{2,3}}'::topology.topoelementarray
+);
+
+SELECT * FROM upgrade_test.domain_test;
SELECT topology.DropTopology('upgrade_test');
SELECT topology.DropTopology('upgrade_test_copy');
diff --git a/topology/test/tests.mk b/topology/test/tests.mk
index aa1a67526..ea1609deb 100644
--- a/topology/test/tests.mk
+++ b/topology/test/tests.mk
@@ -96,4 +96,6 @@ TESTS += \
$(top_srcdir)/topology/test/regress/validatetopologyrelation_large.sql \
$(top_srcdir)/topology/test/regress/validatetopology.sql \
$(top_srcdir)/topology/test/regress/validatetopology_large.sql \
- $(top_srcdir)/topology/test/regress/verifylargeids.sql
+ $(top_srcdir)/topology/test/regress/verifylargeids.sql \
+ $(top_srcdir)/topology/test/regress/fix_topogeometry_columns.sql
+
diff --git a/topology/topology.sql.in b/topology/topology.sql.in
index 96095fd5b..c364af34d 100644
--- a/topology/topology.sql.in
+++ b/topology/topology.sql.in
@@ -1417,6 +1417,7 @@ LANGUAGE 'plpgsql' VOLATILE STRICT;
#include "sql/manage/CopyTopology.sql.in"
#include "sql/manage/FindTopology.sql.in"
#include "sql/manage/FindLayer.sql.in"
+#include "sql/manage/FixCorruptTopoGeometryColumn.sql.in"
#include "sql/manage/populate_topology_layer.sql.in"
#include "sql/manage/RenameTopology.sql.in"
#include "sql/manage/ValidateTopology.sql.in"
-----------------------------------------------------------------------
Summary of changes:
NEWS | 14 +++-
doc/extras_topology.xml | 51 +++++++++++++
topology/Makefile.in | 1 +
.../sql/manage/FixCorruptTopoGeometryColumn.sql.in | 83 ++++++++++++++++++++++
...gacy_query.sql => fix_topogeometry_columns.sql} | 8 +--
.../test/regress/fix_topogeometry_columns_expected | 7 ++
.../regress/hooks/hook-after-upgrade-topology.sql | 14 ++++
topology/test/tests.mk | 4 +-
topology/topology.sql.in | 1 +
9 files changed, 176 insertions(+), 7 deletions(-)
create mode 100644 topology/sql/manage/FixCorruptTopoGeometryColumn.sql.in
copy topology/test/regress/{legacy_query.sql => fix_topogeometry_columns.sql} (52%)
create mode 100644 topology/test/regress/fix_topogeometry_columns_expected
hooks/post-receive
--
PostGIS
More information about the postgis-tickets
mailing list