graphql-engine
graphql-engine copied to clipboard
citus: support event triggers on local and reference tables
Supporting event triggers on distributed tables is not straightforward on Citus. Ideally, we'll need to create our capture tables on all worker nodes. Depending on the kind of table, we'll need to create a trigger on the backing postgres table to insert into the capture table on the node on which the backing table resides.
The current preview version of Citus (in US east region) maintains a copy of a reference table on the coordinator node. If we scope this down to only support triggers on local and reference tables, we can get away by creating the capture tables only on the coordinator node. Event triggers on local tables work as expected and for reference tables, we just have to create the trigger on the coordinator's copy. This is from Microsoft's response:
select distinct
s.shardid
from pg_dist_shard_placement sp join pg_dist_shard s
on s.shardid=sp.shardid
where logicalrelid='category'::regclass;
(category
is a reference table) would respond with something like:
shardid
---------
102008
The trigger on the reference table’s coordinator copy can then be created as follows:
CREATE TRIGGER event_log_capture AFTER INSERT OR DELETE OR UPDATE ON category_102008
FOR EACH ROW EXECUTE PROCEDURE event_log_capture();
I was curious if there has been any progress on this?