How to check if running in autonomous transaction?

Question

In a trigger, I need to do some stuffs only when the code is not running from autonomous transaction.

Is there a way to detect if the code is running in the context of an autonomous transaction?

Interesting question. I don't think you can (it is basically just an extra transaction that the PSQL code starts and manages on your behalf), but I'm not sure. I'll do some digging. Is there a specific use case you have in mind that you want to know this? — Mark Rotteveel, Aug 05 '22 at 08:10
@MarkRotteveel Yes, it's an `ON START TRANSACTION` trigger, and I need that each transaction, except for autonomous transactions, logs some informations who will be available for all the users. These informations are `IDTRANSACTION`, `USER` (and other..) and I need them to be available for all other users (included not admin users, who can't see other user records in `mon$...` tables -> If you know any other way to make other users attachments/transactions informations available for not-admin users, it would help me alot). — Fabrizio, Aug 05 '22 at 08:54
For this reason, I thought to simply write the informations from the `ON START TRANSACTION` trigger and use a `READ UNCOMMITTED` transaction to read them, but it seems that [`READ UNCOMMITTED` is not supported by Firebird](https://stackoverflow.com/q/73162888/4528159). So, I'm trying to write the informations by using an autonomous transaction, but it obviously causes the `ON START TRANSACTION` trigger to be executed again and again, blocking everything. — Fabrizio, Aug 05 '22 at 08:55
You could upgrade to Firebird 4.0 and grant your users the system privilege `MONITOR_ANY_ATTACHMENT`, though that does come with caveats, like context variables being visible to all those users, and I'm not sure if this allows users to kill other's statements or attachments (through delete on the monitoring tables), or just view them. — Mark Rotteveel, Aug 05 '22 at 09:02
Thanks for your suggestion, this is surely one more reason for upgrading to Firebird 4.0. Anyhow, I don't think we would be able to upgrade soon. Until that time, I would be glad to find a solution for FB 3.5 (It would be sufficient to be able to recognize the autonomous transaction and I should be able to avoid the infinite recursion) — Fabrizio, Aug 05 '22 at 09:35

Fabrizio · Answer 1 · 2022-10-05T13:24:04.827

I found an alternative solution by using a context variable due to avoid the infinite recursion.

CREATE OR ALTER TRIGGER d_logtran
ACTIVE ON TRANSACTION START POSITION 0
AS
DECLARE VARIABLE v_val VARCHAR(1);
DECLARE VARIABLE v_idconnection bigint_t;
DECLARE VARIABLE v_idtransaction bigint_t;
DECLARE VARIABLE v_username shortdescription_t;
BEGIN
  v_val = RDB$GET_CONTEXT('USER_SESSION', 'LOCK');

  IF (v_val = 'T') THEN
    EXIT;

  RDB$SET_CONTEXT('USER_SESSION', 'LOCK', 'T');

  v_idconnection = CURRENT_CONNECTION;
  v_idtransaction = CURRENT_TRANSACTION;
  v_username = CURRENT_USER;

  IN AUTONOMOUS TRANSACTION DO
    BEGIN
      INSERT INTO log_transaction
        (idconnection, idtransaction, username)
      VALUES
        (:v_idconnection, :v_idtransaction, :v_username);
    END

  RDB$SET_CONTEXT('USER_SESSION', 'LOCK', NULL);
END

How to check if running in autonomous transaction?

1 Answers1