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

Question

我在使用timescaledb创建连续聚合时出错，该数据库使用postgres物化视图：

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:创建物化视图。。。WITH DATA不能在事务块内运行

我已经设置了自动提交，但没有帮助

Answer 1

修正了它：

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

Answer 2

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

1. 不要通过将隔离级别设置为ISOLATION_LEVEL_AUTOCOMMIT来创建事务，正如在另一个答复中所回答的那样
2. 不要通过指定WITH NO DATA和refreshing separately或通过policy来具体化连续聚合

第二种情况是：

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;""")