Re: Logical Replication - behavior of TRUNCATE ... CASCADE - Mailing list pgsql-hackers

On Thu, May 20, 2021 at 5:03 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Fri, May 7, 2021 at 6:06 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:
> >
> > On Mon, May 3, 2021 at 6:08 PM Bharath Rupireddy
> > <bharath.rupireddyforpostgres@gmail.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.

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.

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com