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

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

    Тензор
    Разработчик системы СБИС

    Комментарии 12

      +1
      Еще вариант — использовать тип uuid вместо serial, тогда никаких последовательностей не нужно.
        0
        В случае, когда используемый ORM это позволяет — тоже можно. Но тогда обычно про serial и не вспоминают.
          +1

          UUID надо генерировать при создании сущности в памяти, а не при вставке в базу. Тогда от ORM ничего особенного и не требуется (да, в случае с mysql-ем там свои нюансы, но мы же про постгрес).

            0
            Я про то, что если ORM позволяет связывать сущности в памяти по UUID (а не просто генерировать как одно из полей), тогда в базе serial обычно не встречается вовсе.
          +1

          У них свои минусы.

          0
          просто
          Сконвертировать поле и вместо smallserial использовать serial или bigserial — это позволит продлить агонию приложения на месяцы или даже годы.

          Серьёзно? У вас были случаи исчерпания диапазона bigserial?

            0
            Для serial — были.
            И это очень легко, если приложение делает, например, 1K/sec таких операций: 2^31 / 1000 ops / 86400 sec ~= 25 дней.
            bigserial вряд ли удастся застать на своем веку. :)
              0
              Ну так, для serial у многих были. А bigserial для того и нужен, чтобы не париться. При закладываемом жизненном цикле приложения в 10 лет надо
              (2^63)/(365*10*86400)~3e10 операций в секунду делать. Слоник так быстро не бегает…
            0
            Kilor, что мешает использовать NOT EXISTS при наличии уникального ключа (ключ стостоит из одного, двух полей) вместо ON CONFLICT?
              +1
              NOT EXISTS во вложенном запросе не гарантирует атомарность операции и может приводить к ошибкам конкурентного доступа:
              SELECT -- 0 rows
                  SELECT -- 0 rows
              INSERT
                  INSERT -- fail

              ON CONFLICT — гарантирует, поскольку сначала захватывает блокировку на уникальном ключе:
              LOCK(uniqueVal)
                  LOCK(uniqueVal) -- wait
              INSERT
                  ON CONFLICT
              +1

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


              В PL/pgSQL обработка исключений реализуется при помощи SAVEPOINT. Если в хранимой процедуре/функции имеется блок "EXCEPTION", то при каждом выполнении этого кода будет создаваться SAVEPOINT, и соответственно, будет увеличиваться счётчик транзакций. Если такая функция применяется к возвращаемым строкам запроса, то это может приводить к аварийной остановке кластера, я читал про такие кейсы.


              Об этом не сказано в документации, но есть подробный комментарий в исходном коде.


              Отсюда вытекает рекомендация. Не используйте обработчики исключений внутри функций, которые вы собираетесь применять к строкам, возвращаемых запросами. Этот механизм предназначен для процедур, реализующих бизнес логику на стороне СУБД,

                0
                Может, запилите полноценную статью с примерами?

              Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

              Самое читаемое