COULD YOU REMOVE THE PARTS OF EMAILS YOU ARE NOT RESPONDING TO WHEN REPLYING IN THE THREAD?
****** THANKS ******
[...] Then B believes that A succeeded, which is not the case.
No, just your design is unhappy
SELECT A(..) SET SESSION VARIABLE status_ok = false; -- do all, if fails there, -- then follow line fails too, or never be executed SET SESSION VARIABLE status_ok = true;
My point is that there is no commit in this code, the commit is performed *AFTER* the last set session, and it mail fail then.
or
SET SESSION VARIABLE status_ok = true TRY do something CATCH ROLLBACK SET SESSION VARIABLE status_ok = false
Both I can do in current PL
The fact that "do something" worked does not preclude the overall transaction to work and to revert "do something" and let status_ok as true.
The key issue is that the final status (commit or rollback) of the containing transaction cannot be known from within the function, so the session variables cannot reflect this status.
So somehow the status_ok variable must be (1) rolledback to previous value or (2) removed (3) set to FALSE or (4) set to NULL. It cannot be TRUE if A containing transactions has failed for the security as I understand it.
you don't need do rollback variable if you write well A
My point is that A may still fail *after* setting the variable, because it is in a transaction.
If you use patterns that I wrote - the security context will be valid always.
No: This pattern assumes that operations started in the "TRY" zone cannot fail later on... This assumption is false because of possible deferred triggers for instance. See attached example:
ok .. it is pretty artificial, but ok. In this case the reset to NULL on ROLLBACK should be enough.
NOTICE: SET secured = FALSE NOTICE: SET secured = TRUE ERROR: insert or update on table "log" violates foreign key constraint "log_sid_fkey" DETAIL: Key (sid)=(3) is not present in table "stuff".
The error occurs after secured has been set to TRUE.
It is possible only if you are use deferred constraints. It is hard to imagine this scenario in functions like A. Probably you would not to risk on rollback log information. So you will use there elog or some form of autonomous transaction.
