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

From: Andrey Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: Logical replication can be broken by domain constraint with NOT VALID option
Date: 2019-11-04 02:32:58
Message-ID: 48ef40e1-ea03-3eac-6f68-32d10e8cab08@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

On 03/11/2019 20:42, Tom Lane wrote:
> Andrey Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru> writes:
>> 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.
>
> I'm not sure this is something we should attempt to fix. There are
> an infinite number of ways you can break logical replication by
> presenting it with inconsistent data, and that's really what you've
> done here.

This problem reproduced by standard way from the documentation. I assume
this inconsistency option is allowed by SQL standard because it has a
practical usage.

>
>> 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.
>
> That patch is certainly utterly unacceptable. It'd allow the
> receipient to accept data that violates the domain constraint.

If this is the only reason, I propose a new version of the patch (see in
attachment). It is satisfy the "Paranoid safety" rule.
>
> The situation you're describing would probably best be handled by
> not adding the constraint on the replica side until all the
> bad data has been corrected (and replicated).

On any PostgreSQL-based multimaster system, this will be a problem.

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

Attachment Content-Type Size
v2-0001-Fix-the-problem-of-logical-replication-with-domain-N.patch text/x-patch 2.4 KB

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2019-11-04 14:07:47 BUG #16093: Facing the clustering issue while installing the postgres in window10
Previous Message Tom Lane 2019-11-03 22:40:00 Re: BUG #16059: Tab-completion of filenames in COPY commands removes required quotes