postgresql - 奇怪的 now() 与 Postgres 触发器的时差

Question

在 Postgres 10.10 数据库中，我有一个表 table1 , 和 AFTER INSERT触发 table1为 table2 :

CREATE TABLE table1 (
    id SERIAL PRIMARY KEY,
    -- other cols
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL
);

CREATE UNIQUE INDEX table1_pkey ON table1(id int4_ops);

CREATE TABLE table2 (
    id SERIAL PRIMARY KEY,
    table1_id integer NOT NULL REFERENCES table1(id) ON UPDATE CASCADE,
    -- other cols (not used in query)
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL
);

CREATE UNIQUE INDEX table2_pkey ON table2(id int4_ops);

此查询在应用程序启动时执行:

CREATE OR REPLACE FUNCTION after_insert_table1()
RETURNS trigger AS
$$
BEGIN
    INSERT INTO table2 (table1_id, ..., created_at, updated_at)
    VALUES (NEW.id, ..., 'now', 'now');
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

DROP TRIGGER IF EXISTS after_insert_table1 ON "table1";

CREATE TRIGGER after_insert_table1
AFTER INSERT ON "table1"
FOR EACH ROW 
EXECUTE PROCEDURE after_insert_table1();      

我注意到一些 created_at和 updated_at table2 上的值与 table1 不同.事实上， table2大多数具有较旧的值。

这里有 10 个连续的条目，它们显示了在几分钟内大幅波动的差异:

|table1_id|table1_created            |table2_created               |diff            |
|---------|--------------------------|-----------------------------|----------------|
|2000     |2019-11-07 22:29:47.245+00|2019-11-07 19:51:09.727021+00|-02:38:37.517979|
|2001     |2019-11-07 22:30:02.256+00|2019-11-07 13:18:29.45962+00 |-09:11:32.79638 |
|2002     |2019-11-07 22:30:43.021+00|2019-11-07 13:44:12.099577+00|-08:46:30.921423|
|2003     |2019-11-07 22:31:00.794+00|2019-11-07 19:51:09.727021+00|-02:39:51.066979|
|2004     |2019-11-07 22:31:11.315+00|2019-11-07 13:18:29.45962+00 |-09:12:41.85538 |
|2005     |2019-11-07 22:31:27.234+00|2019-11-07 13:44:12.099577+00|-08:47:15.134423|
|2006     |2019-11-07 22:31:47.436+00|2019-11-07 13:18:29.45962+00 |-09:13:17.97638 |
|2007     |2019-11-07 22:33:19.484+00|2019-11-07 17:22:48.129063+00|-05:10:31.354937|
|2008     |2019-11-07 22:33:51.607+00|2019-11-07 19:51:09.727021+00|-02:42:41.879979|
|2009     |2019-11-07 22:34:28.786+00|2019-11-07 13:18:29.45962+00 |-09:15:59.32638 |
|2010     |2019-11-07 22:36:50.242+00|2019-11-07 13:18:29.45962+00 |-09:18:20.78238 |

顺序条目在序列中具有相似的差异(主要是负/主要是正)和相似的数量级(主要是几分钟 vs 主要是几小时)，但也有异常(exception)

以下是前 5 个最大的积极差异:

|table1_id|table1_created            |table2_created               |diff            |
|---------|--------------------------|-----------------------------|----------------|
|1630     |2019-10-25 21:12:14.971+00|2019-10-26 00:52:09.376+00   |03:39:54.405    |
|950      |2019-09-16 12:36:07.185+00|2019-09-16 14:07:35.504+00   |01:31:28.319    |
|1677     |2019-10-26 22:19:12.087+00|2019-10-26 23:38:34.102+00   |01:19:22.015    |
|58       |2018-12-08 20:11:20.306+00|2018-12-08 21:06:42.246+00   |00:55:21.94     |
|171      |2018-12-17 22:24:57.691+00|2018-12-17 23:16:05.992+00   |00:51:08.301    |

以下是前 5 个最大的负面差异:

