[SCM] PostGIS branch master updated. 3.4.0rc1-1105-gcb1a9e88e
git at osgeo.org
git at osgeo.org
Tue May 7 02:24:02 PDT 2024
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, master has been updated
via cb1a9e88e5e12f8bdbaa8ab8b86989d7702be6f8 (commit)
from fd3a41f8decabff53bad205ce0b84b008e49ab35 (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 cb1a9e88e5e12f8bdbaa8ab8b86989d7702be6f8
Author: Lars Aksel Opsahl <lop at skogoglandskap.no>
Date: Sat Apr 20 07:08:33 2024 +0200
Allow use of custom sequences in topology tables
Closes #5721
Includes regress test
diff --git a/NEWS b/NEWS
index 3e97c2452..afe3a39f5 100644
--- a/NEWS
+++ b/NEWS
@@ -44,6 +44,7 @@ Andreas Schild (German Team)
* New Features *
+ - #5721, Allow sharing sequences between different topologies (Lars Opsahl)
- #5667, TopoGeo_LoadGeometry (Sandro Santilli)
- #5055, add explicit <> geometry operator to prevent non-unique
error with <> and != (Paul Ramsey)
diff --git a/topology/postgis_topology.c b/topology/postgis_topology.c
index b6c5ea8ac..3a3441480 100644
--- a/topology/postgis_topology.c
+++ b/topology/postgis_topology.c
@@ -2137,8 +2137,14 @@ cb_getNextEdgeId( const LWT_BE_TOPOLOGY* topo )
LWT_ELEMID edge_id;
initStringInfo(sql);
- appendStringInfo(sql, "SELECT nextval('\"%s\".edge_data_edge_id_seq')",
- topo->name);
+ appendStringInfo(sql, "SELECT nextval("
+ "SUBSTRING(column_default, "
+ "POSITION('(' IN column_default)+2, "
+ "(POSITION(':' IN column_default)-POSITION('(' IN column_default)-3))"
+ ") "
+ "FROM information_schema.columns "
+ "WHERE table_schema = '%s' AND table_name='edge_data' AND column_name = 'edge_id' \n",
+ topo->name);
spi_result = SPI_execute(sql->data, false, 0);
MemoryContextSwitchTo( oldcontext ); /* switch back */
if ( spi_result != SPI_OK_SELECT )
diff --git a/topology/sql/populate.sql.in b/topology/sql/populate.sql.in
index bd21a513e..a5d94673c 100644
--- a/topology/sql/populate.sql.in
+++ b/topology/sql/populate.sql.in
@@ -93,6 +93,7 @@ DECLARE
nodeid int;
rec RECORD;
containing_face int;
+ seq_name_node text;
BEGIN
--
-- Atopology and apoint are required
@@ -152,10 +153,16 @@ BEGIN
--
-- Get new node id from sequence
--
- FOR rec IN EXECUTE 'SELECT nextval(' ||
- quote_literal(
- quote_ident(atopology) || '.node_node_id_seq'
- ) || ')'
+ EXECUTE FORMAT(
+ $fmt$
+ SELECT column_default
+ FROM information_schema.columns
+ WHERE table_schema = %1$L AND table_name='node' AND column_name = 'node_id'
+ $fmt$,
+ atopology
+ ) INTO seq_name_node;
+
+ FOR rec IN EXECUTE 'SELECT ' || seq_name_node
LOOP
nodeid = rec.nextval;
END LOOP;
@@ -205,6 +212,7 @@ DECLARE
edgeid int;
rec RECORD;
ix geometry;
+ seq_name_edge_data text;
BEGIN
--
-- Atopology and apoint are required
@@ -333,11 +341,16 @@ BEGIN
--
-- Get new edge id from sequence
--
- FOR rec IN EXECUTE 'SELECT nextval(' ||
- quote_literal(
- quote_ident(atopology) || '.edge_data_edge_id_seq'
- ) || ')'
- LOOP
+ EXECUTE FORMAT(
+ $fmt$
+ SELECT column_default
+ FROM information_schema.columns
+ WHERE table_schema = %1$L AND table_name='edge_data' AND column_name = 'edge_id'
+ $fmt$,
+ atopology
+ ) INTO seq_name_edge_data;
+
+ FOR rec IN EXECUTE 'SELECT ' || seq_name_edge_data LOOP
edgeid = rec.nextval;
END LOOP;
@@ -446,6 +459,7 @@ DECLARE
loc float8;
segnum int;
numsegs int;
+ seq_name_face text;
BEGIN
--
-- Atopology and apoly are required
@@ -605,10 +619,16 @@ BEGIN
--
-- Get new face id from sequence
--
- FOR rec IN EXECUTE 'SELECT nextval(' ||
- quote_literal(
- quote_ident(atopology) || '.face_face_id_seq'
- ) || ')'
+ EXECUTE FORMAT(
+ $fmt$
+ SELECT column_default
+ FROM information_schema.columns
+ WHERE table_schema = %1$L AND table_name='face' AND column_name = 'face_id'
+ $fmt$,
+ atopology
+ ) INTO seq_name_face;
+
+ FOR rec IN EXECUTE 'SELECT ' || seq_name_face
LOOP
faceid = rec.nextval;
END LOOP;
diff --git a/topology/test/regress/share_sequences.sql b/topology/test/regress/share_sequences.sql
new file mode 100644
index 000000000..c90080f62
--- /dev/null
+++ b/topology/test/regress/share_sequences.sql
@@ -0,0 +1,38 @@
+BEGIN;
+set client_min_messages to ERROR;
+
+SELECT topology.CreateTopology('Main',4326) > 0; -- Create the master topology
+SELECT topology.st_createtopogeo('Main','SRID=4326;POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(5 5, 8 9, 4 2, 5 5))');
+SELECT max(edge_id)||' max edge_id Main.edge_data' from "Main".edge_data;
+SELECT max(node_id)||' max node_id Main.node' from "Main".node;
+SELECT max(face_id)||' max node_id Main.face' from "Main".face;
+
+
+SELECT topology.CreateTopology('temp01',4326) > 0; -- Create temp01 toplogies to use the masters primary keys
+ALTER TABLE temp01.edge_data ALTER COLUMN edge_id set default nextval('"Main".edge_data_edge_id_seq'::regclass);
+ALTER TABLE temp01.node ALTER COLUMN node_id set default nextval('"Main".node_node_id_seq'::regclass);
+ALTER TABLE temp01.face ALTER COLUMN face_id set default nextval('"Main".face_face_id_seq'::regclass);
+DROP SEQUENCE temp01.edge_data_edge_id_seq; -- Drop sequences since we do no need them any more since we now use the main
+DROP SEQUENCE temp01.node_node_id_seq;
+DROP SEQUENCE temp01.face_face_id_seq;
+
+SELECT topology.CreateTopology('temp02',4326) > 0; -- Create temp02 toplogies to use the masters primary keys
+ALTER TABLE temp02.edge_data ALTER COLUMN edge_id set default nextval('"Main".edge_data_edge_id_seq'::regclass);
+ALTER TABLE temp02.node ALTER COLUMN node_id set default nextval('"Main".node_node_id_seq'::regclass);
+ALTER TABLE temp02.face ALTER COLUMN face_id set default nextval('"Main".face_face_id_seq'::regclass);
+DROP SEQUENCE temp02.edge_data_edge_id_seq; -- Drop sequences since we do no need them any more since we now use the main
+DROP SEQUENCE temp02.node_node_id_seq;
+DROP SEQUENCE temp02.face_face_id_seq;
+
+SELECT topology.st_createtopogeo('temp01', 'SRID=4326;POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(5 5, 8 9, 4 2, 5 5))'); -- Add to temp01 id will start on 1
+SELECT max(edge_id)||' max edge_id temp01.edge_data' from temp01.edge_data;
+SELECT max(node_id)||' max node_id temp01.node' from temp01.node;
+SELECT max(face_id)||' max face_id temp01.face' from temp01.face;
+
+
+SELECT topology.addEdge('temp02', 'LINESTRING(8 10, 10 10, 10 12, 8 10)'); -- Add to temp01 id will not start on 1
+SELECT max(edge_id)||' max edge_id temp02.edge_data' from temp02.edge_data;
+SELECT max(node_id)||' max node_id temp02.node' from temp02.node;
+SELECT max(face_id)||' max face_id temp02.face' from temp02.face;
+
+ROLLBACK;
diff --git a/topology/test/regress/share_sequences_expected b/topology/test/regress/share_sequences_expected
new file mode 100644
index 000000000..b94d73c85
--- /dev/null
+++ b/topology/test/regress/share_sequences_expected
@@ -0,0 +1,15 @@
+t
+Topology Main populated
+2 max edge_id Main.edge_data
+2 max node_id Main.node
+2 max node_id Main.face
+t
+t
+Topology temp01 populated
+4 max edge_id temp01.edge_data
+4 max node_id temp01.node
+4 max face_id temp01.face
+5
+5 max edge_id temp02.edge_data
+5 max node_id temp02.node
+0 max face_id temp02.face
diff --git a/topology/test/tests.mk b/topology/test/tests.mk
index cc4b2b4b2..cbe263369 100644
--- a/topology/test/tests.mk
+++ b/topology/test/tests.mk
@@ -48,8 +48,9 @@ TESTS += \
$(top_srcdir)/topology/test/regress/polygonize.sql \
$(top_srcdir)/topology/test/regress/populate_topology_layer.sql \
$(top_srcdir)/topology/test/regress/removeunusedprimitives.sql \
- $(top_srcdir)/topology/test/regress/renametopology.sql \
$(top_srcdir)/topology/test/regress/renametopogeometrycolumn.sql \
+ $(top_srcdir)/topology/test/regress/renametopology.sql \
+ $(top_srcdir)/topology/test/regress/share_sequences.sql \
$(top_srcdir)/topology/test/regress/sqlmm.sql \
$(top_srcdir)/topology/test/regress/st_addedgemodface.sql \
$(top_srcdir)/topology/test/regress/st_addedgenewfaces.sql \
@@ -71,11 +72,12 @@ TESTS += \
$(top_srcdir)/topology/test/regress/st_simplify.sql \
$(top_srcdir)/topology/test/regress/topo2.5d.sql \
$(top_srcdir)/topology/test/regress/topoelementarray_agg.sql \
+ $(top_srcdir)/topology/test/regress/topoelement_cast.sql \
$(top_srcdir)/topology/test/regress/topoelement.sql \
- $(top_srcdir)/topology/test/regress/topogeo_addlinestring.sql \
$(top_srcdir)/topology/test/regress/topogeo_addlinestring_robust.sql \
- $(top_srcdir)/topology/test/regress/topogeo_addpoint.sql \
+ $(top_srcdir)/topology/test/regress/topogeo_addlinestring.sql \
$(top_srcdir)/topology/test/regress/topogeo_addpoint_merge_edges.sql \
+ $(top_srcdir)/topology/test/regress/topogeo_addpoint.sql \
$(top_srcdir)/topology/test/regress/topogeo_addpolygon.sql \
$(top_srcdir)/topology/test/regress/topogeo_loadgeometry.sql \
$(top_srcdir)/topology/test/regress/topogeom_addtopogeom.sql \
@@ -85,6 +87,5 @@ TESTS += \
$(top_srcdir)/topology/test/regress/topojson.sql \
$(top_srcdir)/topology/test/regress/topologysummary.sql \
$(top_srcdir)/topology/test/regress/totopogeom.sql \
- $(top_srcdir)/topology/test/regress/validatetopology.sql \
$(top_srcdir)/topology/test/regress/validatetopologyrelation.sql \
- $(top_srcdir)/topology/test/regress/topoelement_cast.sql
+ $(top_srcdir)/topology/test/regress/validatetopology.sql
-----------------------------------------------------------------------
Summary of changes:
NEWS | 1 +
topology/postgis_topology.c | 10 ++++--
topology/sql/populate.sql.in | 46 ++++++++++++++++++--------
topology/test/regress/share_sequences.sql | 38 +++++++++++++++++++++
topology/test/regress/share_sequences_expected | 15 +++++++++
topology/test/tests.mk | 11 +++---
6 files changed, 101 insertions(+), 20 deletions(-)
create mode 100644 topology/test/regress/share_sequences.sql
create mode 100644 topology/test/regress/share_sequences_expected
hooks/post-receive
--
PostGIS
More information about the postgis-tickets
mailing list