社区首页 >问答首页 >如何在PostgreSQL中生成唯一的时间戳？

问如何在PostgreSQL中生成唯一的时间戳？
EN

Stack Overflow用户

提问于 2016-01-15 18:48:26

回答 2查看 2.7K关注 0票数 1

我的想法是实现一个基本的“向量时钟”，其中时间戳是基于时钟的，总是向前走，并且保证是唯一的。

例如，在一个简单的表格中：

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 suggested将current_clock()与SEQUENCE对象混合在一起，但这可能意味着要摆脱TIMESTAMP类型。尽管我真的想不出它是如何解决隔离问题的。

有没有一个通用的模式来避免这种情况？

感谢您的见解:)

postgresql

race-condition

vector-clock

回答 2

Stack Overflow用户

发布于 2016-01-15 19:29:52

我的两点意见(灵感来自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

票数 1

Stack Overflow用户

发布于 2016-01-15 19:23:03

如果你像你说的那样只有一个Postgres服务器，我认为使用时间戳+序列可以解决这个问题，因为序列是非事务性的，并且尊重插入顺序。如果你有db分片，那么它将会复杂得多，但也许BDR中2nd象限的分布式序列可能会有所帮助，但我不认为有序性会得到尊重。我在下面添加了一些代码，如果你已经设置好测试它的话。

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)