Thread: PostgreSQL 15.9 Update: Partitioned tables with foreign key constraints

Hi,

the PostgreSQL 15.9 release notes instruct to look out for especially detached partitions with foreign key constraints.
I'min the process of updating our databases from 15.8 to 15.9 now and found a case where the select statement returns a
constraint.

The release notes say nothing about when to fix that using the generated add or drop statements.

Do I want/need to do that before or after I exchange the software? And if it is to be done on a particular of the two
releases,why is that? 

https://www.postgresql.org/docs/15/release-15-9.html
Section E.1.2, changelog entry 5.

Any insight would be highly appreciated. Thanks in advance.

Cheers
Paul

On 11/19/24 08:11, Paul Foerster wrote:
> Hi,
> 
> the PostgreSQL 15.9 release notes instruct to look out for especially detached partitions with foreign key
constraints.I'm in the process of updating our databases from 15.8 to 15.9 now and found a case where the select
statementreturns a constraint.
 
> 
> The release notes say nothing about when to fix that using the generated add or drop statements.
> 
> Do I want/need to do that before or after I exchange the software? And if it is to be done on a particular of the two
releases,why is that?
 
> 
> https://www.postgresql.org/docs/15/release-15-9.html
> Section E.1.2, changelog entry 5.
> 
> Any insight would be highly appreciated. Thanks in advance.

Read this:

https://www.postgresql.org/about/news/out-of-cycle-release-scheduled-for-november-21-2024-2958/

and hold off awhile.

> 
> Cheers
> Paul
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Paul Foerster <paul.foerster@gmail.com> writes:
> the PostgreSQL 15.9 release notes instruct to look out for especially detached partitions with foreign key
constraints.I'm in the process of updating our databases from 15.8 to 15.9 now and found a case where the select
statementreturns a constraint. 

> The release notes say nothing about when to fix that using the generated add or drop statements.

Generally speaking, our release notes are addressed to someone who's
already installed the update (or a later one).

            regards, tom lane

Hi Paul,

On 2024-Nov-19, Paul Foerster wrote:

> the PostgreSQL 15.9 release notes instruct to look out for especially
> detached partitions with foreign key constraints. I'm in the process
> of updating our databases from 15.8 to 15.9 now and found a case where
> the select statement returns a constraint.
> 
> The release notes say nothing about when to fix that using the
> generated add or drop statements.
> 
> Do I want/need to do that before or after I exchange the software? And
> if it is to be done on a particular of the two releases, why is that?

It doesn't really matter when you do it, because the constraint only
gets broken by running DETACH with the old server code.  You have
already run the DETACH sometime in the past (that's how the constraint
got broken), which means you would not run it again now to the same
table.  The old server code will behave correctly when doing ADD / DROP
constraint, as will the new server code.  Feel free to run it when it's
more convenient to you.

I'd advise against running ALTER TABLE DETACH until you have upgraded,
however -- at least, for partitioned tables that have foreign keys
pointing to other partitioned tables.

Thanks,

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"Postgres is bloatware by design: it was built to house
 PhD theses." (Joey Hellerstein, SIGMOD annual conference 2002)

Hi Adrian,

> On 19 Nov 2024, at 17:17, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> Read this:
>
> https://www.postgresql.org/about/news/out-of-cycle-release-scheduled-for-november-21-2024-2958/
>
> and hold off awhile.

Thanks very much. I will.

Cheers,
Paul

Hi Tom,

> On 19 Nov 2024, at 17:25, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
> Generally speaking, our release notes are addressed to someone who's
> already installed the update (or a later one).

Thank you for the advice.

Cheers,
Paul

Hi Alvaro,

> On 19 Nov 2024, at 17:34, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
>
> It doesn't really matter when you do it, because the constraint only
> gets broken by running DETACH with the old server code.  You have
> already run the DETACH sometime in the past (that's how the constraint
> got broken), which means you would not run it again now to the same
> table.  The old server code will behave correctly when doing ADD / DROP
> constraint, as will the new server code.  Feel free to run it when it's
> more convenient to you.

Thanks.

> I'd advise against running ALTER TABLE DETACH until you have upgraded,
> however -- at least, for partitioned tables that have foreign keys
> pointing to other partitioned tables.

I have no influence on that. It's a third party application. In fact, I can't even do much about the applications
developedinhouse because they're too big. That means, if there is anything built into an application, then it takes
manymoons to get it out again. 

Cheers,
Paul