A possible theory is that pg_largeobject_metadata_oid_index has been
corrupt for a long time, allowing a lot of duplicate entries to be made.
However, unless pg_largeobject's pg_largeobject_loid_pn_index is *also*
corrupt, you'd think that creation of such duplicates would still be
stopped by that unique index. There's something mighty odd here.Let me roll back the test instance to before the first vacuumlo run and verify if the index was OK before - will only get to do that on monday though.
To follow up on this and share my research: The index was corrupt also in the version before the vacuumlo (and possibly much longer), so that was only the indicator, not the cause.
pg_largeobject's pg_largeobject_loid_pn_index was OK, and we most certainly did not have an OID overflow as max(OID) was around 90 million, far from the 4 billion for the 32 bit rollover. So that indicated another problem source.
I was able to correlate the transaction ids (xmin) of the duplicate rows in pg_largeobject_meta with times stored in another table, and found the duplicates were all added on one specific day, while the originals nicely spread out over time. I was further able to trace that date back to a cluster upgrade (pg_upgradecluster) we performed with that machine, so I guess we have a pretty good candidate. After the upgrade, no more duplicates were produced.
As we have a way to recover, I'm not too concerned about this, but to add to the lessons learned: should there have been messages in the upgrade log or is this a known issue when upgrading? I'm pretty sure I checked the logs, but might have missed something if looking for the wrong things.
Thanks,
Tobias
