10.0: Logical replication doesn't execute BEFORE UPDATE OF <columns> trigger

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

Responses

Browse pgsql-bugs by date

  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

Browse pgsql-hackers by date

  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