Re: Allowing TRUNCATE of FK target when session_replication_role=replica

On Tue, Oct 31, 2023, at 5:09 AM, Hannu Krosing wrote:
> Currently we do not allow TRUNCATE of a table when any Foreign Keys
> point to that table.

It is allowed iif you *also* truncate all tables referencing it.

> At the same time we do allow one to delete all rows when
> session_replication_role=replica

That's true.

> This causes all kinds of pain when trying to copy in large amounts of
> data, especially at the start of logical replication set-up, as many
> optimisations to COPY require the table to be TRUNCATEd .
>
> The main two are ability to FREEZE while copying and the skipping of
> WAL generation in case of wal_level=minimal, both of which can achieve
> significant benefits when data amounts are large.

The former is true but the latter is not. Logical replication requires
wal_level = logical. That's also true for skipping FSM.

> Is there any reason to not allow TRUNCATE when
> session_replication_role=replica ?

That's basically the same proposal as [1]. That patch was rejected because it
was implemented in a different way that doesn't require the
session_replication_role = replica to bypass the FK checks.

That's basically the same proposal as [1]. That patch was rejected because it
was implemented in a different way that doesn't require the
session_replication_role = replica to bypass the FK checks.

There are at least 3 cases that can benefit from this feature:

1) if your scenario includes an additional table only in the subscriber
side that contains a foreign key to a replicated table then you will break your
replication like

ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "foo" references "bar".
HINT: Truncate table "foo" at the same time, or use TRUNCATE ... CASCADE.
CONTEXT: processing remote data for replication origin "pg_16406" during
message type "TRUNCATE" in transaction 12880, finished at 0/297FE08

and you have to manually fix your replication. If we allow
session_replication_role = replica to bypass FK check for TRUNCATE commands, we
wouldn't have an error. I'm not saying that it is a safe operation for logical
replication scenarios. Maybe it is not because table foo will contain invalid
references to table bar and someone should fix it in the subscriber side.
However, the current implementation already allows such orphan rows due to
session_replication_role behavior.

2) truncate table at subscriber side during the initial copy. As you mentioned,
this feature should take advantage of the FREEZE and FSM optimizations. There
was a proposal a few years ago [2].

3) resynchronize a table. Same advantages as item 2.

> Unless there are any serious objections, I will send a patch to also
> allow TRUNCATE in this case.
>

You should start checking the previous proposal [1].

[1] https://www.postgresql.org/message-id/ff835f71-3c6c-335e-4c7b-b9e1646cf3d7%402ndquadrant.it
[2] https://www.postgresql.org/message-id/CF3B6672-2A43-4204-A60A-68F359218A9B%40endpoint.com

--
Euler Taveira
EDB https://www.enterprisedb.com/