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 не после каждой операции, а только после некоторой группы, мы будем «накручивать» счетчик транзакций пропорционально меньше.

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