Hi hackers, I've found something that looks like a bug.
Steps to reproduce ------------------ There are 3 instances of PostgreSQL 10.0 - inst1, inst2 and inst3. There is a table `test` on every instance: ``` CREATE TABLE test(k TEXT PRIMARY KEY, v TEXT); ``` Both inst1 and inst2 have `allpub` publication: ``` CREATE PUBLICATION allpub FOR ALL TABLES; ``` ... and inst3 is subscribed for both publications: ``` CREATE SUBSCRIPTION allsub1 CONNECTION 'host=10.128.0.16 user=eax dbname=eax' PUBLICATION allpub; CREATE SUBSCRIPTION allsub2 CONNECTION 'host=10.128.0.26 user=eax dbname=eax' PUBLICATION allpub; ``` So basically it's two masters, one replica configuration. To resolve insert/update conflicts I've created the following triggers on inst3: ``` CREATE OR REPLACE FUNCTION test_before_insert() RETURNS trigger AS $$ BEGIN RAISE NOTICE 'test_before_insert trigger executed'; IF EXISTS (SELECT 1 FROM test where k = new.k) THEN RAISE NOTICE 'test_before_insert trigger - merging data'; UPDATE test SET v = v || ';' || new.v WHERE k = new.k; RETURN NULL; END IF; RETURN NEW; END $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION test_before_update() RETURNS trigger AS $$ BEGIN RAISE NOTICE 'test_before_update trigger executed'; IF EXISTS (SELECT 1 FROM test where k = new.k) THEN RAISE NOTICE 'test_before_update trigger - merging data'; UPDATE test SET v = v || ';' || new.v WHERE k = new.k; DELETE FROM test where k = old.k; RETURN NULL; END IF; RETURN NEW; END $$ LANGUAGE plpgsql; create trigger test_before_insert_trigger before insert on test for each row execute procedure test_before_insert(); create trigger test_before_update_trigger before update of k on test for each row execute procedure test_before_update(); ALTER TABLE test ENABLE REPLICA TRIGGER test_before_insert_trigger; ALTER TABLE test ENABLE REPLICA TRIGGER test_before_update_trigger; ``` The INSERT trigger works just as expected, however the UPDATE trigger doesn't. On inst1: ``` insert into test values ('k1', 'v1'); ``` In inst2: ``` insert into test values ('k4', 'v4'); update test set k = 'k1' where k = 'k4'; ``` Now on inst3: ``` select * from test; ``` Expected result --------------- Rows are merged to: ``` k | v ----+------- k1 | v1;v4 ``` This is what would happen if all insert/update queries would have been executed on one instance. Actual result ------------- Replication fails, log contains: ``` [3227] ERROR: duplicate key value violates unique constraint "test_pkey" [3227] DETAIL: Key (k)=(k1) already exists. [3176] LOG: worker process: logical replication worker for subscription 16402 (PID 3227) exited with exit code 1 ``` What do you think? -- Best regards, Aleksander Alekseev
signature.asc
Description: PGP signature