Re: Bug in recovery of drop database?

On 2/6/25 14:28, David Steele wrote:
> On 2/6/25 13:05, Andres Freund wrote:
>
>> IOW, while the two-step-drop-database is an issue, even without it, your
>> reproducer doesn't seem like it actually would work, even if it were a
>> single
>> step, to implement the below scenario:
>
> The repro works almost every time for me on all six active branches. It
> hasn't worked a few times, but in those cases I suspect I accidentally
> skipped a step. I'm running it manually each time.

Sorry, I misunderstood you here -- I thought you were saying the
reproducer doesn't work as is, which was confusing since it definitely does.

But I think it would work just fine if recovery was able stop before the
drop database. The timestamp is gathered before the drop database so I'd
expect that timestamp to get me recovered to before the drop database
whether it is inclusive or not. Certainly that's what the users expect
and that's what happens if I add another transaction before the drop
database.

Time-targeted recovery, which is by far the most common, doesn't always
work as one would hope depending on what WAL records are available to
guide recovery. The test scenarios that users setup are usually very
misleading because the lack of organic database activity often leads to
unexpected results.

I think in a database with normal activity this condition would be
fairly hard to hit, but that doesn't make it any less a bug. And the
fact that it is so easy to hit in common test scenarios makes users
worry about whether Postgres is able to recover consistently. I've seen
two reports of this recently which likely means even more people are
running into it.

Regards,
-David