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

On Sat, May 22, 2021 at 10:33 AM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
> 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.
>
> I agree we suggest having the same schema but we still allow something
> extra on the subscriber side, e.g if the publisher table has T(a,b)
> then we allow the subscriber to have T(a,b,c) right? Since this is
> logical replication is always good that we don't enforce on what
> schema/dependent table subscriber can have unless it is of utmost
> necessity. That's the reason we enforce that at least the table name
> of the publisher should match on subscriber and at least the column
> name of the publisher should be there on the subscriber, but there is
> no restriction on the subscriber to have a few extra columns. I think
> if we allow subscribers to have some extra FK table on the subscribed
> table then that will only improve the flexibility. In the current
> case since we don't want to truncate the subscriber's local table, we
> are restricting the cascade but then we are restricting the subscriber
> to have any FK table on the subscribed table which I think is a
> restriction and it will be good to allow this based on some parameter.
>

I don't deny that this can allow some additional cases than we allow
today but was just not sure whether users really need it. If we want
to go with such an option then as mentioned earlier, we should
consider another proposal for subscriber-side truncate [1] because we
might need a cascade operation there as well but for a slightly
different purpose.

> Sad that, if we assume that the subscriber schema/dependent table
> should always be the same as primary then shouldn't we just do the
> CASCADE truncate if the publisher is doing that. We are not doing
> that to protect the truncation of some of the local tables on
> subscribers.
>
> I think DDL replication should not change this. DDL replication will
> replicate all object from publisher to subscriber but that doesn't
> mean that subscriber can not create some extra tables which are
> dependent on the replicated table. I think such an extension is good
> for logical replication.
>

Fair enough. I mentioned DDL replication because once we have that
then probably lesser users want to manually perform schema alterations
for the replicated objects.

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

--
With Regards,
Amit Kapila.