У меня есть таблица event
со столбцом unique_time
типа timestamptz
. Мне нужно, чтобы каждое из значений в unique_time
было уникальным.
Учитывая ввод timestamptz
, input_time
, мне нужно найти значение минимумtimestamptz
, которое удовлетворяет следующим критериям:
input_time
unique_time
Я не могу просто добавить одну микросекунду к наибольшему значению в unique_time
, потому что мне нужно минимальное значение, удовлетворяющее вышеуказанным критериям.
Есть ли краткий способ вычислить это как часть вставки или обновления таблицы event
?
@Bergi Пока они не точно одновременно, все в порядке. Таким образом, допустимы два значения с разницей в одну микросекунду. Я ожидаю, что в большинстве случаев количество сталкивающихся значений будет очень небольшим, но могут быть некоторые крайние случаи, которые могут периодически вызывать несколько десятков столкновений. Это происходит достаточно редко, чтобы в этих случаях производительность немного страдала, но достаточно часто, чтобы я все же хотел, чтобы в этих случаях он работал правильно.
Я могу придумать 3 подхода: а) использовать generate_series($input_time, $input_time+100 microsecond, 1 microsecond)
, выбрать минимум WHERE NOT EXISTS(…)
. Недостаток: ему нужно стоп-значение, и он может потерпеть неудачу. Также нет представления о производительности (на самом деле он может генерировать все строки). б) Используйте CTE для создания реальной бесконечной последовательности в правильном порядке. Я помню сообщение SO, делающее это (с целыми числами), но не могу найти ссылку. c) используйте PL/SQL и простой цикл WHILE
, который увеличивает временную метку до тех пор, пока вставка не удалась.
Пожалуйста, добавьте определение таблицы к запросу + несколько тестовых данных для экспериментов. Учитывая это, я ожидаю, что это будет довольно тривиально.
Ах, забыл о подходах из моего комментария, которые пытались сгенерировать (бесконечную) последовательность всех временных меток в микросекундах, следующих за $input_time
. Есть гораздо более простой запрос, который может генерировать именно ту метку времени, которая вам нужна:
INSERT INTO event(unique_time, others)
SELECT MIN(candidates.time), $other_values
FROM (
SELECT $input_time AS "time"
UNION ALL
SELECT unique_time + 1 microsecond AS time
FROM event
WHERE unique_time >= $input_time
) AS candidates
WHERE NOT EXISTS (
SELECT *
FROM unique_time coll
WHERE coll.unique_time = candidates.time
);
Тем не менее, я не уверен, насколько хорошо Postgres может оптимизировать это, агрегат MIN
может загружать все временные метки из event
, которые больше, чем $input_time
— что может быть хорошо, если вы всегда добавляете события в конец, но все же. Вероятно, лучшей альтернативой было бы
INSERT INTO event(unique_time, others)
SELECT available.time, $other_values
FROM (
SELECT *
FROM (
SELECT $input_time AS "time"
UNION ALL
SELECT unique_time + 1 microsecond AS time
FROM event
WHERE unique_time >= $input_time
) AS candidates
WHERE NOT EXISTS (
SELECT *
FROM unique_time coll
WHERE coll.unique_time = candidates.time
)
ORDER BY candidates.unique_time ASC
) AS available
ORDER BY available.time ASC
LIMIT 1;
Это может (я не знаю) по-прежнему оценивать сложный подзапрос каждый раз, когда вы что-то вставляете, что было бы довольно неэффективно, если большая часть ввода не вызывает коллизию. Также я понятия не имею, насколько хорошо это работает при одновременных нагрузках (т. е. при одновременном выполнении запроса несколькими транзакциями) и есть ли возможные условия гонки.
В качестве альтернативы просто используйте цикл WHILE
(в клиенте или PL/SQL), который пытается вставить значение до тех пор, пока не добьется успеха, и увеличивает метку времени на каждой итерации - см. Ответ @Erwin Brandstetter для этого.
Я предлагаю функцию с циклом:
CREATE OR REPLACE FUNCTION f_next_free(_input_time timestamptz, OUT _next_free timestamptz)
LANGUAGE plpgsql STABLE STRICT AS
$func$
BEGIN
LOOP
SELECT INTO _next_free _input_time
WHERE NOT EXISTS (SELECT FROM event WHERE unique_time = _input_time);
EXIT WHEN FOUND;
_input_time := _input_time + interval '1 us';
END LOOP;
END
$func$;
Вызов:
SELECT f_next_free('2022-05-17 03:44:22.771741+02');
Убедитесь, что у вас есть индекс на event(unique_time)
. Если столбец определен UNIQUE
или PRIMARY KEY
, этот индекс присутствует неявно.
Связанный:
Поскольку временные метки Postgres имеют разрешение в микросекундах, следующая свободная временная метка находится на расстоянии не менее 1 микросекунды (interval '1 us'
). Видеть:
Также может быть рекурсивным CTE, но накладные расходы, вероятно, больше.
Is there a concise way to compute this as part of an
INSERT
orUPDATE
to theevent
table?
Вышеупомянутое, очевидно, зависит от состояния гонки. Любое количество одновременных транзакций может найти одно и то же свободное место. Postgres не может блокировать строки, которых еще нет.
Поскольку вы хотите INSERT
(аналогично UPDATE
), я предлагаю INSERT .. ON CONFLICT DO NOTHING
вместо этого напрямую в цикле. Опять же, нам нужен UNIQUE
или PRIMARY KEY
на unique_time
:
CREATE OR REPLACE FUNCTION f_next_free(INOUT _input_time timestamptz, _payload text)
LANGUAGE plpgsql AS
$func$
BEGIN
LOOP
INSERT INTO event (unique_time, payload)
VALUES (_input_time, _payload)
ON CONFLICT (unique_time) DO NOTHING;
EXIT WHEN FOUND;
_input_time := _input_time + interval '1 us';
END LOOP;
END
$func$;
Адаптируйте свою «полезную нагрузку» соответственно.
Успешный INSERT
блокирует строку. Даже если параллельные транзакции еще не могут видеть вставленную строку, индекс UNIQUE
является абсолютным.
(Вы мог заставляете его работать с рекомендательные замки...)
Значит, даже если разница всего в одну микросекунду, это будет считаться? Сколько значений у вас есть в
unique_time
, которые, вероятно, столкнутся сinput_time
?