Horton
Menu

Re: Column Filtering in Logical Replication

Amit Kapila <amit.kapila16@gmail.com>

View thread
From: Amit Kapila <amit.kapila16@gmail.com>
To: Rahila Syed <rahilasyed90@gmail.com>
Cc: Tomas Vondra <tomas.vondra@enterprisedb.com>, Alvaro Herrera <alvherre@alvh.no-ip.org>, Peter Smith <smithpb2250@gmail.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2021-08-12T08:29:27Z
Lists: pgsql-hackers 
On Thu, Aug 12, 2021 at 8:40 AM Rahila Syed <rahilasyed90@gmail.com> wrote:
>
>>
>> Can you please explain why you have the restriction for including
>> replica identity columns and do we want to put a similar restriction
>> for the primary key? As far as I understand, if we allow default
>> values on subscribers for replica identity, then probably updates,
>> deletes won't work as they need to use replica identity (or PK) to
>> search the required tuple. If so, shouldn't we add this restriction
>> only when a publication has been defined for one of these (Update,
>> Delete) actions?
>
>
> Yes, like you mentioned they are needed for Updates and Deletes to work.
> The restriction for including replica identity columns in column filters exists because
> In case the replica identity column values did not change, the old row replica identity columns
> are not sent to the subscriber, thus we would need new replica identity columns
> to be sent to identify the row that is to be Updated or Deleted.
> I haven't tested if it would break Insert as well  though. I will update the patch accordingly.
>

Okay, but then we also need to ensure that the user shouldn't be
allowed to enable the 'update' or 'delete' for a publication that
contains some filter that doesn't have replica identity columns.

>>
>> Another point is what if someone drops the column used in one of the
>> publications? Do we want to drop the entire relation from publication
>> or just remove the column filter or something else?
>>
>
> Thanks for pointing this out. Currently, this is not handled in the patch.
> I think dropping the column from the filter would make sense on the lines
> of the table being dropped from publication, in case of drop table.
>

I think it would be tricky if you want to remove the column from the
filter because you need to recompute the entire filter and update it
again. Also, you might need to do this for all the publications that
have a particular column in their filter clause. It might be easier to
drop the entire filter but you can check if it is easier another way
than it is good.

>>
>> Do we want to consider that the columns specified in the filter must
>> not have NOT NULL constraint? Because, otherwise, the subscriber will
>> error out inserting such rows?
>>
> I think you mean columns *not* specified in the filter must not have NOT NULL constraint
> on the subscriber, as this will break during insert, as it will try to insert NULL for columns
> not sent by the publisher.
>

Right.

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