Table unknown in procedure autonomous transaction statement in Firebird

Question

I have to create table and insert data into it. The operation isn't meant to be repeated often, but we need some kind of automation.

I use autonomous transaction, but get the same error. According to release notes:

The WITH AUTONOMOUS TRANSACTION setting starts a new transaction with the same parameters as CURRENT_TRANSACTION. That transaction will be committed if the statement is executed....

Why is the exception thrown (Dynamic SQL Error. SQL error code = -204. Table unknown.)?

CREATE OR ALTER PROCEDURE write_sh_record_with_clenup (
    tagid TYPE OF COLUMN "SNAPSHOT".tagid,
    scannername VCHAR30_REQ)
AS
  DECLARE VARIABLE tablename TABLE_NAME;
  DECLARE PROCEDURE create_sh_table (name TABLE_NAME)
  AS
  BEGIN
    EXECUTE STATEMENT 'CREATE TABLE "' || :name || '" (tagid TAG_ID, dateyear DATE_PART, dayofyear DATE_PART, totalseconds SECONDS_TIMESTAMP, "VALUE" DOUBLE PRECISION);'
    WITH AUTONOMOUS TRANSACTION;
    WHEN ANY DO
    BEGIN
      EXCEPTION ex_custom USING (UPPER('create_sh_table'), GDSCODE, SQLCODE, SQLSTATE);
    END
  END
BEGIN
  :tablename = 'SH_' || :scannername;
  IF (get_table_exists(:tablename) = 'F') THEN
    EXECUTE PROCEDURE create_sh_table(:tablename);
  EXECUTE STATEMENT 'INSERT INTO ' || :tablename || ' SELECT * FROM snapshot WHERE tagid = ' || :tagid || ';';
END

Answer 1

If you're running this with isolation level SNAPSHOT or SNAPSHOT TABLE STABILITY, this is entirely expected: your procedure can only see the state of other transactions that were committed before its transaction started: it will not be able to see the changes of the autonomous transaction. If you're using READ COMMITTED, it is also expected behaviour since Firebird 4.0.

Firebird 4.0 introduced a new way of handling READ COMMITTED transactions, read committed read consistency, and this is enabled by default, even if mode read committed record_version or no record_version is requested.

With this read consistency mode, a statement will see the state of committed transactions as they were before the statement started. In the case of a stored procedure, that statement is the DSQL select or execute procedure that directly or indirectly called the procedure.

As a result, even though you created the table in an autonomous transaction, and that transaction was committed, as far as the outer procedure is concerned, it doesn't exist as that transaction was committed after it started.

The real solution is to not create tables on the fly like this, but create them ahead of time, but alternatively, you have two workarounds:

1. Disable the setting ReadConsistency in firebird.conf by setting it to 0 instead of its default of 1, and make sure your transaction is started as READ COMMITTED RECORD_VERSION or READ COMMITTED NO RECORD_VERSION (and not as READ COMMITTED READ CONSISTENCY).

   I do not recommend this option, as this is intended as a transitional configuration, and will eventually disappear: do not rely on it, you'll only delay the pain of having to address this.

2. Execute the insert with an autonomous transaction as well.

   This is only a viable workaround if you don't need to be able to see uncommitted changes from the current transaction during the insert.