我该如何使用psycopg2创建物化视图？

Question

我在使用使用TimescaleDB创建连续聚合时遇到了错误，该错误涉及PostgreSQL materialized views：

connection = psycopg2.connect(DATABASE_URI)
cursor = connection.cursor()
cursor.execute(
     """CREATE MATERIALIZED VIEW quotes_1h WITH
    (timescaledb.continuous)
    AS
    SELECT ticker, time_bucket('1h', time) as hour,
    min(close) as low,
    max(close) as high,
    first(close, time) as open,
    last(close, time) as close
    FROM quotes
    GROUP BY
    ticker, time_bucket('1h', time);""")
connection.commit()

错误信息： psycopg2.errors.ActiveSqlTransaction: CREATE MATERIALIZED VIEW ... WITH DATA不能在事务块内运行

我已经开启了自动提交，但并没有起作用。

- Hossein

---

我无法复制这个。而且查询字符串不正确。这部分 CREATE MATERIALIZED VIEW quotes_1h WITH。 - Adrian Klaver

Answer 1

在TimescaleDB中，当前不支持在同一事务中创建连续聚合并将其实例化。因此有两种选择：

1. 通过将隔离级别设置为ISOLATION_LEVEL_AUTOCOMMIT来避免创建事务，就像另一个回复中所回答的那样。
2. 通过指定WITH NO DATA并通过单独刷新或通过策略来避免实例化连续聚合。

第二种情况将是：

cursor.execute(
     """CREATE MATERIALIZED VIEW quotes_1h WITH
    (timescaledb.continuous)
    AS
    SELECT ticker, time_bucket('1h', time) as hour,
    min(close) as low,
    max(close) as high,
    first(close, time) as open,
    last(close, time) as close
    FROM quotes
    GROUP BY
    ticker, time_bucket('1h', time)
    WITH NO DATA;""")

- k_rus

---

嗨@k_rus，关于这个问题在timescaledb的github上有吗？我遇到了这个问题，然后在stackoverflow上找到了答案... - Jeff

@Jeff 我不知道问题是否存在。 - k_rus

Answer 2

使用以下代码进行修复：

from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
connection.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)

- Hossein

---

截至2021年6月22日，使用最新的psycopg2、TimescaleDB和PostgreSQL版本时，此方法仍不能正常工作。 - Sean McCarthy