PostgreSQL cannot begin/end transactions in PL/pgSQL
Solution 1
A plpgsql function automatically runs inside a transaction. It all succeeds or it all fails. The manual:
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. For more about that see Section 42.6.6.
So, if you need to, you can catch an exception that theoretically might occur (but is very unlikely).
Details on trapping errors in the manual.
Your function reviewed and simplified:
CREATE FUNCTION foo(v_weather text
, v_timeofday text
, v_speed text
, v_behavior text)
RETURNS SETOF custombehavior
LANGUAGE plpgsql AS
$func$
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 FROM defaultbehavior
WHERE a = 'RAIN'
AND b = 'NIGHT'
AND c = '45MPH'
);
RETURN QUERY
SELECT * FROM custombehavior WHERE ... ;
END
$func$;
If you actually need to begin/end transactions like indicated in the title look to SQL procedures in Postgres 11 or later (CREATE PROCEDURE
). See:
Solution 2
Update: after PostgreSQL version 11. you can control transaction inside Store Procedure.
===== Before Version 10:
START TRANSACTION;
select foo() ;
COMMIT;
"Unfortunately Postgres has no stored procedures, so you always need to manage the transaction in the calling code" – a_horse_with_no_name
Transaction in an exception block - how?
Tim
Updated on July 09, 2022Comments
-
Tim almost 2 years
I am seeking clarification of how to ensure an atomic transaction in a plpgsql function, and where the isolation level is set for this particular change to the database.
In the plpgsql function shown below, I want to make sure that BOTH the deletion AND the insertion succeed. I am getting an error when I try to wrap them in a single transaction:
ERROR: cannot begin/end transactions in PL/pgSQL
What happens during execution of the function below if another user has added a default behavior for circumstances ('RAIN', 'NIGHT', '45MPH') after this function has deleted the custom row but before it has had a chance to insert the custom row? Is there an implicit transaction wrapping the insert and delete so that both are rolled back if another user has changed either of the rows referenced by this function? Can I set the isolation level for this function?
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;
-
Tim over 11 yearsthank you. For some odd reason I have difficulty making sense of the postgreSQL documentation, which has a reputation for being excellent. I am not yet "on the wavelength" of the author(s). For example, I found this unclear:
within a transaction established by an outer query
. What "outer query" is the document referring to? Is "implicit" rather than "outer" what is intended here? -
Erwin Brandstetter over 11 yearsThe point is: a function has to be called to be executed. Like
SELECT foo()
. It always runs inside a transaction as a whole. No exceptions. You can have subtransaction like I hinted .. -
Tim over 11 yearsAh, now I get it, the SELECT instantiates the transaction. Danke, nochmal.
-
Charlie 木匠 over 2 yearsThanks Laurenz Albe. Answer was updated.
-
Meglio almost 2 years@Tim note that it is not the case that one call to a function will run in a separate transaction. It will run inside the existing transaction, if any. And if none exist, then SELECT will form a separate transaction, within which your function will be called.