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.