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

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?
If we see why this is so: the publisher sends both tbl_pk and tbl_fk
rels to the subscriber and the TRUNCATE tbl_pk, tbl_fk; is allowed
(see the code in heap_truncate_check_FKs) even if RESTRICT option is
specified.

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