Feedback-ID: ia2694551:Fastmail
Date: Sat, 30 Nov 2024 08:41:02 +0100
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Paul Foerster <paul.foerster@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, 
	Adrian Klaver <adrian.klaver@aklaver.com>, pgsql-general list <pgsql-general@lists.postgresql.org>
Subject: Re: PostgreSQL 15.10 update corrective action for ATTACH
 PARTITION/DETACH PARTITION
Message-ID: <202411300741.wuc7rpucq72o@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <41055379-D0D4-487D-9D8D-A624B79FEA56@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/202411300741.wuc7rpucq72o%40alvherre.pgsql>
Precedence: bulk

Hello,

On 2024-Nov-29, Paul Foerster wrote:

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