Re: [PATCH] Add `truncate` option to subscription commands

On Mon, Oct 12, 2020 at 3:44 AM David Christensen <david@endpoint.com> wrote:
>
>
> On Oct 11, 2020, at 1:14 PM, Euler Taveira <euler.taveira@2ndquadrant.com> wrote:
>
> 
> On Fri, 9 Oct 2020 at 15:54, David Christensen <david@endpoint.com> wrote:
>>
>>
>> Enclosed find a patch to add a “truncate” option to subscription commands.
>>
>> When adding new tables to a subscription (either via `CREATE SUBSCRIPTION` or `REFRESH PUBLICATION`), tables on the
targetwhich are being newly subscribed will be truncated before the data copy step.  This saves explicit coordination
ofa manual `TRUNCATE` on the target tables and allows the results of the initial data sync to be the same as on the
publisherat the time of sync. 
>>
>> To preserve compatibility with existing behavior, the default value for this parameter is `false`.
>>
>
> Truncate will fail for tables whose foreign keys refer to it. If such a feature cannot handle foreign keys, the
usefulnesswill be restricted. 
>
>
> This is true for existing “truncate” with FKs, so doesn’t seem to be any different to me.
>

What would happen if there are multiple tables and truncate on only
one of them failed due to FK check? Does it give an error in such a
case, if so will the other tables be truncated?

> Hypothetically if you checked all new tables and could verify if there were FK cycles only already in the new tables
beingadded then “truncate cascade” would be fine. Arguably if they had existing tables that were part of an FK that
wasn’tfully replicated they were already operating brokenly. 
>

I think if both PK_table and FK_table are part of the same
subscription then there should be a problem as both them first get
truncated? If they are part of a different subscription (or if they
are not subscribed due to whatever reason) then probably we need to
deal such cases carefully.

--
With Regards,
Amit Kapila.