Re: [PATCH] session_replication_role = replica with TRUNCATE

On 18 January 2018 at 17:14, Peter Eisentraut
<peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
> On 12/29/17 07:01, Marco Nenciarini wrote:
>> The current behavior of session_replication_role = replica with TRUNCATE
>> is not the same of with the other commands.
>> It does not check FKs for INSERT/UPDATE/DELETE but it does for TRUNCATE,
>> so one cannot execute TRUNCATE on a table when it is possible to DELETE
>> from table without WHERE clause.
>>
>> I'm attaching a simple patch to make TRUNCATE match behavior of DELETE
>> for session_replication_role = replica.
>
> I'm wondering whether this shouldn't be fixed the other way around,
> namely have foreign keys always enforced on replicas.
>
> I'm not aware of an explanation why it currently works the way it does,
> other than that FKs happen to be implemented by triggers and triggers
> happen to work that way. But I think it's pretty bogus that logical
> replication subscriptions can insert data that violates constraints.
> It's also weird that you can violate deferred unique constraints but not
> immediate ones (I think, not tested).

The explanation is two-fold.

1. If we pass valid data from the publisher, it should still be valid
data when it gets there. If we cannot apply changes then the
subscriber is broken and the subscription cannot continue, so
executing the a constraint trigger does not solve the problem. (That
is why we will eventually be sending patches to handle breakage, which
is what you need for multi-master/BDR). It's not the role of this
patch to solve that problem, this patch just completes the set of
operations that can be successfully published.

2. If we know it already passes, then executing the constraint trigger
is just wasted cycles.

The subscriber side already has various requirements, such as manually
created DDL, so requiring sensible FKs is not so bad. This situation
will improve over next few years.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services