how can i create materialized view with psycopg2?

Question

I'm getting an error on creating continuous aggregates with timescaledb which uses postgres 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()

the error: psycopg2.errors.ActiveSqlTransaction: CREATE MATERIALIZED VIEW ... WITH DATA cannot run inside a transaction block

I have set the auto-commit on but it didn't help

Answer 1

Creating a continuous aggregate and materializing it in the same transaction is currently not supported in TimescaleDB. Thus there are two choices:

1. Don't create a transaction by setting isolation level to ISOLATION_LEVEL_AUTOCOMMIT as answered in another reply.
2. Don't materialize the continuous aggregate by specifying WITH NO DATA and refreshing separately or through a policy.

The second case will be:

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

Answer 2

fixed it with:

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