Как добавлять, изменять и удалять строки

Команды INSERT, UPDATE, DELETE и MERGE изменяют данные. Эту часть SQL называют DML — языком управления данными (Data Manipulation Language).

Перед изменением нужно понимать три вещи: какие строки попадут под команду, какое правило защитит одновременные запросы и какой результат получит приложение.

#INSERT и RETURNING

INSERT добавляет строки:

INSERT INTO orders (org_id, user_id, status)
VALUES ($1, $2, 'pending')
RETURNING id, status, created_at;

$1 и $2 — параметры. Драйвер базы передаст их отдельно от текста SQL, что защищает запрос от внедрения чужого кода.

RETURNING сразу возвращает значения фактически созданной строки, включая автоматический id и время. Отдельный SELECT потребовал бы ещё одного обращения к серверу и мог бы увидеть уже изменённое состояние.

При вставке нескольких строк нельзя без дополнительного признака связывать ответы с исходным списком только по позиции: SQL не обещает нужный прикладной порядок.

#Повтор запроса без повторного эффекта

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

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

CREATE TABLE webhook_inbox (
    org_id bigint NOT NULL,
    external_id text NOT NULL,
    payload jsonb NOT NULL,
    attempts integer NOT NULL DEFAULT 1,
    received_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    PRIMARY KEY (org_id, external_id)
);

INSERT INTO webhook_inbox (org_id, external_id, payload)
VALUES ($1, $2, $3)
ON CONFLICT (org_id, external_id) DO UPDATE
SET attempts = webhook_inbox.attempts + 1,
    payload = EXCLUDED.payload
RETURNING org_id, external_id, attempts;

ON CONFLICT описывает действие при нарушении указанной уникальности. Такую вставку с обработкой конфликта часто называют UPSERT. EXCLUDED.payload — значение из строки, которую пытались вставить.

Нужно заранее решить, что делать, если тот же external_id пришёл с другим содержимым: отклонить, сохранить первый вариант или вести историю. Молчаливое перезаписывание — тоже решение, но оно должно быть осознанным.

#Проверка и изменение одной командой

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

Поместим проверку в саму команду:

UPDATE inventory
SET reserved = reserved + $2,
    updated_at = clock_timestamp()
WHERE product_id = $1
  AND available - reserved >= $2
RETURNING available - reserved AS remaining;

Проверка остатка и изменение происходят как одна операция. Если подходящих строк нет, RETURNING вернёт пустой результат. Приложение отдельно решает, означает ли это отсутствие товара или нехватку остатка.

#MERGE: несколько вариантов изменения

MERGE сопоставляет входные строки с целевой таблицей и выбирает действие для совпавших и новых строк:

MERGE INTO inventory AS target
USING incoming_inventory AS source
ON target.product_id = source.product_id
WHEN MATCHED AND source.observed_at > target.observed_at THEN
    UPDATE SET available = source.available,
               observed_at = source.observed_at
WHEN NOT MATCHED THEN
    INSERT (product_id, available, observed_at)
    VALUES (source.product_id, source.available, source.observed_at);

source — входные данные, target — изменяемая таблица. Каждая входная строка должна однозначно определять целевую. Если несколько входных строк пытаются изменить один товар, сначала нужно выбрать одну из них по понятному правилу.

MERGE не заменяет уникальные ограничения, блокировки и повтор транзакции при конфликте.

#Загрузка файла через промежуточную таблицу

COPY быстро загружает много строк. Команда \copy выполняется клиентом psql и читает файл с компьютера клиента. Обычный COPY FROM читает файл от имени сервера PostgreSQL и требует соответствующих прав.

Непроверенный файл лучше сначала загрузить во временную приёмную таблицу (staging table):

CREATE UNLOGGED TABLE import_orders_stage (
    source_line bigint,
    org_slug text,
    external_id text,
    amount_text text,
    occurred_at_text text
);

\copy import_orders_stage FROM 'orders.csv' WITH (FORMAT csv, HEADER true)

Все спорные поля сначала хранятся как текст. Затем можно найти неверные строки и сохранить их отдельно вместе с причиной отказа:

SELECT source_line, amount_text
FROM import_orders_stage
WHERE amount_text !~ '^[0-9]+([.][0-9]{1,2})?$';

Такое отдельное хранилище ошибок называют карантином. Только проверенные строки преобразуют к нужным типам и переносят в основную таблицу.

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

#Неоднозначность UPDATE ... FROM

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

WITH source AS (
    SELECT DISTINCT ON (external_id)
           external_id, status
    FROM incoming_statuses
    ORDER BY external_id, observed_at DESC, source_line DESC
)
UPDATE orders AS o
SET status = source.status
FROM source
WHERE source.external_id = o.external_id
RETURNING o.id, o.status;

Полный ORDER BY определяет победителя даже при одинаковом времени.

#Большие изменения порциями

Один UPDATE или DELETE на миллионы строк создаёт долгую транзакцию и много WAL. PostgreSQL не поддерживает прямой UPDATE ... LIMIT, поэтому сначала выбирают порцию по стабильному ключу:

WITH batch AS (
    SELECT id
    FROM events
    WHERE processed_at IS NULL
    ORDER BY id
    FOR UPDATE SKIP LOCKED
    LIMIT 1000
)
UPDATE events AS e
SET processed_at = clock_timestamp()
FROM batch
WHERE e.id = batch.id
RETURNING e.id;

FOR UPDATE блокирует выбранные строки для изменения. SKIP LOCKED пропускает строки, уже взятые другим обработчиком. Это удобно для очереди задач, но не для отчёта, который обязан видеть полный набор.

Между порциями фиксируют транзакцию и следят за задержкой запросов, WAL, отставанием реплики, местом на диске и числом старых версий строк.

#Практика

В лаборатории 08 вы дважды отправите одно событие и проверите, что в базе осталась одна текущая строка, а счётчик попыток увеличился. Затем повторите правило через MERGE.

Подробнее: INSERT, UPDATE, MERGE, COPY.