28.06.2020 20:20:26 |
Автор: Сегодня разберем пару примеров, как неудачная организация БД и кода могут превратить наше приложение в клубок проблем:
- накрутка
serialприON CONFLICT - накрутка счетчика транзакций
Накрутка serial при ON CONFLICT
Давайте представим, что нам понадобилась небольшая таблица-словарь на пару десятков тысяч записей что-то вроде списка форматов ошибок PostgreSQL.
Наше приложение (или все-таки разработчики?) любит суррогатные ключи, поэтому сразу добавим в таблицу в качестве
PRIMARY
KEY автоинкремент-поле с типом serial.
Точнее, smallserial ведь мы точно знаем, что
строк будет не больше 215:
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 = 1INSERT 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);-- отвязываем DEFAULTALTER TABLE tbl ALTER COLUMN pk DROP DEFAULT;-- создаем "промежуточное" VIEWCREATE 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 выполняется "вместо" заказанной операции над VIEWCREATE 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 = 1INSERT 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 не после каждой операции, а
только после некоторой группы, мы будем накручивать счетчик
транзакций пропорционально меньше.Ну как, стало немного полегче?..
Категории: 
Пример
работы connection pool
Одно из наглядных объяснений 32-bit
transaction ID wraparound
Информация о транзакциях хранится
отдельно от контейнера записи
Timescale базируется на ядре PostgreSQL,
но "заточена" на timeseries-data
Хранение
данных "по столбцам"
Визуальный анализ плана запроса
Диаграмма выполнения плана
Тепловая карта операций по таблицам