Continuous Aggregates and PostGis datatypes/funtions

TimescaleDB and PostgreSQL
1

I have some Select which used PostGis Functions and datatypes it works perfect but then I try to make a Continius Agregate with the same selection problems started - during update I I have an “ERROR: type “spheroid” does not exist” message. What can be wrong?

upd.
Request actually call function marked as IMMUTABLE, which do some calculations over passed geometry type objects

2

Hi Anton, did you get it working? Yes, continuous aggregates only works with immutable functions.

3

No, I still get “ERROR: type “spheroid” does not exist” when (and only when) use it in continiuos agregate

4

Also as I findout it also cannot see postgis function (f.e. ERROR: function st_distancespheroid(public.geometry, public.geometry) does not exist).

And As I said before this problem happends only with Continius Agregation.

5

Update
Its problem not with Postgis - its problem with any usage of and custom datatypes/functions inside called functions

F.e. lets create any useless function:

CREATE OR REPLACE FUNCTION test_return_text(text) RETURNS text AS $$
DECLARE
BEGIN
    RETURN CONCAT('Passed text is: ', $1);
END;
$$ LANGUAGE plpgsql
IMMUTABLE;

Sow not lets create agregation:

CREATE MATERIALIZED VIEW TEST_TSAGR_1
WITH (timescaledb.continuous)
AS SELECT 
    time_bucket('1 d'::interval, time) as bucket,
    test_return_text('test') as test
FROM mytable
GROUP BY bucket;

Example above works good.

Now lets create another function, which just call test_return_text:

CREATE OR REPLACE FUNCTION test_call() RETURNS uuid  AS $$
DECLARE
BEGIN
    RETURN test_return_text('test');
END;
$$ LANGUAGE plpgsql
IMMUTABLE;

Sow not lets create which uses a new function agregation:

CREATE MATERIALIZED VIEW TEST_TSAGR_2
WITH (timescaledb.continuous)
AS SELECT 
    time_bucket('1 d'::interval, time) as bucket,
    test_call() as test
FROM mytable
GROUP BY bucket;

the result is…

NOTICE: refreshing continuous aggregate “test_tsagr_2”

ERROR: function test_return_text(unknown) does not exist
LINE 1: test_return_text(‘test’)
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: test_return_text(‘test’)
CONTEXT: PL/pgSQL function public.test_call() line 4 at RETURN
SQL statement “INSERT INTO _timescaledb_internal._materialized_hypertable_11 SELECT * FROM _timescaledb_internal._partial_view_11 AS I WHERE I.bucket >= ‘4714-11-24 00:00:00+00 BC’ AND I.bucket < ‘2025-01-01 00:00:00+00’ ;”

UPD.
The same if I make custom aggregation function - sfunc also have simmular errors if call any custom function or use custom data type.

P.S.
It reproduced in ts2.11 and ts2.13.1 on PG 13 ang PG 16 (I have not tried other versions combination)
Also I have not find any info about such restrictions in documentation (correct me if i’m wrong), so it looks like bug.

6

Thanks for all the input here. Feel free to create an issue on github to allow us to better track it! Seems your example proves it’s a bug.