|table1_id|table1_created            |table2_created               |diff            |
|---------|--------------------------|-----------------------------|----------------|
|1427     |2019-10-15 16:03:43.641+00|2019-10-14 17:59:41.57749+00 |-22:04:02.06351 |
|1426     |2019-10-15 13:26:07.314+00|2019-10-14 18:00:50.930513+00|-19:25:16.383487|
|1424     |2019-10-15 13:13:44.092+00|2019-10-14 18:00:50.930513+00|-19:12:53.161487|
|4416     |2020-01-11 00:15:03.751+00|2020-01-10 08:43:19.668399+00|-15:31:44.082601|
|4420     |2020-01-11 01:58:32.541+00|2020-01-10 11:04:19.288023+00|-14:54:13.252977|

负差异的数量超过正差异的 10 倍。数据库时区是 UTC。
table2.table1_id是外键，所以应该不可能在插入之前插入 table1完成。
table1.created_at由 Sequelize 设置，使用选项 timestamps: true在模型上。

当一行插入到 table1 中时，它是在事务中完成的。从我可以找到的文档中，触发器是在同一个事务中执行的，所以我想不出这样做的原因。

我可以通过将触发器更改为使用 NEW.created_at 来解决此问题而不是“现在”，但我很好奇是否有人知道这个错误的原因是什么？

这是用于生成上述差异表的查询:

SELECT
    table1.id AS table1_id,
    table1.created_at AS table1_created,
    table2.created_at AS table2_created,
    (table2.created_at - table1.created_at) AS diff
FROM table1
INNER JOIN table2   ON 
    table2.table1_id = table1.id AND (
        (table2.created_at - table1.created_at) > '2 min' OR 
        (table1.created_at - table2.created_at) > '2 min')
ORDER BY diff;

Answer 1

虽然 'now' 不是一个普通的字符串，它在这个上下文中也不是一个函数，而是一个 特殊日期/时间输入 . The manual:

... simply notational shorthands that will be converted to ordinary date/time values when read. (In particular, now and related strings are converted to a specific time value as soon as they are read.)

PL/pgSQL 函数的主体存储为字符串，每个嵌套的 SQL 命令在每个 session 第一次控制到达时被解析和准备。 The manual:

The PL/pgSQL interpreter parses the function's source text and produces an internal binary instruction tree the first time the function is called (within each session). The instruction tree fully translates the PL/pgSQL statement structure, but individual SQL expressions and SQL commands used in the function are not translated immediately.

As each expression and SQL command is first executed in the function, the PL/pgSQL interpreter parses and analyzes the command to create a prepared statement, using the SPI manager's SPI_prepare function. Subsequent visits to that expression or command reuse the prepared statement.

还有更多。继续阅读。但这对于我们的案例来说已经足够了:

第一次执行触发器 每个 session , 'now'转换为当前时间戳(交易时间戳)。在同一个事务中进行更多插入时，与 transaction_timestamp() 没有任何区别。因为这在设计上是稳定的。
但是同一 session 中的每个后续事务都会在 table2 中插入相同的、恒定的时间戳。 , 而 table1 的值可能是任何东西(不确定 Sequelize 在那里做了什么)。如果 table1 中有新值是当时的时间戳，这会导致您的测试出现“负”差异。 ( table2 中的时间戳会更旧。)

解决方案

您真正想要的情况 'now'少之又少。通常，您需要函数 now() (没有单引号!) - 相当于 CURRENT_TIMESTAMP (标准 SQL)和 transaction_timestamp() .相关(推荐阅读!):

Difference between now() and current_timestamp

在您的特殊情况下，我建议 column defaults而不是在触发器中做额外的工作。如果您设置相同的默认值 now()在 table1和 table2 ，您还消除了 INSERT 的任何废话至 table1可能会添加。而且您甚至不必再在插入中提及这些列:

CREATE TABLE table1 (
    id SERIAL PRIMARY KEY,
    -- other cols
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()   -- or leave this one NULL?
);

CREATE TABLE table2 (
    id SERIAL PRIMARY KEY,
    table1_id integer NOT NULL REFERENCES table1(id) ON UPDATE CASCADE,
    -- other cols (not used in query)
    created_at timestamptz NOT NULL DEFAULT now(),  -- not 'now'!
    updated_at timestamptz NOT NULL DEFAULT now()   -- or leave this one NULL?
);

CREATE OR REPLACE FUNCTION after_insert_table1()
  RETURNS trigger LANGUAGE plpgsql AS
$$
BEGIN
   INSERT INTO table2 (table1_id)  -- more columns? but not: created_at, updated_at
   VALUES (NEW.id);                -- more columns?

   RETURN NULL;                     -- can be NULL for AFTER trigger
END
$$;