Automatic Compression in TimescaleDB hypertables with integer

Viewed 45

I use a hypertable with integer (independent of time) in TimescaleDB, trying to keep a certain number of new data and compress the rest automatically. This is a test code.

CREATE TABLE test_tbl(id serial, data integer, PRIMARY KEY(id));
SELECT create_hypertable('test_tbl', 'id', chunk_time_interval => 100);
ALTER TABLE test_tbl SET (timescaledb.compress);
CREATE OR REPLACE FUNCTION max_id() returns INTEGER LANGUAGE SQL STABLE as $$ SELECT COALESCE(max(id), 0) FROM test_tbl; $$;
SELECT set_integer_now_func('test_tbl', 'max_id');

INSERT INTO test_tbl (data) SELECT (i * 2) FROM generate_series(1, 555) as i;

SELECT add_compression_policy('test_tbl', INTEGER '200');

However, compressions are not performed and an error that the relation does not exist is logged.

2022-07-20 14:03:54.776 JST [18600] LOG:  job 1003 threw an error
2022-07-20 14:03:54.776 JST [18600] ERROR:  relation "test_tbl" does not exist at character 35
2022-07-20 14:03:54.776 JST [18600] QUERY:   SELECT COALESCE(max(id), 0) FROM test_tbl; 
2022-07-20 14:03:54.776 JST [18600] CONTEXT:  SQL function "max_id" during startup
    PL/pgSQL function _timescaledb_internal.policy_compression_execute(integer,integer,anyelement,integer,boolean,boolean) line 19 at assignment
    SQL statement "CALL _timescaledb_internal.policy_compression_execute(

            job_id, htid, lag_value::INTEGER,

            maxchunks, verbose_log, recompress_enabled

          )"
    PL/pgSQL function _timescaledb_internal.policy_compression(integer,jsonb) line 61 at CALL

I would like to know the problem and solution of the above.

Thanks.

0 Answers
Related