Re: Allowing TRUNCATE of FK target when session_replication_role=replica
| От | Euler Taveira |
|---|---|
| Тема | Re: Allowing TRUNCATE of FK target when session_replication_role=replica |
| Дата | |
| Msg-id | 6e04dc71-717c-486d-aead-52079bf096f7@app.fastmail.com обсуждение исходный текст |
| Ответ на | Allowing TRUNCATE of FK target when session_replication_role=replica (Hannu Krosing <hannuk@google.com>) |
| Ответы |
Re: Allowing TRUNCATE of FK target when session_replication_role=replica
(Hannu Krosing <hannuk@google.com>)
|
| Список | pgsql-hackers |
On Tue, Oct 31, 2023, at 5:09 AM, Hannu Krosing wrote:
Currently we do not allow TRUNCATE of a table when any Foreign Keyspoint 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 whensession_replication_role=replica
That's true.
This causes all kinds of pain when trying to copy in large amounts ofdata, especially at the start of logical replication set-up, as manyoptimisations to COPY require the table to be TRUNCATEd .The main two are ability to FREEZE while copying and the skipping ofWAL generation in case of wal_level=minimal, both of which can achievesignificant 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 whensession_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 alsoallow TRUNCATE in this case.
You should start checking the previous proposal [1].
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Nathan BossartДата:
Сообщение: Re: [17] Special search_path names "!pg_temp" and "!pg_catalog"