Rails单元测试中Postgres的clock_timestamp()为何停止更新？

问题现象 #

在Rails应用中，依赖系统时钟的PostgreSQL查询使用clock_timestamp()函数时，Rails控制台中能正常返回实时时间，系统时间与数据库时间的差值保持稳定；但在单元测试中，clock_timestamp()的返回值却完全冻结，每次查询都返回同一个时间，导致系统时间与数据库时间的差值随时间不断增大。

控制台运行代码：

conn = ActiveRecord::Base.connection
10.times do
  sleep(0.3)
  sys_t, conn_t = Time.now.utc, conn.select_value("SELECT clock_timestamp()")
  drift = sys_t - conn_t
  warn "Drift between clocks: #{drift}"
end

控制台输出（差值稳定）：

Drift between clocks: -0.002037
Drift between clocks: -0.001479
Drift between clocks: -0.001332
Drift between clocks: -0.001402
Drift between clocks: -0.000776
Drift between clocks: -0.001721
Drift between clocks: -0.001453
Drift between clocks: -0.001522
Drift between clocks: -0.000768
Drift between clocks: -0.001513

单元测试代码：

require "test_helper"

class BleghTest < ActiveSupport::TestCase
  def test_drift
    conn = ActiveRecord::Base.connection
    10.times do
      sleep(0.3)
      sys_t, conn_t = Time.now.utc, conn.select_value("SELECT clock_timestamp()")
      drift = sys_t - conn_t
      warn "Drift between clocks: #{drift}"
    end
  end
end

测试输出（差值持续增大，说明数据库时间未更新）：

# Running tests with run options --seed 15342:

Drift between clocks: -0.000866
Drift between clocks: 0.301889
Drift between clocks: 0.604441
Drift between clocks: 0.912252
Drift between clocks: 1.215088
Drift between clocks: 1.517945
Drift between clocks: 1.820126
Drift between clocks: 2.125937
Drift between clocks: 2.428927
Drift between clocks: 2.731635

问题根源 #

Rails默认的单元测试会为每个测试方法包裹事务（通过use_transactional_tests或use_transactional_fixtures配置），并复用同一个数据库连接。虽然PostgreSQL的clock_timestamp()本身不受事务开始时间影响，但在Rails测试的事务上下文里，连接复用机制会导致查询的时间值被异常缓存，最终使得clock_timestamp()返回的时间不再更新。

解决方案 #

方案1：禁用测试的事务隔离 #

在测试类中直接禁用事务测试，改用截断表的方式清理测试数据：

class BleghTest < ActiveSupport::TestCase
  self.use_transactional_tests = false # Rails 5+
  # 或 self.use_transactional_fixtures = false（旧版本Rails）

  def test_drift
    # 测试代码不变
  end
end

如果需要全局禁用，可在test_helper.rb中添加配置。注意：这种方式会增加测试时间，因为每次测试都需要截断表。

方案2：每次查询重新获取数据库连接 #

在循环中每次重新从连接池获取连接，避免复用同一个事务内的连接：

def test_drift
  10.times do
    sleep(0.3)
    # 从连接池取出新连接
    conn = ActiveRecord::Base.connection_pool.checkout
    conn_t = conn.select_value("SELECT clock_timestamp()")
    # 将连接放回连接池
    ActiveRecord::Base.connection_pool.checkin(conn)
    sys_t = Time.now.utc
    drift = sys_t - conn_t
    warn "Drift between clocks: #{drift}"
  end
end

这种方式不需要修改全局测试配置，仅针对需要实时时间的测试调整。

方案3：手动回滚并重新开启事务 #

在每次查询前手动结束当前事务并开启新事务，强制连接刷新时间：

def test_drift
  conn = ActiveRecord::Base.connection
  10.times do
    sleep(0.3)
    # 回滚当前事务并开启新事务
    conn.rollback_db_transaction
    conn.begin_db_transaction
    sys_t, conn_t = Time.now.utc, conn.select_value("SELECT clock_timestamp()")
    drift = sys_t - conn_t
    warn "Drift between clocks: #{drift}"
  end
end

注意：这种方式会影响测试数据的隔离性，需确保测试数据不会被意外清理。

内容的提问来源于stack exchange，提问作者Julik