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.