Re: Column Filtering in Logical Replication

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)