Re: pg_upgrade check for invalid databases

On 30/09/2024 17.29, Daniel Gustafsson wrote:
>> On 30 Sep 2024, at 16:55, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> TBH I'm not finding anything very much wrong with the current
>> behavior... this has to be a rare situation, do we need to add
>> debatable behavior to make it easier?
>
> One argument would be to make the checks consistent, pg_upgrade generally tries
> to report all the offending entries to help the user when fixing the source
> database. Not sure if it's a strong enough argument for carrying code which
> really shouldn't see much use though.
In general, I agree that this situation should be rare for deliberate
DROP DATABASE interrupted in interactive sessions.

Unfortunately, for (popular) tools that perform automatic "temporary
database" cleanup, we could recently see an increase in invalid databases.

The additional check for pg_upgrade was made necessary due to several
unrelated customers having invalid databases that stem from left-over
Prisma Migrate "shadow databases" [1]. We could not reproduce this
Prisma Migrate issue yet, as those migrations happened some time ago.
Maybe this bug really stems from a much older Prisma Migrate version and
we only see the fallout now. This is still a TODO item.

But it appears that this tool can get interrupted "at the wrong time"
while it is deleting temporary databases (probably a manual Ctrl-C), and
clients are unaware that this can then leave behind invalid databases.

Those temporary databases do not cause any harm as they are not used
anymore. But eventually, PG installations will be upgraded to the next
major version, and it is only then when those invalid databases
resurface after pg_upgrade fails to run the checks.

Long story short: interactive DROP DATABASE interrupts are rare (they do
exist, but customers are usually aware). Automation tools on the other
hand may run DROP DATABASE and when they get interrupted at the wrong
time they will then produce several left-over invalid databases.
pg_upgrade will then fail to run the checks.

[1]
https://www.prisma.io/docs/orm/prisma-migrate/understanding-prisma-migrate/shadow-database