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