User-Agent: Cyrus-JMAP/3.5.0-alpha0-533-gf73e617b8a-fm-20210712.002-gf73e617b
Mime-Version: 1.0
Message-Id: <48e13621-10f7-49ef-bcbe-ed530195efd2@www.fastmail.com>
In-Reply-To: <202107132106.wvjgvjgcyezo@alvherre.pgsql>
References: <202107132106.wvjgvjgcyezo@alvherre.pgsql>
Date: Tue, 13 Jul 2021 21:57:44 -0300
From: "Euler Taveira" <euler@eulerto.com>
To: "Alvaro Herrera" <alvherre@alvh.no-ip.org>
Cc: "Peter Smith" <smithpb2250@gmail.com>,
 "Amit Kapila" <amit.kapila16@gmail.com>,
 "Rahila Syed" <rahilasyed90@gmail.com>,
 "Peter Eisentraut" <peter.eisentraut@enterprisedb.com>,
 =?UTF-8?Q?=C3=96nder_Kalac=C4=B1?= <onderkalaci@gmail.com>,
 japin <japinli@hotmail.com>, "Michael Paquier" <michael@paquier.xyz>,
 "David Steele" <david@pgmasters.net>, "Craig Ringer" <craig@2ndquadrant.com>,
 "Tomas Vondra" <tomas.vondra@2ndquadrant.com>,
 "Amit Langote" <amitlangote09@gmail.com>,
 "PostgreSQL Hackers" <pgsql-hackers@lists.postgresql.org>
Subject: Re: row filtering for logical replication
Content-Type: multipart/alternative;
 boundary=17b1a0f9b3fe454db8fa8039cd59419a
Archived-At: 
 <https://www.postgresql.org/message-id/48e13621-10f7-49ef-bcbe-ed530195efd2%40www.fastmail.com>
Precedence: bulk

--17b1a0f9b3fe454db8fa8039cd59419a
Content-Type: text/plain

On Tue, Jul 13, 2021, at 6:06 PM, Alvaro Herrera wrote:
> 1. if you use REPLICA IDENTITY FULL, then the expressions would work
> even if they use any other column with DELETE.  Maybe it would be
> reasonable to test for this in the code and raise an error if the
> expression requires a column that's not part of the replica identity.
> (But that could be relaxed if the publication does not publish
> updates/deletes.)
I thought about it but came to the conclusion that it doesn't worth it.  Even
with REPLICA IDENTITY FULL expression evaluates to false if the column allows
NULL values. Besides that REPLICA IDENTITY is changed via another DDL (ALTER
TABLE) and you have to make sure you don't allow changing REPLICA IDENTITY
because some row filter uses the column you want to remove from it.

> 2. For UPDATE, does the expression apply to the old tuple or to the new
> tuple?  You say it's the new tuple, but from the user point of view I
> think it would make more sense that it would apply to the old tuple.
> (Of course, if you're thinking that the R.I. is the PK and the PK is
> never changed, then you don't really care which one it is, but I bet
> that some people would not like that assumption.)
New tuple. The main reason is that new tuple is always there for UPDATEs.
Hence, row filter might succeed even if the row filter contains a column that
is not part of PK or REPLICA IDENTITY. pglogical also chooses to use new tuple
when it is available (e.g. for INSERT and UPDATE). If you don't like this
approach we can (a) create a new publication option to choose between old tuple
and new tuple for UPDATEs or (b) qualify columns using a special reference
(such as NEW.id or OLD.foo). Both options can provide flexibility but (a) is
simpler.

> I think it is sensible that it's the old tuple that is matched, not the
> new; consider what happens if you change the PK in the update and the
> replica already has that tuple.  If you match on the new tuple and it
> doesn't match the expression (so you filter out the update), but the old
> tuple does match the expression, then the replica will retain the
> mismatching tuple forever.
> 
> 3. You say that a NULL value in any of those columns causes the
> expression to become false and thus the tuple is not published.  This
> seems pretty unfriendly, but maybe it would be useful to have examples
> of the behavior.  Does ExecInitCheck() handle things in the other way,
> and if so does using a similar trick give more useful behavior?
ExecInitCheck() is designed for CHECK constraints and SQL standard requires
taht NULL constraint conditions are not treated as errors. This feature uses a
WHERE clause and behaves like it. I mean, a NULL result does not return the
row. See ExecQual().


