psycopg2未正确处理“时区（'America/Chicago'，now（））”

Question

def fetch_last_recorded_activity_date(l_pgsql_pool, l_pg_conn, l_sql_statement):
    """

    :param l_pgsql_pool: Postgres Connection Pool
    :param l_pg_conn: Postgres connection object to run various operations
    :return:
    """
    try:
        with l_pg_conn.cursor() as cur:  # If connectionPool is being used whilte creating new connection,it
            # tries to use existing connection as much as possible. With in connection, you can have multiple cursors
            # for various operations
            cur.execute(l_sql_statement)
            last_recorded_activity_details = cur.fetchone()
            return last_recorded_activity_details

    except (Exception, psycopg2.Error) as error:
        log.error(error)
    finally:
        if cur is not None:
            cur.close()


    while True:
            doc = {}
            sql_statement = """
                             select max(completed_date) at time zone 'utc' at time zone 'america/Chicago' as LastRecordedDate, timezone('america/Chicago',now()) as TimeofCheck
                             from adw.activity_fact
                             where completed_date at time zone 'utc' at time zone 'america/Chicago' between current_date and current_date+1               
                             """
            last_recorded_activity_date, current_date_frm_server = fetch_last_recorded_activity_date(
                pgsql_pool, pg_conn,sql_statement)
            log.debug(
                'Last recorded activity date: {} Servertime when pgsql'
                ' to fetch last recorded activity date is executed: {}'
                    .format(last_recorded_activity_date, current_date_frm_server)
            )
            time.sleep(20)
            sql_statement=''
            current_date_frm_server = None

我正在尝试从服务器获取查询执行时间。为此，我在SQL语句中使用timezone('America/Chicago', now())作为TimeofCheck

我每20秒向Postgres提交一个查询。请查找上面的相关代码。不知怎的，psycopg2正在缓存 TimeofCheck的结果，它根本不更新。上面的脚本正在生成以下结果

01-27 16:40:57 [DEBUG:MainProcess] Last recorded activity date: 2020-01-27 16:39:53.028000 Servertime when pgsql to fetch last recorded activity date is executed: 2020-01-27 16:41:05.033359

01-27 16:41:18 [DEBUG:MainProcess] Last recorded activity date: 2020-01-27 16:39:55.385000 Servertime when pgsql to fetch last recorded activity date is executed: 2020-01-27 16:41:05.033359

01-27 16:41:38 [DEBUG:MainProcess] Last recorded activity date: 2020-01-27 16:40:42.074000 Servertime when pgsql to fetch last recorded activity date is executed: 2020-01-27 16:41:05.033359

01-27 16:41:59 [DEBUG:MainProcess] Last recorded activity date: 2020-01-27 16:40:50.706000 Servertime when pgsql to fetch last recorded activity date is executed: 2020-01-27 16:41:05.033359

01-27 16:42:19 [DEBUG:MainProcess] Last recorded activity date: 2020-01-27 16:41:30.628000 Servertime when pgsql to fetch last recorded activity date is executed: 2020-01-27 16:41:05.033359

01-27 16:42:39 [DEBUG:MainProcess] Last recorded activity date: 2020-01-27 16:41:30.628000 Servertime when pgsql to fetch last recorded activity date is executed: 2020-01-27 16:41:05.033359

它得到的LastRecordedDate是正确的，但是TimeOfCheck返回的是常量值

环境信息

Python 3.6.4 |Anaconda, Inc.| (default, Jan 16 2018, 12:04:33)
[GCC 4.2.1 Compatible Clang 4.0.1 (tags/RELEASE_401/final)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> print(psycopg2.__version__)
2.8.4 (dt dec pq3 ext lo64)

Answer 1

now（）是事务开始时的时间戳，并且所有查询都在同一事务中运行。因此，它们都共享相同的时间戳。您应该使用自动提交模式或在每条语句之间提交事务。保持交易打开的时间比必须的时间长不是一个好的做法，所以你应该在睡觉前关闭它