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

On Thu, May 20, 2021 at 5:03 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> 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.

This proposal can be useful even after the DDL replication feature
gets in. I think having a use case like the following is quite
possible(in theory) without any differences in the DDLs in both the
publisher and subscriber tables: a subscriber table which is
subscribed to a publisher, can act as primary key table for other
subscriber tables called foreign key tables, refer to the use case I
specified at [1]. In that case, TRUNCATE ... CASCADE on the publisher
table, will not truncate the subscriber foreign key tables. The
proposal here is to pass on the CASCADE option to the subscriber so
that the subscriber foreign key tables too get truncated. And I agree
that this is a very narrow problem to solve. I came across the comment
before ExecuteTruncateGuts in logical/worker.c, so I thought it's
worth doing it.

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

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

Thanks. I will respond in that thread.

With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com