Sample Header Ad - 728x90

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