Feedback-ID: i0fe9450f:Fastmail
Date: Thu, 5 Oct 2023 07:44:14 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Thomas Munro <thomas.munro@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, 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
Message-ID: <ZR3qvrYULJWaUnBK@paquier.xyz>
References: <18146-04e908c662113ad5@postgresql.org>
 <9fc4118d2a8f2290d8598a9c4d451c4c90b17cee.camel@cybertec.at>
 <ZR0TPQvVQqIzuPsG@paquier.xyz>
 <2583426.1696429441@sss.pgh.pa.us>
 <CA+hUKG+V39zO6E9kzg=SP0S8jGYy6AYY9xWCkGLqKxfUJuuFeg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="NYxcb7/XBnJw39AD"
Content-Disposition: inline
In-Reply-To: 
 <CA+hUKG+V39zO6E9kzg=SP0S8jGYy6AYY9xWCkGLqKxfUJuuFeg@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/ZR3qvrYULJWaUnBK%40paquier.xyz>
Precedence: bulk


--NYxcb7/XBnJw39AD
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Thu, Oct 05, 2023 at 10:12:27AM +1300, Thomas Munro wrote:
> On Thu, Oct 5, 2023 at 3:26=E2=80=AFAM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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.
>
> This thread seems to be saying otherwise:
>=20
> https://www.postgresql.org/message-id/flat/2348.1544474335%40sss.pgh.pa.us

Yeah, that's not been solved.  I've put my mind on this problem for a
few hours last May, just before PGCon, and there was an argument that
marking all the pages as dirty is kind of a waste of resources as it
would create WAL for data that's going to be gone a little bit later
as an effect of the truncate, leading to an extra burst of I/O
especially for large truncatoins.  FWIW, I think that I'd be
personally OK with using this method.  At least that's safe, simple,
backpatchable and it does not require any new magic.  I know that
there are voices that argued against this method, but here we are N
years later, so perhaps we should just do that on HEAD at least.

>> 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.
>=20
> It's surprising that ftruncate() AKA chsize() is able to fail like
> this (I am not a Windows user but AFAIR that sharing stuff obstructs
> stuff like open, unlink, rename, so it surprises me to see it come up
> with ftruncate, since we must already have made it past the open
> stage).  Hmm, the documentation is scant, but I know from my attempts
> to use large files that chsize() is probably some kind of wrapper
> around SetEndOfFile() or similar, and that is documented as failing if
> someone has the file mapped.  I don't know why someone would have the
> file mapped, though.

(shrug)

> But as for what we should do about it, PANIC (as suggested by several
> people) seems better than corruption, if we're not going to write some
> kind of resilience?  How else are we supposed to deal with "this
> shouldn't happen, and if it does we're hosed?"

A PANIC may be OK for this specific syscall and would be better, but
the problematic area is larger than that as we'd still finish with a
corruption as long as there's an ERROR or a FATAL between the moment
the buffers (potentially dirty, with live-still-dead-in-memory tuples
on disk) are discarded and the moment the truncation fails.  Another
method discussed is the use of a critical section (I recall that there
were some pallocs in this area, actually, but got nothing on my notes
about that...).=20
--
Michael

--NYxcb7/XBnJw39AD
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmUd6r4ACgkQnvQgOdby
QH2iDQ//auHyQVNKy8oNpEq44vAJ6zAbtzHSWrOIbUXgwTR+1YA8Mh9hUWyTbSuw
airRNc6OXtAlOyC6Ebdq3gOGHLW6xPjyuafAQWGsF/+k+gectwnu2zULJjDgF0cs
vcW5uOIO7VmP2O0+pp8uBPR2oQ4iosDjO+XpGB08y/7w5EixjeiStRwwwGBan/Fi
2OEGTWwPHDR19/ks1IBBPA3a20y0sqYRYI6plsrwWVfpyPOxGN27Cn2J8sNAZq+G
axhkz4bpkLiUTzu885ecH2D/9LAsPcgqmL5DsZp5qx19RpGKtVH/wJFwzjaMzZzP
BopOJrbFCv585DoIhd5eNEtUZiiEdvTqmuPmyRNvLzLZLymiQ5wZYlKvbu1ggLSK
DWJdpdSbTRrq4NlRcyRC1xIgAoYpFgvT9Z8is4ktSCoJIMP0mVhq0smpSF/T7tuJ
ZNckJpVcj8UtrHfRK/vjnPLjD2KY2T0ScrihQu0FxA+4H8RERMNMzdLZGosnzB7z
+TrIUZD0JbFKlqK7GAW+LlGsuAUehfHoG4EGFGJO+UYiLNZ+dlKm4ZhNt7GzRu9/
4ayV8/Euh9Aw3uuQtNMafQDpXBTgfmc8IJR9ZcGO5NeL4n/HmxHPTSgyrO1VpGmi
1LbOQD8IbpuXTqC+uhnNLOjO/V7Eii4uSKacNj5qAinQYhhNxhI=
=qHN1
-----END PGP SIGNATURE-----

--NYxcb7/XBnJw39AD--