--
Euler Taveira
EDB   https://www.enterprisedb.com/

--17b1a0f9b3fe454db8fa8039cd59419a
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE html><html><head><title></title><style type=3D"text/css">p.Mso=
Normal,p.MsoNoSpacing{margin:0}</style></head><body><div>On Tue, Jul 13,=
 2021, at 6:06 PM, Alvaro Herrera wrote:<br></div><blockquote type=3D"ci=
te" id=3D"qt" style=3D""><div>1. if you use REPLICA IDENTITY FULL, then =
the expressions would work<br></div><div>even if they use any other colu=
mn with DELETE.&nbsp; Maybe it would be<br></div><div>reasonable to test=
 for this in the code and raise an error if the<br></div><div>expression=
 requires a column that's not part of the replica identity.<br></div><di=
v>(But that could be relaxed if the publication does not publish<br></di=
v><div>updates/deletes.)<br></div></blockquote><div>I thought about it b=
ut came to the conclusion that it doesn't worth it.&nbsp; Even<br></div>=
<div>with REPLICA IDENTITY FULL expression evaluates to false if the col=
umn allows<br></div><div>NULL values. Besides that REPLICA IDENTITY is c=
hanged via another DDL (ALTER<br></div><div>TABLE) and you have to make =
sure you don't allow changing REPLICA IDENTITY<br></div><div>because som=
e row filter uses the column you want to remove from it.<br></div><div><=
br></div><blockquote type=3D"cite" id=3D"qt" style=3D""><div>2. For UPDA=
TE, does the expression apply to the old tuple or to the new<br></div><d=
iv>tuple?&nbsp; You say it's the new tuple, but from the user point of v=
iew I<br></div><div>think it would make more sense that it would apply t=
o the old tuple.<br></div><div>(Of course, if you're thinking that the R=
.I. is the PK and the PK is<br></div><div>never changed, then you don't =
really care which one it is, but I bet<br></div><div>that some people wo=
uld not like that assumption.)<br></div></blockquote><div>New tuple. The=
 main reason is that new tuple is always there for UPDATEs.<br></div><di=
v>Hence, row filter might succeed even if the row filter contains a colu=
mn that<br></div><div>is not part of PK or REPLICA IDENTITY. pglogical a=
lso chooses to use new tuple<br></div><div>when it is available (e.g. fo=
r INSERT and UPDATE). If you don't like this<br></div><div>approach we c=
an (a) create a new publication option to choose between old tuple<br></=
div><div>and new tuple for UPDATEs or (b) qualify columns using a specia=
l reference<br></div><div>(such as NEW.id or OLD.foo). Both options can =
provide flexibility but (a) is<br></div><div>simpler.<br></div><div><br>=
</div><blockquote type=3D"cite" id=3D"qt" style=3D""><div>I think it is =
sensible that it's the old tuple that is matched, not the<br></div><div>=
new; consider what happens if you change the PK in the update and the<br=
></div><div>replica already has that tuple.&nbsp; If you match on the ne=
w tuple and it<br></div><div>doesn't match the expression (so you filter=
 out the update), but the old<br></div><div>tuple does match the express=
ion, then the replica will retain the<br></div><div>mismatching tuple fo=
rever.<br></div><div><br></div><div>3. You say that a NULL value in any =
of those columns causes the<br></div><div>expression to become false and=
 thus the tuple is not published.&nbsp; This<br></div><div>seems pretty =
unfriendly, but maybe it would be useful to have examples<br></div><div>=
of the behavior.&nbsp; Does ExecInitCheck() handle things in the other w=
ay,<br></div><div>and if so does using a similar trick give more useful =
behavior?<br></div></blockquote><div>ExecInitCheck() is designed for CHE=
CK constraints and SQL standard requires<br></div><div>taht NULL constra=
int conditions are not treated as errors. This feature uses a<br></div><=
div>WHERE clause and behaves like it. I mean, a NULL result does not ret=
urn the<br></div><div>row. See ExecQual().<br></div><div><br></div><div>=
<br></div><div id=3D"sig112409080"><div class=3D"signature">--<br></div>=
<div class=3D"signature">Euler Taveira<br></div><div class=3D"signature"=
>EDB&nbsp;&nbsp; <a href=3D"https://www.enterprisedb.com/">https://www.e=
nterprisedb.com/</a><br></div></div><div><br></div></body></html>
--17b1a0f9b3fe454db8fa8039cd59419a--