Logical replication can be broken by domain constraint with NOT VALID option

From: Andrey Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Logical replication can be broken by domain constraint with NOT VALID option
Date: 2019-11-03 07:57:00
Message-ID: 4bdc6250-03c6-0090-df77-580f998c4547@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi,

During patch development I ran into a small problem (see attachment,
fail_replication.sh):
1. We have a table with logical replication to another node.
2. On the master and replica add such "NOT VALID" domain constraint on
the table that some tuples violates the constraint.
3. UPDATE the table: set value of the tuple that violates constraint to
correct value.
4. That's all!

The reason for this problem is that on UPDATE walsender sends old tuple
value (that violates the constraint) with new version (satisfied the
constraint).
Replication worker at replica node restores slot from transfer
representation. During this process domain checking constraint and
returns an ERROR.
Because we can't apply WAL record of the UPDATE command, logical
replication will be stopped at all.
As I understand, this problem can be reproduced in all postgres versions
with logical replication feature.
This problem can be solved by many ways and approaches. I wrote the
patch to solve this problem (see in attachment) by the shortest way.

--
Andrey Lepikhov
Postgres Professional
https://postgrespro.com
The Russian Postgres Company

Attachment Content-Type Size
fail_replication.sh application/x-shellscript 1.4 KB
0001-Fix-the-problem-of-logical-replication-with-domain-N.patch text/x-patch 1.1 KB

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Manuel Rigger 2019-11-03 11:55:11 Re: ALTER TABLE results in "ERROR: could not open relation with OID 43707388"
Previous Message Steven Winfield 2019-11-02 22:00:30 RE: BUG #16059: Tab-completion of filenames in COPY commands removes required quotes