Find the next free timestamp not in a table yet

Question

I have a table, event, with a column unique_time of type timestamptz. I need each of the values in unique_time to be unique.

Given a timestamptz input, input_time, I need to find the minimum timestamptz value that satisfies the following criteria:

• the result must be >= input_time
• the result must not already be in unique_time

I cannot merely add one microsecond to the greatest value in unique_time, because I need the minimum value that satisfies the above criteria.

Is there a concise way to compute this as part of an insert or update to the event table?

Answer 1

I suggest a function with a loop:

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$;

Call:

SELECT f_next_free('2022-05-17 03:44:22.771741+02');

Be sure to have an index on event(unique_time). If the column is defined UNIQUE or PRIMARY KEY, that index is there implicitly.

Related:

• Can I make a plpgsql function return an integer without using a variable?
• Select rows which are not present in other table
• BREAK statement in PL/pgSQL

Since Postgres timestamps have microsecond resolution, the next free timestamp is at least 1 microsecond (interval '1 us') away. See:

• Ignoring time zones altogether in Rails and PostgreSQL

Could also be a recursive CTE, but the overhead is probably bigger.

Concurrency!

Is there a concise way to compute this as part of an INSERT or UPDATE to the event table?

The above is obviously subject to a race condition. Any number of concurrent transaction might find the same free spot. Postgres cannot lock rows that are not there, yet.

Since you want to INSERT (similar for UPDATE) I suggest INSERT .. ON CONFLICT DO NOTHING instead in a loop directly. Again, we need a UNIQUE or PRIMARY KEY on 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$;

Adapt your "payload" accordingly.

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 ...)