Sql 查找尚未在表中的下一个空闲时间戳

发布于05月17日

我有一张表，event，有一个timestamptz型unique_time列.我需要unique_time中的每个值都是唯一的.

给定一个timestamptz输入input_time，我需要找到满足以下条件的minimum timestamptz值:

结果必须是&gt；=input_time
结果一定不是unique_time分

我不能只给unique_time中的最大值加1微秒，因为我需要满足上述条件的最小值.

是否有一种简洁的方法可以将其作为event表的插入或更新的一部分进行计算？

推荐答案

我建议使用带有循环的函数:

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.

并发！

有没有一种简洁的方法可以将其作为INSERT或UPDATE到event表的一部分进行计算？

上述情况显然受制于比赛条件.任何数量的并发事务都可能找到相同的空闲位置.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$;

相应地调整"有效载荷".

A successful INSERT locks the row. Even if concurrent transactions cannot see the inserted row yet, a UNIQUE index is absolute.
(You could make it work with advisory locks ...)