Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m3PYo-0006ZF-NJ for pgsql-hackers@arkaria.postgresql.org; Tue, 13 Jul 2021 21:03:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1m3PYn-0003LV-KR for pgsql-hackers@arkaria.postgresql.org; Tue, 13 Jul 2021 21:03:21 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m3PYm-0003LM-Rm for pgsql-hackers@lists.postgresql.org; Tue, 13 Jul 2021 21:03:20 +0000 Received: from new3-smtp.messagingengine.com ([66.111.4.229]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m3PYf-00064F-S8 for pgsql-hackers@lists.postgresql.org; Tue, 13 Jul 2021 21:03:19 +0000 Received: from compute1.internal (compute1.nyi.internal [10.202.2.41]) by mailnew.nyi.internal (Postfix) with ESMTP id 1963358046E; Tue, 13 Jul 2021 17:03:13 -0400 (EDT) Received: from imap21 ([10.202.2.71]) by compute1.internal (MEProxy); Tue, 13 Jul 2021 17:03:13 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=eulerto.com; h= mime-version:message-id:in-reply-to:references:date:from:to:cc :subject:content-type; s=fm2; bh=plaDP7xxm/aOlHxibf7TW5m3sj4ajWm JGwKjSBNFC5A=; b=OcRBuV3xnbbR5qFEyVMGpCnSthhkVqadhgxL17o08limBSr wFm7kPqPrtz76QeqsLs7azDRtZuvSXLAjbBu2Be135GdND+pd/xq/9rcNitOriDL 0RxeFqvVcZ1ZCZ7scTUmMqxBCPfD8pISo4FIsXZl+bfWku4lUujEWi5PBYVdhi+c +Nz3xBxdDP0MBdfhOfABSGmKpwtBn7ijpP5ANKCtaLDp+iXqkex4x9pvr4cFA0X2 NugE9Cep0MHZsEx7tugOuVRa6OE2tluo94sR5ll/SQG4759EeeP8yLwS6GHAue2L XHIq1+ecCv5kxkBciYc/6oTe5CYSHZeRpug2R3g== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:date:from:in-reply-to :message-id:mime-version:references:subject:to:x-me-proxy :x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm3; bh=plaDP7 xxm/aOlHxibf7TW5m3sj4ajWmJGwKjSBNFC5A=; b=VW578zLzc5pfVP1HAKLwPE fDHVqRYMZ6z+Bs1GcwnhBED4vFqFQwJw3dmaY52bnsw0LwxEW/Z9CD0jB4ppC8gX Fb8dZsDcnIfMxnxtDSONe3tiMMq2QQxAxpmvGCpGbhmY0QHafYCrWnUnGsU3hfsP WzNGw+jEJhmKLSN11Bv5mHQXsiENqszHMLr4ejT/7HL3CeIpjzMbKupiVcOl4iHH SP7dguKjf6vYEcZqBFIYRleepvsJ+1aIdi3gEaNCzDLhXgL30xqoLV0fB8aRnNuG 4vkDjNAGs0SXfHWQJZlTOGVtrUjJJJ+0Ks8elssegv05UGKrxQ91r4d2kxsmu/8A == X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvtddrudeigdduvdcutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenuc fjughrpefofgggkfgjfhffhffvufgtsegrtderreerredtnecuhfhrohhmpedfgfhulhgv rhcuvfgrvhgvihhrrgdfuceovghulhgvrhesvghulhgvrhhtohdrtghomheqnecuggftrf grthhtvghrnhepjeethfeuvdfgffdtteevudeftdejueffkeelveekhfevkeegleefhfej hedtjeegnecuffhomhgrihhnpegvnhhtvghrphhrihhsvggusgdrtghomhenucevlhhush htvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegvuhhlvghrsegvuhhl vghrthhordgtohhm X-ME-Proxy: Received: by mailuser.nyi.internal (Postfix, from userid 501) id 0CDF151C0060; Tue, 13 Jul 2021 17:03:11 -0400 (EDT) X-Mailer: MessagingEngine.com Webmail Interface User-Agent: Cyrus-JMAP/3.5.0-alpha0-533-gf73e617b8a-fm-20210712.002-gf73e617b Mime-Version: 1.0 Message-Id: In-Reply-To: <86dcff6e-9b5f-0f9a-8602-69a5602a30c9@enterprisedb.com> References: <849ee491-bba3-c0ae-cc25-4fce1c03f105@enterprisedb.com> <202107120148.l4nueeibgynb@alvherre.pgsql> <33c033f7-be44-e241-5fdf-da1b328c288d@enterprisedb.com> <15cdb1b63669b6e6d7a3dcd9a21f156aa1bea8fd.camel@j-davis.com> <86dcff6e-9b5f-0f9a-8602-69a5602a30c9@enterprisedb.com> Date: Tue, 13 Jul 2021 18:02:43 -0300 From: "Euler Taveira" To: "Tomas Vondra" , "Jeff Davis" , "Amit Kapila" Cc: "Alvaro Herrera" , "Greg Nancarrow" , "Peter Smith" , "Rahila Syed" , "Peter Eisentraut" , =?UTF-8?Q?=C3=96nder_Kalac=C4=B1?= , japin , "Michael Paquier" , "David Steele" , "Craig Ringer" , "Tomas Vondra" , "Amit Langote" , "PostgreSQL Hackers" Subject: Re: row filtering for logical replication Content-Type: multipart/alternative; boundary=56d7847a48a44a5e8038c45b9e0bf4ca List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --56d7847a48a44a5e8038c45b9e0bf4ca Content-Type: text/plain On Tue, Jul 13, 2021, at 4:07 PM, Tomas Vondra wrote: > On 7/13/21 5:44 PM, Jeff Davis wrote: > > On Tue, 2021-07-13 at 10:24 +0530, Amit Kapila wrote: 8< > >> (c) the columns > >> referred to in the filter should be part of PK or Replica Identity. > > > > Why? > > > > I'm not sure either. This patch uses the old row for DELETE operations and new row for INSERT and UPDATE operations. Since we usually don't use REPLICA IDENTITY FULL, all columns in an old row that are not part of the PK or REPLICA IDENTITY are NULL. The row filter evaluates NULL to false. Documentation says The WHERE clause should contain only columns that are part of the primary key or be covered by REPLICA IDENTITY otherwise, DELETE operations will not be replicated. That's because old row is used and it only contains primary key or columns that are part of the REPLICA IDENTITY; the remaining columns are NULL. For INSERT and UPDATE operations, any column might be used in the WHERE clause. New row is used and it contains all columns. A NULL value causes the expression to evaluate to false; avoid using columns without not-null constraints in the WHERE clause. The WHERE clause does not allow functions and user-defined operators. -- Euler Taveira EDB https://www.enterprisedb.com/ --56d7847a48a44a5e8038c45b9e0bf4ca Content-Type: text/html Content-Transfer-Encoding: quoted-printable
On Tue, Jul 13,= 2021, at 4:07 PM, Tomas Vondra wrote:
On 7/13/21 5:44 PM, Jeff Davis wrote:
> On Tue, 2021-07-13 at 10:24 +0530, Amit Kapila wrote:
8<

>> (c) the columns
&= gt;> referred to in the filter should be part of PK or Replica Identi= ty.
> 
> Why?
>&= nbsp;

I'm not sure either.
This patch uses the old row for DELETE operations and new row= for INSERT and
UPDATE operations. Since we usually don't = use REPLICA IDENTITY FULL, all
columns in an old row that = are not part of the PK or REPLICA IDENTITY are NULL.
The r= ow filter evaluates NULL to false. Documentation says

=
  <para>
   The <litera= l>WHERE</literal> clause should contain only columns that are
   part of the primary key or be covered  by= <literal>REPLICA
   IDENTITY</literal&= gt; otherwise, <command>DELETE</command> operations will not=
   be replicated. That's because old row is use= d and it only contains primary
   key or columns= that are part of the <literal>REPLICA IDENTITY</literal>; t= he
   remaining columns are <literal>NULL&= lt;/literal>. For <command>INSERT</command>
   and <command>UPDATE</command> operations, any = column might be used in the
   <literal>WH= ERE</literal> clause. New row is used and it contains all
   columns. A <literal>NULL</literal> value = causes the expression to evaluate
   to false; a= void using columns without not-null constraints in the
&nb= sp;  <literal>WHERE</literal> clause. The <literal&g= t;WHERE</literal> clause does
   not allow= functions and user-defined operators.
  </para>= ;


--
Euler Taveira
EDB   https://www.enterprisedb.com/
=
--56d7847a48a44a5e8038c45b9e0bf4ca--