pgsafe 0.8.6

Static safety linter for PostgreSQL DDL migrations — catches unsafe schema changes before they lock or break production.
Documentation 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107

use pg_query::protobuf::ConstrType;
use pg_query::NodeEnum;

use super::Rule;
use crate::{RuleHit, Severity};

pub struct AddCheckWithoutNotValid;

impl Rule for AddCheckWithoutNotValid {
    fn id(&self) -> &'static str {
        "add-check-without-not-valid"
    }
    fn severity(&self) -> Severity {
        Severity::Error
    }

    fn check(&self, node: &NodeEnum, out: &mut Vec<RuleHit>) {
        for c in super::constraints_being_added(node) {
            if matches!(ConstrType::try_from(c.contype), Ok(ConstrType::ConstrCheck))
                && !c.skip_validation
            {
                out.push(RuleHit {
                    message: "Adding a CHECK constraint without NOT VALID scans the whole table \
                              under an ACCESS EXCLUSIVE lock."
                        .into(),
                    guidance:
                        "Add the CHECK with NOT VALID, then run VALIDATE CONSTRAINT separately \
                               (SHARE UPDATE EXCLUSIVE — concurrent reads and writes are allowed)."
                            .into(),
                });
            }
        }

        // An inline CHECK on an `ADD COLUMN` scans the whole table to validate the constraint under
        // an ACCESS EXCLUSIVE lock — regardless of any DEFAULT, and even with no default (NULL rows
        // are still scanned; measured ~320ms-1s on 10M rows vs <1ms for a plain ADD COLUMN). The
        // inline form cannot take NOT VALID, so the safe rewrite differs from the constraint form.
        // (A same-migration empty table is exempted separately by the new-table tracker.)
        for col in super::columns_being_added(node) {
            if super::column_has_constraint(col, ConstrType::ConstrCheck) {
                out.push(RuleHit {
                    message: "An inline CHECK on a new column scans the whole table to validate it \
                              under an ACCESS EXCLUSIVE lock."
                        .into(),
                    guidance: "Add the column without the CHECK, then ADD CONSTRAINT ... CHECK (...) \
                               NOT VALID, then VALIDATE CONSTRAINT separately (SHARE UPDATE EXCLUSIVE)."
                        .into(),
                });
            }
        }
    }
}

#[cfg(test)]
mod tests {
    use crate::{lint_sql, LintOptions};

    #[test]
    fn flags_check_without_not_valid() {
        let findings = lint_sql(
            "ALTER TABLE t ADD CONSTRAINT ck CHECK (a > 0)",
            &LintOptions::default(),
        )
        .unwrap();
        assert!(findings
            .iter()
            .any(|f| f.rule_id == "add-check-without-not-valid"));
    }

    #[test]
    fn ignores_check_with_not_valid() {
        let findings = lint_sql(
            "ALTER TABLE t ADD CONSTRAINT ck CHECK (a > 0) NOT VALID",
            &LintOptions::default(),
        )
        .unwrap();
        assert!(findings
            .iter()
            .all(|f| f.rule_id != "add-check-without-not-valid"));
    }

    #[test]
    fn flags_inline_check_on_add_column_with_default() {
        let findings = lint_sql(
            "ALTER TABLE t ADD COLUMN x int DEFAULT 1 CHECK (x > 0)",
            &LintOptions::default(),
        )
        .unwrap();
        assert!(findings
            .iter()
            .any(|f| f.rule_id == "add-check-without-not-valid"));
    }

    #[test]
    fn flags_inline_check_on_add_column_without_default() {
        // Even with no default, the inline CHECK is validated by scanning the table (the NULL rows
        // are still scanned) under ACCESS EXCLUSIVE — proven by tests/rule_proofs.rs (seq_scan++).
        let findings = lint_sql(
            "ALTER TABLE t ADD COLUMN x int CHECK (x > 0)",
            &LintOptions::default(),
        )
        .unwrap();
        assert!(findings
            .iter()
            .any(|f| f.rule_id == "add-check-without-not-valid"));
    }
}