INSTEAD OF INSERT ON never gets triggered from continous aggregate (timescale)
0
votes
1
answer
56
views
I'm trying to make a trigger based on when a continuous aggregate is updated.
I had the trigger working on the source table, but when trying to add it on the aggregate:
CREATE TRIGGER request_insert_trigger
AFTER INSERT ON request_summary
FOR EACH ROW
EXECUTE FUNCTION notify_request_insert();
I get:
ERROR: "request_summary" is a view
Views cannot have row-level BEFORE or AFTER triggers
So I changed
AFTER INSERT ON
to INSTEAD OF INSERT ON
(see below), based on the documentation, and then I dont get an error. **But my notification never triggers.**
Perhaps what I'm trying to do isn't even possible? (but if it is not possible to have triggers on views, why does the trigger creation with AFTER INSERT ON
not give an error?)
I've tried using non-real-time aggregation, but the problem remains even then. There are no errors in my logs.
CREATE MATERIALIZED VIEW request_summary
WITH (timescaledb.continuous) AS
SELECT name,
time_bucket(INTERVAL '5s', time) AS bucket,
AVG(response_time),
count(*)
FROM request
GROUP BY name, bucket;
CREATE OR REPLACE FUNCTION notify_request_insert()
RETURNS trigger AS $$
DECLARE
payload JSON;
BEGIN
-- Convert the new row into JSON
payload := row_to_json(NEW);
-- Send the notification with the payload
PERFORM pg_notify('insert_event', payload::text);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER request_insert_trigger
INSTEAD OF INSERT ON request_summary
FOR EACH ROW
EXECUTE FUNCTION notify_request_insert();
Asked by Cyberwiz
(101 rep)
Jul 16, 2024, 08:35 PM
Last activity: Jul 23, 2024, 03:13 PM
Last activity: Jul 23, 2024, 03:13 PM