External trigger after each continuous aggregate finished?

Franky1 · March 31, 2024, 7:13pm

I watched the new “Timecale for Finance” webinar episodes on youtube.
In connection with the hierarchical continuous aggregates, I had the following question: Let’s assume I continuously write trades into the TimescaleDB and let the continuous aggregates run automatically. So far everything is clear.

Now I would like to trigger a trading algorithm with the latest data, lets say, every 15 minutes, 1 hour, 1 day, etc., just as an example. Is there any way in TimescaleDB to trigger an external application, webhook, message queue, notification etc. after each continuous aggregate successfully finished?

jonatasdp · April 1, 2024, 1:58pm

Hi @Franky1, glad that you watched it!

You can build it in several ways. As the time is global, you can also think about triggering your systems independently knowing your data will be already processed.

If you want to start with something inside postgresql, I’d say that broadcasting the message through NOTIFY/LISTEN would be a great start to stream the candlesticks.

github.com

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


      
          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;

Another idea that maybe useful: sql-snippets/notify.sql at master · jonatas/sql-snippets · GitHub