Как генерировать уникальные метки времени в PostgreSQL?

Question

Моя идея состоит в том, чтобы реализовать базовые "векторные часы", где временные метки основаны на часах, всегда идут вперед и гарантированно будут уникальными.

Например, в простой таблице:

CREATE TABLE IF NOT EXISTS timestamps (
    last_modified TIMESTAMP UNIQUE
);

Я использую триггер, чтобы установить значение метки времени перед вставкой. Это в основном просто уходит в будущее, когда две вставки прибывают одновременно:

CREATE OR REPLACE FUNCTION bump_timestamp()
RETURNS trigger AS $$
DECLARE
    previous TIMESTAMP;
    current TIMESTAMP;
BEGIN
     previous := NULL;
     SELECT last_modified INTO previous
      FROM timestamps
     ORDER BY last_modified DESC LIMIT 1;

     current := clock_timestamp();
     IF previous IS NOT NULL AND previous >= current THEN
        current := previous + INTERVAL '1 milliseconds';
     END IF;
     NEW.last_modified := current;
     RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS tgr_timestamps_last_modified ON timestamps;

CREATE TRIGGER tgr_timestamps_last_modified
BEFORE INSERT OR UPDATE ON timestamps
FOR EACH ROW EXECUTE PROCEDURE bump_timestamp();

Затем я запускаю огромное количество вставок в двух отдельных клиентах:

DO
$$
BEGIN
    FOR i IN 1..100000 LOOP
       INSERT INTO timestamps DEFAULT VALUES;
    END LOOP;
END;
$$;

Как и ожидалось, я получаю столкновения:

ERROR: duplicate key value violates unique constraint "timestamps_last_modified_key"
État SQL :23505
Détail :Key (last_modified)=(2016-01-15 18:35:22.550367) already exists.
Contexte : SQL statement "INSERT INTO timestamps DEFAULT VALUES"
PL/pgSQL function inline_code_block line 4 at SQL statement

@rach предложил смешать current_clock() с SEQUENCE объект, но это, вероятно, будет означать избавление от TIMESTAMP тип. Хотя я не могу понять, как это решит проблему изоляции...

Есть ли общая схема, чтобы этого избежать?

Спасибо за ваши идеи:)

Answer 1

Мои два цента (по мотивам http://tapoueh.org/blog/2013/03/15-batch-update).

попробуйте добавить следующее перед большим количеством вставок:

LOCK TABLE timestamps IN SHARE MODE;

Официальная документация находится здесь: http://www.postgresql.org/docs/current/static/sql-lock.html

Answer 2

Если у вас есть только один сервер Postgres, как вы сказали, я думаю, что использование timestamp + sequence может решить проблему, потому что последовательность не транзакционна и соответствует порядку вставки. Если у вас есть db shard, то это будет намного сложнее, но, возможно, распределенная последовательность 2-го квадранта в BDR может помочь, но я не думаю, что порядочность будет соблюдаться. Я добавил немного кода ниже, если у вас есть настройки для его тестирования.

CREATE SEQUENCE "timestamps_seq";

-- Let's test first, how to generate id.
SELECT extract(epoch from now())::bigint::text || LPAD(nextval('timestamps_seq')::text, 20, '0') as unique_id ;

           unique_id
--------------------------------
 145288519200000000000000000010
(1 row)


CREATE TABLE IF NOT EXISTS timestamps (
    unique_id TEXT UNIQUE NOT NULL DEFAULT extract(epoch from now())::bigint::text || LPAD(nextval('timestamps_seq')::text, 20, '0')
);


INSERT INTO timestamps DEFAULT VALUES;
INSERT INTO timestamps DEFAULT VALUES;
INSERT INTO timestamps DEFAULT VALUES;

select * from timestamps;
           unique_id
--------------------------------
 145288556900000000000000000001
 145288557000000000000000000002
 145288557100000000000000000003
(3 rows)

Дай мне знать, если это работает. Я не администратор баз данных, поэтому, возможно, будет неплохо спросить и на dba.stackexchange.com о возможном побочном эффекте.