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

Поиск
Список
Период
Сортировка
Искать
От
Brent Wood
Тема
Re: DELETING then INSERTING record with same PK in the same TRANSACTION
Дата
Msg-id
SY4PR01MB7076851903BD922E73E4988AA12E9@SY4PR01MB7076.ausprd01.prod.outlook.com
Ответ на
DELETING then INSERTING record with same PK in the same TRANSACTION (Andrew Hardy)
Список
Дерево обсуждения
DELETING then INSERTING record with same PK in the same TRANSACTION Andrew Hardy <andrew.hardy@sabstt.com>
Re: DELETING then INSERTING record with same PK in the same TRANSACTION "David G. Johnston" <david.g.johnston@gmail.com>
Re: DELETING then INSERTING record with same PK in the same TRANSACTION Andrew Hardy <andrew.hardy@sabstt.com>
Re: DELETING then INSERTING record with same PK in the same TRANSACTION Andrew Hardy <andrew.hardy@sabstt.com>
Re: DELETING then INSERTING record with same PK in the same TRANSACTION Adrian Klaver <adrian.klaver@aklaver.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 Adrian Klaver <adrian.klaver@aklaver.com>
Re: DELETING then INSERTING record with same PK in the same TRANSACTION "David G. Johnston" <david.g.johnston@gmail.com>
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>
Re: DELETING then INSERTING record with same PK in the same TRANSACTION "David G. Johnston" <david.g.johnston@gmail.com>
My take on this...

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.

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

I don't see how you can do this within a transaction, someone else might?



Brent Wood

Principal Technician, Fisheries
NIWA
DDI:  +64 (4) 3860529

From: Andrew Hardy <andrew.hardy@sabstt.com>
Sent: Thursday, February 10, 2022 07:11
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: DELETING then INSERTING record with same PK in the same TRANSACTION
 
Hi,

When I:

Begin a transaction
DELETE from <table> where id (PK) = <somevalue>
INSERT INTO <table> VALUES (<values - same PK>)
...
...
...
COMMIT

I get 

insert into "itinerary" ("dk", "locator") values ($1, $2) - duplicate key value violates unique constraint "itinerary_pkey"

Do I need some particular kind of settings on my transaction to be able to delete and insert afresh in the same transaction?

In case it is relevant - the first delete will lead to cascaded deletes on children.

Alternatively I wonder if I change the PK column value to "<somevalue>-FORDELETION" will I be free to insert under the same original PK value in the same transaction, then delete the FORDELETE item just before committing or will I hit the same issue?

Thanks,

Andrew


Brent Wood
Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery
+64-4-386-0529

National Institute of Water & Atmospheric Research Ltd (NIWA)
301 Evans Bay Parade Hataitai Wellington New Zealand
Connect with NIWA: niwa.co.nz Facebook LinkedIn Twitter Instagram
To ensure compliance with legal requirements and to maintain cyber security standards, NIWA's IT systems are subject to ongoing monitoring, activity logging and auditing. This monitoring and auditing service may be provided by third parties. Such third parties can access information transmitted to, processed by and stored on NIWA's IT systems
В списке pgsql-general по дате отправления
Предыдущее
От: Andrew Hardy
Дата:
Следующее
От: Adrian Klaver
Дата:
FAQ