Re: Logical Replication - behavior of TRUNCATE ... CASCADE

On Fri, May 7, 2021 at 6:06 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
> On Mon, May 3, 2021 at 6:08 PM Bharath Rupireddy
> <bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
> >
> > Having said that, isn't it good if we can provide a subscription
> > (CREATE/ALTER) level option say "cascade"(similar to other options
> > such as binary, synchronous_commit, stream) default being false, when
> > set to true, we send upstream CASCADE option to ExecuteTruncateGuts in
> > apply_handle_truncate? It will be useful to truncate all the dependent
> > tables in the subscriber. Users will have to use it with caution
> > though.
>
> I think this could be a useful feature in some cases. Suppose
> subscriber has some table that is dependent on the subscribed table,
> in such case if the main table gets truncated it will always error out
> in subscriber, which is fine. But if user doesn’t want error and he
> is fine even if the dependent table gets truncated so I feel there
> should be some option to set that.
>

Such a case is possible in theory but why would the user need it? We
generally recommend having the same schema for relations between
publishers and subscribers, so won't that mean that there is less
chance of such cases? And after we have DDL replication, won't
defining a different schema for replicated objects be difficult to
maintain.

Having said that, I see a different use case of such an option which
is related to the proposal [1] where the patch provides a truncate
option to truncate tables before initial sync. The cascade option
could be useful in that feature to resolve some of the PK-FK issues
raised in that thread.

[1] - https://www.postgresql.org/message-id/CF3B6672-2A43-4204-A60A-68F359218A9B%40endpoint.com

--
With Regards,
Amit Kapila.