Use triggers on distributed hypertables
Triggers on distributed hypertables work in much the same way as triggers on regular hypertables. They have the same limitations. But there are some differences due to the data being distributed across multiple nodes:
- Row-level triggers fire on the data node where the row is inserted. The
triggers must fire where the data is stored, because
AFTERrow triggers need access to the stored data. The chunks on the access node do not contain any data, so they have no triggers.
- Statement-level triggers fire once on each affected node, including the access node. For example, if a distributed hypertable includes 3 data nodes, inserting 2 rows of data executes a statement-level trigger on the access node and either 1 or 2 data nodes, depending on whether the rows go to the same or different nodes.
- A replication factor greater than 1 further causes the trigger to fire on multiple nodes. Each replica node fires the trigger.
Create a trigger on a distributed hypertable by using
TRIGGER as usual. The trigger, and the function it executes,
is automatically created on each data node. If the trigger function references
any other functions or objects, they need to be present on all nodes before you
create the trigger.
Creating a trigger on a distributed hypertable
- If your trigger needs to reference another function or object, use
distributed_execto create the function or object on all nodes.
- Create the trigger function on the access node. This example creates a dummy
trigger that raises the notice 'trigger fired':
CREATE OR REPLACE FUNCTION my_trigger_func() RETURNS TRIGGER LANGUAGE PLPGSQL AS $BODY$ BEGIN RAISE NOTICE 'trigger fired'; RETURN NEW; END $BODY$;
- Create the trigger itself on the access node. This example causes the
trigger to fire whenever a row is inserted into the hypertable
hyper. Note that you don't need to manually create the trigger on the data nodes. This is done automatically for you.
CREATE TRIGGER my_trigger AFTER INSERT ON hyper FOR EACH ROW EXECUTE FUNCTION my_trigger_func();
If you have a statement-level trigger, or a replication factor greater than 1, the trigger fires multiple times. To avoid repetitive firing, you can set the trigger function to check which data node it is executing on.
For example, write a trigger function that raises a different notice on the access node compared to a data node:
CREATE OR REPLACE FUNCTION my_trigger_func() RETURNS TRIGGER LANGUAGE PLPGSQL AS $BODY$ DECLARE is_access_node boolean; BEGIN SELECT is_distributed INTO is_access_node FROM timescaledb_information.hypertables WHERE hypertable_name = <TABLE_NAME> AND hypertable_schema = <TABLE_SCHEMA>; IF is_access_node THEN RAISE NOTICE 'trigger fired on the access node'; ELSE RAISE NOTICE 'trigger fired on a data node'; END IF; RETURN NEW; END $BODY$;
Found an issue on this page?Report an issue!