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

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(dot)Wood(at)niwa(dot)co(dot)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.