[Bug]: Continious Aggregate - Not possible to use custom datatypes or functions inside any functions called in selection

[RiseOfDeath]

What type of bug is this?

Unexpected error

What subsystems and features are affected?

Continuous aggregate

What happened?

If you create any Select for continous aggregation where called any function (just function or aggregate) which use inside any custom data type (exlude data type which used as this function parameter) or call any other fuction causes "function does not exist" error.

TimescaleDB version affected

2.13.1 and 2.11 (maybe others too)

PostgreSQL version used

13 and 16 (maybe others)

What operating system did you use?

Don't know (I have used official docker image)

What installation method did you use?

Docker

What platform did you run on?

Other, Not applicable

Relevant log output and stack trace

No response

How can we reproduce the bug?

Create any hypertable f.e. "mytable" with time field named f.e. "time".

Then let's try to 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 now lets create agregation which used this function:

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 text AS $$
DECLARE
BEGIN
    RETURN test_return_text('test');
END;
$$ LANGUAGE plpgsql
IMMUTABLE;

Letts create agregation which uses a new function:

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’ ;”

This problem happends with my own custom functions/data types and with datatypes/functions from extensions (f.e. I have this problem with PostGis).

[akuzm]

It works if you qualify the call with schema name in test_call(), i.e. return public.test_return_text('test');. AFAIK in the continuous aggregate functions, the search path is locked down to builtin and timescaledb functions. Otherwise the refresh procedure could pick some other user-defined function, and this can become a privilege escalation vulnerability.