Обсуждение: Allowing TRUNCATE of FK target when session_replication_role=replica

Hi

Currently we do not allow TRUNCATE of a table when any Foreign Keys
point to that table.

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

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.

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

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


Best Regards
Hannu

Currently we do not allow TRUNCATE of a table when any Foreign Keys

point to that table.

At the same time we do allow one to delete all rows when

session_replication_role=replica

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.

Is there any reason to not allow TRUNCATE when

session_replication_role=replica ?

Unless there are any serious objections, I will send a patch to also

allow TRUNCATE in this case.

Thanks for the pointers.

One thing though re:
> The former is true but the latter is not. Logical replication requires
> wal_level = logical. That's also true for skipping FSM.

wal_level=logical is only needed *at provider* side, at least when
running pglogical.

Also, even for native logical replication it is possible to disconnect
the initial copy from CDC streaming, in which case again you can set
wal_level=minimal on the target side.

Will check the [1] and [2] and come back with more detailed proposal.

---
Best regards,
Hannu




On Tue, Oct 31, 2023 at 5:56 PM Euler Taveira <euler@eulerto.com> wrote:
>
> 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/
>

One thing though re:

> The former is true but the latter is not. Logical replication requires

> wal_level = logical. That's also true for skipping FSM.

wal_level=logical is only needed *at provider* side, at least when

running pglogical.