Обсуждение: DELETING then INSERTING record with same PK in the same TRANSACTION

Поиск
Список
Период
Сортировка

DELETING then INSERTING record with same PK in the same TRANSACTION

От
Andrew Hardy
Дата:
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


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

От
Brent Wood
Дата:
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

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

От
Adrian Klaver
Дата:
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

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

От
"David G. Johnston"
Дата:
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.

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

От
Andrew Hardy
Дата:
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 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.

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

От
Adrian Klaver
Дата:
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

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

От
"David G. Johnston"
Дата:
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.


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

От
Bryn Llewellyn
Дата:
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.

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

От
Thomas Kellerer
Дата:
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;

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

От
"David G. Johnston"
Дата:
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.

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

От
Andrew Hardy
Дата:
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:
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 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.