Найти следующую свободную временную метку, которой еще нет в таблице

У меня есть таблица event со столбцом unique_time типа timestamptz. Мне нужно, чтобы каждое из значений в unique_time было уникальным.

Учитывая ввод timestamptz, input_time, мне нужно найти значение минимумtimestamptz, которое удовлетворяет следующим критериям:

  • результат должен быть >= input_time
  • результат не должен быть уже в unique_time

Я не могу просто добавить одну микросекунду к наибольшему значению в unique_time, потому что мне нужно минимальное значение, удовлетворяющее вышеуказанным критериям.

Есть ли краткий способ вычислить это как часть вставки или обновления таблицы event?

Значит, даже если разница всего в одну микросекунду, это будет считаться? Сколько значений у вас есть в unique_time, которые, вероятно, столкнутся с input_time?

Bergi 17.05.2022 00:32

@Bergi Пока они не точно одновременно, все в порядке. Таким образом, допустимы два значения с разницей в одну микросекунду. Я ожидаю, что в большинстве случаев количество сталкивающихся значений будет очень небольшим, но могут быть некоторые крайние случаи, которые могут периодически вызывать несколько десятков столкновений. Это происходит достаточно редко, чтобы в этих случаях производительность немного страдала, но достаточно часто, чтобы я все же хотел, чтобы в этих случаях он работал правильно.

Laurence Gonsalves 17.05.2022 00:39

Я могу придумать 3 подхода: а) использовать generate_series($input_time, $input_time+100 microsecond, 1 microsecond), выбрать минимум WHERE NOT EXISTS(…). Недостаток: ему нужно стоп-значение, и он может потерпеть неудачу. Также нет представления о производительности (на самом деле он может генерировать все строки). б) Используйте CTE для создания реальной бесконечной последовательности в правильном порядке. Я помню сообщение SO, делающее это (с целыми числами), но не могу найти ссылку. c) используйте PL/SQL и простой цикл WHILE, который увеличивает временную метку до тех пор, пока вставка не удалась.

Bergi 17.05.2022 00:50

Пожалуйста, добавьте определение таблицы к запросу + несколько тестовых данных для экспериментов. Учитывая это, я ожидаю, что это будет довольно тривиально.

wildplasser 17.05.2022 00:58
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
2
4
46
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ах, забыл о подходах из моего комментария, которые пытались сгенерировать (бесконечную) последовательность всех временных меток в микросекундах, следующих за $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 or UPDATE to the event 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 является абсолютным.
(Вы мог заставляете его работать с рекомендательные замки...)

Другие вопросы по теме