ContinuouSQL Triggers
pipelinedb.com
ContinuouSQL Triggers
1–9 of 9 posts
Re: ContinuouSQL Triggers
#2Are there certain types of continuous views where continuous triggers work better or worse? I can imagine things like alerts on moving averages could get tricky and possibly send repeated alerts when predefined thresholds are crossed multiple times.
Re: ContinuouSQL Triggers
#3Couple of questions:
- If the trigger function hits a service that takes a long time to respond, how does that affect the system?
- Why did you have to reimplement trigger functions, what was the problem with using trigger functions for tables on continuous views?
Re: ContinuouSQL Triggers
#4Are there certain types of continuous views where continuous triggers work better or worse? I can imagine things like alerts on moving averages could get tricky and possibly send repeated alerts when predefined thresholds are crossed multiple times.
They work better for non-sliding window queries. Triggers on sliding window queries are much more resource intensive, both for CPU and memory. Essentially, the trigger process has to keep track of tuples for each step in the window and combine them whenever any tuple is updated to get the new value.
Re: ContinuouSQL Triggers
#5Couple of questions: - If the trigger function hits a service that takes a long time to respond, how does that affect the system? - Why did you have to reimplement trigger functions, what was the problem with using trigger functions for tables on continuous views?
A) Continuous triggers are implemented by using the logical replication features of Postgres. This means that changes are consumed from a queue and evaluated in a separate process to the rest of the system. A long running trigger will block the trigger process only.
B) We do re-use quite a bit of the standard trigger mechanisms, but we wanted to use our own evaluation context to support extra features. For example, continuous triggers can be defined on sliding windows, which require the trigger to be evaluated over multiple transformed tuples in the underlying table.
Re: ContinuouSQL Triggers
#6This whole example represents a common workflow in fraud detection (alerts on data that hits a certain pattern). However, when I worked at Braintree, doing this real-time was tough and we'd analyze stuff retrospectively.
Re: ContinuouSQL Triggers
#7Can you create a trigger on any continuous query
Re: ContinuouSQL Triggers
#8Can you create a trigger on any continuous query
Almost. Currently continuous views that contain an ORDER BY or LIMIT clause are not supported.
Re: ContinuouSQL Triggers
#9Can you create a trigger on any continuous query
[deleted]