Главы

  1. Введение в документы
  2. Базовые возможности JSON
  3. JSON в таблицах
  4. Индексирование JSON
  5. Ограничения в документах
  6. Язык путей JSONPath
  7. Отчеты и функции
  8. Функции на языке Python
  9. Версионирование и архивация документов
  10. Релевантный поиск

Содержание

В этой главе мы поговорим о том, как добавить документам ограничения: проверку поля предикатом, гарантию уникальности в рамках таблицы или ее части. Мы рассмотрим ссылочную целостность, познакомимся с вычисляемыми столбцами и другими возможностями Postgres.

В начале книги мы упоминали разницу между реляционными и документными базами данных. В первом случае данные хранятся в виде плоских таблиц; им нельзя придать произвольную форму. Документ, напротив, принимает любую структуру и вложенность в зависимости от ситуации.

Плоские таблицы сильны в отношениях между ними. Легко сделать так, чтобы одна таблица ссылалась на другую, соединить их как по горизонтали (оператор JOIN), так и вертикали (UNION). Таблице можно назначить ограничения и проверки, которые база выполняет за нас. Независимо от того, из какой платформы мы обращаемся к базе, она не позволит записать неверные данные.

Документы, напротив, слабы в связях и ограничениях. При работе с ними предполагается, что приложение само следит за целостностью данных. Например, перед тем как добавить документ А со ссылкой на В, мы проверяем, что последний существует.

Postgres – реляционная база данных, и многие ее ограничения можно применить к документам. Ниже мы рассмотрим основные случаи и их особенности.

Огриничение CHECK

Первый вид ограничений называется CHECK – простая проверка. В ней указывают выражение, и если при вставке или изменении записи оно ложно, операция отменяется.

Добавим проверку на то, что номер заявки не пуст. Для этого сперва удалим индекс на поле application_id, который мы создали в прошлой главе:

drop index if exists idx_applications_application_id;

Код ниже создаст ограничение с типом CHECK:

alter table applications add constraint ctr_doc_application_id_nn
check (doc->'application_id' is not null);

За выражением ...add constraint следует машинное имя ограничения. Убедитесь, что имя хотя бы примерно сообщает о том, что и как оно проверяет. В нашем случае doc и application_id указывают на колонку и поле документа, а часть nn означает not null. За именем следует тип ограничения (CHECK), а дальше – код, специфичный для конкретного типа. По ходу главы мы рассмотрим и другие виды ограничений.

Убедимся, что не получится добавить документ без номера:

insert into applications (doc) values (
  '{"test": "abc"}'
);

-- ERROR:  new row for relation "applications" violates check constraint "ctr_doc_application_id_nn"
-- DETAIL:  Failing row contains (b6edce9d-285f-4df0-9bba-5f65d390608a, {"test": "abc"}, 2026-06-05 17:49:33.757553+03, null).

Подчеркнем, что проверка не зависит от языка или платформы, из которой мы обращаемся. Она задана на уровне базы и поэтому сработает всегда.

Улучшим нашу проверку так, чтобы она проверяла, что в поле application_id записано именно число. Однако в прошлых экспериментах мы добавили документы, где поле application_id не было числом. Чтобы удалить их, выполните запрос с выражением на языке JSON Path (мы изучим его в следующей главе):

delete from applications
where doc @@ '$.application_id.type() != "number" ';

Удалите прежнее ограничение:

alter table applications
drop constraint ctr_doc_application_id_nn;

и добавьте новое:

alter table applications add constraint ctr_doc_application_id_int
check ((doc->>'application_id')::int is not null);

Теперь при попытке записать документ с неверным номером получим ошибку:

insert into applications (doc) values (
  '{"application_id": "abc"}'
);

-- ERROR: cannot cast jsonb string to type integer

Вероятно, вам захочется добавить как можно больше проверок CHECK, чтобы защититься от ошибок. Увы, это сомнительная практика. Множественные проверки замедляют вставку и изменение данных, поэтому за безопасность вы платите быстродействием. В документе могут быть сотни полей, в том числе опциональных, и проверка их всех ни к чему хорошему не приведет.

