Почему SELECT перед INSERT не защищает от дубликатов!

Рассмотрим пример на PostgreSQL: две транзакции одновременно пытаются создать пользователя с одинаковым email. Сначала создадим таблицу с ограничением UNIQUE:

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);

Перед добавлением пользователя приложение проверяет, существует ли такой email:

SELECT id
FROM users
WHERE email = 'admin@example.com';

Если запись не найдена, выполняется вставка:

INSERT INTO users (email)
VALUES ('admin@example.com');

При параллельных запросах обе транзакции могут не найти запись. Первая выполнит INSERT и COMMIT, а вторая получит ошибку нарушения UNIQUE. Это типичная race condition (check-then-act). Без ограничения UNIQUE возникли бы дубликаты.

В PostgreSQL такую ситуацию можно обработать через ON CONFLICT без предварительного SELECT:

INSERT INTO users (email)
VALUES ('admin@example.com')
ON CONFLICT (email) DO NOTHING
RETURNING id;

Если запись создана, RETURNING вернёт её id. При конфликте вставка пропускается, а результат будет пустым. Если существующую запись необходимо обновить, используется DO UPDATE:

INSERT INTO users (email)
VALUES ('admin@example.com')
ON CONFLICT (email)
DO UPDATE SET email = EXCLUDED.email
RETURNING id;

Но обновлять email тем же значением только ради получения id не стоит: операция UPDATE может создавать новую версию строки, запускать триггеры и увеличивать нагрузку.

Если нужен id существующей записи без обновления, после DO NOTHING можно выполнить отдельный SELECT, учитывая уровень изоляции и возможность повторной попытки.

🔥 SELECT перед INSERT не гарантирует уникальность при конкурентном доступе. Её обеспечивает ограничение UNIQUE, а ON CONFLICT позволяет корректно обрабатывать конфликты без предварительной проверки.

➡️ SQL Ready | #практика

Ищите данные внутри JSON без разворачивания массивов!

Когда нужно отфильтровать элементы массива внутри JSON, обычно используют jsonb_array_elements(), дополнительные соединения и условия WHERE.

Но PostgreSQL поддерживает JSONPath — язык запросов к JSON, который позволяет описать фильтрацию прямо внутри пути.

Например, получить названия товаров дороже 1000:

SELECT jsonb_path_query_array(
payload,
'$.items[*] ? (@.price > 1000).name'
)
FROM orders;

Здесь [*] перебирает элементы массива, ? (...) фильтрует их, а .name извлекает нужное поле. Результат возвращается одним JSON-массивом.

Можно использовать JSONPath и непосредственно в WHERE:

SELECT id
FROM orders
WHERE payload @? '$.items[*] ? (@.price > 1000)';

Оператор @? проверяет, существует ли элемент, соответствующий указанному пути. Для подходящих запросов по jsonb PostgreSQL также может использовать индекс GIN.

🔥 JSONPath позволяет фильтровать вложенные JSON-массивы прямо в выражении, без разворачивания элементов и дополнительных соединений.

➡️ SQL Ready | #совет

📂 Шпаргалка по AWS RDS — управление SQL-базами данных!

Amazon RDS — управляемый сервис AWS, который упрощает развёртывание, администрирование и масштабирование реляционных баз данных.

На картинке — 6 основных этапов работы с RDS: от создания и настройки БД до резервного копирования, мониторинга и обеспечения отказоустойчивости.

Шпаргалка поможет быстро вспомнить основные возможности сервиса и разобраться, какие задачи AWS автоматизирует. Сохрани, чтобы не потерять!

➡️ SQL Ready | #ресурс

✍️ PostgreSQL — подборка статей о работе с базами данных!

Статьи помогают разобраться в технических аспектах PostgreSQL и расширить знания о работе с реляционными базами данных. Ресурс будет полезен для изучения возможностей PostgreSQL, практических подходов к работе с данными и решения задач, возникающих при разработке приложений. Подойдёт backend-разработчикам, SQL-разработчикам и администраторам баз данных.

📌 Оставляю ссылочку: prototypes.ventures

➡️ SQL Ready | #сайт

Как избежать блокировки всей таблицы при добавлении UNIQUE в PostgreSQL!

Добавление ограничения UNIQUE на большую таблицу может стать проблемой на проде. ALTER TABLE ... ADD CONSTRAINT UNIQUE создаёт уникальный индекс и требует блокировки, которая на время операции препятствует конкурентным изменениям таблицы.

Рассмотрим таблицу с миллионами записей:

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    email TEXT NOT NULL
);

Необходимо гарантировать уникальность email, сохранив возможность выполнять INSERT, UPDATE и DELETE во время построения индекса.

В PostgreSQL для этого предусмотрен CREATE UNIQUE INDEX CONCURRENTLY:

CREATE UNIQUE INDEX CONCURRENTLY
    users_email_uidx
ON users (email);

В отличие от обычного создания индекса, CONCURRENTLY не блокирует конкурентные операции записи на протяжении построения. При этом операция выполняется в несколько этапов, использует несколько проходов по таблице и ожидает завершения определённых транзакций.

После успешного построения индекс можно привязать к ограничению:

ALTER TABLE users
ADD CONSTRAINT users_email_key
UNIQUE USING INDEX users_email_uidx;

postgresql использует существующий индекс, поэтому повторное построение не требуется. Однако ALTER TABLE всё равно запрашивает блокировку ACCESS EXCLUSIVE, пусть обычно и на короткое время.

Во-первых, CREATE INDEX CONCURRENTLY нельзя выполнять внутри явного блока транзакции:

BEGIN;

CREATE UNIQUE INDEX CONCURRENTLY
    users_email_uidx
ON users (email);

COMMIT;

Такой запрос завершится ошибкой.

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

DROP INDEX CONCURRENTLY users_email_uidx;

Особенно важно, что неудачно построенный уникальный индекс в некоторых случаях продолжает проверять уникальность новых значений, несмотря на статус INVALID.

Наконец, USING INDEX поддерживает не любой индекс: для ограничения UNIQUE нужен подходящий уникальный B-tree индекс без выражений и частичного предиката.

🔥 Для добавления UNIQUE на большую таблицу можно сначала построить индекс через CREATE UNIQUE INDEX CONCURRENTLY, а затем подключить его через ALTER TABLE ... USING INDEX. Это позволяет избежать длительной блокировки записи при построении индекса, но не исключает кратковременную блокировку при добавлении самого ограничения.

➡️ SQL Ready | #практика

😍 PostgreSQL — серия обучающих статей по работе с базами данных!

Русскоязычная подборка материалов, посвящённых изучению PostgreSQL и работе с реляционными базами данных. Статьи объединены в серию, что позволяет последовательно знакомиться с СУБД и изучать отдельные темы. Ресурс поможет разобраться в основах PostgreSQL, принципах работы с данными и использовании SQL. Такой формат удобен как для самостоятельного обучения, так и для повторения материала при решении практических задач.

📌 Оставляю ссылочку: urhoba.net

➡️ SQL Ready | #сайт