Русский
Русский
English
Статистика
Реклама

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

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

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

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

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

Источник: habr.com
К списку статей
Опубликовано: 28.06.2020 20:20:26
0

Сейчас читают

Комментариев (0)
Имя
Электронная почта

Блог компании тензор

Postgresql

Sql

Администрирование баз данных

Dba

Serial

Unique

On conflict

Upsert

Триггер

Instead of

Транзакции

Savepoint

Wraparound

Категории

Последние комментарии

  • Имя: Макс
    24.08.2022 | 11:28
    Я разраб в IT компании, работаю на арбитражную команду. Мы работаем с приламы и сайтами, при работе замечаются постоянные баны и лаги. Пацаны посоветовали сервис по анализу исходного кода,https://app Подробнее..
  • Имя: 9055410337
    20.08.2022 | 17:41
    поможем пишите в телеграм Подробнее..
  • Имя: sabbat
    17.08.2022 | 20:42
    Охренеть.. это просто шикарная статья, феноменально круто. Большое спасибо за разбор! Надеюсь как-нибудь с тобой связаться для обсуждений чего-либо) Подробнее..
  • Имя: Мария
    09.08.2022 | 14:44
    Добрый день. Если обладаете такой информацией, то подскажите, пожалуйста, где можно найти много-много материала по Yggdrasil и его уязвимостях для написания диплома? Благодарю. Подробнее..
© 2006-2024, personeltest.ru