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 1m3hmq-0004Wq-3D for pgsql-hackers@arkaria.postgresql.org; Wed, 14 Jul 2021 16:31:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1m3hmo-0006Na-Tj for pgsql-hackers@arkaria.postgresql.org; Wed, 14 Jul 2021 16:31:02 +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 1m3hmn-0006NS-PF for pgsql-hackers@lists.postgresql.org; Wed, 14 Jul 2021 16:31:02 +0000 Received: from new1-smtp.messagingengine.com ([66.111.4.221]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m3hmj-0006r6-0A for pgsql-hackers@lists.postgresql.org; Wed, 14 Jul 2021 16:31:00 +0000 Received: from compute1.internal (compute1.nyi.internal [10.202.2.41]) by mailnew.nyi.internal (Postfix) with ESMTP id DAAAA580A87; Wed, 14 Jul 2021 12:30:55 -0400 (EDT) Received: from imap21 ([10.202.2.71]) by compute1.internal (MEProxy); Wed, 14 Jul 2021 12:30:55 -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=dJ0ePmesA7GXPwIyDPNwInar/Zv8g/L LIRJO/pdcgNs=; b=GT5kGz+FM1C/+P7uFDv1/NAjDqRfvjoWeXjsGeiPcBHZFpb T6Fb0Gy7j1anrtYwkJCdv8sKMENk9ec1BpYSil9vwR+GVZMW5e97gC4i/JlDbwWp /xpkVAVTNj4+zKinJeiBj3CFeKOYGGPQLs7zGdt5ejJBDauVH1cZq/FzFQNQ82J4 XRZ0bacFiVKEQ3rLOaH0mnM6hOZeljQtrp4Ij5rrj8d0zuKjUJbdnKZlDapna7CO I6psvBS9NXWGryJ7ObgJK20NAB+xbh3A13tC3r5s03RFvoTlYZT2eA7dYmrh9aHc si35A85oRQx9yyd3EBO9JF0AdqQGcQpA6Av7Law== 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=dJ0ePm esA7GXPwIyDPNwInar/Zv8g/LLIRJO/pdcgNs=; b=K/+i/a+Hn+RHj7J95JKGO/ lctulZPWSwqvVypoNOjiVTVMT30HZJIUXciq/nmj8NKcJfF1vOJFUZ9lrfFJXvT8 7w+TXfQKbr3yIKu9qeszRM2ocKfwaPUKnmzkMlaO85DVot+szDuv++bxrW2O/uHy LZ0vZFC+UTN9NnDFoc7+E2nVLW2+Q0y4I4HdDzVHpy9wt75hi9dqkr7OfyF2ncK5 V+e622AxtqN14da4kvJ7ghKiQJWhtBzOVKZkEtnnfR8sOMoMfMCW0qvYDz8YksK8 QlMBXJc+BZSMY0TPadQfusDzyBYnt0A09heTBt4UKHgRoPLIcXTRksuT4j5KB2Gg == X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvtddrudekgdelkecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenuc fjughrpefofgggkfgjfhffhffvufgtsegrtderreerredtnecuhfhrohhmpedfgfhulhgv rhcuvfgrvhgvihhrrgdfuceovghulhgvrhesvghulhgvrhhtohdrtghomheqnecuggftrf grthhtvghrnhepjeethfeuvdfgffdtteevudeftdejueffkeelveekhfevkeegleefhfej hedtjeegnecuffhomhgrihhnpegvnhhtvghrphhrihhsvggusgdrtghomhenucevlhhush htvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegvuhhlvghrsegvuhhl vghrthhordgtohhm X-ME-Proxy: Received: by mailuser.nyi.internal (Postfix, from userid 501) id C3F8251C0060; Wed, 14 Jul 2021 12:30:54 -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: <5a3f74df-ffa1-4126-a5d8-dbb081d3e439@www.fastmail.com> In-Reply-To: References: <202107132106.wvjgvjgcyezo@alvherre.pgsql> <48e13621-10f7-49ef-bcbe-ed530195efd2@www.fastmail.com> Date: Wed, 14 Jul 2021 13:30:33 -0300 From: "Euler Taveira" To: "Dilip Kumar" , "Tomas Vondra" Cc: "Amit Kapila" , "Alvaro Herrera" , "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=b745ef2b62274dffb33059ef800121df List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --b745ef2b62274dffb33059ef800121df Content-Type: text/plain On Wed, Jul 14, 2021, at 11:48 AM, Dilip Kumar wrote: > On Wed, Jul 14, 2021 at 8:04 PM Tomas Vondra > wrote: > > > > > Perhaps the best way forward is to stick to the approach that INSERT > > uses new, DELETE uses old and UPDATE works as DELETE+INSERT (probably), > > and leave anything fancier (like being able to reference both versions > > of the row) for a future patch. > > If UPDATE works as DELETE+ INSERT, does that mean both the OLD row and > the NEW row should satisfy the filter, then only it will be sent? > That means if we insert a row that is not satisfying the condition > (which is not sent to the subscriber) and later if we update that row > and change the values such that the modified value matches the filter > then we will not send it because only the NEW row is satisfying the > condition but OLD row doesn't. I am just trying to understand your > idea. Or you are saying that in this case, we will not send anything > for the OLD row as it was not satisfying the condition but the > modified row will be sent as an INSERT operation because this is > satisfying the condition? That's a fair argument for the default UPDATE behavior. It seems we have a consensus that UPDATE operation will use old row. If there is no objections, I will change it in the next version. We can certainly discuss the possibilities for UPDATE operations. It can choose which row to use: old, new or both (using an additional publication argument or OLD and NEW placeholders to reference old and new rows are feasible ideas). -- Euler Taveira EDB https://www.enterprisedb.com/ --b745ef2b62274dffb33059ef800121df Content-Type: text/html Content-Transfer-Encoding: quoted-printable
On Wed, Jul 14,= 2021, at 11:48 AM, Dilip Kumar wrote:
On Wed, Jul 14, 2021 at 8:04 PM Tomas Vondra=
>

> Perhaps the best way forward is to stick to the ap= proach that INSERT
> uses new, DELETE uses old and UPDA= TE works as DELETE+INSERT (probably),
> and leave anyth= ing fancier (like being able to reference both versions
&g= t; of the row) for a future patch.

If UPDAT= E works as DELETE+ INSERT, does that mean both the OLD row and
=
the NEW row should satisfy the filter, then only it will be sent?
That means if we insert a row that is not satisfying the co= ndition
(which is not sent to the subscriber) and later if= we update that row
and change the values such that the mo= dified value matches the filter
then we will not send it b= ecause only the NEW row is satisfying the
condition but OL= D row doesn't.  I am just trying to understand your
i= dea.  Or you are saying that in this case, we will not send anythin= g
for the OLD row as it was not satisfying the condition b= ut the
modified row will be sent as an INSERT operation be= cause this is
satisfying the condition?
That's a fair argument for the default UPDATE behavior. It seem= s we have a
consensus that UPDATE operation will use old r= ow. If there is no objections, I
will change it in the nex= t version.

We can certainly discuss the pos= sibilities for UPDATE operations. It can choose
which row = to use: old, new or both (using an additional publication argument or
OLD and NEW placeholders to reference old and new rows are f= easible ideas).


--
Eu= ler Taveira
EDB   https://www.enterprisedb.com/

--b745ef2b62274dffb33059ef800121df--