PostgreSQL Add not null constraints to large tables

Projects are developing, the customer base is expanding, databases are expanding, and the moment comes when we begin to notice that once simple manipulations on databases require more complex actions, and the cost of an error goes up dramatically. It is no longer possible to play data from one column to another at a time, it is better to roll indexes asynchronously, defaultnow you can no longer add columns with values.

, , not null constraint . constraint PostgreSQL access exclusive lock , ; , null , . , not null constraint, .

TL;DR:

1. PostgreSQL 12+ check constraint , "" not null constraint .
2. , pg_attribute ( ).

PostgreSQL . 8.2.0 index concurrently, 9.1.0 foreign constraints not valid validate constraints, . not null constraint . -

... set not null not valid;

, constraint . constraint , , nulls:

... set not_null_but_dont_check_anything_because_i_checked_myself_i_swear;

, PostgreSQL, , . , , , . , , , , — ?

, , - not null.

1. Just do it!

— , , 24/7. , constraint , .

2. Just… don't do it?

, , not null constraint - . , , constraints — , . , not null, , , , : unique constraints, not null constraints, foreign key constraints, etc.

3.

foreign key, . not null constraint , , nulls .

, , , not null constraint, , workaround. , , .

Update: Melkij, PostgreSQL, , constraints . .. constraint .

4. check constraint

PostgreSQL not valid not null constraints, not valid check constraints. :

alter table MY_AWESOME_TABLE add constraint CHECK_MY_AWESOME_TABLE_ON_MY_SPLENDID_COLUMN check (MY_SPLENDID_COLUMN is not null) not valid;

alter table MY_AWESOME_TABLE validate constraint CHECK_MY_AWESOME_TABLE_ON_MY_SPLENDID_COLUMN;

: not null constraint ! validate share update exclusive lock, .

? -, . , nullable, — , , , . , . , , , , , , , .

-, , ORM. , , not null constraints, CUBA Studio, , Java . Studio not null constraints check constraints, JPA @NotNull, nullable, SQL update , . , , , , , .

not null constraints, check constraints . , check constraints not null constraints, PostgreSQL 12 . .

5. check constraint,

PostgreSQL 12 : nulls, constraint , nulls . DEBUG1, : "existing constraints on column MY_AWESOME_TABLE.MY_SPLENDID_COLUMN are sufficient to prove that it does not contain nulls".

Release notes , release notes , .

PostgreSQL, , not null constraint . check constraint , nulls, - — .

6.

not null constraint PostgreSQL? , . pg_attribute. , , : , , ( ), , .. boolean attnotnull, , nulls .

.

, . ( , foreign constraints, etc.), , , - : "alter table MY_AWESOME_TABLE add constraint …". . , SQL . , , not null constraint pg_attribute, , PostgreSQL side-effect.

not null constraint , . , not null . check constraint — workarounds, , PostgreSQL. workarounds , .

, :

-- alter table MY_AWESOME_TABLE alter column MY_SPLENDID_COLUMN set not null ;
do $$
    begin
        if (select exists (select 1 from MY_AWESOME_TABLE where MY_SPLENDID_COLUMN is null limit 1)) then
            raise exception 'MY_AWESOME_TABLE.MY_SPLENDID_COLUMN contains null values';
        else
            update PG_ATTRIBUTE set ATTNOTNULL = true
                where ATTRELID = (select OID  from PG_CLASS where RELNAME = lower('MY_AWESOME_TABLE'))
                    and ATTNAME = lower('MY_SPLENDID_COLUMN');
        end if;
    end $$;

select exists . not null constraint, 100%, , , null .

. , .

, , workaround PostgreSQL: "It is possible to change this column to enable or disable the constraint." 10.12 . — , , . : "docs: remove mention that attnotnull should be changed". - "because it's a bad practice", "because I was asked by Tom to do it", "because recent changes in the attribute cache made it unsafe to work with the table directly", "just because I decided so", . , , , , . , , , .

Update: , , attnotnull: https://www.postgresql.org/message-id/flat/20140724122945.GD16857%40alap3.anarazel.de.

, ? : constraint , - nulls; PostgreSQL , .

— . not null constraint , nulls. Select , . , constraint update/insert, . constraint , , — . .., , . , - .

, — , - , . , : . : , not null constraint; , attnotnull, , ; , / pg_attribute ( , , - ?).

. ATController. : PostgreSQL ; , permissions , ; pg_attribute; check constraint, , nulls, , , nulls (, full table scan ). , , .

, , attnotnull, , , not null nulls. , update insert, . (logical replication), . SQL ( pg_dump, ). , . “ update insert”. : attnotnull slot_compile_deform. : PostgreSQL JIT . ( ). attnotnull. true, , null, , . JIT PostgreSQL 11 . PostgreSQL 12 JIT .

, , attnotnull , . attnotnull = true , null, , .

. . CacheInvalidateHeapTuple, pg_class, pg_attribute, pg_index. , , .

, , not null constraint — attnotnull pg_attribute. ( ) — . , PostgreSQL , , ( JIT ).

, , (partitioning). , attnotnull, ATController PostgreSQL.

PostgreSQL 12+, check constraint , not null constraint . nulls .

, not null constraint , pg_attribute.