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

From: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
To: Paul Foerster <paul(dot)foerster(at)gmail(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION
Date: 2024-11-30 07:41:02
Message-ID: 202411300741.wuc7rpucq72o@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

On 2024-Nov-29, Paul Foerster wrote:

> > On 29 Nov 2024, at 18:15, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> 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)

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Paul Foerster 2024-11-30 08:44:25 Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION
Previous Message Adrian Klaver 2024-11-30 01:47:55 Re: Errors when restoring backup created by pg_dumpall