From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michael Paquier <michael@paquier.xyz>
cc: Laurenz Albe <laurenz.albe@cybertec.at>, rootcause000@gmail.com,
        pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18146: Rows reappearing in Tables after Auto-Vacuum Failure
 in PostgreSQL on Windows
In-reply-to: <ZR0TPQvVQqIzuPsG@paquier.xyz>
References: <18146-04e908c662113ad5@postgresql.org>
 <9fc4118d2a8f2290d8598a9c4d451c4c90b17cee.camel@cybertec.at>
 <ZR0TPQvVQqIzuPsG@paquier.xyz>
Comments: In-reply-to Michael Paquier <michael@paquier.xyz>
	message dated "Wed, 04 Oct 2023 16:24:45 +0900"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2583425.1696429441.1@sss.pgh.pa.us>
Date: Wed, 04 Oct 2023 10:24:01 -0400
Message-ID: <2583426.1696429441@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2583426.1696429441%40sss.pgh.pa.us>
Precedence: bulk

Michael Paquier <michael@paquier.xyz> writes:
> On Wed, Oct 04, 2023 at 09:17:11AM +0200, Laurenz Albe wrote:
>> Data corruption like this is not necessarily caused by a PostgreSQL bug.

> Err, well...  A failure on the end-of-vacuum truncation should not
> lead to corruption afterwards as well, and this ought to be safe even
> if this step failed.  This is a very tricky problem that nobody has
> really looked into yet.

ISTM we did identify the problem: while all the tuples in the
pages-to-be-truncated should be dead and thus invisible, it may
be that some of those pages are dirty and haven't been written
out of shared buffers yet, and the page versions on disk contain
tuples that look live.  If VACUUM discards those dirty buffers
and then fails to truncate, voila you have tuples rising from
the dead.

I'm too lazy to check the commit log right now, but I think
we did implement a fix for that (ie, flush dirty pages even
if we anticipate them going away due to truncation).  But as
Laurenz says, v10 is out of support and possibly didn't get
that fix.  Even if it did, you'd need to be running one of
the last minor releases, because this wasn't very long ago.

In the end though, the *real* problem here is running on a
platform that randomly disallows writes to disk.  There's only
so much that Postgres can possibly do about unreliability of the
underlying platform.  I would never run a production database on
Windows, because it's just too prone to that sort of BS.

			regards, tom lane