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

[robe]

I had this query:

INSERT INTO boston.roads(tlid, fullname, mtfcc, topo)
SELECT DISTINCT ON (m.tlid) m.tlid, fullname, mtfcc,
	topology.CreateTopoGeom('topo_boston',
			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 :

topologysummary
----------------
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.

[robe]

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.

[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 ?

[strk]

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

[robe]

Milestone renamed