Horton
Menu

Re: Column Filtering in Logical Replication

Amit Kapila <amit.kapila16@gmail.com>

View thread
From: Amit Kapila <amit.kapila16@gmail.com>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>, 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-14T11:51:41Z
Lists: pgsql-hackers 
On Wed, Jan 12, 2022 at 2:40 AM Justin Pryzby <pryzby@telsasoft.com> wrote:
>
> Is there any coordination between the "column filter" patch and the "row
> filter" patch ?  Are they both on track for PG15 ?  Has anybody run them
> together ?
>

The few things where I think we might need to define some common
behavior are as follows:

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.

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.

2. Handling of partitioned tables vs. Replica Identity (RI): When
adding a partitioned table with a column list to the publication (with
publish_via_partition_root = false), we should check the Replica
Identity of all its leaf partition as the RI on the partition is the
one actually takes effect when publishing DML changes. We need to
check RI while attaching the partition as well, as the newly added
partitions will automatically become part of publication if the
partitioned table is part of the publication. If we don't do this the
later deletes/updates can fail.

All these cases are dealt with in row filter patch because of the
on-the-fly check which means we check the validation of columns in row
filters while actual operation update/delete via
CheckCmdReplicaIdentity and cache the result of same for future use.
This is inline with existing checks of RI vs. operations on tables.
The primary reason for this was we didn't want to handle validation of
row filters at so many places.

3. Tablesync.c handling: Ideally, it would be good if we have a single
query to fetch both row filters and column filters but even if that is
not possible in the first version, the behavior should be same for
both queries w.r.t partitioned tables, For ALL Tables and For All
Tables In Schema cases.

Currently, the column filter patch doesn't seem to respect For ALL
Tables and For All Tables In Schema cases, basically, it just copies
the columns it finds through some of the publications even if one of
the publications is defined as For All Tables. The row filter patch
ignores the row filters if one of the publications is defined as For
ALL Tables and For All Tables In Schema.

For row filter patch, if the publication contains a partitioned table,
the publication parameter publish_via_partition_root determines if it
uses the partition row filter (if the parameter is false, the default)
or the root partitioned table row filter and this is taken care of
even during the initial tablesync.

For column filter patch, if the publication contains a partitioned
table, it seems that it finds all columns that the tables in its
partition tree specified in the publications, whether
publish_via_partition_root is true or false.

We have done some testing w.r.t above cases with both patches and my
colleague will share the results.

-- 
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