PostgreSQL Antipatterns: накручиваем себе проблемы

Некоторые ситуации в работе PostgreSQL кажутся неочевидными, пока не попытаешься детально понять, «почему это работает так». Из-за незнания таких особенностей иногда разработчик сам провоцирует проблемы для нормальной работы своего приложения в будущем.

Сегодня разберем пару примеров, как неудачная организация БД и кода могут превратить наше приложение в клубок проблем:

• накрутка serial при ON CONFLICT
• накрутка счетчика транзакций

Накрутка serial при ON CONFLICT

Давайте представим, что нам понадобилась небольшая таблица-словарь на пару десятков тысяч записей — что-то вроде списка форматов ошибок PostgreSQL.

Наше приложение (или все-таки разработчики?) любит суррогатные ключи, поэтому сразу добавим в таблицу в качестве PRIMARY KEY автоинкремент-поле с типом serial. Точнее, smallserial — ведь мы точно знаем, что строк будет не больше 2¹⁵:

CREATE TABLE tbl(
  pk
    smallserial
      PRIMARY KEY
, val
    integer
      UNIQUE
);

Пытаться вставлять данные в него мы иногда будем, но новых среди них будет немного. Поэтому для удобства вставки, чтобы не заниматься обработкой исключений уникальности в своем коде, воспользуемся появившимся с версии 9.5 функционалом INSERT ... ON CONFLICT ...:

INSERT INTO tbl(val) VALUES(1) ON CONFLICT DO NOTHING;
-- 1 строка
INSERT INTO tbl(val) VALUES(1) ON CONFLICT DO NOTHING;
-- 0 строк, и никаких ошибок!

Теперь оставим наше приложение спокойно работать, и… Вот ровно с такой ошибкой к нам и прибегут через несколько дней или недель:

ERROR:  nextval: reached maximum value of sequence "tbl_pk_seq" (32767)

И потом начнут приходить все чаще и чаще. Но как появилось столько записей? Почему не сработал ON CONFLICT?…

Дело в том, что они и «не появились». Давайте еще раз с нуля посмотрим на происходящее в нашей таблице:

TRUNCATE TABLE tbl RESTART IDENTITY;

INSERT INTO tbl(val) VALUES(1) ON CONFLICT DO NOTHING RETURNING *;
-- 1 строка: pk = 1, val = 1
INSERT INTO tbl(val) VALUES(1) ON CONFLICT DO NOTHING RETURNING *;
-- 0 строк
INSERT INTO tbl(val) VALUES(2) ON CONFLICT DO NOTHING RETURNING *;
-- 1 строка: pk = 3, val = 2

Как так pk = 3, ведь вставилось всего 2 строки? Мы запутались…


На самом-то деле, все объясняется просто. Посмотрим внимательно, что из себя представляет наша таблица:

_tmp=# \d tbl
                             Table "public.tbl"
 Column |   Type   | Collation | Nullable |             Default
--------+----------+-----------+----------+---------------------------------
 pk     | smallint |           | not null | nextval('tbl_pk_seq'::regclass)
 val    | integer  |           |          |
Indexes:
    "tbl_pkey" PRIMARY KEY, btree (pk)
    "tbl_val_key" UNIQUE CONSTRAINT, btree (val)

Типы данных smallserial, serial и bigserial не являются настоящими типами, а представляют собой просто удобное средство для создания столбцов с уникальными идентификаторами (подобное свойству AUTO_INCREMENT в некоторых СУБД). В текущей реализации запись:

CREATE TABLE имя_таблицы (
    имя_столбца SERIAL
);

равнозначна следующим командам:

CREATE SEQUENCE имя_таблицы_имя_столбца_seq AS integer;
CREATE TABLE имя_таблицы (
    имя_столбца integer NOT NULL DEFAULT nextval('имя_таблицы_имя_столбца_seq')
);
ALTER SEQUENCE имя_таблицы_имя_столбца_seq OWNED BY имя_таблицы.имя_столбца;

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

То есть наш smallserial превратился в тыкву в поле smallint с DEFAULT-значением из последовательности tbl_pk_seq.

А последовательность — штука нетранзакционная:

Значение, выделенное из последовательности, считается «задействованным», даже если строку с этим значением не удалось вставить в таблицу. Это может произойти, например, при откате транзакции, добавляющей данные.

То есть мы сначала сгенерировали DEFAULT-значение, «использовали» значение pk = 2, а потом его не вставили в таблицу из-за конфликта уникальности val, скрыв проблему с помощью ON CONFLICT DO NOTHING. И после очередной такой попытки у нас просто «кончилась» последовательность.

