PostgreSQL | Как получить предыдущее значение существующего идентификатора?

PostgreSQL | Как получить предыдущее значение существующего идентификатора?

Как получить предыдущую запись таблицы по предполагаемому первичному ключу?

Суть проблемы заключается в том, что при использовании генераторов последовательностей SEQUENCE, можно получать «дырки» в первичных ключах.

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

Чем больше последовательных идентификаторов удалено, тем крупнее размер «дырки«.

Хочется иметь такую возможность, при которой функция в PostgreSQL будет принимать значение уникального идентификатора первичного ключа и будет возвращать актуальный существующий предыдущий ключ.

Заранее хочу уточнить. Мы говорим о целочисленных идентификаторах таблиц, которые имеют тип bigint.

Но сперва пример SQL-запроса, который вернёт предыдущую актуальную запись для идентификатора 4000000:

SELECT id FROM t_scheme.t_name WHERE id < 4000000 ORDER BY id DESC LIMIT 1;
или
SELECT * FROM t_scheme.t_name WHERE id < 4000000 ORDER BY id DESC LIMIT 1;

Здесь у нас схема данных называется «t_scheme«, а таблица называется «t_name «.

Получили запись таблицы по предыдущему идентификатору в PostgreSQL
Получили запись таблицы по предыдущему идентификатору в PostgreSQL

 

Столбец с идентификаторами называется «id» и он же является столбцом с первичными ключами. Это значит, что по нему PostgreSQL автоматически построил индекс.

Важная часть в этом запросе — это предложение «ORDER BY id DESC LIMIT 1«. Так как у нас в таблице первичные ключи инкрементируются (увеличиваются на единицу) по возрастанию, то мы должны получить упорядоченный список идентификаторов по убыванию. Перебор элементов в команде SELECT будет двигаться от большего значения к меньшему в упорядоченном списке. В итоге первый из существующих идентификаторов, который меньше искомого значения, будет первым в этом упорядоченном списке. И нам останется только получить его при помощи LIMIT.

Скорость выполнения запроса на получение предыдущего идентификатора таблицы в PostgreSQL
Скорость выполнения запроса на получение предыдущего идентификатора таблицы в PostgreSQL

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

 

Функция для получения предыдущего существующего значения целочисленного идентификатора по заданному значению

CREATE OR REPLACE FUNCTION a.get_prev_id_from_t_d(x_id bigint)
RETURNS bigint
LANGUAGE 'plpgsql'
COST 100
IMMUTABLE PARALLEL UNSAFE
AS $BODY$
DECLARE
  -- DROP FUNCTION a.get_prev_id_from_t_d(x_id bigint);
BEGIN
  RETURN (SELECT id FROM a.t_d WHERE id < x_id ORDER BY id DESC LIMIT 1);
END;
$BODY$;

Функция написана под конкретную таблицу в базе данных. Функция принимает только целое число, которое потенциально должно быть идентификатором в таблице.

Чтобы функция могла участвовать в оптимизации запросов «планировщика команд PostgreSQL«, нам нужно указать «характеристику изменчивости» в значении «IMMUTABLE PARALLEL UNSAFE«.

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

Пробуем сделать вызов нашей собственной функции:

SELECT a.get_prev_id_from_t_d(4000000);
Скорость выполнения функции на получение предыдущего идентификатора таблицы в PostgreSQL
Скорость выполнения функции на получение предыдущего идентификатора таблицы в PostgreSQL

 

Без оптимизации функции для планировщика, скорость выполнения её вызова существенно замедляется.

Скорость выполнения функции на получение предыдущего идентификатора таблицы в PostgreSQL без оптимизации
Скорость выполнения функции на получение предыдущего идентификатора таблицы в PostgreSQL без оптимизации

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

Как итог 0.001 против 0.308 мс. Разница просто огромна!

Только потому, что функция всегда будет возвращать определённый результат при одинаково переданных параметрах, мы можем использовать «характеристику изменчивости» в значении «IMMUTABLE PARALLEL UNSAFE«.

 

Информационные ссылки

Официальный сайт WEB-оболочки pgAdminhttps://www.pgadmin.org

Официальный сайт СУБД PostgreSQLhttps://www.postgresql.org

Команды SQLhttps://postgrespro.ru/docs/postgresql/15/sql-commands

Команда CREATE FUNCTIONhttps://postgrespro.ru/docs/postgresql/15/sql-createfunction

Команда CREATE SEQUENCEhttps://postgrespro.ru/docs/postgresql/15/sql-createsequence

Информация для оптимизации функций — https://postgrespro.ru/docs/postgresql/15/xfunc-optimization

Категории изменчивости функций — https://postgrespro.ru/docs/postgresql/15/xfunc-volatility