Duplicate rows in roads/line/polygon tables ?

[cquest]

I've found a few duplicated rows in the planet_osm_roads table on one of the world databases.
This database is quite old, and has been updated using diff for several years.

They all happened on geometries originating from relations (boundaries, etc).
I've not yet checked the line and polygon tables.

I discovered that by trying to create a unique index on these tables in order to be able to use pg_repack to vacuum/cluster these tables without global lock.

[joto]

Do you actually have duplicate rows or just duplicate ids? Several rows with the same id can happen in an osm2pgsql databases, due to long linestring being split into shorter parts or multipolygons being split into their constituent polygons.

If you have actual duplicate rows, where the whole rows are identical, then that would mean there is a bug somewhere.

[cquest]

Duplicate rows, I compared the way column and it was exactly the same.
I'm aware of multipolygons/linestring being split and thus duplicate IDs.

Did the same cleanup on the line table and found 14628 duplicated rows, now deleted.

[joto]

Okay, then there is (or was) a bug somewhere. But that's kind of hard to track down after several years and probably multiple versions of osm2pgsql involved. So unless that's something that can be reproduced with the current version and known input data, there isn't much we can do about it. Also: We are not investing any ressources in the psqgl output any more, because of the move to flex.

[cquest]

Fine for me !
I did not open issue for that for the reason you mentionned, just wanted to share my findings.

Just in case, here is a query to remove those duplicates:
delete from planet_osm_polygon where ctid in (select ctid from (select ctid, row_number() over (partition by osm_id, way order by osm_id) as row_num from planet_osm_polygon) t where row_num>1);