End the execution SQL statement operation in postgresql

There are two ways to end the process:

SELECT pg_cancel_backend(PID)

Cancel background operations and roll back uncommitted things (select);

SELECT pg_terminate_backend(PID)

Interrupt session and roll back uncommitted things (select, update, delete, drop);

SELECT * FROM pg_stat_activity;

According to datid=10841

SELECT pg_terminate_backend (10841);

Added: PostgreSQL cannot start/end transactions in PL/pgSQL

I'm seeking clarification on how to ensure atomic transactions in the plpgsql function, and setting the isolation level for this specific change for the database.

In the plpgsql function shown below, I want to make sure that the BOTH is deleted and inserted successfully. When I try to wrap them in a transaction, I get an error:

Error: Unable to start/end transaction in PL/pgSQL.

If another user has deleted a custom record in this function, but before this function has the opportunity to insert a custom record, and adds default behavior for the situation (RAIN, NIGHT, 45MPH), what happens during the execution of the following function? Is there an implicit transaction wrapper insertion and removal so that if another user has changed any of the rows referenced by this function, both will be rolled back? Can I set the isolation level for this feature?

create function foo(v_weather varchar(10), v_timeofday varchar(10), v_speed varchar(10),
 v_behavior varchar(10))
 returns setof CUSTOMBEHAVIOR
 as $body$
 begin
 -- run-time error if either of these lines is un-commented
 -- start transaction ISOLATION LEVEL READ COMMITTED;
 -- or, alternatively, set transaction ISOLATION LEVEL READ COMMITTED;
  delete from CUSTOMBEHAVIOR 
  where weather = 'RAIN' and timeofday = 'NIGHT' and speed= '45MPH' ;
 -- if there is no default behavior insert a custom behavior
 if not exists
  (select id from DEFAULTBEHAVIOR where a = 'RAIN' and b = 'NIGHT' and c= '45MPH') then 
  insert into CUSTOMBEHAVIOR
  (weather, timeofday, speed, behavior)
  values
  (v_weather, v_timeofday, v_speed, v_behavior);
 end if;
 return QUERY
 select * from CUSTOMBEHAVIOR where ... ;
 -- commit;
 end
 $body$
 LANGUAGE plpgsql

A plpgsql function runs automatically in a transaction. All of this succeeds, everything fails.

I quotethe manual on plpgsql functions：

Functions and trigger procedures are always executed within a transaction established by an outer query — they cannot start or commit that transaction, since there would be no context for them to execute in. However, a block containing an EXCEPTION clause effectively forms a subtransaction that can be rolled back without affecting the outer transaction.

So, if you need to, you can catch exceptions that may occur in theory (but unlikely).

Details on trapping errors in the manual.

Your feature review and simplification:

CREATE FUNCTION foo(v_weather text
   , v_timeofday text
   , v_speed text
   , v_behavior text)
 RETURNS SETOF custombehavior AS
$body$
BEGIN
DELETE FROM custombehavior
WHERE weather = 'RAIN'
AND timeofday = 'NIGHT'
AND speed = '45MPH';
INSERT INTO custombehavior (weather, timeofday, speed, behavior)
SELECT v_weather, v_timeofday, v_speed, v_behavior
WHERE NOT EXISTS (
 SELECT 1 FROM defaultbehavior
 WHERE a = 'RAIN'
 AND b = 'NIGHT'
 AND c = '45MPH'
 );
RETURN QUERY
SELECT * FROM custombehavior WHERE ... ;
END
$body$LANGUAGE plpgsql

The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.