Florian Klein is experiencing an issue where the sink plugged to a materialized view is yielding events for rows that didn't change. He suspects it might be related to the append_only strategy. How can this behavior be corrected?
Florian Klein
Asked on Jul 10, 2023
To make a sink plugged to a materialized view yield events only for the rows that changed in the view, you can use a combination of triggers and conditions to filter out unchanged rows. Here's a general approach:
Example:
-- 1. Create a trigger function
CREATE OR REPLACE FUNCTION check_changes()
RETURNS TRIGGER AS $$
BEGIN
NEW.changed := true; -- Set a flag to indicate a change
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- 2. Create a trigger on the materialized view
CREATE TRIGGER mat_view_trigger
AFTER INSERT OR UPDATE ON materialized_view
FOR EACH ROW
EXECUTE FUNCTION check_changes();
-- 3. Modify the sink query to filter out unchanged rows
SELECT *
FROM materialized_view
WHERE changed = true; -- Filter based on the flag