26 Star 18 Fork 216

openGauss / blog

 / 详情

transaction read only 设置不生效问题

已完成
缺陷
创建于  
2020-08-07 11:57
根据官方文档描述,可以使用transaction_read_only参数控制事务的读写状态。但实际测试时发现该参数并不生效。实验结果如下:
[omm@db1 ~]$ gs_guc reload -N all -I all -c "transaction_read_only=on"
Begin to perform gs_guc for all datanodes.

Total instances: 2. Failed instances: 0.
Success to perform gs_guc!

[omm@db1 db1]$ gsql -d postgres -p 26000
gsql ((openGauss 1.0.0 build 0bd0ce80) compiled at 2020-06-30 18:19:27 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

postgres=# select name,setting,boot_val,reset_val from pg_settings where name='transaction_read_only';
         name          | setting | boot_val | reset_val
-----------------------+---------+----------+-----------
 transaction_read_only | off     | off      | off

postgres=# create table t3(id int,name varchar(20));
CREATE TABLE
以上示例发现,虽然使用guc设置transaction_read_only=on,但实际并没有生效,依然可以对数据库做DML/DDL操作。

为什么会不生效?

在实际transaction相关参数查看时发现了default_transaction_read_only参数,该参数默认设置为off状态。
postgres=#  select name,setting,boot_val,reset_val from pg_settings where name like '%transaction_read_only';
             name              | setting | boot_val | reset_val
-------------------------------+---------+----------+-----------
 default_transaction_read_only | off     | off      | off
 transaction_read_only         | off     | off      | off

当把default_transaction_read_only参数设置为on时,测试结果如下:

[omm@db1 ~]$ gs_guc reload -N all -I all -c "default_transaction_read_only=on"
Begin to perform gs_guc for all datanodes.

Total instances: 2. Failed instances: 0.
Success to perform gs_guc!

postgres=#  select name,setting,boot_val,reset_val from pg_settings where name like '%transaction_read_only';
             name              | setting | boot_val | reset_val
-------------------------------+---------+----------+-----------
 default_transaction_read_only | on      | off      | on
 transaction_read_only         | on      | off      | off
(2 rows)
当对default_transaction_read_only 参数做修改后,transaction_read_only参数也随之修改,事务状态也成功变更为read only模式,DML/DDL拒绝执行,示例如下:
postgres=# insert into t1 values(222);
ERROR:  cannot execute INSERT in a read-only transaction
由此可以发现,直接决定事务读写状态的应该是default_transaction_read_only参数,至于default_transaction_read_only 和 transaction_read_only参数的区别和关系,目前官方文档还没有指出,期待进一步完善。 

评论 (5)

贾军锋 创建了缺陷
贾军锋 关联仓库设置为openGauss/blog
展开全部操作日志

Hey @贾军锋, Welcome to openGauss Community.
All of the projects in openGauss Community are maintained by @opengauss-bot.
That means the developers can comment below every pull request or issue to trigger Bot Commands.
Please follow instructions at https://gitee.com/opengauss/community/blob/master/contributors/command.en.md to find the details.

opengauss-bot 添加了
 
kind/bug
标签

Hey @贾军锋 please feel free to fix it.
Thanks very much.

zhangxubo 添加了
 
sig/infra
标签

目前单机、单机主备模式下,该参数在数据库恢复过程中或者在备机里,固定为true;否则,固定为default_transaction_read_only的值。

zhangxubo 任务状态待办的 修改为已确认
zhangxubo 任务状态已确认 修改为已完成

登录 后才可以发表评论

状态
负责人
项目
里程碑
Pull Requests
关联的 Pull Requests 被合并后可能会关闭此 issue
分支
开始日期   -   截止日期
-
置顶选项
优先级
预计工期 (小时)
参与者(5)
5622128 opengauss bot 1581905080 5227357 xiangxinyong 1578982846 5353117 xixicat 1589531730
1
https://gitee.com/opengauss/blog.git
git@gitee.com:opengauss/blog.git
opengauss
blog
blog

搜索帮助

344bd9b3 5694891 D2dac590 5694891