"ERROR: portal snapshots (1) did not account for all active snapshots (2)" When

TimescaleDB and PostgreSQL
1

I’m getting the same error as this guy but in a different situation.

I have a procedure that dynamically creates the text of a query into a text variable that then it gets EXECUTED. That query does three things:

  1. CREATE MATERIALIZED VIEW … WITH (timescaledb.continuous) AS SELECT … WITH NO DATA;
  2. CALL refresh_continuous_aggregate(…
  3. SELECT add_continuous_aggregate_policy(…

I make the creation and population of the continuous aggregate two separate steps because if not postgresql complains that ERROR: CREATE MATERIALIZED VIEW ... WITH DATA cannot be executed from a function. But when executing it in two separate steps, timescaledb says the following:

ERROR:  portal snapshots (1) did not account for all active snapshots (2)

If I remove CALL refresh_continuous_aggregate(... then it works, so it’s related to the populating/refresh step.

2

Hi @H25E, can you share what version you’re using? I see this related fix was merged recently.

3

2.9.2.

I have seen that if I create a separeted procedure to only do CALL refresh_continuous_aggregate(... then if I execute them inside the procedure as static queries they work. If I generate the refresh queries dynamically (that’s what I want) then it fails with the same exact error.

This works:

CALL refresh_continuous_aggregate('xxx_min', NULL, INTERVAL '1 minute');
CALL refresh_continuous_aggregate('xxx_hour', NULL, INTERVAL '1 hour');
CALL refresh_continuous_aggregate('xxx_day', NULL, INTERVAL '1 day');
CALL refresh_continuous_aggregate('xxx_week', NULL, INTERVAL '1 week');
CALL refresh_continuous_aggregate('xxx_month', '2017-01-01', INTERVAL '1 month');

This doesn’t (snapshot error):

FOREACH time_unit IN ARRAY time_units LOOP
	refresh_query = format($$CALL refresh_continuous_aggregate('xxx_%s', NULL, INTERVAL '1 %s');$$, time_unit, time_unit);
	EXECUTE refresh_query;
END LOOP;

Also in a unrelated issue, the month continous aggregate can’t be updated with an open interval. This works:
CALL refresh_continuous_aggregate('xxx_month', '2017-01-01', INTERVAL '1 mon');

This returns “ERROR: timestamp out of range”:
CALL refresh_continuous_aggregate('xxx_month', NULL, INTERVAL '1 mon');

Feels a little buggy maybe?

4

Probably it’s a bug! I’m not sure if it’s related to wrapping things in a transaction or not. Feel free to create a new issue for each case as they’re isolated examples and let’s confirm it with the core team.