From: | Aleksander Alekseev <a(dot)alekseev(at)postgrespro(dot)ru> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>, pgsql-bugs(at)postgresql(dot)org |
Subject: | 10.0: Logical replication doesn't execute BEFORE UPDATE OF <columns> trigger |
Date: | 2017-10-09 14:13:41 |
Message-ID: | 20171009141341.GA16999@e733.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Marko Tiikkaja | 2017-10-09 14:52:16 | Re: BUG #14830: Missed NOTIFications, PostgreSQL 9.1.24 |
Previous Message | Hans Buschmann | 2017-10-09 08:26:41 | Re: BUG #14844: Failure/Duplicate key value with ALTER DATABASE set search_path |
From | Date | Subject | |
---|---|---|---|
Next Message | Alexander Korotkov | 2017-10-09 14:22:27 | Re: Pluggable storage |
Previous Message | Ants Aasma | 2017-10-09 14:08:05 | Re: Discussion on missing optimizations |