Re: BUG: Postgres 14 + vacuum_defer_cleanup_age + FOR UPDATE + UPDATE

Hi,

Heikki, Andrey, CCing you because you wrote

commit 6655a7299d835dea9e8e0ba69cc5284611b96f29
Author: Heikki Linnakangas <heikki(dot)linnakangas(at)iki(dot)fi>
Date: 2019-07-24 20:24:07 +0300

Use full 64-bit XID for checking if a deleted GiST page is old enough.

On 2023-01-07 19:09:56 -0800, Andres Freund wrote:
> I haven't found other problematic places in HEAD, but did end up find a less
> serious version of this bug in < 14: GetFullRecentGlobalXmin(). I did verify
> that with vacuum_defer_cleanup_age set GetFullRecentGlobalXmin() returns
> values that look likely to cause problems. Its "just" used in gist luckily.

Is there a good way to make breakage in the page recycling mechanism
visible with gist? I guess to see corruption, I'd have to halt a scan
before a page is visited with gdb, then cause the page to be recycled
prematurely in another session, then unblock the first? Which'd then
visit that page, thinking it to be in a different part of the tree than
it actually is?

I'm pretty sure it's broken though.

On 13, with vacuum_defer_cleanup_age=0, the attached script has two
consecutive VACUUM VERBOSEs output

106 index pages have been deleted, 0 are currently reusable.
106 index pages have been deleted, 0 are currently reusable.

in the presence of a prepared transaction. Which makes sense.

But with vacuum_defer_cleanup_age=10000

106 index pages have been deleted, 0 are currently reusable.
106 index pages have been deleted, 106 are currently reusable.

which clearly doesn't seem right.

I just can't quite judge how bad that is.

Greetings,

Andres Freund