Более удачное решение – проверять документ в приложении до записи в базу. Обычно это делают при помощи JSON-схемы. Библиотеки для работы со схемами есть для всех популярных языков. Храните схемы в отдельном репозитории, чтобы ими могли пользоваться клиенты на Python, Java и других языках. Приведем фрагмент схемы нашей заявки:

{
  "type": "object",
  "properties": {
    "id": {"type": "string", "format": "uuid"},
    "status": {"enum": ["active", "deleted", "pending"]},
    "created_at": {"type": "string", "format": "date-time"},
    "application_id": {"type": "integer"},
    "organization": {...},
    ...
  },
  "required": ["id", "status", "created_at", "..."]
}

Резюмируюя, можно сказать, что ограничения CHECK малополезны для документов. Если можно вынести проверку в схему, так и следует поступить. В редких случаях CHECK может опираться на глобальные настройки или параметры сеанса. Однако такие примеры не будем рассматривать ввиду их надуманности.

Ограничение UNIQUE и уникальный индекс

Гораздо интересней ограничения, которые нельзя вынести в JSON-схему. Это может быть проверка поля на уникальность: гарантия, что в таблице нет другой заявки с таким номером. В этом случае JSON-схема не поможет: ей доступен только документ, но не содержимое базы. Технически возможно написать валидатор схемы, который обращается к базе в поисках дубликата, но такое решение грубо и неуклюже.

При создании таблицы можно задать поле или их комбинацию уникальными. Первый случай – одно уникальное поле – подходит для различных кодов, артикулов, адресов электронной почты и других атрибутов, которые однозначно определяют сущность. В таблице ниже поле email задано уникальным, чтобы не допустить двух пользователей с одной почтой (проблему регистра и мусорных символов мы не рассматриваем).

create table users_demo(
    id integer primary key,
    full_name text not null,
    email text not null,
    unique(email)
);

Уникальная комбинация полезна в таблицах-мостах; такие таблицы связывают другие сущности, например публикацию и тег. Чтобы не допустить случая, когда у статьи два одинаковых тега, комбинацию post_id и tag_id объявляют уникальной:

create table post_tags(
    post_id integer not null,
    tag_id integer not null,
    created_at timestamptz not null default current_timestamp,
    unique (post_id, tag_id)
);

Если вы забыли часть unique в объявлении таблицы, легко добавить ограничение командой:

alter table post_tags
add constraint ctx_post_id_tag_id_u
unique (post_id, tag_id);

Если мы выполним следующий запрос дважды:

insert into post_tags (post_id, tag_id)
values (100, 10);

то во второй раз получим ошибку уникальности:

ERROR:  duplicate key value violates unique constraint "ctx_post_id_tag_id_u"
DETAIL:  Key (post_id, tag_id)=(100, 10) already exists.

Особенность ограничения UNIQUE в том, что оно принимает столбцы, а не произвольные выражения. Не получится создать уникальное ограничение на поле документа. С точки зрения Postgres это неверный синтаксис:

alter table applications
add constraint ctx_doc_application_id_u
unique (((doc->>'application_id')::int));

-- ERROR:  syntax error at or near "("
-- LINE 3: unique (((doc->>'application_id')::int));

И все же есть способ добиться желаемого. Postgres предлагает индекс btree с гарантией уникальности. Он проверяет, что значение встречается в индексе не больше одного раза, а иначе получим ошибку. Поскольку это индекс, в выражении может быть не только столбец, но и сложная конструкция.

Удалите из таблицы applications ограничения, что мы создали ранее. Посмотреть их имена можно командой \d+ applications:

Indexes:
    "applications_pkey" PRIMARY KEY, btree (id)
    ...
Check constraints:
    "ctr_doc_application_id_int" CHECK (((doc ->> 'application_id'::text)::integer) IS NOT NULL)

Затем создайте уникальный индекс командой:

create unique index idx_doc_application_id_u
on applications (((doc->>'application_id')::int));

Свойство unique поддерживает только индекс btree. Применить его к индексам GIN или GIST не получится.

