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

Em dom., 3 de nov. de 2019 às 23:33, Andrey Lepikhov
<a.lepikhov@postgrespro.ru> escreveu:
>
> On 03/11/2019 20:42, Tom Lane wrote:
> > Andrey Lepikhov <a.lepikhov@postgrespro.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.
>
Could you point out the problem in the documentation?

> >
> >> 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.
> >
I don't think that is acceptable either. If you have different schemas
(even for a small period of time), you should handle it dropping and
recreating the constraints. Logical replication is far from a complete
feature. There should be cases that someone wants to enforce even the
FK constraints in the subscriber. I certainly wouldn't like to open
that can of worms. Relaxing constraints could lead to inconsistent
datasets across nodes. If you want to accept constraint violation,
drop the constraints.

> > 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.
>
... if you do not replicate DDLs in the same order it occurs or if you
have different schemas.


--
   Euler Taveira                                   Timbira -
http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento