在Postgresql中,NOW()不是当前时间,而是当前事务开始时的时间. Psycopg2可能会隐式为您启动一个事务,并且由于事务永远不会被关闭(通过提交或其他方式),因此时间戳会“卡住”并变得陈旧.

可能的修复：

>经常提交(如果你只做SELECT,那就很傻)
>设置Psycopg2以使用不同的行为来自动创建事务(可能很难做到正确,并会影响应用程序的其他部分)
>使用不同的时间戳函数,例如statement_timestamp()(不符合sql标准,但在这种情况下非常完美)

从the manual,section 9.9.4开始,重点增加：

Postgresql provides a number of
functions that return values related
to the current date and time. These
sql-standard functions all return
values based on the start time of the
current transaction:

• CURRENT_DATE
• CURRENT_TIME
• CURRENT_TIMESTAMP
• CURRENT_TIME(precision)
• CURRENT_TIMESTAMP(precision)
• LOCALTIME LOCALTIMESTAMP
• LOCALTIME(precision)
• LOCALTIMESTAMP(precision)

CURRENT_TIME and CURRENT_TIMESTAMP
deliver values with time zone;
LOCALTIME and LOCALTIMESTAMP
deliver values without time zone.

CURRENT_TIME,CURRENT_TIMESTAMP,
LOCALTIME,and LOCALTIMESTAMP can
optionally be given a precision
parameter,which causes the result to
be rounded to that many fractional
digits in the seconds field. Without a
precision parameter,the result is
given to the full available precision.

…

Since these functions return the
start time of the current transaction,
their values do not change during the
transaction. This is considered a
feature: the intent is to allow a
single transaction to have a
consistent notion of the “current”
time,so that multiple modifications
within the same transaction bear the
same time stamp.

Note: Other database systems might advance these values more frequently.

Postgresql also provides functions
that return the start time of the
current statement,as well as the
actual current time at the instant the
function is called. The complete list
of non-sql-standard time functions is:

• now()
• transaction_timestamp()
• statement_timestamp()
• clock_timestamp()
• timeofday()

now() is a traditional Postgresql
equivalent to CURRENT_TIMESTAMP.
transaction_timestamp() is likewise
equivalent to CURRENT_TIMESTAMP,but
is named to clearly reflect what it
returns. statement_timestamp()
returns the start time of the current
statement (more specifically,the time
of receipt of the latest command
message from the client).
statement_timestamp() and
transaction_timestamp() return the
same value during the first command of
a transaction,but might differ during
subsequent commands.
clock_timestamp() returns the actual
current time,and therefore its value
changes even within a single sql
command. timeofday() is a historical
Postgresql function. Like
clock_timestamp(),it returns the
actual current time,but as a
formatted text string rather than a
timestamp with time zone value.