Date: Tue, 11 Jan 2022 21:41:28 -0300
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>,
	Tomas Vondra <tomas.vondra@enterprisedb.com>,
	Amit Kapila <amit.kapila16@gmail.com>,
	"houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>,
	Rahila Syed <rahilasyed90@gmail.com>,
	Peter Smith <smithpb2250@gmail.com>, pgsql-hackers@postgresql.org
Subject: Re: Column Filtering in Logical Replication
Message-ID: <202201120041.p24wvsfcsope@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <202201111852.sg6v76xr6qkh@alvherre.pgsql>
Archived-At: 
 <https://www.postgresql.org/message-id/202201120041.p24wvsfcsope%40alvherre.pgsql>
Precedence: bulk

On 2022-Jan-11, Alvaro Herrera wrote:

> On 2022-Jan-10, Alvaro Herrera wrote:
> 
> > Hmm.  So you're saying that we should only raise errors about the column
> > list if we are publishing UPDATE or DELETE, but otherwise let the
> > replica identity be anything.  OK, I'll see if I can come up with a
> > reasonable set of rules ...
> 
> This is an attempt to do it that way.  Now you can add a table to a
> publication without regards for how column filter compares to the
> replica identity, as long as the publication does not include updates
> and inserts.

I discovered a big hole in this, which is that ALTER PUBLICATION SET
(publish='insert,update') can add UPDATE publishing to a publication
that was only publishing INSERTs.  It's easy to implement a fix: in
AlterPublicationOptions, scan the list of tables and raise an error if
any of them has a column list that doesn't include all the columns in
the replica identity.

However, that proposal has an ugly flaw: there is no index on
pg_publication_rel.prpubid, which means that the only way to find the
relations we need to inspect is to seqscan pg_publication_rel.

Also, psql's query for \dRp+ uses a seqscan in pg_publication_rel.

Therefore, I propose to add an index on pg_publication_rel.prpubid.

-- 
Álvaro Herrera              Valdivia, Chile  —  https://www.EnterpriseDB.com/
"¿Qué importan los años?  Lo que realmente importa es comprobar que
a fin de cuentas la mejor edad de la vida es estar vivo"  (Mafalda)