DELETING then INSERTING record with same PK in the same TRANSACTION

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

[https://www.niwa.co.nz/static/niwa-2018-horizontal-180.png] <https://www.niwa.co.nz&gt;
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<https://www.niwa.co.nz&gt; Facebook<https://www.facebook.com/nzniwa&gt; LinkedIn<https://www.linkedin.com/company/niwa&gt; Twitter<https://twitter.com/niwa_nz&gt; Instagram<https://www.instagram.com/niwa_science&gt;
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

On 2/9/22 10:11 AM, Andrew Hardy wrote:

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"

<table> = itinerary?

What fields is unique constraint "itinerary_pkey" actually pointing at?

There should be more to the error message. Can you provide the complete
message?

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

--
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, Feb 9, 2022 at 11:12 AM Andrew Hardy <andrew.hardy@sabstt.com>
wrote:

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

No. I cannot reproduce your claim with a trivial example on stock 13.5.

You will need to be less vague and at least provide an exact reproducer
script.

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

children.

This may indeed be relevant. Again, you need to provide an exact
reproducer, not expect others to reverse-engineer one for you.

David J.

Really appreciate the input thank you.

I shall try to provide a full script, I guess if the cascades are relevant
in going to have to also provide all of the indirect table creations and
all of the earlier inserts to those various tables.

At the very least I'll try to provide a fuller error message and a minimum
structure that will cause it to occur.

Andrew

On Wed, 9 Feb 2022, 19:26 David G. Johnston, <david.g.johnston@gmail.com>
wrote:

On 2/9/22 10:34, Brent Wood wrote:

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.

No:

\d animals
Table "public.animals"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+---------
id | integer | | not null |
cond | character varying(200) | | not null |
animal | character varying(200) | | not null |
Indexes:
"animals_pkey" PRIMARY KEY, btree (id)

delete from animals where id = 1;
DELETE 1

insert into animals values (1, 'great', 'opossum');
INSERT 0 1

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

--
Adrian Klaver
adrian.klaver@aklaver.com

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.

david.g.johnston@gmail.com wrote:

The convention on these lists is to inline or bottom post (and to trim the reply to just the pertinent parts).

Just for completeness, I expected this test to run without error. (I tried it in PG Version 14.1).

create table t(k int primary key, v text not null);
insert into t(k, v) values (1, 'one'), (2, 'two');
select k, v from t order by k;

start transaction;
delete from t where k = 1;
insert into t(k, v) values(1, 'new one');
commit;

select k, v from t order by k;

Indeed it did run without error. And I saw the results that I expected.

David G. Johnston schrieb am 09.02.2022 um 21:47:

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.

You can defer unique constraints, but not primary key constraints.

create table t
(
id integer
);

alter table t
add constraint unique_id
unique (id)
deferrable initially deferred;

On Wed, Feb 9, 2022 at 2:24 PM Thomas Kellerer <shammat@gmx.net> wrote:

David G. Johnston schrieb am 09.02.2022 um 21:47:

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.

You can defer unique constraints, but not primary key constraints.

Actually we are both wrong...

"This controls whether the constraint can be deferred. A constraint that is
not deferrable will be checked immediately after every command. Checking of
constraints that are deferrable can be postponed until the end of the
transaction (using the SET CONSTRAINTS command). NOT DEFERRABLE is the
default. Currently, only UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES
(foreign key) constraints accept this clause."

https://www.postgresql.org/docs/current/sql-createtable.html

In any case I would not expect that primary key constraints would behave
differently than any other unique constraint. The null-ness check for a PK
cannot be deferred, however.

"NOT NULL and CHECK constraints are not deferrable. Note that deferrable
constraints cannot be used as conflict arbitrators in an INSERT statement
that includes an ON CONFLICT DO UPDATE clause."

David J.

As requested here is a full script and the issue does not arise.

My sincere apologies it appears the issue is most likely with my client
programming code.
My first ever post here. I shall definitely be providing a full working
SQL script from the very start on any future post.
Many thanks and apologies again.

CREATE TABLE public.itineraryx (
dk varchar(10) NULL,
"locator" varchar(20) NOT NULL,
CONSTRAINT itinerary_pkeyx PRIMARY KEY (locator)
);

CREATE TABLE public.hotel_bookingx (
itinerary varchar(20) NULL,
"hotelName" varchar(50) NULL,
id bigserial NOT NULL,
CONSTRAINT hotel_booking_pkeyx PRIMARY KEY (id)
);

ALTER TABLE public.hotel_bookingx ADD CONSTRAINT
hotel_booking_itinerary_foreignx FOREIGN KEY (itinerary) REFERENCES
itineraryx(locator) ON DELETE CASCADE;

INSERT INTO public.itineraryx
(dk, "locator")
VALUES('tba', 'TEST0001');

INSERT INTO public.hotel_bookingx
(itinerary, "hotelName")
VALUES('TEST0001', 'Crown');

BEGIN TRANSACTION;
delete from itineraryx i where i."locator" = 'TEST0001';

INSERT INTO public.itineraryx
(dk, "locator")
VALUES('tba2', 'TEST0001');

INSERT INTO public.hotel_bookingx
(itinerary, "hotelName")
VALUES('TEST0001', 'Crown2');

COMMIT TRANSACTION;

On Wed, 9 Feb 2022 at 20:01, Andrew Hardy <andrew.hardy@sabstt.com> wrote: