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

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

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

Скорость выполнения запроса на получение предыдущего идентификатора таблицы в 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 без оптимизации

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

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

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

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

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

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

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

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

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

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

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