← назад к разделу

Чтение безопасно, изменение нет. UPDATE без WHERE перепишет всю таблицу, и волшебной кнопки «отменить» в базе не существует. Поэтому эта статья наполовину про синтаксис, наполовину про технику безопасности, и вторая половина важнее.

Одно предупреждение до начала. Песочница курса отдаёт соединение только для чтения, поэтому примеры из этой статьи кнопкой «Запустить» не проверить: это единственная статья цикла, которую отрабатывают на своей базе. Поднять её на минуту проще, чем кажется: docker run -e POSTGRES_PASSWORD=x -p 5432:5432 postgres:17, и любой клиент подключится к пустой базе, где ломать нечего.

Обязательно

Три команды

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

INSERT INTO customer (id, first_name, last_name, email, status, created_at)
VALUES ('cus-10', 'Grigory', 'Orlov', 'g.orlov@example.com', 'ACTIVE', now());

Перечисляете столбцы и значения к ним. Пропустить столбец можно только тогда, когда базе есть чем его заполнить: у него задано значение по умолчанию или он разрешает пустоту. У customer обязательны все шесть: забудете status или created_at, и база не примет строку с ошибкой «null value in column "status" violates not-null constraint». Здесь id задан явно, потому что в учебной базе он строковый; там, где ключ числовой с автоинкрементом, его не указывают, база выдаёт его сама.

UPDATE меняет существующие строки:

UPDATE orders SET status = 'PAID', paid_at = now() WHERE id = 'ord-12';

SET говорит, что менять, WHERE у каких строк. Чтобы увидеть, что именно изменилось, не нужен второй запрос: RETURNING возвращает затронутые строки прямо из команды изменения, и это работает у всех трёх:

UPDATE orders SET status = 'PAID', paid_at = now()
WHERE id = 'ord-12'
RETURNING id, status, paid_at;

DELETE удаляет строки:

DELETE FROM orders WHERE id = 'ord-23';

Заказ выбран не наугад. Попробуйте удалить ord-13, и база откажет: на него ссылается заказ-замена ord-15, а внешний ключ на то и заведён, чтобы ссылка не повисла в пустоте. Ошибка выглядит так: «violates foreign key constraint». Выходов несколько, удалить или переподвесить ссылающиеся строки, но начинается всё с того, чтобы выяснить, кто на эту строку смотрит.

Правило номер один: WHERE не опция

У UPDATE и DELETE есть общее свойство, из-за которого сломано больше данных, чем из-за всего остального вместе: без WHERE они применяются ко всем строкам таблицы. UPDATE orders SET status = 'PAID', и все заказы в базе «оплачены».

UPDATE … WHERE id = 'ord-12' проверка SELECT: 1 строка изменена 1 строка ответ базы: UPDATE 1 UPDATE … без WHERE проверки не было изменены все 23 строки ответ базы: UPDATE 23

Запрос без WHERE синтаксически верен, и база выполнит его не моргнув: все заказы станут оплаченными. Единственная защита это ритуал: тот же фильтр сначала прогоняют SELECT, а после изменения смотрят на число затронутых строк.

Отсюда ритуал, который стоит довести до автоматизма:

  1. Сначала SELECT с тем же WHERE: SELECT * FROM orders WHERE id = 'ord-12'; посмотрите, что именно попадёт под изменение. Одна строка? Та самая?
  2. Только теперь UPDATE или DELETE с тем же самым условием.
  3. После контрольный SELECT или RETURNING: изменилось то, что хотели.

И смотрите на ответ базы: она всегда сообщает, сколько строк затронуто. Ожидали UPDATE 1, увидели UPDATE 5000, и это последний момент, когда ещё можно нажать ROLLBACK.

Транзакции: страховка с кнопкой «отмена»

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

BEGIN;
UPDATE orders SET status = 'CANCELLED' WHERE id = 'ord-12';
SELECT id, status FROM orders WHERE id = 'ord-12';

Между BEGIN и COMMIT изменения видны только вам, остальные видят старые данные. Если проверка показала то, что хотели, фиксируете:

COMMIT;

Если нет, откатываете, и всё сделанное после BEGIN исчезает, как будто ничего не было:

ROLLBACK;

У открытой транзакции есть цена. «Остальные видят старые данные» это про тех, кто просто читает. А тот, кому нужны ровно те же строки на изменение, не увидит ничего: его запрос встанет и будет ждать вашего COMMIT или ROLLBACK. Пока транзакция висит, соседи стоят, поэтому её не оставляют открытой «на подумать» и не уходят с ней на обед.

Автокоммит: почему BEGIN иногда не спасает

Ритуал выше молча предполагает, что запрос не фиксируется, пока вы не сказали COMMIT. В большинстве клиентов это не так. DBeaver, pgAdmin и psql по умолчанию работают в режиме автокоммита: каждая команда это своя транзакция, которая фиксируется сразу после выполнения. UPDATE без WHERE в этом режиме необратим в ту же секунду, и никакой ROLLBACK ему уже не поможет.

