citusdata / citusdata/citus

Implement CREATE SUBSCRIPTION for distributed tables via a trigger

Open
#3,140 3 comments 0 reactions 0 assignees View on GitHub
feature
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.