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-09T10:45:32Z
Lists: pgsql-hackers 
On Mon, Aug 9, 2021 at 3:59 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Mon, Aug 9, 2021 at 1:36 AM Rahila Syed <rahilasyed90@gmail.com> wrote:
> >
> >> Having said that, I'm not sure I agree with this design decision; what I
> >> think this is doing is hiding from the user the fact that they are
> >> publishing columns that they don't want to publish.  I think as a user I
> >> would rather get an error in that case:
> >
> >
> >>   ERROR:  invalid column list in published set
> >>   DETAIL:  The set of published commands does not include all the replica identity columns.
> >
> >
> >> or something like that.  Avoid possible nasty surprises of security-
> >> leaking nature.
> >
> >
> > Ok, Thank you for your opinion. I agree that giving an explicit error in this case will be safer.
> >
>
> +1 for an explicit error in this case.
>
> 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?
>
> 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?
>
> 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 noticed that other databases provide this feature [1] and they allow
users to specify "Columns that are included in Filter" or specify "All
columns to be included in filter except for a subset of columns". I am
not sure if want to provide both ways in the first version but at
least we should consider it as a future extensibility requirement and
try to choose syntax accordingly.

[1] - https://docs.oracle.com/en/cloud/paas/goldengate-cloud/gwuad/selecting-columns.html#GUID-9A851C8B-48F7-43DF-8D98-D086BE069E20

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