Date: Sun, 26 Dec 2021 13:09:18 -0300
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Euler Taveira <euler@eulerto.com>
Cc: Amit Kapila <amit.kapila16@gmail.com>,
	Peter Smith <smithpb2250@gmail.com>,
	Ajin Cherian <itsajin@gmail.com>,
	"houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>,
	Greg Nancarrow <gregn4422@gmail.com>,
	vignesh C <vignesh21@gmail.com>,
	"tanghy.fnst@fujitsu.com" <tanghy.fnst@fujitsu.com>,
	Dilip Kumar <dilipbalaut@gmail.com>,
	Rahila Syed <rahilasyed90@gmail.com>,
	Peter Eisentraut <peter.eisentraut@enterprisedb.com>,
	=?utf-8?B?w5ZuZGVyIEthbGFjxLE=?= <onderkalaci@gmail.com>,
	japin <japinli@hotmail.com>, Michael Paquier <michael@paquier.xyz>,
	David Steele <david@pgmasters.net>,
	Craig Ringer <craig@2ndquadrant.com>,
	Amit Langote <amitlangote09@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: row filtering for logical replication
Message-ID: <202112261609.qp3j4dz2ha2b@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <4571f556-0209-495a-8eed-89367ae09a2a@www.fastmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/202112261609.qp3j4dz2ha2b%40alvherre.pgsql>
Precedence: bulk

On 2021-Dec-26, Euler Taveira wrote:

> On Sat, Dec 25, 2021, at 1:20 AM, Amit Kapila wrote:
> > On Fri, Dec 24, 2021 at 11:04 AM Peter Smith <smithpb2250@gmail.com> wrote:
> > >
> > > So, IMO the PG docs wording for this part should be relaxed a bit.
> > >
> > > e.g.
> > > BEFORE:
> > > +   A nullable column in the <literal>WHERE</literal> clause could cause the
> > > +   expression to evaluate to false; avoid using columns without not-null
> > > +   constraints in the <literal>WHERE</literal> clause.
> > > AFTER:
> > > +   A nullable column in the <literal>WHERE</literal> clause could cause the
> > > +   expression to evaluate to false. To avoid unexpected results, any possible
> > > +   null values should be accounted for.

Is this actually correct?  I think a null value would cause the
expression to evaluate to null, not false; the issue is that the filter
considers a null value as not matching (right?).  Maybe it's better to
spell that out explicitly; both these wordings seem distracting.

You have this elsewhere:

+      If the optional <literal>WHERE</literal> clause is specified, only rows
+      that satisfy the <replaceable class="parameter">expression</replaceable> 
+      will be published. Note that parentheses are required around the 
+      expression. It has no effect on <literal>TRUNCATE</literal> commands.

Maybe this whole thing is clearer if you just say "If the optional WHERE
clause is specified, rows for which the expression returns false or null
will not be published."  With that it should be fairly clear what
happens if you have NULL values in the columns used in the expression,
and you can just delete that phrase you're discussing.

-- 
Álvaro Herrera              Valdivia, Chile  —  https://www.EnterpriseDB.com/