Явный BEGIN автокоммит отключает до ближайшего COMMIT или ROLLBACK: в psql это работает всегда, в графических клиентах тоже, если клиент честно передаёт команды на сервер. Надёжнее переключить режим: в DBeaver это кнопка «Auto-commit» на панели соединения, в pgAdmin настройка «Autocommit» в редакторе запросов, в psql команда \set AUTOCOMMIT off. И проверить индикатор режима до первого изменяющего запроса, а не после.

Автокоммит в клиенте: DBeaver и pgAdmin

Ритуал «BEGIN, проверил, ROLLBACK» из этой статьи не работает в клиенте, где включён автокоммит: клиент отправляет каждую команду отдельной транзакцией, и UPDATE зафиксирован раньше, чем вы успели посмотреть на результат. В DBeaver режим переключают кнопкой на панели (Auto или Manual commit) или в настройках соединения, и после переключения ROLLBACK возвращает то, что было; в pgAdmin автокоммит выключают в настройках запросного окна. В psql умолчание тоже автокоммит, и там спасает явный BEGIN: пока транзакция открыта, каждая следующая команда входит в неё. Проверить режим проще всего до боевой команды: BEGIN, безобидный SELECT, и клиент, у которого транзакция открыта, покажет это в статусе.

Массовая подготовка данных

Тестовые данные редко вставляют по одной строке. INSERT … SELECT берёт строки из запроса и кладёт их в таблицу, так размножают заказы для нагрузки или копируют покупателей в новый статус:

INSERT INTO orders (id, customer_id, seller_id, status, currency, total_amount, created_at)
SELECT 'copy-' || id, customer_id, seller_id, 'PENDING_PAYMENT', currency, total_amount, now()
FROM orders
WHERE status = 'COMPLETED';

Повторный запуск такого скрипта упрётся в уникальность ключа. Чтобы сценарий подготовки можно было гонять сколько угодно раз, конфликт описывают прямо в команде: INSERT … ON CONFLICT (id) DO NOTHING молча пропускает строки, которые уже есть, а DO UPDATE SET status = EXCLUDED.status обновляет их новыми значениями. Работает это только при уникальном ограничении на названных столбцах, иначе базе нечем распознать конфликт.

Очистить таблицу целиком быстрее всего TRUNCATE orders: он не проходит по строкам, а отбрасывает данные разом, поэтому на миллионе строк занимает миллисекунды там, где DELETE без WHERE работает минуты. Разница не только в скорости: у TRUNCATE нет WHERE, он не запускает построчные триггеры, а с RESTART IDENTITY заодно сбрасывает счётчики автоинкремента. Таблицу, на которую ссылаются внешние ключи, он не тронет без CASCADE, и это спасает от очистки заказов вместе с платежами по неосторожности. В PostgreSQL оба работают внутри транзакции и откатываются ROLLBACK.

Подготовка данных: INSERT … SELECT, RETURNING, TRUNCATE

Три приёма, ради которых эту статью обычно и открывают, готовя стенд.

INSERT ... SELECT заполняет таблицу результатом запроса вместо списка значений: скопировать заказы одного покупателя тестовому пользователю или собрать таблицу-витрину.

INSERT INTO orders (customer_id, status, total_amount)
SELECT 'cus-test', status, total_amount
FROM orders
WHERE customer_id = 'cus-01';

RETURNING возвращает строки, которые команда только что вставила, изменила или удалила: сгенерированный id после INSERT, чтобы не спрашивать его отдельным запросом, или список удалённых, чтобы записать в журнал.

INSERT INTO orders (customer_id, status) VALUES ('cus-01', 'DRAFT') RETURNING id;
DELETE FROM orders WHERE status = 'DRAFT' AND created_at < now() - interval '30 days' RETURNING id;

TRUNCATE orders очищает таблицу целиком за миллисекунды, не проверяя строки по одной, как DELETE без WHERE. Он мгновенный именно потому, что ничего не проверяет: внешние ключи из других таблиц не дадут его выполнить без CASCADE, а с CASCADE он очистит и их. На стенде это инструмент, на боевой базе почти всегда авария; DELETE хотя бы можно откатить порциями.

Когда меняют одновременно

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

прочитал → записал SELECT stock: 1 сосед тоже читает: 1 оба пишут stock = 0 продано две штуки условный UPDATE UPDATE … AND stock > 0 первый: UPDATE 1 второй: UPDATE 0 честный отказ

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

Лечится тем, что проверка и изменение становятся одним запросом. База выполняет UPDATE под блокировкой строки: второй запрос ждёт первого, а потом проверяет условие заново, уже по новому значению:

UPDATE products SET stock = stock - 1
WHERE id = 'prod-01' AND stock > 0
RETURNING stock;

