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

On Mon, May 3, 2021 at 10:42 AM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:
>
> Hi,
>
> In apply_handle_truncate, the following comment before ExecuteTruncateGuts says that it defaults to RESTRICT even if
theCASCADE 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
andsubscriber. 
> 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
thiscommand is run on the publisher, the CASCADE option is sent to the subscriber, see DecodeTruncate. But the apply
workerignores it and passes DROP_RESTRICT to ExecuteTruncateGuts. Therefore, the expectation(per the comment) is that
onthe subscriber, the behavior should be equivalent to TRUNCATE tbl_pk;, so an error is expected. But we are also
receivingthe tbl_fk in the remote rels along with tbl_pk, so the behavior is equivalent to (3) and both tbl_pk and
tbl_fkare truncated. 
>
> Does the comment still hold true? Does ignoring the CASCADE option make sense in apply_handle_truncate, as we are
receivingall 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