Firebird存储过程自治事务语句报“表不存在”错误的原因排查

问题根源 #

Firebird默认采用SNAPSHOT事务隔离级别，这个级别下，事务只能看到自己启动时已存在的数据库对象。你用自治事务创建表后，自治事务会提交并生成新表，但主事务是在表创建前就启动的，它的元数据视图不会自动刷新，所以主事务完全看不到这个刚创建的新表，执行插入时自然会抛出SQL错误码-204的“表不存在”异常。

简单说：主事务和自治事务完全独立，自治事务创建的新对象，主事务在自身结束前根本不可见。

解决办法 #

方案1：将创建表与插入操作放在同一个自治事务中 #

把判断表是否存在、创建表、插入数据的逻辑全部打包到同一个自治事务里，整个流程在同一事务中执行，自然能看到刚创建的表。修改后的代码示例：

CREATE OR ALTER PROCEDURE write_sh_record_with_clenup (
    tagid TYPE OF COLUMN "SNAPSHOT".tagid,
    scannername VCHAR30_REQ)
AS
  DECLARE VARIABLE tablename TABLE_NAME;
BEGIN
  :tablename = 'SH_' || :scannername;
  -- 所有逻辑在同一个自治事务中完成
  EXECUTE STATEMENT '
    -- 直接查询系统表判断表是否存在（排除系统表）
    IF NOT EXISTS(SELECT 1 FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = ''' || :tablename || ''' AND RDB$SYSTEM_FLAG = 0) THEN
    BEGIN
      CREATE TABLE "' || :tablename || '" (
        tagid TAG_ID, 
        dateyear DATE_PART, 
        dayofyear DATE_PART, 
        totalseconds SECONDS_TIMESTAMP, 
        "VALUE" DOUBLE PRECISION
      );
    END
    -- 参数化传递tagid，避免SQL注入
    INSERT INTO "' || :tablename || '" SELECT * FROM snapshot WHERE tagid = ?'
  (:tagid)
  WITH AUTONOMOUS TRANSACTION;
  
  WHEN ANY DO
  BEGIN
    EXCEPTION ex_custom USING (UPPER('write_sh_record_with_clenup'), GDSCODE, SQLCODE, SQLSTATE);
  END
END

方案2：插入操作单独使用自治事务 #

如果不想合并逻辑，也可以给插入语句单独加上WITH AUTONOMOUS TRANSACTION，让插入在新事务中执行，新事务能看到之前自治事务创建的表：

-- 原代码中插入部分修改为：
EXECUTE STATEMENT 'INSERT INTO "' || :tablename || '" SELECT * FROM snapshot WHERE tagid = ?'
(:tagid)
WITH AUTONOMOUS TRANSACTION;

额外提醒 #

你的原代码存在SQL注入风险：直接把scannername和tagid拼接到SQL语句中，若输入包含特殊字符（比如引号），会导致SQL语法错误，甚至被恶意利用。建议对scannername做合法性校验（比如只允许字母、数字、下划线），tagid则用参数化方式传递，避免风险。

内容的提问来源于stack exchange，提问作者Anthony Voronkov