Postgres触发器的增量是原子的还是高并发安全的?

给定以下设置:

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TABLE IF NOT EXISTS foo (
  id TEXT DEFAULT gen_random_uuid () NOT NULL,
  text TEXT NOT NULL,
  is_latest BOOLEAN DEFAULT TRUE,
  version INTEGER NOT NULL DEFAULT 0,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX foo_id_idx ON foo (id, is_latest);
CREATE INDEX foo_updated_at_idx ON foo (updated_at);
CREATE INDEX foo_created_at_idx ON foo (created_at);

CREATE OR REPLACE FUNCTION foo_copy_row ()
  RETURNS TRIGGER
AS $BODY$
BEGIN
  NEW.version = OLD.version + 1;
  NEW.is_latest = TRUE;
  NEW.updated_at = NOW();
  NEW.created_at = OLD.created_at;
  INSERT INTO foo (id, text, is_latest, version, updated_at, created_at)
  VALUES (OLD.id, OLD.text, NULL, OLD.version, OLD.updated_at, OLD.created_at);
  RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER COPY BEFORE
UPDATE
  ON foo FOR EACH ROW EXECUTE PROCEDURE foo_copy_row ();

我能够成功地对我的数据进行版本化,并且在每次更新时原子性地增加 version 列。

我的问题是当我在期望的同一行上有高并发更新时 ORDER BY id, version DESC 和 ORDER BY id, updated_at DESC 一模一样但不一样。

以下是我更新行的方式:

INSERT INTO foo (text) VALUES ('hello')
RETURNING *;

UPDATE foo SET text = 'welcome'
WHERE id = 'some-uuid' AND is_latest = TRUE
RETURNING *;

这是一个结果的例子:

SELECT id, is_latest, version, updated_at, created_at FROM foo ORDER BY id, updated_at DESC;

-

                 id                  | is_latest | version |          updated_at           |          created_at
-------------------------------------+-----------+---------+-------------------------------+-------------------------------
4d2339ba-eb1f-4925-a4bc-753f2994bd5f | t         |       4 | 2018-07-22 16:12:55.702035+00 | 2018-07-22 16:12:55.694725+00
4d2339ba-eb1f-4925-a4bc-753f2994bd5f |           |       2 | 2018-07-22 16:12:55.698144+00 | 2018-07-22 16:12:55.694725+00
4d2339ba-eb1f-4925-a4bc-753f2994bd5f |           |       1 | 2018-07-22 16:12:55.697429+00 | 2018-07-22 16:12:55.694725+00
4d2339ba-eb1f-4925-a4bc-753f2994bd5f |           |       3 | 2018-07-22 16:12:55.697157+00 | 2018-07-22 16:12:55.694725+00
4d2339ba-eb1f-4925-a4bc-753f2994bd5f |           |       0 | 2018-07-22 16:12:55.694725+00 | 2018-07-22 16:12:55.694725+00

丢失的是什么?

触发器是事务的一部分吗?在执行之前和之后,更新锁是否保留?

是否有可能以两行相同的id和版本号结束?