Как получить предыдущую запись таблицы по предполагаемому первичному ключу?
Суть проблемы заключается в том, что при использовании генераторов последовательностей 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 «.
Столбец с идентификаторами называется «id» и он же является столбцом с первичными ключами. Это значит, что по нему PostgreSQL автоматически построил индекс.
Важная часть в этом запросе — это предложение «ORDER BY id DESC LIMIT 1«. Так как у нас в таблице первичные ключи инкрементируются (увеличиваются на единицу) по возрастанию, то мы должны получить упорядоченный список идентификаторов по убыванию. Перебор элементов в команде SELECT будет двигаться от большего значения к меньшему в упорядоченном списке. В итоге первый из существующих идентификаторов, который меньше искомого значения, будет первым в этом упорядоченном списке. И нам останется только получить его при помощи LIMIT.
В зависимости от нагрузки базы, скорости выполнения могут принимать разные значения. На скриншоте выше показаны скорости выполнения запроса по таблице из 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);
Без оптимизации функции для планировщика, скорость выполнения её вызова существенно замедляется.
Использование «характеристику изменчивости» в значении «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
