Opened 9 years ago

Closed 8 years ago

Last modified 3 years ago

#1186 closed defect (invalid)

CreateTopoGeom I think is over-called in some cases and creates orphan relations

Reported by: robe Owned by: strk
Priority: low Milestone: PostGIS Fund Me
Component: topology Version: master
Keywords: Cc:


I had this query:

INSERT INTO boston.roads(tlid, fullname, mtfcc, topo)
SELECT DISTINCT ON (m.tlid) m.tlid, fullname, mtfcc,
			2,3, ('{{' || e.edge_id::text || ',2}}')::topology.topoelementarray)
FROM tiger_data.ma_edges AS m 
		INNER JOIN topo_boston.edge As e
		ON m.tlid = e.edge_id
		WHERE m.mtfcc LIKE 'S%';

Which results in creation of 25075 records.

However when I run summary: SELECT topology.TopologySummary?('topo_boston');

It gives me:

Topology topo_boston (15), SRID 2249, precision 0.25
20576 nodes, 31597 edges, 11109 faces, 25144 topogeoms in 1 layers
Layer 3, type Lineal (2), 25144 topogeoms
 Deploy: boston.roads.topo

I suspect the function is being called for every duplicate and not checking that the topo geometry is already registered in relations for that table.

I had even more ridiculous answer of :

Topology topo_boston (15), SRID 2249, precision 0.25
20576 nodes, 31597 edges, 11109 faces, 50288 topogeoms in 1 layers
Layer 2, type Lineal (2), 50288 topogeoms Deploy: boston.roads.topo

The first time I created my table and then cancelled the insert midway when I realized I was adding more than roads.

This is on PostgreSQL 9.1 rc1 so not sure if its a PostgreSQL bug of topology bug.

Change History (4)

comment:1 Changed 9 years ago by robe

Milestone: PostGIS 2.0.0PostGIS Future
Priority: mediumlow

disregard my cancelled query comment. I realized those ophans happened because I did a select without an insert.

However the DISTINCT ON case and the select without insert does demonstrate that the relations table and summary stats can easily get out of synch with reality. Perhaps the only sane thing to do is to provide a cleanup relation function that interogates the tables to verify the topogeom still exists.

comment:2 Changed 8 years ago by strk

So can we close this ticket and file an enhancement one for a garbage-collector removing TopoGeometry objects not found in their deploy table ?

comment:3 Changed 8 years ago by strk

Resolution: invalid
Status: newclosed

See #1844 for an enhancement ticket to drop orphaned TopoGeometry objects.

comment:4 Changed 3 years ago by robe

Milestone: PostGIS FuturePostGIS Fund Me

Milestone renamed

Note: See TracTickets for help on using tickets.