В прошлой главе мы уже создали индекс btree для поля application_id под названием idx_applications_application_id. С новым уникальным индексом в нем нет нужды, поэтому удалите его командой drop index.

Убедимся, что база воспротивится документу с номером, который уже есть в таблице:

insert into applications (doc) values (
  '{"application_id": "12345"}'
);

-- ERROR:  duplicate key value violates unique constraint "idx_doc_application_id_u"
-- DETAIL:  Key (((doc ->> 'application_id'::text)::integer))=(12345) already exists.

Что произойдет, если в документе вообще нет номера? Оказывается, их можно добавить сколько угодно:

insert into applications (doc) values (
  '{"test": "abc"}'
);

-- INSERT 0 1

Почему Postgres пропускает документы без номера? Дело в том, что когда поля application_id нет, выражение doc ->> 'application_id' возвращает NULL. Уникальный индекс устроен так, что каждый NULL не равен другому NULL. Это общая идиома Postgres: NULL считается неизвестностью, а неизвестности нельзя сравнивать.

На поведение NULL можно повлиять, указав опцию индекса nulls not distinct. Удалите прежний индекс и создайте новый:

drop index idx_doc_application_id_u;

create unique index idx_doc_application_id_u
on applications (((doc->>'application_id')::int))
nulls not distinct;

Теперь индекс трактует значения NULL как равные. Получится добавить только один документ без номера, но не больше:

insert into applications (doc) values (
  '{"test": "abc"}'
);

-- OK

insert into applications (doc) values (
  '{"test": "abc"}'
);

-- ERROR:  duplicate key value violates unique constraint "idx_doc_application_id_u"
-- DETAIL:  Key (((doc->>'application_id'::text)::integer))=(null) already exists.

Подчеркнем: проверку заполненности полей лучше поручить приложению. Уникальный индекс должен делать только ту работу, которая не под силу схеме: проверять данные на уровне базы.

Выше мы сделали номер уникальным глобально, то есть в рамках всей таблицы. Иногда уникальность требуется в рамках периода. Скажем, бухгалтерия обнуляет счетчик заявок каждый год. Номер 12345 может встречаться в заявках за 2025 и 2024 годы, но не одновременно в том или другом.

Чтобы построить подобный индекс, сперва удалим прошлый:

drop index idx_doc_application_id_u;

Подготовим неизменяемую функцию created_at_year. Она принимает документ и возвращает дату из поля created_at, срезанную на начало года:

create or replace function created_at_year(doc jsonb)
returns timestamptz
language sql immutable strict parallel safe
return date_trunc('year', (doc->>'created_at')::timestamp);

Объявим индекс, уникальный по двум критериям:

create unique index idx_doc_app_id_date_u
on applications (
    ((doc->>'application_id')::int),
    (created_at_year(doc))
);

Убедимся, что он работает: добавим две заявки с одинаковыми номерами за разные годы:

insert into applications (doc) values (
  '{"application_id": "10001111", "created_at": "2026-06-05T15:33:55Z"}'
);

insert into applications (doc) values (
  '{"application_id": "10001111", "created_at": "2027-06-05T15:33:55Z"}'
);

Обе вставки сработали без ошибок. Но если повторить один из запросов, операция не пройдет:

insert into applications (doc) values (
  '{"application_id": "10001111", "created_at": "2026-12-05T15:33:55Z"}'
);

-- ERROR:  duplicate key value violates unique constraint "idx_doc_app_id_date_u"
-- DETAIL:  Key (((doc ->> 'application_id'::text)::integer), get_year(doc))=(10001111, 2026-01-01 00:00:00+03) already exists.

Напомним: даже если индекс составной, можно пользоватся его лидирующими элементами, в нашем случае – критерием ((doc->>'application_id')::int). Это значит, что быстрый поиск по номеру по-прежнему в нашем распоряжении. Возможно, вам понадобится другой доступ: сначала за указанный год, а затем по номеру. В этом случае переставьте компоненты индекса местами.

