Jira (PDB-5553) Remove foreign keys from report partitions

[Austin Blatt (Jira)]

Austin Blatt created an issue

PuppetDB / PDB-5553 Remove foreign keys from report partitions Issue Type: Improvement Assignee: Unassigned Created: 2022/10/20 2:02 PM Priority: Normal Reporter: Austin Blatt Foreign keys are "one-directional" in theory (ie. one table references another), but Postgres implements them via bidirectional triggers on each table. This means that when dropping a report partition that has FKs on certnames, environments, and producers we need access exclusive locks on all those tables. This causes all commands (maybe-create-certname), and queries (active nodes CTE) to back up until it finishes. Additionally, the GC routine generally gets its locks in the opposite order of command processing (that is both wrong and bad), which can cause deadlocks (see PE-34458). Add Comment

This message was sent by Atlassian Jira (v8.20.11#820011-sha1:0629dd8)

[Nick Lewis (Jira)]

Nick Lewis updated an issue

PuppetDB / PDB-5553 Remove foreign keys from report partitions

Change By: Nick Lewis Team: Skeletor

Add Comment

[Austin Blatt (Jira)]

Austin Blatt updated an issue

PuppetDB / PDB-5553 Remove foreign keys from report partitions

Change By: Austin Blatt

Foreign keys are "one-directional" in theory (ie. one table references another), but Postgres implements them via bidirectional triggers on each table. This means that when dropping a report partition that has FKs on certnames, environments, and producers we need access exclusive locks on all those tables. This causes all commands (maybe-create-certname), and queries (active nodes CTE) to back up until it finishes. Additionally, the GC routine generally gets its locks in the opposite order of command processing (that is both wrong and bad), which can cause deadlocks (see PE-34458).

Add Comment

[Cas Donoghue (Jira)]

Cas Donoghue updated an issue

PuppetDB / PDB-5553 Remove foreign keys from report partitions

Change By: Cas Donoghue Story Points: 1

Add Comment

[Cas Donoghue (Jira)]

Cas Donoghue updated an issue

PuppetDB / PDB-5553 Remove foreign keys from report partitions

Change By: Cas Donoghue Sprint: Skeletor 11/23/2022

Add Comment

[Austin Blatt (Jira)]

Austin Blatt assigned an issue to Austin Blatt

PuppetDB / PDB-5553 Remove foreign keys from report partitions

Change By: Austin Blatt Assignee: Austin Blatt

Add Comment

[Cas Donoghue (Jira)]

Cas Donoghue updated an issue

PuppetDB / PDB-5553 Remove foreign keys from report partitions

Change By: Cas Donoghue Sprint: Skeletor 11/23/2022 , Skeletor 12/07/2022

Add Comment

[Sean McDonald (Jira)]

Sean McDonald updated an issue

PuppetDB / PDB-5553 Remove foreign keys from report partitions

Change By: Sean McDonald Sprint: Skeletor 11/23/2022, Skeletor 12/07/2022 , Skeletor 12/21/2022

Add Comment

[Cas Donoghue (Jira)]

Cas Donoghue updated an issue

PuppetDB / PDB-5553 Remove foreign keys from report partitions

Change By: Cas Donoghue Sprint: Skeletor 11/23/2022, Skeletor 12/07/2022, Skeletor 12/21/2022 , Skeletor 01/18/2023

Add Comment

[Cas Donoghue (Jira)]

Cas Donoghue updated an issue

PuppetDB / PDB-5553 Remove foreign keys from report partitions

Change By: Cas Donoghue Sprint: Skeletor 11/23/2022, Skeletor 12/07/2022, Skeletor 12/21/2022, Skeletor 01/18/2023 , Skeletor 02/01/2023

Add Comment

[Cas Donoghue (Jira)]

Cas Donoghue updated an issue

PuppetDB / PDB-5553 Remove foreign keys from report partitions

Change By: Cas Donoghue Sprint: Skeletor 11/23/2022, Skeletor 12/07/2022, Skeletor 12/21/2022, Skeletor 01/18/2023, Skeletor 02/01/2023 , Skeletor 02/15/2023

Add Comment

[Cas Donoghue (Jira)]

Cas Donoghue updated an issue

PuppetDB / PDB-5553 Remove foreign keys from report partitions

Change By: Cas Donoghue Sprint: Skeletor 11/23/2022, Skeletor 12/07/2022, Skeletor 12/21/2022, Skeletor 01/18/2023, Skeletor 02/01/2023, Skeletor 02/15/2023 , Skeletor 03/01/2024

Add Comment

[Austin Blatt (Jira)]

Austin Blatt commented on PDB-5553

Re: Remove foreign keys from report partitions Migration and its test are ready for review, I am investigating if the purge nodes GC operation needs modification as a result. I will also add a test if that is the case, given no tests are currently failing.

Add Comment

[Austin Blatt (Jira)]

Austin Blatt commented on PDB-5553

Re: Remove foreign keys from report partitions

I verified that removing the foreign key resulted in purging nodes failing to remove the reports. I pushed a fix for that issue, but it still needs a test as none of our existing ones failed.

Add Comment

[Austin Blatt (Jira)]

Austin Blatt commented on PDB-5553

Re: Remove foreign keys from report partitions

added tests, this is ready for merge now

Add Comment

[Joshua Partlow (Jira)]

Joshua Partlow commented on PDB-5553

Re: Remove foreign keys from report partitions

The 7.x pipeline passed, and this is in 2021.7.3-rc1-60-gee127a6 which has been through the 2021.7.x integration pipeline. Adding a merge up pr for main now.

Add Comment

[Joshua Partlow (Jira)]

Joshua Partlow commented on PDB-5553

Re: Remove foreign keys from report partitions

This is merged up to main in ef929079a which is in 2023.1.0-rc1-74-g0c461fa, and that has run in the pe main ci. But that currently has a bunch of failures related to the test forge cert being expired.

Add Comment

[Austin Blatt (Jira)]

Austin Blatt updated an issue

PuppetDB / PDB-5553

Remove foreign keys from report partitions

Change By: Austin Blatt Release Notes: Not Needed Release Notes Summary: This has little user-facing effect, the improvement to partition management is probably better documented in the release note for switching to declarative partitioning.

Add Comment

[Austin Blatt (Jira)]

Austin Blatt updated an issue

PuppetDB / PDB-5553 Remove foreign keys from report partitions

Change By: Austin Blatt Fix Version/s: PDB 7.12.2 Fix Version/s: PDB 8.0.0

Add Comment

[Jonathan Newman (Jira)]

Jonathan Newman updated an issue

PuppetDB / PDB-5553 Remove foreign keys from report partitions

Change By: Jonathan Newman Fix Version/s: PDB 7.12.2 Fix Version/s: PDB 7.13.0

Add Comment