pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andrey Lepikhov <a.lepikhov@postgrespro.ru>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Logical replication can be broken by domain constraint with NOT VALID option
Date: Mon, 4 Nov 2019 07:32:58 +0500
Message-ID: <48ef40e1-ea03-3eac-6f68-32d10e8cab08@postgrespro.ru> (raw)
In-Reply-To: <29155.1572795733@sss.pgh.pa.us>
References: <4bdc6250-03c6-0090-df77-580f998c4547@postgrespro.ru>
	<29155.1572795733@sss.pgh.pa.us>



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.

> 
>> 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

Attachments:

  v2-0001-Fix-the-problem-of-logical-replication-with-domain-N.patch (text/x-patch, 0B) download

view thread (5+ messages)  latest in thread

Message-ID: <48ef40e1-ea03-3eac-6f68-32d10e8cab08@postgrespro.ru>
Permalink:  ../48ef40e1-ea03-3eac-6f68-32d10e8cab08@postgrespro.ru/
Also on:    postgresql.org/message-id/48ef40e1-ea03-3eac-6f68-32d10e8cab08@postgrespro.ru

 ·  · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: a.lepikhov@postgrespro.ru, tgl@sss.pgh.pa.us
  Subject: Re: Logical replication can be broken by domain constraint with NOT VALID option
  In-Reply-To: <48ef40e1-ea03-3eac-6f68-32d10e8cab08@postgrespro.ru>
  References: <4bdc6250-03c6-0090-df77-580f998c4547@postgrespro.ru> <29155.1572795733@sss.pgh.pa.us> <48ef40e1-ea03-3eac-6f68-32d10e8cab08@postgrespro.ru>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox