Get best of the week

PostgreSQL的 向大型表添加非空约束



项目正在发展，客户群正在扩展，数据库也在扩展，当我们开始注意到一旦对数据库进行简单的操作就需要更复杂的操作，并且错误的成本急剧上升的时刻到来了。不再可以一次将数据从一列播放到另一列，最好异步滚动索引，default现在您不再可以添加带有值的列。


, , 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.

More articles:


All Articles