Trigger to update value after chunk is dropped

TimescaleDB and PostgreSQL
1

Hello,

I have a drop chunks policy for my hypertable that deletes data more than 1 month old.

I want to create new columns in a different table where I include max and min time values in the hypertable. I’d like to update min time value every time chunks are dropped so the value is always up-to-date.I have tried creating an AFTER DELETE trigger but it doesn´t work.
Is there a way to do so?

I am using PostgreSQL 9.6 and TimescaleDB 1.7.1.

I’m including a minimal example here:

CREATE TABLE available_time (
    min_time TIMESTAMP WITHOUT TIME ZONE,
    max_time TIMESTAMP WITHOUT TIME ZONE
);

CREATE TABLE data (
    ts TIMESTAMP WITHOUT TIME ZONE,
    val real
);

SELECT conv.create_hypertable('data', 'ts');
SELECT conv.add_drop_chunks_policy('data', INTERVAL '1 month', cascade_to_materializations => true);

CREATE FUNCTION update_min_value() RETURNS trigger AS $update_min_value$
     BEGIN
		UPDATE available_time
		SET min_value = (select min(ts) from data);
	    RETURN NULL;
    END;
$update_min_value$ LANGUAGE plpgsql;

CREATE TRIGGER update_min_value AFTER DELETE ON data
FOR EACH ROW EXECUTE PROCEDURE update_min_value();