3 ms·
A few questions: - Are you using pg_repack? I'm fairly sure its logic has some holes - last time I checked its bug tracker listed potential for data corruption
by anarazel 1y ago
A few questions:
- Are you using pg_repack? I'm fairly sure its logic has some holes - last time I checked its bug tracker listed potential for data corruption that could cause issues like this.
- Have you done OS upgrades? Did affected indexes have any columns affected by collations?
- Have you done analysis on the heap page? E.g. is there any valid data on the page? What is the page's LSN compared to the LSN on index pages pointing to non-existing tuples on the page?
- richvdh 1y ago- We've used pg_repack in the past, though I'm 90% sure we didn't use it in the timeframe in which this corruption must have happened. Anyway, we'll look into this further: thanks for the suggestion. - Yes, we've done OS upgrades. Back in 2021, our DB servers were on Debian Buster; they are now on Bookworm. We're aware of the problems caused by collation changes, and indeed that was one of the first things we checked; but we're careful to use the C locale for our database, so believe we're safe on that front. - For the example we gave (index page 192904826, referencing heap page 264925234), the index page LSN is DB4A3/C73ED0C0, and the heap page LSN is DB4FA/4CAAB9D8, so the index page was written shortly before the heap page. The blog post shows the output of SELECT * FROM heap_page_items for the heap page: it looks like a regular empty page to me.