Уникальный индекс дает еще одну возможность: можно указать его в выражении ON CONFLICT при вставке документа. Напишем запрос UPSERT, который реагирует именно на конфликт номера и года, а не другие ограничения:

insert into applications (doc) values (
  '{"application_id": "10001111", "created_at": "2026-12-05T15:33:55Z"}'
) on conflict (
    ((doc->>'application_id')::int),
    (created_at_year(doc))
)
do nothing;

-- INSERT 0 0

Что именно поместить после DO – зависит от конкретной задачи. Выше мы проигнорировали операцию, но возможно, вам потребуется обновить какие-то поля документа.

Обратите внимание, что мы указали выражение индекса, а не его имя. Форма ON CONFLICT ожидает от нас либо имя уникального столбца, либо выражение, по которому построен уникальный индекс. В результате запрос выглядит громоздко. Если вы строете запросы при помощи ORM, поместите выражение индекса в константу.

Создайте уникальный индекс для другого поля или их комбинации. Напишите UPSERT с разными действиями в случае конфликта.

Ограничение FOREIGN KEY (ссылки)

Рассмотрим еще одно ограничение Postgres для внешний ключей (ссылок) между таблицами. Проблема, которую мы пытаемся решить, звучит так: если первый документ ссылается на второй, то второй обязательно существует. Из этого следует, что нельзя добавить документ, который ссылается на то, чего нет в базе. Вдобавок нельзя удалить документ, на который кто-то ссылается.

Вместе эти ограничения называются целостностью данных. Целостность уберегает базу от многих ошибок; ее отключают только в особых случаях, всегда осознанно и принимая риски.

Как правило, документные базы слабы в плане целостности ссылок. Предполагается, что эту работу выполняет приложение: перед вставкой документа делается запрос, чтобы убедиться, что документы по ссылкам существуют. В распределенных системах целостность не проверяется намеренно. Время от времени запускают скрипт, который находит ссылки, ведущие в никуда, и как-то их исправляет.

Поскольку мы храним документы в Postgres, нам доступны ссылочные ограничения. Ниже мы покажем техническую возможность, а пользоваться ей или нет – остается на ваше усмотрение.

Ссылка между таблицами подразумевает, что по крайней мере таблиц две. На текущий момент у нас только один вид документа – заявка. Чтобы это исправить, заведем второй тип документа – организацию, на которую ссылаются заявки.

Добавим организации за несколько шагов. Первый – подготовим для них таблицу. Ее структура в точности повторяет таблицу заявок, отличается только название:

create table organizations (
    id uuid primary key default uuid_generate_v4(),
    doc jsonb compression lz4 not null,
    created_at timestamptz not null default current_timestamp,
    updated_at timestamptz
);

Второй шаг – добавим в таблицу организации. Очевидно, их первичные ключи должны быть взяты из заявок, а не сгенерированы случайно. Прежде всего удалим мусорные заявки, которые мы создали во время прошлых экспериментов. Для этого выполним DELETE с условием, что поле organization→id пустое:

