Postgres queries without a transaction?

Grafana PostgreSQL
,
1
  • What Grafana version and what operating system are you using?

Grafana v8.0.1

  • What are you trying to achieve?

I have a Postgres (TimescaleDB) data source and as part of a query in a panel, I’m trying to call a procedure refresh_continuous_aggregate that can not be ran inside a transaction. It’s not too important why but roughly, I want to only update the aggregate when querying the data and only in the time scale that user picked in Grafana.

  • How are you trying to achieve it?

In the query window, I simply want to do something like:

CALL refresh_continuous_aggregate('my_agg', $__timeFrom(), $__timeTo());
-- the actual query using my_agg
SELECT blah, bluh FROM my_agg WHERE …;
  • What happened?

The database comes back with

error:"db query error: pq: refresh_continuous_aggregate() cannot run inside a transaction block"
  • What did you expect to happen?

I thought the code would work as-is, just like it does when pasted over into psql.

I can’t find anywhere online struggling because of this problem nor can I find any information about making Grafana not wrap the queries in transactions.

2

FWIW, I ended up not using the aggregates and don’t need this functionality at all anymore. But it would be nice to know if/why transactions are forced.