Re: Violation of primary key constraint

Toby Murray <toby(dot)murray(at)gmail(dot)com> writes:
> I just had some interaction with RhodiumToad on IRC about a duplicated
> primary key problem I ran into today. After some poking around he
> suggested that I send this to -bugs since it seems like an interesting
> error.

I poked around in the PK index file (thanks for sending that) and could
not find anything that looks wrong. There are a lot of duplicate keys
(a few of the keys appear more than a thousand times) but I think this
is just the result of update activity that hasn't been vacuumed away
yet. I count 181340233 leaf index tuples bearing 168352931 distinct
key values --- that makes for a dead-tuple fraction of 7.7% which is
not quite enough to trigger an autovacuum, so it's not terribly
surprising that the dups are still present.

At this point it seems that it's not the index's fault. What seems more
likely is that somehow the older heap entry failed to get marked "dead"
after an UPDATE.

> ... Especially the one with the ID
> 26709186 since it hasn't been changed in OpenStreetMap in years so
> there is no reason for it to have been touched in any way since the
> import.

Yeah, it's a bit hard to explain that this way unless there was an
UPDATE that didn't change the timestamp or version. How sure are you
that the updating process always changes those?

> Here are some queries and their results that RhodiumToad had me run to
> try and track things down:

Could we see the full results of heap_page_items(get_raw_page()) for
each of the pages where any of these tuples live, viz

11249625
1501614
11247884
1520052
1520056
11249780
1528888
11249622

regards, tom lane