Implement CREATE SUBSCRIPTION for distributed tables via a trigger
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
Logical replication can run BEFORE triggers on the destination which would allow us to route tuples coming in via logical replication into the distributed table instead of the local heap.
Proof-of-concept:
On source server (localhost:9700):
```sql
CREATE TABLE test (x int, y int);
CREATE PUBLICATION test_pub FOR TABLE test WITH (publish = 'insert');
INSERT INTO test VALUES (100,100);
```
On the coordinator:
```sql
CREATE TABLE test (x int, y int);
SELECT create_distributed_table('test','x');
CREATE OR REPLACE FUNCTION public.test_trigger()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE
BEGIN
-- Insert into distributed table
INSERT INTO test VALUES (NEW.x, NEW.y);
-- Do not insert into local table
RETURN NULL;
END;
$function$;
CREATE TRIGGER test_trig
BEFORE INSERT ON test
FOR EACH ROW
EXECUTE FUNCTION test_trigger();
BEGIN;
SET LOCAL citus.enable_ddl_propagation TO off;
ALTER TABLE test ENABLE REPLICA TRIGGER test_trig;
END;
CREATE SUBSCRIPTION test_sub CONNECTION 'host=localhost port=9700' PUBLICATION test_pub;
-- wait a few seconds and then run:
SELECT * FROM test;
┌─────┬─────┐
│ x │ y │
├─────┼─────┤
│ 100 │ 100 │
└─────┴─────┘
(1 row)
```
All subsequent inserts are also replicated.
We should implement a general-purpose trigger function that captures insert/update/delete (and maybe truncate?) for any table.
Contributor guide
Assessment
This issue has not been assessed yet.