Hi, While reviewing Shihao’s patch [1], I noticed a gap in how PostgreSQL handles enforcement and validation of constraints. I would like to bring this up for discussion.
# Current behavior Each "CREATE TABLE p" below starts a separate example. The examples below use CHECK constraints, but the same rules should apply to foreign-key constraints as well. 1. Adding an enforced but unvalidated constraint When adding a constraint to an existing table, users can specify ENFORCED NOT VALID to skip validation of existing rows while enforcing the constraint on subsequent inserts and updates. This allows existing violations to remain for now: ``` evantest=# create table p (a int); CREATE TABLE evantest=# alter table p add constraint c check (a > 0) enforced not valid; ALTER TABLE evantest=# select conrelid::regclass, conenforced, convalidated from pg_constraint where conname='c'; conrelid | conenforced | convalidated ----------+-------------+-------------- p | t | f (1 row) ``` 2. Enabling an existing NOT ENFORCED constraint Now consider this workflow: ``` evantest=# create table p (a int, constraint c check (a > 0) not enforced); CREATE TABLE evantest=# insert into p values (-1); INSERT 0 1 evantest=# alter table p alter constraint c enforced; ERROR: check constraint "c" of relation "p" is violated by some row ``` The user creates a table with a NOT ENFORCED constraint and subsequently inserts data that violates it. Later, the user may want to enforce the constraint on new inserts and updates while retaining the existing violations, perhaps to clean them up gradually. However, enabling the constraint also validates the existing rows, so the command fails. There is currently no direct way to change this existing constraint from unenforced and unvalidated to enforced but unvalidated. The user would have to drop it and add an equivalent constraint with ENFORCED NOT VALID. This seems like a gap compared with the first workflow: PG supports the desired state when adding a constraint, but does not provide a direct transition to that state when enabling an existing constraint. 3. NOT VALID in CREATE TABLE For a CHECK constraint on a newly created table, the following declarations produce these catalog states: ``` evantest=# create table p (a int, constraint c check (a > 0) enforced not valid); CREATE TABLE evantest=# select conrelid::regclass, conenforced, convalidated from pg_constraint where conname='c'; conrelid | conenforced | convalidated ----------+-------------+-------------- p | t | t (1 row) evantest=# create table p (a int, constraint c check (a > 0) not enforced); CREATE TABLE evantest=# select conrelid::regclass, conenforced, convalidated from pg_constraint where conname='c'; conrelid | conenforced | convalidated ----------+-------------+-------------- p | f | f (1 row) evantest=# create table p (a int, constraint c check (a > 0) not enforced not valid); CREATE TABLE evantest=# select conrelid::regclass, conenforced, convalidated from pg_constraint where conname='c'; conrelid | conenforced | convalidated ----------+-------------+-------------- p | f | f (1 row) ``` Marking an enforced constraint valid immediately is reasonable because the table is initially empty. However, NOT ENFORCED and NOT ENFORCED NOT VALID currently have the same effect. CREATE TABLE accepts NOT VALID even though its documentation doesn't list that option, which I found confusing when testing this. # What I would propose The idea is to preserve the validation mode while enforcement is disabled, so that disabling and re-enabling a constraint does not discard that choice. 1. Define convalidated as a saved validation mode when conenforced is false * If convalidated is false, when enforce the constraint, no validation will be performed; and convalidated remains false * If convalidated is true, when enforce the constraint, validation will be performed; and convalidated remains true In the second case, if validation fails, the command fails and the constraint remains NOT ENFORCED, with its saved validation mode unchanged: (false, true). Conversely, changing a constraint to NOT ENFORCED would preserve convalidated. This would allow an enforced but unvalidated constraint to be disabled and later re-enabled without validation, while a previously validated constraint would require validation when re-enabled. A constraint would be considered enforced and validated only when both flags are true. 2. Preserve the requested mode when creating a NOT ENFORCED constraint * create table p (a int, constraint c check (a > 0) not enforced) -- conenforced=f convalidated=t * create table p (a int, constraint c check (a > 0) not enforced not valid) -- conenforced=f convalidated=f The same rules should apply to ALTER TABLE ... ADD CONSTRAINT. Neither form would validate existing rows while the constraint is NOT ENFORCED; the difference would determine what happens when enforcement is enabled later. For an enforced constraint on a newly created empty table, I agree that there is no need to retain NOT VALID. 3. Honor the saved mode when enabling enforcement With this proposal, a constraint created as NOT ENFORCED NOT VALID could be enabled without validating existing rows. We could also emit a notice to make the resulting state clear: ``` evantest=# CREATE TABLE p (a int, CONSTRAINT c CHECK (a > 0) NOT ENFORCED NOT VALID); CREATE TABLE evantest=# ALTER TABLE p ALTER CONSTRAINT c ENFORCED; NOTICE: constraint "c" of relation "p" remains NOT VALID DETAIL: Enforcement has been enabled without validating existing rows. HINT: Use ALTER TABLE p VALIDATE CONSTRAINT c to validate existing rows. ALTER TABLE ``` The constraint would then have conenforced = true and convalidated = false. Subsequent inserts and updates would be checked, while the existing violating row could remain. After addressing the existing violations, the user could explicitly run: ``` ALTER TABLE p VALIDATE CONSTRAINT c; ``` Successful validation would set convalidated to true, which is the current behavior. Plain NOT ENFORCED would continue to require validation when enabled. Therefore, the failing example in the current-behavior section would still fail; the workflow above would be available when the user had explicitly selected NOT ENFORCED NOT VALID. # Previous discussions I found an earlier thread discussing related ideas: * Peter Eisentraut suggested a similar interpretation in [2]: the validation flag could record the behavior to restore when enforcement is enabled again. * Álvaro Herrera proposed ALTER TABLE ... ALTER CONSTRAINT ... ENFORCED NOT VALID in [3], making the transition to enforced but unvalidated explicit. * Ashutosh Bapat described similar rules for preserving the validation mode across enforcement changes in [4]. I think Álvaro’s proposed syntax could coexist with this approach as an explicit override of the saved mode. For example, it could allow a constraint with (conenforced, convalidated) = (false, true) to become (true, false) when the user now wants to enable enforcement without validation. I also see that the later discussion settled in [5]. I would like to revisit whether preserving the validation choice across disabling and re-enabling enforcement would be useful enough to justify the additional state. I have CC’d participants from the previous discussion. Before preparing an implementation patch, I would like to hear what folks think about this approach. [1] https://postgr.es/m/CAGRkXqSGE=fQrjCoNQsyiCZRKfQ4SrB015CAR=3pXYB5tB=i...@mail.gmail.com [2] https://www.postgresql.org/message-id/cd3e30c3-9763-425f-965a-5817ba91da72%40eisentraut.org [3] https://www.postgresql.org/message-id/202502131157.4oqj3eha7yum%40alvherre.pgsql [4] https://www.postgresql.org/message-id/CAExHW5tqoQvkGbYJHQUz0ytVqT7JyT7MSq0xuc4-qSQaNPfRBQ%40mail.gmail.com [5] https://www.postgresql.org/message-id/4419cb73-99a1-4bc5-bbb4-8695cb276b6f%40eisentraut.org Best regards, -- Chao Li (Evan) HighGo Software Co., Ltd. https://www.highgo.com/