Ответ базы и есть результат: UPDATE 1 значит, единица снята, UPDATE 0 значит, товара нет, и это честный отказ без дополнительных проверок. Тот же приём закрывает лимиты: слот доставки на двадцать заказов записывают через UPDATE slots SET taken = taken + 1 WHERE id = … AND taken < capacity, и двадцать пятый заказ получает ноль строк вместо места в переполненном слоте. Правило «один покупатель, один промокод» отдают уникальному ограничению на пару (code, customer_id) и INSERT … ON CONFLICT DO NOTHING: повторная попытка возвращает INSERT 0 0, и приложение узнаёт об отказе из счётчика строк, а не из отдельного запроса.

Когда изменить нужно несколько строк согласованно, а не одну, строку сначала захватывают: SELECT … FOR UPDATE блокирует её до конца транзакции, и все, кто хочет ту же строку, ждут. Держать такую блокировку надо коротко: длинная транзакция вокруг оформления заказа выстраивает очередь на ходовом товаре быстрее, чем та разбирается. Отдельный случай очереди заданий, где несколько копий сервиса разбирают одну таблицу: SELECT … FOR UPDATE SKIP LOCKED LIMIT 10 отдаёт каждому воркеру только свободные строки, пропуская захваченные соседями, и одно задание никогда не достаётся двоим. Так делают отложенную отмену неоплаченных заказов без брокера: задание и заказ ложатся одной транзакцией, а воркеры опрашивают таблицу раз в несколько секунд. Как блокировки и уровни изоляции устроены глубже, в статье про уровни изоляции.

Тестовый стенд и боевая база

Руками данные меняют на тестовом стенде. Там это нормальная часть работы: подготовить данные для проверки, воспроизвести редкое состояние, откатить сценарий назад. На боевой базе ручная правка это чрезвычайное событие, которое согласуют, оформляют и делают с оглядкой. Прежде чем выполнить изменяющий запрос, убедитесь, к какой базе вы подключены: вкладки в клиенте выглядят одинаково, а базы за ними разные, и проверка «куда я подключён» однажды спасёт карьеру.

Дополнительно: при первом чтении можно пропустить

Глубже: гонка за остатком: условный UPDATE и блокировка строкирасширенное

«Когда меняют одновременно» выше про то, что база не даст двум транзакциям записать одну строку разом: вторая ждёт первую. Но ждать мало. Классическая ошибка называется «прочитал, посчитал, записал»: приложение читает остаток товара, видит 1, и два таких приложения одновременно решают, что можно продать, и оба пишут 0. База обе записи пропустила, каждая по отдельности была законной.

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

UPDATE stock
SET quantity = quantity - 1
WHERE product_id = 'p-01' AND quantity > 0;

Условие в WHERE перепроверяется на уже заблокированной строке, поэтому из двух одновременных команд одна изменит строку, а вторая увидит quantity = 0 и не изменит ничего; приложение смотрит на число затронутых строк и понимает, что товар кончился. Тот же приём для слота доставки (WHERE booked < capacity) и промокода (WHERE uses < max_uses).

Второе лекарство, когда решение сложнее одного условия: прочитать строку с блокировкой, SELECT ... FOR UPDATE, посчитать в приложении и записать в той же транзакции. Пока транзакция открыта, вторая такая же команда ждёт на этой строке, а после коммита первой читает уже новое значение. Изолированность, о которой говорит статья про схему, защищает от чтения чужих незафиксированных изменений; от гонки «прочитал, посчитал, записал» она не защищает, это делают либо условный UPDATE, либо явная блокировка.

Коротко

  • UPDATE и DELETE без WHERE меняют всю таблицу; сначала тот же фильтр в SELECT, потом изменение, потом взгляд на число затронутых строк.
  • RETURNING показывает изменённые строки без второго запроса.
  • Транзакция это «всё или ничего»; до COMMIT изменений не видит никто, ROLLBACK возвращает как было, а открытая транзакция держит соседей.
  • Клиенты по умолчанию в автокоммите: без явного BEGIN или выключенного режима откатывать нечего.
  • INSERT … SELECT для массовой подготовки, ON CONFLICT для повторяемых скриптов, TRUNCATE для очистки целиком.
  • «Прочитал, посчитал, записал» проигрывает гонку; условный UPDATE проверяет и меняет одним запросом, и UPDATE 0 это честный отказ.
  • FOR UPDATE захватывает строку на время транзакции, SKIP LOCKED раздаёт очередь заданий нескольким воркерам без дублей.
  • В DBeaver и pgAdmin выключают автокоммит, иначе ROLLBACK возвращать нечего; в psql спасает явный BEGIN.
  • Стенд готовят через INSERT … SELECT, RETURNING отдаёт вставленные и удалённые строки, TRUNCATE чистит таблицу целиком и без отката порциями.
  • Остаток, слот и промокод меняют условным UPDATE … WHERE остаток > 0 или через SELECT … FOR UPDATE; чтение, расчёт и запись тремя командами это гонка.

Что почитать дальше