Event based user actions

Dheeraj_Saxena · May 27, 2024, 2:12pm

I am in the process of building out an algo trading system using TimescaleDB.
We have a continuous aggregate that stores the OHLCV for candles.

The issue is that we want to trigger certain logic on the close of every candle within a bucket - for example, find out is the RSI exceeded 70 for any symbol in the 15 min period.

For this, we have written the logic in a python function but the problem is how to invoke that function? How do we know when the OHLC row for the last 15 min candle has been inserted into Timescale?

Does the product offer any events that can be triggered everytime a new row is added in a bucket?

jonatasdp · May 30, 2024, 3:24pm

Hey @Dheeraj_Saxena, here’s a simple example:

github.com

jonatas/sql-snippets/blob/2f886d561776723041c0e731788f970e7c891d44/caggs_notify.sql#L47-L64


      
          CREATE OR REPLACE FUNCTION notify_new_row() RETURNS TRIGGER AS $$
            DECLARE
            threshold INTERVAL := INTERVAL '0 seconds';
            h1 candlestick_1h := null;
          BEGIN
            select * from candlestick_1h order by time_bucket desc limit 1 into h1;
            IF (NEW.time - h1.time_bucket) >= threshold THEN
              PERFORM pg_notify('h1', row_to_json(h1)::text);
            END IF;
            RETURN NEW;
          END;
          $$ LANGUAGE plpgsql;
          
          
          CREATE TRIGGER notify_new_row_trigger
          AFTER INSERT ON ticks
          FOR EACH ROW
          EXECUTE FUNCTION notify_new_row();

Remember that Timescaledb is just Postgres on asteroids, so the continuous aggregates is also a hypertable, so you can also insert a trigger

Dheeraj_Saxena · May 30, 2024, 5:08pm

Thanks so much @jonatasdp - really appreciate the help here!