Re: Column Filtering in Logical Replication

On Sat, Sep 4, 2021 at 10:12 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Thu, Sep 2, 2021 at 2:51 AM Rahila Syed <rahilasyed90(at)gmail(dot)com> wrote:
> >
> >>>
> >>> 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.
> >> I will look into fixing this. Probably this won't be a problem in
> >> case the column is auto generated or contains a default value.
> >>
> >
> > I am not sure if this needs to be handled. Ideally, we need to prevent the subscriber tables from having a NOT NULL
> > constraint if the publisher uses column filters to publish the values of the table. There is no way
> > to do this at the time of creating a table on subscriber.
> >
> > As this involves querying the publisher for this information, it can be done at the time of initial table synchronization.
> > i.e error out if any of the subscribed tables has NOT NULL constraint on non-filter columns.
> > This will lead to the user dropping and recreating the subscription after removing the
> > NOT NULL constraint from the table.
> > I think the same can be achieved by doing nothing and letting the subscriber error out while inserting rows.
> >
>
> That makes sense and also it is quite possible that users don't have
> such columns in the tables on subscribers. I guess we can add such a
> recommendation in the docs instead of doing anything in the code.
>
> Few comments:
> ============
>

Did you give any thoughts to my earlier suggestion related to syntax [1]?

[1] - https://www.postgresql.org/message-id/CAA4eK1J9b_0_PMnJ2jq9E55bcbmTKdUmy6jPnkf1Zwy2jxah_g%40mail.gmail.com

--
With Regards,
Amit Kapila.