MIME-Version: 1.0
From: Hannu Krosing <hannuk@google.com>
Date: Tue, 31 Oct 2023 09:09:24 +0100
Message-ID: <CAMT0RQQfF3k_Ri7-zvQ2X=mt=d0Qx5qGJojk=1P8oaL6yU=+1w@mail.gmail.com>
Subject: Allowing TRUNCATE of FK target when session_replication_role=replica
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAMT0RQQfF3k_Ri7-zvQ2X%3Dmt%3Dd0Qx5qGJojk%3D1P8oaL6yU%3D%2B1w%40mail.gmail.com>
Precedence: bulk

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