DataBases, PostgreSQL

gen_uuid() — генерация UUID в PostgreSQL с кастомным алгоритмом

Оставить комментарий

Предлагаемая функция public.gen_uuid() формирует UUID  следующей структуры:

TTTTTTTT-SSSS-SSAA-OOOO-RRRRRRRRRRRR

  • T — Timestamp segment (4 bytes)
  • S — Microseconds with .0000 precission
  • A — Application type segment (2 bytes)
  • O — Object type segment (4 bytes)
  • R — Random segment (4 bytes)

Что это нам дает?

  1. UUID содержит зашифрованную метку времени (с точностью до микросекунды), которая позволяет:
    • Сортировать поле с UUID по времени создания записи
    • Извлекать время создания UUID — см.  статью get_uuid_timestamp
  2. UUID содержит информацию об объекте и приложении (передаются в параметрах вызова функции p_objtype и p_apptype), в рамках которого он был сгенерирован. Это позволяет по UUID четко понимать в какой таблице базы был сгенерирован UUID,
  3. UUID содержит большую рандомную часть (12 шестнадцатиричных разрядов), которая исключает дублирование UUID при генерации множества (более миллиона) UUID в одну микросекунду.

Параметры вызова

  • p_objtype — integer — default=0 — идентификатор объекта системы;
  • p_apptype — integer — default=0 — идентификатор приложения (проекта).

Возвращаемое значение: UUID

Скрипт

Скрипт для создания процедуры public.gen_uuid()

CREATE OR REPLACE FUNCTION public.gen_uuid (
 p_objtype integer = 0,
 p_apptype integer = 0
)
RETURNS uuid AS
$body$
DECLARE
 v_ts timestamptz DEFAULT clock_timestamp();
 v_ms double precision DEFAULT EXTRACT(SECOND FROM (v_ts));
 v_uuid varchar(32) DEFAULT '';
 v_objtype varchar(4) DEFAULT '0000';
 v_apptype varchar(2) DEFAULT '00';
BEGIN
 -- Prepare Object type value
 IF (p_objtype > 0) THEN
 v_objtype := lpad(right(to_hex(p_objtype),4),4,'0');
 END IF;

-- Prepare Application type value
 IF (p_apptype > 0) THEN
 v_apptype := lpad(right(to_hex(p_apptype),2),2,'0');
 END IF;

-- Compile UUID in format TTTTTTTT-SSSS-SSAA-OOOO-RRRRRRRRRRRR
 ---- T - Timestamp segment (4 bytes)
 ---- S - Microseconds with .0000 precission
 ---- A - Application type segment (2 bytes)
 ---- O - Object type segment (4 bytes)
 ---- R - Random segment (4 bytes)
 v_uuid := v_uuid || to_hex(EXTRACT(EPOCH FROM v_ts)::integer)::text
 || lpad(to_hex((trunc(v_ms * 1000000) - trunc(v_ms) * 1000000)::integer)::text,6,'0')
 || v_apptype
 || v_objtype
 || lpad(to_hex((random()*65535)::bigint),4,'0')
 || lpad(to_hex((random()*65535)::bigint),4,'0')
 || lpad(to_hex((random()*65535)::bigint),4,'0');
 RETURN v_uuid::uuid;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

Пример

Использования функции для формирования поля id как первичного ключа таблицы:

CREATE TABLE public.my_table (
 id UUID DEFAULT gen_uuid(128, 2) NOT NULL,
 name TEXT NOT NULL,
 CONSTRAINT my_table_pkey PRIMARY KEY(id)
) 
WITH (oids = false);

В данном примере мы передали параметры 128 и 2. Пример ID получаемый в таблице:

5ad03903-0882-5b02-0080-f309d520867c

Просто взглянув на UUID вы уже можете четко понимать, что он относится к данной таблице.

  • x02 = 2 — Приложение (проект) — наш тестовый проект
  • x0080 = 128 — Объект (таблица) — my_table

Совет

Весьма полезно создать в БД таблицу, в которой регистрировать все используемые apptype и objtype с описанием контекста (приложение, схема, таблица)

Добавить комментарий