Subject: Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION

From: Alvaro Herrera <[email-masked]>
Date: Sat, 30 Nov 2024 08:41:02 +0100

Hello,

On 2024-Nov-29, Paul Foerster wrote:

> > On 29 Nov 2024, at 18:15, Alvaro Herrera <[email-masked]> wrote:

> > This reports case 2 as OK and case 1 as bogus, as should be.  I tried
> > adding more partitions and this seems to hold correctly.  I was afraid
> > though that this would fail if we create an FK in an intermediate level
> > of the partition hierarchy ... but experimentation doesn't seem to give
> > that result.  I've run out of time today to continue to look though.
> 
> Thanks very much for this really detailed analysis and sharing your
> insights. I'll give the new query a try on Monday when I'm back at
> work. Do I also need to recheck all other databases with this new
> query which didn't report anything with the original query?

Only if you have self-referencing FKs in partitioned tables.  It
would be an interesting data point to verify whether this reports
anything else.  Also, I'd be really curious if your databases include
the case I'm suspicious about: a multi-level hierarchy containing an FK
that points to an intermediate level of itself.

> > Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
> > "La vida es para el que se aventura"
> 
> You're located in the middle of the forest east of Freiburg im
> Breisgau in Germany? 🤣

I'm within fives minutes of longitude and latitude of that location, yes
:-) I didn't want to give unnecessary precision there, but is somebody
wants to chat sometime or whatever is welcome to ping me.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"Estoy de acuerdo contigo en que la verdad absoluta no existe...
El problema es que la mentira sí existe y tu estás mintiendo" (G. Lama)

---

From: Paul Foerster <[email-masked]>
Date: Sat, 30 Nov 2024 09:44:25 +0100

Hi Alvaro,

> On 30 Nov 2024, at 08:41, Alvaro Herrera <[email-masked]> wrote:
> 

The instance I reported was the only one in our several hundred databases. So I guess this is really a corner case. As mentioned I'll try on Monday.

> wants to chat sometime or whatever is welcome to ping me.

Then you're not really far way. I'm located in the Solothurn, Switzerland area which is only less than 90 km away. Drop me a line if you ever make it to Solothurn. 🤣

Cheers,
Paul