Horton
Menu

Re: Column Filtering in Logical Replication

Amit Kapila <amit.kapila16@gmail.com>

View thread
From: Amit Kapila <amit.kapila16@gmail.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Justin Pryzby <pryzby@telsasoft.com>, Peter Eisentraut <peter.eisentraut@enterprisedb.com>, Tomas Vondra <tomas.vondra@enterprisedb.com>, Hou, Zhijie/侯 志杰 <houzj.fnst@fujitsu.com>, Rahila Syed <rahilasyed90@gmail.com>, Peter Smith <smithpb2250@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2022-01-15T03:45:07Z
Lists: pgsql-hackers 
On Fri, Jan 14, 2022 at 7:08 PM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
>
> On 2022-Jan-14, Amit Kapila wrote:
>
> > 1. Replica Identity handling: Currently the column filter patch gives
> > an error during create/alter subscription if the specified column list
> > is invalid (Replica Identity columns are missing). It also gives an
> > error if the user tries to change the replica identity. However, it
> > doesn't deal with cases where the user drops and adds a different
> > primary key that has a different set of columns which can lead to
> > failure during apply on the subscriber.
>
> Hmm, yeah, I suppose we should check that the primary key is compatible
> with the column list in all publications.  (I wonder what happens in the
> interim, that is, what happens to tuples modified after the initial PK
> is dropped and before the new PK is installed.  Are these considered to
> have "replica identiy nothing"?)
>

I think so.

> > I think another issue w.r.t column filter patch is that even while
> > creating publication (even for 'insert' publications) it should check
> > that all primary key columns must be part of published columns,
> > otherwise, it can fail while applying on subscriber as it will try to
> > insert NULL for the primary key column.
>
> I'm not so sure about the primary key aspects, actually; keep in mind
> that the replica can have a different table definition, and it might
> have even a completely different primary key.  I think this part is up
> to the user to set up correctly; we have enough with just trying to make
> the replica identity correct.
>

But OTOH, the primary key is also considered default replica identity,
so I think users will expect it to work. You are right this problem
can also happen if the user defined a different primary key on a
replica but that is even a problem in HEAD (simple inserts will fail)
but I am worried about the case where both the publisher and
subscriber have the same primary key as that works in HEAD.

-- 
With Regards,
Amit Kapila.

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