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

On Mon, May 3, 2021 at 1:02 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
> 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.

Thanks. I was of the thinking that the subscriber table can not have
references to other subscriber-local tables and they should also be
having the same column constraints as the publisher table columns.
But I was wrong. I tried the use case [1] where the subscriber table
tbl_pk, that was subscribed to the changes from the publisher, is
being referenced by another subscriber-local table tbl_fk. In this
case, the comment and the code that sends only RESTRICT behaviour
ignoring the upstream CASCADE option make sense.

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.

Note that the comment already says this:
/*
* 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.
*/

Thoughts?

[1]
On publisher:
DROP TABLE tbl_pk CASCADE;
CREATE TABLE tbl_pk(id int primary key);
INSERT INTO tbl_pk (SELECT x FROM generate_series(1,10) x);
DROP PUBLICATION testpub;
CREATE PUBLICATION testpub FOR TABLE tbl_pk;

On subscriber:
DROP TABLE tbl_pk CASCADE;
CREATE TABLE tbl_pk(id int primary key);
DROP TABLE tbl_fk;
CREATE TABLE tbl_fk(id1 int references tbl_pk(id));
DROP SUBSCRIPTION testsub;
CREATE SUBSCRIPTION testsub CONNECTION 'host=localhost dbname=postgres
user=bharath port=5432' PUBLICATION testpub;
INSERT INTO tbl_fk (SELECT x FROM generate_series(1,10) x);

On publisher:
TRUNCATE tbl_pk CASCADE;
SELECT count(id) FROM tbl_pk; -- 0

On subscriber we get error, because the RESTRICT option is passed to
ExecuteTruncateGuts in logical apply worker and the table tbl_pk is
referenced by tbl_fk, so tbl_pk is not truncated.
SELECT count(id) FROM tbl_pk; -- non-zero

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