Re: Remove duplicated row in pg_largeobject_metadata

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