Horton
Menu

Re: Column Filtering in Logical Replication

Amit Kapila <amit.kapila16@gmail.com>

View thread
From: Amit Kapila <amit.kapila16@gmail.com>
To: Tomas Vondra <tomas.vondra@enterprisedb.com>
Cc: "houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>, Peter Eisentraut <peter.eisentraut@enterprisedb.com>, Alvaro Herrera <alvherre@alvh.no-ip.org>, Justin Pryzby <pryzby@telsasoft.com>, Rahila Syed <rahilasyed90@gmail.com>, Peter Smith <smithpb2250@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>, "shiy.fnst@fujitsu.com" <shiy.fnst@fujitsu.com>
Date: 2022-03-21T11:55:28Z
Lists: pgsql-hackers 
On Sat, Mar 19, 2022 at 3:56 AM Tomas Vondra
<tomas.vondra@enterprisedb.com> wrote:
>
> On 3/18/22 15:43, Tomas Vondra wrote:
> >
>
> As for the issue reported by Shi-San about replica identity full and
> column filters, presumably you're referring to this:
>
>   create table tbl (a int, b int, c int);
>   create publication pub for table tbl (a, b, c);
>   alter table tbl replica identity full;
>
>   postgres=# delete from tbl;
>   ERROR:  cannot delete from table "tbl"
>   DETAIL:  Column list used by the publication does not cover the
>            replica identity.
>
> I believe not allowing column lists with REPLICA IDENTITY FULL is
> expected / correct behavior. I mean, for that to work the column list
> has to always include all columns anyway, so it's pretty pointless. Of
> course, we might check that the column list contains everything, but
> considering the list does always have to contain all columns, and it
> break as soon as you add any columns, it seems reasonable (cheaper) to
> just require no column lists.
>

Fair point. We can leave this as it is.

> I also went through the patch and made the naming more consistent. The
> comments used both "column filter" and "column list" randomly, and I
> think the agreement is to use "list" so I adopted that wording.
>
>
> However, while looking at how pgoutput, I realized one thing - for row
> filters we track them "per operation", depending on which operations are
> defined for a given publication. Shouldn't we do the same thing for
> column lists, really?
>
> I mean, if there are two publications with different column lists, one
> for inserts and the other one for updates, isn't it wrong to merge these
> two column lists?
>

The reason we can't combine row filters for inserts with
updates/deletes is that if inserts have some column that is not
present in RI then during update filtering (for old tuple) it will
give an error as the column won't be present in WAL log.

OTOH, the same problem won't be there for the column list/filter patch
because all the RI columns are there in the column list (for
update/delete) and we don't need to apply a column filter for old
tuples in either update or delete.

Basically, the filter rules are slightly different for row filters and
column lists, so we need them (combine of filters) for one but not for
the other. Now, for the sake of consistency with row filters, we can
do it but as such there won't be any problem or maybe we can just add
a comment for the same in code.

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