До сих пор мы в основном читали данные. Теперь учимся их менять. Эта группа команд в postgresql называется DML - data manipulation language. Звучит сухо, но именно тут живут самые болезненные ошибки: затёртые строки, забытый WHERE в UPDATE, гонки при вставке. В уроке разберём четыре рабочих команды (INSERT, UPDATE, DELETE и MERGE), фишку RETURNING, паттерн UPSERT через ON CONFLICT и поговорим, как не превратить изменение данных в потерю данных. База - PostgreSQL 15, по дороге отмечу, что подвезли в 16 и 17.
Как это работает
Любая команда изменения в PostgreSQL выполняется внутри транзакции. Если вы не открыли её явно через BEGIN, сервер сам обернёт одиночный запрос в неявную транзакцию: либо всё применилось, либо ничего. Это и есть та самая транзакционная безопасность, ради которой люди выбирают нормальную СУБД.
Важная особенность движка: PostgreSQL не переписывает строку на месте. При UPDATE он создаёт новую версию строки, а старую помечает как устаревшую. Эту модель называют MVCC. Отсюда два следствия. Первое - параллельные читатели не блокируют писателя и видят согласованный снимок данных. Второе - устаревшие версии (мёртвые кортежи) копятся, и их потом подчищает vacuum. То есть массовый UPDATE - это по сути массовая вставка плюс работа для автовакуума.
INSERT кладёт новые строки. UPDATE меняет существующие по условию WHERE. DELETE удаляет по WHERE. Команда без WHERE трогает всю таблицу - запомните это как мантру. UPSERT (INSERT ON CONFLICT) решает задачу вставь, а если такой ключ уже есть, то обнови. MERGE из PG15 - это декларативный способ за один проход развести строки на три сценария: совпало, не совпало в таблице, не совпало в источнике.
SQL и примеры
Работаем со схемой bookings демобазы Авиаперевозки. Начнём с простой вставки нового аэропорта. RETURNING возвращает то, что реально записалось - удобно для сгенерированных значений.
Код: Выделить всё
INSERT INTO airports_data (airport_code, airport_name, city, coordinates, timezone)
VALUES ('NSK', '{"en": "New Test"}', '{"en": "Test City"}',
point(82.6, 55.0), 'Asia/Novosibirsk')
RETURNING airport_code, city;
Код: Выделить всё
INSERT INTO flights_archive
SELECT * FROM flights
WHERE status = 'Arrived' AND actual_arrival < now() - interval '1 year';
Код: Выделить всё
UPDATE bookings
SET total_amount = total_amount * 1.05
WHERE book_date::date = DATE '2017-07-01';
Код: Выделить всё
UPDATE ticket_flights tf
SET amount = tf.amount
FROM flights f
WHERE tf.flight_id = f.flight_id
AND f.status = 'Cancelled';
Код: Выделить всё
DELETE FROM boarding_passes bp
USING ticket_flights tf, flights f
WHERE bp.ticket_no = tf.ticket_no
AND bp.flight_id = tf.flight_id
AND tf.flight_id = f.flight_id
AND f.status = 'Cancelled'
RETURNING bp.ticket_no, bp.flight_id;
Код: Выделить всё
INSERT INTO flight_load (flight_id, seats_taken)
VALUES (1234, 1)
ON CONFLICT (flight_id)
DO UPDATE SET seats_taken = flight_load.seats_taken + EXCLUDED.seats_taken;
Код: Выделить всё
INSERT INTO airports_data (airport_code, airport_name, city, coordinates, timezone)
VALUES ('SVO', '{"en": "x"}', '{"en": "x"}', point(0,0), 'UTC')
ON CONFLICT (airport_code) DO NOTHING;
Код: Выделить всё
MERGE INTO flight_load AS t
USING staging_load AS s
ON t.flight_id = s.flight_id
WHEN MATCHED THEN
UPDATE SET seats_taken = s.seats_taken
WHEN NOT MATCHED THEN
INSERT (flight_id, seats_taken) VALUES (s.flight_id, s.seats_taken);
Частые грабли
- UPDATE или DELETE без WHERE применяется ко всей таблице. Отрабатывает мгновенно, откатить можно только если вы внутри открытой транзакции и ещё не сделали COMMIT.
- Между UPSERT и MERGE есть разница в гонках. ON CONFLICT атомарен и безопасен при параллельных вставках. MERGE в PG15/16 атомарность ключа сам не гарантирует - при конкурентной нагрузке может упасть с unique violation, нужен уникальный индекс и иногда повтор.
- ON CONFLICT требует реального ограничения - первичного ключа или UNIQUE-индекса по указанным столбцам. Без него Postgres не знает, что считать конфликтом, и выдаст ошибку.
- EXCLUDED путают с именем таблицы. В DO UPDATE через имя таблицы доступна старая строка, через EXCLUDED - предложенная к вставке. Перепутаете - получите не ту арифметику.
- Массовый UPDATE раздувает таблицу мёртвыми версиями и нагружает vacuum. Большие правки лучше дробить на пакеты по диапазону ключа.
- Порядок строк при UPDATE не определён, поэтому два встречных UPDATE одних и тех же строк в разных транзакциях легко дают взаимоблокировку (deadlock). Обновляйте в согласованном порядке.
- RETURNING возвращает финальное состояние строки после срабатывания триггеров и DEFAULT - это и плюс, и сюрприз, если триггер переписал значение.
- Создайте таблицу: CREATE TABLE flight_load (flight_id int PRIMARY KEY, seats_taken int NOT NULL DEFAULT 0).
- Откройте транзакцию через BEGIN и выполните INSERT одной строки с RETURNING - посмотрите, что вернулось.
- Выполните тот же INSERT ещё раз, но с ON CONFLICT (flight_id) DO UPDATE SET seats_taken = flight_load.seats_taken + 1. Проверьте SELECT, что счётчик вырос.
- Сделайте UPDATE без WHERE, затем ROLLBACK и убедитесь через SELECT, что изменения откатились.
- Создайте staging_load с парой строк (одна с существующим flight_id, одна с новым) и запустите MERGE из примера.
- Удалите тестовую строку через DELETE ... RETURNING и зафиксируйте COMMIT.
- Запустите VACUUM (VERBOSE) flight_load и найдите в выводе число вычищенных мёртвых версий.
- Почему UPDATE в PostgreSQL порождает мёртвые версии строк и при чём тут vacuum?
- Чем псевдотаблица EXCLUDED отличается от имени целевой таблицы внутри ON CONFLICT DO UPDATE?
- В каких случаях UPSERT через ON CONFLICT безопаснее MERGE при параллельной нагрузке?
- Что вернёт RETURNING, если на таблице висит BEFORE-триггер, меняющий вставляемое значение?
- Зачем в DELETE используют USING, а в UPDATE - FROM, и что они дают?
- Какие возможности MERGE появились в PG17 по сравнению с PG15?
