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.