Re: BUG #17245: Index corruption involving deduplicated entries

On Thu, Oct 28, 2021 at 2:40 PM Thomas Munro <thomas(dot)munro(at)gmail(dot)com> wrote:
> Relevant to this hypothesis, it's interesting that FreeBSD 13.0 is the
> first release that switched to OpenZFS, a big rebase to a common ZFS
> implementation shared with eg Ubuntu (whereas previously multiple
> groups targeting different OSes maintained different forks deriving
> from OpenSolaris code). I see there is another suspected related
> report[1], and it's on Ubuntu, but filesystem is not mentioned.

Anything is possible. But Kamigishi Rei has said that this database
has never had a hard crash or unclean shut down, which I definitely
believe. Also, they are using ECC on a Xeon processor. This is the
kind of hardware that is generally assumed to be very reliable.

Kamigishi Rei has been an exemplary example of how to report a bug to
an open source community. I want to thank him again. Thanks!

A second similar complaint from Herman Verschooten on Slack didn't
mention ZFS at all. A third similar-seeming report on Slack was from
somebody named Brandon Ros, who used Ubuntu (I believe 20.04, like
Herman Verschooten). Also no indication that ZFS was used.

I find it slightly hard to believe that it's ZFS, simply because all 3
complaints involve Postgres 14. And have a lot of common factors. For
example, Herman also used foreign keys -- a lot of users never bother
with them. And like Kamigishi Rei, Herman found that a REINDEX (or was
it VACUUM FULL?) seemingly made the problem go away.

Brandon Ros gave less information (never talked to him directly), but
did say that he saw extensive corruption, which this looks just like.
Most of the indexes are not corrupt at all, but a small number
(usually all on the same one or two tables) are very corrupt indeed --
even though the usual issues with collation could be ruled out. I
don't want to overstate my confidence here (I could easily be wrong),
but this all seems to add up to a pattern.

--
Peter Geoghegan