Re: DELETING then INSERTING record with same PK in the same TRANSACTION

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: DELETING then INSERTING record with same PK in the same TRANSACTION
Дата
Msg-id CAKFQuwYke8ndVd1xS0gHnYJngCY-REx6aL-0HYiVux2+UHYQtQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: DELETING then INSERTING record with same PK in the same TRANSACTION  (Brent Wood <Brent.Wood@niwa.co.nz>)
Ответы Re: DELETING then INSERTING record with same PK in the same TRANSACTION  (Bryn Llewellyn <bryn@yugabyte.com>)
Re: DELETING then INSERTING record with same PK in the same TRANSACTION  (Thomas Kellerer <shammat@gmx.net>)
Список pgsql-general
Дерево обсуждения
The convention on these lists is to inline or bottom post (and to trim the reply to just the pertinent parts).

On Wed, Feb 9, 2022 at 12:33 PM Brent Wood <Brent.Wood@niwa.co.nz> wrote:
Because both statements are in the transaction, the delete is not fully actioned until the commit. So it still exists in the table when you try to insert the record with the duplicate key.

A transaction makes your actions invisible (more or less) to other concurrent sessions in the system.  But so far as your own session is concerned subsequent commands get to see the changes made during previous commands.

Check if the error is generated during the transaction or at the commit stage, run it without the commit, rollback instead to check this.

You cannot defer uniqueness checks to transaction commit so either it is going to fail on the insert or it will not fail at all.
I don't see how you can do this within a transaction, someone else might?

That should be a good indicator that you are missing something, because the presence or absence of a transaction should not be impacting this at all.  This is much more likely operator error rather than a system bug, and so reproducing the error is the only reasonable first step - since the pseudo-code that was provided and the error simply do not make sense.

David J.


В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: DELETING then INSERTING record with same PK in the same TRANSACTION
Следующее
От: Bryn Llewellyn
Дата:
Сообщение: Re: DELETING then INSERTING record with same PK in the same TRANSACTION