PostgreSQL只读模式切换：存储过程执行ALTER DATABASE报错求助

当数据库的default_transaction_read_only设为ON时，调用你编写的存储过程切换到非只读模式时出现报错cannot execute ALTER DATABASE in a read-only transaction，核心原因如下：

• 数据库默认只读时，所有新建事务会继承只读属性，包括调用存储过程的DO块事务。
• PostgreSQL不允许在事务启动后修改其读写模式，因此存储过程内的set transaction read write;语句无效，事务仍保持只读状态，而ALTER DATABASE属于写操作，无法在只读事务中执行。

结论：可以通过存储过程将数据库移出只读模式，只需调整存储过程或调用方式，确保执行ALTER DATABASE的事务处于读写状态，以下是两种可行方案：

方案1：修改调用方式，显式在读写事务中执行存储过程 #

首先简化存储过程，移除无效的事务设置语句：

CREATE OR REPLACE PROCEDURE tmp_util_archive_set_db_read_only(IN pisreadlonly boolean)
LANGUAGE plpgsql
AS
$$
BEGIN
  IF pIsReadlOnly THEN
    ALTER DATABASE mpbr SET default_transaction_read_only TO ON;
  ELSE
    ALTER DATABASE mpbr SET default_transaction_read_only TO OFF;
  END IF;
END
$$;

然后替代原来的DO块，显式启动读写事务调用存储过程：

BEGIN;
SET TRANSACTION READ WRITE;
CALL tmp_util_archive_set_db_read_only(pisreadlonly := false);
COMMIT;

方案2：修改存储过程，使用独立自治事务 #

利用PostgreSQL存储过程支持事务控制的特性，在存储过程内部启动独立的读写事务，不受外部事务模式影响：

CREATE OR REPLACE PROCEDURE tmp_util_archive_set_db_read_only(IN pisreadlonly boolean)
LANGUAGE plpgsql
AS
$$
BEGIN
  -- 启动独立的读写事务执行修改操作
  BEGIN
    SET TRANSACTION READ WRITE;
    IF pIsReadlOnly THEN
      ALTER DATABASE mpbr SET default_transaction_read_only TO ON;
    ELSE
      ALTER DATABASE mpbr SET default_transaction_read_only TO OFF;
    END IF;
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      RAISE; -- 抛出异常，确保错误被感知
  END;
END
$$;

修改后，直接调用存储过程即可（包括用DO块调用），内部事务会强制使用读写模式执行修改操作。

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