CODEWITHSUNDEEP
pythonpostgresqlpsycopg2timescaledb
Hossein
Hossein

Reputation: 41

how can i create materialized view with psycopg2?

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

Upvotes: 4

Views: 2236

Answers (2)

k_rus
k_rus

Reputation: 3219

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

Upvotes: 4

Hossein
Hossein

Reputation: 41

fixed it with:

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

Upvotes: 0

Related Questions