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

sh,On Mon, May 3, 2021 at 12:37 PM Bharath Rupireddy
<bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
>
> On Mon, May 3, 2021 at 11:59 AM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> >
> > On Mon, May 3, 2021 at 10:42 AM Bharath Rupireddy
> > <bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
> > >
> > > Hi,
> > >
> > > In apply_handle_truncate, the following comment before ExecuteTruncateGuts says that it defaults to RESTRICT even if the CASCADE option has been specified in publisher's TRUNCATE command.
> > > /*
> > > * Even if we used CASCADE on the upstream primary we explicitly default
> > > * to replaying changes without further cascading. This might be later
> > > * changeable with a user specified option.
> > > */
> > > I tried the following use case to see if that's actually true:
> > > 1) Created two tables tbl_pk (primary key), tbl_fk(references tbl_pk primary key via foreign key) on both publisher and subscriber.
> > > 2) In general, TRUNCATE tbl_pk; or TRUNCATE tbl_pk RESTRICT; would fail because tbl_fk is dependent on tbl_pk.
> > > 3) TRUNCATE tbl_pk, tbl_fk; would work because the dependent table is specified in the command.
> > > 4) TRUNCATE tbl_pk CASCADE; would work because of the CASCADE option and both tbl_pk and tbl_fk are truncated. When this command is run on the publisher, the CASCADE option is sent to the subscriber, see DecodeTruncate. But the apply worker ignores it and passes DROP_RESTRICT to ExecuteTruncateGuts. Therefore, the expectation(per the comment) is that on the subscriber, the behavior should be equivalent to TRUNCATE tbl_pk;, so an error is expected. But we are also receiving the tbl_fk in the remote rels along with tbl_pk, so the behavior is equivalent to (3) and both tbl_pk and tbl_fk are truncated.
> > >
> > > Does the comment still hold true? Does ignoring the CASCADE option make sense in apply_handle_truncate, as we are receiving all the dependent relations in the remote rels from the publisher? Am I missing something?
> > >
> > > The commit id of the feature "Logical replication support for TRUNCATE" is 039eb6e92f, and adding relevant people in cc.
> >
> > Assume this case
> > publisher: tbl_pk -> tbl_fk_pub
> > subscriber: tbl_pk-> tbl_fk_sub
> >
> > Now, in this case, this comment is true right because we are not
> > supposed to truncate tbl_fk_sub on the subscriber side and this should
> > error out.
>
> Here's what I tried, let me know if I'm wrong:
>
> On publisher:
> CREATE TABLE tbl_pk(id int primary key);
> CREATE TABLE tbl_fk(fkey int references tbl_pk(id));
> INSERT INTO tbl_pk (SELECT x FROM generate_series(1,10) x);
> INSERT INTO tbl_fk (SELECT x % 10 + 1 FROM generate_series(5,25) x);
> DROP PUBLICATION testpub;
> CREATE PUBLICATION testpub FOR TABLE tbl_pk, tbl_fk;
>
> On subscriber:
> CREATE TABLE tbl_pk(id int primary key);
> CREATE TABLE tbl_fk(fkey int references tbl_pk(id));
> DROP SUBSCRIPTION testsub;
> CREATE SUBSCRIPTION testsub CONNECTION 'host=localhost dbname=postgres
> user=bharath port=5432' PUBLICATION testpub;
>
> On both publisher and subscriber to ensure that the initial rows were
> replicated:
> SELECT count(id) FROM tbl_pk; -- non zero
> SELECT count(fkey) FROM tbl_fk; -- non zero
>
> On publisher:
> TRUNCATE tbl_pk CASCADE;
> SELECT count(id) FROM tbl_pk; -- 0
> SELECT count(fkey) FROM tbl_fk; -- 0
>
> On subscriber also we get to see 0 rows:
> SELECT count(id) FROM tbl_pk; -- 0
> SELECT count(fkey) FROM tbl_fk; -- 0
>
> But the comment says that tbl_fk shouldn't be truncated as it doesn't
> pass the cascade option to ExecuteTruncateGuts even though it was
> received from the publisher. This behaviour is not in accordance with
> the comment, right?

I think you are comparing the user-exposed behavior with the internal
code comments. The meaning of the comments is that it should not
truncate any table on subscriber using cascade, because there might be
some subscriber-specific relations that depend upon the primary table
and those should not get truncated as a side-effect of the cascade.

For example, you can slightly change your example as below
> On subscriber:
> CREATE TABLE tbl_pk(id int primary key);
> CREATE TABLE tbl_fk_sub(fkey int references tbl_pk(id)); -> this table doesn't refer to tbl_pk on the publisher

So now as part of the truncate tbl_pk the tbl_fk_subould not get
truncated and that is what the comment is trying to say.

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