Horton
Menu

Re: Column Filtering in Logical Replication

Alvaro Herrera <alvherre@alvh.no-ip.org>

View thread
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
Date: 2022-01-12T00:41:28Z
Lists: pgsql-hackers 
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)

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Doc: Explain about Column List feature.

  2. Doc: fix column list vs. replica identity rules.

  3. Prohibit combining publications with different column lists.

  4. Fix the check to limit sync workers.

  5. Wait for subscription to sync in t/031_column_list.sql

  6. Move prattrs to the pg_publication_rel section in docs

  7. Allow specifying column lists for logical replication

  8. Fix row filters with multiple publications

  9. Fix publish_as_relid with multiple publications

  10. Add some additional tests for row filters in logical replication.

  11. Add index on pg_publication_rel.prpubid

  12. Avoid using DefElemAction in AlterPublicationStmt

  13. Small cleanups related to PUBLICATION framework code

  14. Add PublicationTable and PublicationRelInfo structs

  15. Fix various concurrency issues in logical replication worker launching