Message-ID: <b541e75d-3185-4f61-a5d1-6786a8ccc1ce@postsubmeta.net>
Date: Mon, 30 Sep 2024 20:35:12 -0400
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: pg_upgrade check for invalid databases
To: Daniel Gustafsson <daniel@yesql.se>, Tom Lane <tgl@sss.pgh.pa.us>
Cc: Nathan Bossart <nathandbossart@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@postgresql.org>
References: <f9315bf0-e03e-4490-9f0d-5b6f7a6d9908@postsubmeta.net>
 <Zvqx2Xs4RUdByT8S@nathan> <1455756.1727708139@sss.pgh.pa.us>
 <CE534F60-5CDD-489B-BBD1-0A20F1B783BF@yesql.se>
Content-Language: en-US
From: Thomas Krennwallner <tk@postsubmeta.net>
In-Reply-To: <CE534F60-5CDD-489B-BBD1-0A20F1B783BF@yesql.se>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/b541e75d-3185-4f61-a5d1-6786a8ccc1ce%40postsubmeta.net>
Precedence: bulk

On 30/09/2024 17.29, Daniel Gustafsson wrote:
>> On 30 Sep 2024, at 16:55, Tom Lane <tgl@sss.pgh.pa.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