delete from applications
where (doc #>> '{organization,id}') is null;

Выберем из заявок уникальные коды организаций:

select distinct doc #>> '{organization,id}' as org_id
from applications
limit 100;

┌──────────────────────────────────────┐
│                org_id                │
├──────────────────────────────────────┤
│ 00000000-0000-0000-0000-000000000000 │
│ 00000000-0000-0000-0000-000000000001 │
│ 00000000-0000-0000-0000-000000000002 │
│ 00000000-0000-0000-0000-000000000003 │
│ 00000000-0000-0000-0000-000000000004 │
│ 00000000-0000-0000-0000-000000000005 │
│ 00000000-0000-0000-0000-000000000006 │
│ 00000000-0000-0000-0000-000000000007 │
│ 00000000-0000-0000-0000-000000000008 │
│ 00000000-0000-0000-0000-000000000009 │
│ 00000000-0000-0000-0000-000000000010 │
│ 00000000-0000-0000-0000-000000000011 │
│ 00000000-0000-0000-0000-000000000012 │

Доработаем запрос, чтобы вместе с кодом организации формировался документ с минимальным набором полей:

select distinct on (doc #>> '{organization,id}')
    (doc #>> '{organization,id}')::uuid as id,
    jsonb_build_object(
        'id',         (doc #>> '{organization,id}'),
        'status',     'active',
        'code',       (doc #>> '{organization,code}'),
        'short_name', (doc #>> '{organization,short_name}')
    ) as doc
from
    applications
limit
    100;

Частичный результат:

┌──────────────────────────────────────┬───────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                  id                  │                                                        doc                                                        │
├──────────────────────────────────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ 00000000-0000-0000-0000-000000000000 │ {"id": "00000000-0000-0000-0000-000000000000", "code": "0", "status": "active", "short_name": "Organization 0"}   │
│ 00000000-0000-0000-0000-000000000001 │ {"id": "00000000-0000-0000-0000-000000000001", "code": "1", "status": "active", "short_name": "Organization 1"}   │
│ 00000000-0000-0000-0000-000000000002 │ {"id": "00000000-0000-0000-0000-000000000002", "code": "2", "status": "active", "short_name": "Organization 2"}   │
│ 00000000-0000-0000-0000-000000000003 │ {"id": "00000000-0000-0000-0000-000000000003", "code": "3", "status": "active", "short_name": "Organization 3"}   │
│ 00000000-0000-0000-0000-000000000004 │ {"id": "00000000-0000-0000-0000-000000000004", "code": "4", "status": "active", "short_name": "Organization 4"}   │
│ 00000000-0000-0000-0000-000000000005 │ {"id": "00000000-0000-0000-0000-000000000005", "code": "5", "status": "active", "short_name": "Organization 5"}   │
│ 00000000-0000-0000-0000-000000000006 │ {"id": "00000000-0000-0000-0000-000000000006", "code": "6", "status": "active", "short_name": "Organization 6"}   │
│ 00000000-0000-0000-0000-000000000007 │ {"id": "00000000-0000-0000-0000-000000000007", "code": "7", "status": "active", "short_name": "Organization 7"}   │
│ 00000000-0000-0000-0000-000000000008 │ {"id": "00000000-0000-0000-0000-000000000008", "code": "8", "status": "active", "short_name": "Organization 8"}   │
│ 00000000-0000-0000-0000-000000000009 │ {"id": "00000000-0000-0000-0000-000000000009", "code": "9", "status": "active", "short_name": "Organization 9"}   │
│ 00000000-0000-0000-0000-000000000010 │ {"id": "00000000-0000-0000-0000-000000000010", "code": "10", "status": "active", "short_name": "Organization 10"} │
│ 00000000-0000-0000-0000-000000000011 │ {"id": "00000000-0000-0000-0000-000000000011", "code": "11", "status": "active", "short_name": "Organization 11"} │
│ 00000000-0000-0000-0000-000000000012 │ {"id": "00000000-0000-0000-0000-000000000012", "code": "12", "status": "active", "short_name": "Organization 12"} │

Предварите запрос командой insert into organizations, удалите limit – и перенос организаций готов. Вот что получилось у автора:

insert into organizations (id, doc)
select distinct on (doc #>> '{organization,id}')
    (doc #>> '{organization,id}')::uuid as id,
    jsonb_build_object(
        'id',         (doc #>> '{organization,id}'),
        'status',     'active',
        'code',       (doc #>> '{organization,code}'),
        'short_name', (doc #>> '{organization,short_name}')
    ) as doc
from
    applications;

Проверьте число организаций – их будет тысяча:

select count(*) from organizations;

┌───────┐
│ count │
├───────┤
│  1000 │
└───────┘

Итак, заявки и организации согласованы: первые указывают на вторые. Это легко проверить оператором JOIN. Присоединим к заявкам организации и выберем несколько полей из каждого документа:

select
    app.id as app_id,
    org.doc->>'code' as org_code,
    org.doc->>'short_name' as org_name
from
    applications app
join
    organizations org
    on (app.doc #>> '{organization,id}')::uuid = org.id
limit
    100;

┌──────────────────────────────────────┬──────────┬──────────────────┐
│                app_id                │ org_code │     org_name     │
├──────────────────────────────────────┼──────────┼──────────────────┤
│ 00000000-0000-0000-0000-000000635585 │ 585      │ Organization 585 │
│ 00000000-0000-0000-0000-000000635586 │ 586      │ Organization 586 │
│ 00000000-0000-0000-0000-000000635587 │ 587      │ Organization 587 │
│ 00000000-0000-0000-0000-000000635588 │ 588      │ Organization 588 │
│ 00000000-0000-0000-0000-000000635589 │ 589      │ Organization 589 │
│ 00000000-0000-0000-0000-000000635590 │ 590      │ Organization 590 │

Обратите внимание, как легко соединить документы по горизонтали – не все документные базы предлагают такую возможность. В примере выше соединение JOIN сработает быстро: оно происходит по первичному ключу организаций, а первичный ключ всегда индексирован.

Мы добились того, что ссылки на организации верны (не указывают в никуда), однако сейчас это ничем не зафиксировано. Если добавить заявку или удалить организацию, нарушится целостность ссылок. Следующий шаг – сделать так, чтобы база не позволила этого сделать.

Postgres предлагает ограничение FOREIGN KEY – внешний ключ. Оно проверяет, что строка, на которую ссылается поле таблицы, действительно существует. Особенность внешних ключей в том, что они работают только с колонками, а не выражениями. Команда ниже вызовет ошибку синтаксиса, потому что после слов foreign key ожидается колонка или их комбинация, а не выражение:

alter table applications add constraint fk_org_id
foreign key ((doc #>> '{organization,id}')::uuid) references organizations(id);

-- ERROR:  syntax error at or near "("
-- LINE 2: foreign key ((doc #>> '{organization,id}')::uuid) references...

Как и в случае с уникальностью, есть способ добиться желаемого. C версии 13 Postgres поддерживает вычисляемые колонки. От обычных они отличаются тем, что хранят значения, рассчитанные из других колонок. При изменении вторых первые автоматически пересчитываются.

Приведем пример: в таблице demo две обычные колонки x и y, а третья sum – вычисляемая, которая складывает x и y. При вставке не нужно беспокоиться о поведении колонки sum – ее согласованность обеспечит Postgres:

create table demo(
    x integer not null default 0,
    y integer not null default 0,
    sum integer generated always as (x + y) stored
);

insert into demo values (1, 2), (3, 4)
returning x, y, sum;

┌───┬───┬─────┐
│ x │ y │ sum │
├───┼───┼─────┤
│ 1 │ 2 │   3 │
│ 3 │ 4 │   7 │
└───┴───┴─────┘

До версии 18 Postgres поддерживал только один вид вычисляемой колонки – хранимый. Выше это свойство задано словом stored. Хранимая колонка вычисляет значение и записывает на диск. При чтении поля sum не происходит обращения к x или y.

С версии 18 доступен второй тип – виртуальный, когда слово stored опущено. Такая колонка не хранит значение, а вычисляет при каждом обращении. Разница подходов очевидна: первый снижает затраты на вычисления, но потребляет диск. Второй не требует диска, но вынуждает Postgres вычислять поле.

В нашем случае мы выбрали первый вариант с хранением на диске. Мы исходим из предположения, что ссылка на организацию никогда не меняется – передача заявок между организациями не вписывается ни в какие процессы. Напротив, обращаться к этому полю мы будем часто, так что имеет смысл рассчитать его заранее. Также мы считаем, что дисковое хранение в целом дешевле, чем процессорное время. Наконец, хотелось бы, чтобы примеры из книги работали на многих версиях Postgres, а не только начиная с 18.

Независимо от типа – хранимая или виртуальная – вычисляемая колонка доступна только для чтения. Записать в нее данные невозможно.

Вычисляемые колонки предъявляют некоторые требования. Их выражение должно быть чистым – не иметь побочных эффектов (случайных чисел, текущей даты и так далее). Одна вычисляемая колонка не может зависеть от другой: в противном случае пришлось бы строить граф зависимостей, что весьма накладно. Есть и другие, более тонкие условия, которые в данный момент нас не интересуют.

Вычисляемые колонки доступны в индексах и ограничениях. Вынесем поле заявки ((doc #>> '{organization,id}')::uuid) в вычисляемую колонку и наложим на нее ограничение FOREIGN KEY. Первый шаг – подготовка поля:

alter table applications
add column _org_id uuid generated always
as ((doc #>> '{organization,id}')::uuid) stored;

Обратите внимание на два факта. Во-первых, поле начинается с подчеркивания, что говорит о его приватном характере. Во-вторых, выражение приводится к uuid, чтобы его тип совпадал с типом первичного ключа организаций. Приведение типа служит дополнительной проверкой: если добавить заявку с неправильной ссылкой, получим ошибку.

Выберем виртуальную колонку в запросе – она ничем не отличается от обычной:

select id, _org_id from applications limit 10;

┌──────────────────────────────────────┬──────────────────────────────────────┐
│                  id                  │               _org_id                │
├──────────────────────────────────────┼──────────────────────────────────────┤
│ 00000000-0000-0000-0000-000000635649 │ 00000000-0000-0000-0000-000000000649 │
│ 00000000-0000-0000-0000-000000635650 │ 00000000-0000-0000-0000-000000000650 │
│ 00000000-0000-0000-0000-000000635651 │ 00000000-0000-0000-0000-000000000651 │
│ 00000000-0000-0000-0000-000000635652 │ 00000000-0000-0000-0000-000000000652 │
│ 00000000-0000-0000-0000-000000635653 │ 00000000-0000-0000-0000-000000000653 │

Наложим ограничение, что колонка ссылается на организацию:

alter table applications add constraint fk_org_id
foreign key (_org_id) references organizations(id);

Поскольку мы подготовили данные правильно, ошибки не произошло. Теперь мы уверены, что целостность ссылок под защитой. Чтобы это проверить, добавим заявку с организацией, которой не существует:

insert into applications (doc) values ($$
  {
    "application_id": 1999111,
    "organization": {
      "id": "967849e5-9c59-4fa2-bc57-0a4a84bbd41c"
    }
  }
$$::jsonb);

-- ERROR:  insert or update on table "applications" violates foreign key constraint "fk_org_id"
-- DETAIL:  Key (_org_id)=(967849e5-9c59-4fa2-bc57-0a4a84bbd41c) is not present in table "organizations".

В обратную сторону: удалим организацию, на которую ссылается хотя бы одна заявка.

delete from organizations
where id = '00000000-0000-0000-0000-000000000649';

-- ERROR:  update or delete on table "organizations" violates foreign key constraint "fk_org_id" on table "applications"
-- DETAIL:  Key (id)=(00000000-0000-0000-0000-000000000649) is still referenced from table "applications".

В обоих случаях база не выполнила то, что нарушает ограничение.

В теле FOREIGN KEY можно указать реакцию на удаление. Выражение on delete cascade означает, что документы, которые ссылаются на удаляемый, будут тоже удалены. Чтобы проверить это в действии, удалим прошлое ограничение и наложим новое:

alter table applications add constraint fk_org_id
foreign key (_org_id) references organizations(id)
on delete cascade;

Если теперь удалить организацию, исчезнут заявки, которые на нее ссылались. Проверим это:

delete from organizations
where id = '00000000-0000-0000-0000-000000000649';

select id from applications
where (doc #>> '{organization,id}') = '00000000-0000-0000-0000-000000000649';
-- (0 rows)

Каскадное удаление слишком радикально для документов. Иногда на одну сущность ссылаются десятки других, и удаление затронет нужные документы. Маловероятно, каскадное удаление понадобится в боевом запуске, но знать о нем не помешает.

FOREIGN KEY предлагает другой вариант удаления: on delete set null. С ним удаляется только та запись, что мы указали, а ссылкам в других таблицах присваивается NULL. Вспомним, однако, что поле _org_id – вычисляемое, поэтому ему нельзя ничего присвоить. Postgres учитывает этот случай и не позволит создать ограничение:

alter table applications add constraint fk_org_id
foreign key (_org_id) references organizations(id)
on delete set null;

-- ERROR:  invalid ON DELETE action for foreign key constraint containing generated column

Можно решить проблему триггером. Перед удалением организации вызывается функция, которая находит заявки и удаляет в них поле organization. Таким образом DELETE сводится к UPDATE; колонка _org_id будет вычислена повторно и получит значение NULL. Подготовим функцию триггера:

create or replace function fn_applications_org_ref_null()
returns trigger as $$
begin
    update applications
    set doc['organization'] = null
    where (doc #>> '{organization,id}')::uuid = OLD.id;
    return OLD;
end;
$$ language plpgsql;

А вот и сам триггер: он запускает функцию перед удалением организации.

create trigger trg_organizations_before_delete
before delete on organizations
for each row execute function fn_applications_org_ref_null();

Удалим организацию:

delete from organizations
where id = '00000000-0000-0000-0000-000000000651';

Проверим заявку, которая раньше на нее ссылалась. Поля организации пусты, триггер сработал:

select id, _org_id, doc -> 'organization' as org
from applications
where id = '00000000-0000-0000-0000-000000635651';

┌──────────────────────────────────────┬─────────┬──────┐
│                  id                  │ _org_id │ org  │
├──────────────────────────────────────┼─────────┼──────┤
│ 00000000-0000-0000-0000-000000635651 │ <null>  │ null │
└──────────────────────────────────────┴─────────┴──────┘

Прочие сведения

Вычисляемые столбцы можно использовать и для других ограничений. Когда мы рассматривали уникальность, то столкнулись с похожей проблемой: выражение UNIQUE принимает столбец или их кортеж, но не выражение. Тогда мы выкрутились с помощью с уникального индекса, однако был и другой путь: создать вычисляемый столбец и назначить его уникальным. Предлагаем читателю проделать это самостоятельно.

Как и многие другие техники, вычисляемые колонки следует использовать осторожно. Не допускайте их большого количества. Не опирайтесь на них в запросах сверх меры, потому что в случае рефакторинга потребуется много исправлений. Каждая колонка должна быть обоснована тем или иным требованием.

То же самое относится к ограничениям CHECK, UNIQUE и FOREIGN KEY. В реляционной модели их преимущество бесспорно, однако в случае с документами это не всегда так. На долгой дистанции некоторые ограничения оказываются неудобны. Перед их внедрением убедитесь, что они действительно нужны – иными словами, такие требования диктует бизнес.

У вычисляемых колонок есть еще одно свойство: индекс по ним работает немного быстрее, чем по выражениям. Как вы помните, индекс btree сопоставляет значение с позицией строки, где оно находится. В редких случаях Postgres проверяет условие повторно, даже если строка найдена. Скажем, мы создали индекс на выражение (((doc #>> '{organization,id}')::uuid)) и выполнили запрос с условием по нему:

select ... where (((doc #>> '{organization,id}')::uuid)) = <UUID>

Чтобы выполнить условие повторно (это случается при нехватке памяти), Postgres читает колонку doc и берет и из нее поле organization→id. Напомним, что при определенном пороге значения jsonb сбрасываются в TOAST-таблицу, где они хранятся в виде фрагментов. Чтобы получить документ, требуется прочитать и соединить все фрагменты. Для больших документов это влечет дисковые операции и время процессора.

Если же индекс создан по полю _org_id, обращение к документу требуется. Тип uuid использует хранение PLAIN (без тостов и сжатия, см. главу 4), поэтому значение находится в основной таблице, а не TOAST. Проверка _org_id = <UUID> выполняется быстрее и потому предпочтительней.

Итак, мы рассмотрели ограничения документов на уровне базы данных: проверки CHECK, уникальные индексы, внешние ключи и вычисляемые столбцы. Что именно из этого применять – зависит от задачи и в целом остается на усмотрение читателя.

В следующей главе мы вернемся к теме, с которой временно переключились: как извлекать данные из JSON-документов, но на этот раз – более мощными средствами.

← Глава 4. Индексирование JSON