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

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.

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