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

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(dot)hardy(at)sabstt(dot)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(dot)g(dot)johnston(at)gmail(dot)com>
> wrote:
>
>> On Wed, Feb 9, 2022 at 11:12 AM Andrew Hardy <andrew(dot)hardy(at)sabstt(dot)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.
>>
>>