Что делать?

• хорошо
  Стараться не использовать лишние суррогатные ключи в таблицах, где уникальный ключ и так уже есть.
• просто
  Сконвертировать поле и вместо smallserial использовать serial или bigserial — это позволит продлить агонию приложения на месяцы или даже годы.
• разумно
  Не использовать serial и ON CONFLICT на таблицах с ожидаемо существенным количеством конфликтующих вставок.
• странно
  Написать триггер INSTEAD OF для аналогичного по структуре VIEW (или можно хранимую процедуру, но мы ведь не ищем легких путей).

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

CREATE TABLE tbl(
  pk
    smallserial
      PRIMARY KEY
, val
    integer
      UNIQUE
);
-- отвязываем DEFAULT
ALTER TABLE tbl ALTER COLUMN pk DROP DEFAULT;
-- создаем "промежуточное" VIEW
CREATE VIEW _tbl AS TABLE tbl;

CREATE OR REPLACE FUNCTION tbl_serial() RETURNS trigger AS $$
BEGIN
  IF NEW.pk IS NULL THEN
    LOOP -- эмуляция UPSERT через цикл
      PERFORM 1 FROM tbl WHERE val = NEW.val;
      EXIT WHEN FOUND; -- выходим при наличии такого значения в словаре
      BEGIN
        NEW.pk = nextval(pg_get_serial_sequence('tbl', 'pk'));
        INSERT INTO tbl VALUES(NEW.*);
        RETURN NEW;
      EXCEPTION
        WHEN unique_violation THEN -- защита от конкурентной вставки
      END;
    END LOOP;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;
-- триггер INSTEAD OF выполняется "вместо" заказанной операции над VIEW
CREATE TRIGGER serial INSTEAD OF INSERT ON _tbl
  FOR EACH ROW
    EXECUTE PROCEDURE tbl_serial();

Обратите внимание, что дальнейшие вставки мы производим «как бы во VIEW»:

INSERT INTO _tbl(val) VALUES(1) ON CONFLICT DO NOTHING RETURNING *;
-- 1 строка: pk = 1, val = 1
INSERT INTO _tbl(val) VALUES(1) ON CONFLICT DO NOTHING RETURNING *;
-- 0 строк
INSERT INTO _tbl(val) VALUES(2) ON CONFLICT DO NOTHING RETURNING *;
-- 1 строка: pk = 2, val = 2

Ура! Получили ровно то, что хотели, хоть и весьма нетривиально. Поэтому все получилось аккуратно, но котик несколько насторожен.



Он понимает, что со следующей ситуацией так просто уже не разобраться.

Накрутка счетчика транзакций

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

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

SET statement_timeout = '1s';

Но в случае возникновения ошибки мы потеряем с откатом транзакции весь достигнутый прогресс — 9 записей успешно вставили, на 10-й получили таймаут — и все опять заново. Чтобы не терять сразу все, воспользуемся возможностью создания точек сохранения — SAVEPOINT:

BEGIN TRANSACTION;
  INSERT INTO _tbl(val) SELECT 1 FROM pg_sleep(0.1); -- эмулируем задержку
  SAVEPOINT sp1;
  INSERT INTO _tbl(val) SELECT 2 FROM pg_sleep(0.6);
  SAVEPOINT sp2;
  INSERT INTO _tbl(val) SELECT 3 FROM pg_sleep(1.1);
  -- ERROR:  canceling statement due to statement timeout
  ROLLBACK TO SAVEPOINT sp2;
COMMIT TRANSACTION;

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

_tmp=# SELECT xmin, * FROM tbl;
   xmin    | pk | val
-----------+----+-----
 926944639 |  1 |   1
 926944641 |  2 |   2
(2 rows)

Вот только у наших записей оказался разный идентификатор создавшей транзакции — он увеличивается с каждым вызовом SAVEPOINT.

Для детального понимания внутренней механики работы транзакций, субтранзакций и 2PC в PostgreSQL рекомендую ознакомиться со статьей Transactions in PostgreSQL and their mechanism от Movead Li.

На практике такая ситуация приводит к тому, что autovacuum: VACUUM ... (to prevent wraparound) мы будем видеть очень и очень часто, а если ресурсы сервера не «резиновые» — это может стать проблемой.

Что делать?

• не можем позволить себе ждать завершения операции бесконечно
• не хотим терять весь прогресс транзакции
• не должны неоправданно «накручивать» счетчик транзакций

Единственный приемлемый вариант — лавировать между Сциллой и Харибдой.

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

Ну как, стало немного полегче?..