How to retrieve the name of the active SAVEPOINT in Informix

Is there a way in Informix (v12 or higher) to retrieve the name of the current SAVEPOINT?

In Oracle there is something similar: You can name the transaction using SET TRANSACTION NAME and then select the transaction name from v$transaction:

SELECT name 
    FROM v$transaction
    WHERE xidusn
          || '.'
          || xidslot
          || '.'
          || xidsqn = DBMS_TRANSACTION.LOCAL_TRANSACTION_ID;

That is not very straightforward, but it does the trick. Effectively we can use that to have a transaction scoped variable (yes, that is ugly, but it works for years now).

We have a mechanism based on this and would like to port that to Informix. Is there a way to do that?

Of course, if there is a different mechanism providing transaction scoped variables (so DEFINE GLOBAL is not what we are looking for), that would be helpful, too, but I doubt, there is one.

Solution

Thank you all for your comments so far.

Let me show the solution I have come up with. It is just a work in progress idea, but I hope it will lead somewhere: I will need a "audit_lock" table which always contains a record for the current transaction carrying information about the current transaction, especially a username and a unique transaction_id (UUID or similar). That row will be inserted on starting the transaction and deleted before committing it.

Then I have a generic audit_trail table containing the audited information.

All audited tables fill the generic audit trail table using triggers, serializing each audited column into a separate record of the generic audit trail table.

The audit_lock and the audit_trail table need to use row locking. Also to avoid read locks on the audit_lock table we need to set the isolation level to COMMITTED READ LAST COMMITTED. If your use case does not support that, the suggested pattern does not work.

Here's the DDL:

CREATE TABLE audit_lock
(
   transaction_id varchar(40) primary key,
   username varchar(40)
);

alter table audit_lock
lock mode(ROW);

CREATE TABLE audit_trail
(
   id       serial primary key,
   tablename varchar(255) NOT NULL,
   record_id numeric(10) NOT NULL,
   username varchar(40) NOT NULL,
   transaction_id varchar(40) NOT NULL,
   changed_column_name varchar(40),
   old_value varchar(40),
   new_value varchar(40),
   operation varchar(40) NOT NULL,
   operation_date datetime year to second NOT NULL
);

alter table audit_trail
lock mode(ROW);

Now we need to have the audited table:

CREATE TABLE audited_table
(
   id serial,
   somecolumn varchar(40)
);

And the table has an insert trigger writing into the audit_trail:

CREATE PROCEDURE proc_trigger_audit_audited_table () 
REFERENCING OLD AS o NEW AS n FOR audited_table;

INSERT INTO audit_trail
(
  tablename,
  record_id,
  username,
  transaction_id,
  changed_column_name,
  old_value,
  new_value,
  operation,
  operation_date
)
VALUES
(
  'audited_table',
  n.id,
  (SELECT username FROM audit_lock),
  (SELECT transaction_id FROM audit_lock),
  'somecolumn',
  '',
  n.somecolumn,
  'INSERT',
  sysdate
);

END PROCEDURE;

CREATE TRIGGER audit_insert_audited_table INSERT ON audited_table REFERENCING NEW AS post
  FOR EACH ROW(EXECUTE PROCEDURE proc_trigger_audit_audited_table() WITH TRIGGER REFERENCES);

Now let's use that: First the caller of the transaction needs to generate a transaction_id for himself, maybe using a UUID generation mechanism. In the example below the transaction_id is simply '4711'.

BEGIN WORK;

SET ISOLATION TO COMMITTED READ LAST COMMITTED; --should be set globally 

-- Issue the generation of the audit_lock entry at the beginnig of each transaction 
insert into audit_lock (transaction_id, username) values ('4711', 'userA');

-- Is it there?
select * from audit_lock;

-- do data manipulation stuff
insert into audited_table (somecolumn) values ('valueA');

-- Issue that at the end of each transaction 
delete from audit_lock
where transaction_id = '4711';

commit;

In a quick test, all of this worked even in simultaneaous transactions. Of course, that still needs a lot of work and testing, but I currently hope that path is feasible.

Let me also add a little bit more info on the other approach we are using in Oracle: In Oracle we are (ab)using the transaction name, to store exactly the information that in the suggestion above is stored in the audit_lock table.

The rest is the same as above. The triggers work perfectly in that specific application, even though there are of course a lot of scenarios for other applications, where putting insert, delete and update triggers on each table generating records for each changed column in the table would be nuts. In our application it works perfectly for ten years now and it has no mentionable performance impact on the way the application is used.

In the java application server all code blocks, that are changing data, start with setting the transaction name first and then do loads of changes to various tables, that might be issuing all these triggers. All of these are running in the same transaction and since that has a transaction name which contains the application user, the triggers can write that information to the audit trail table.

I know there are other approaches to the problem, and you could even do that with hibernate features only, but our approach allows us to enforce some consistency through the database (NOT NULL constraint in the audit trail table on the username). Since everything is done via triggers, we can let those fail, if the transaction name is not containing the user (by requiring it to be in a specific format). If there any other portions of the application, other applications or ignorant administrators trying to issue updates to the audited tables without respecting to set the transaction name to the specific format, those updates will fail. This makes updates to the audited tables, that do not generate the required audit table entries harder (certainly not impossible, a ill willing admin can do anything, of course).

So all of you that are cringing now, let me quote Luis: Might seem like a terrible idea, but I have my use case ;)

The idea of @Luís to creating a specific table in each transaction to store the information causes a locking issue in systables. Let's call that "transaction info table". That idea did not cross my mind, since DDL causes commits in Oracle. So I tried that in Informix but if I try to create a table called "tblX" in two simultaneaous transactions, the second transaction get's a locking exception:

Cannot update system catalog (systables). [SQL State=IX000, DB Errorcode=-312] 
Next: ISAM error: key value locked [SQL State=IX000, DB Errorcode=-144]

But letting all transactions use the same table as above works, as far as I tested it right now.