处理PostgreSQL Transactoin in Read Only Mode一例
来源:互联网 发布:utorrent linux 64位 编辑:程序博客网 时间:2024/06/05 14:59
问题描述:
今天同事使用桌面直接连接到ArcGIS Data Store中的PostgreSQL空间数据库后,使用桌面工具删除其中的任何一个图层,都会报如下错误:
解决过程:
从错误上看应该说连接的session处于只读模式,因此不让删除表(在PostgreSQL中DDL操作也可以在事务中,这点跟oracle不一样),使用ArcGIS Data Store打包的PostgreSQL客户端连接上去,看看是否也报同样的错误。
db_2d91u=> select current_user;-[ RECORD 1 ]+----------current_user | hsu_nq545db_2d91u=> drop table 一级河流4l;错误: 不能在一个只读模式的事务中执行DROP TABLE
依然报同样的错误,也就是所有的客户端连接上后都是只读模式。
因此判断应该是PostgreSQL的default_transaction_read_only模式值为on了,使用工具进行查看如下:
db_2d91u=> show default_transaction_read_only;-[ RECORD 1 ]-----------------+---default_transaction_read_only | ondb_2d91u=> select * from pg_settings where name='default_transaction_read_only';-[ RECORD 1 ]------------------------------------------------------name | default_transaction_read_onlysetting | onunit |category | Client Connection Defaults / Statement Behaviorshort_desc | Sets the default read-only status of new transactions.extra_desc |context | uservartype | boolsource | databasemin_val |max_val |enumvals |boot_val | offreset_val | onsourcefile |sourceline |db_2d91u=# select * from pg_db_role_setting;-[ RECORD 1 ]--------------------------------------------------------------------------setdatabase | 12029setrole | 0setconfig | {"search_path=\"$user\", public, sde"}-[ RECORD 2 ]--------------------------------------------------------------------------setdatabase | 17418setrole | 0setconfig | {"search_path=\"$user\", public, sde",default_transaction_read_only=true}db_2d91u=# select datname from pg_database where oid=17418;-[ RECORD 1 ]-----datname | db_2d91u
从结果上看该参数生效的值的确是on,导致整个系统处于只读模式。
继续查看postgresql.conf中的default_transaction_read_only值,发现其为off。这跟boot_val | off
一致了,说明在system级别上设置的off.
source | database和pg_db_role_setting中的查询结果,说明在数据库级别上设置了其值为on,并覆盖了system上的设置。
需要将database级别上的参数修改成off。
db_2d91u=# alter database db_2d91u set default_transaction_read_only=off;错误: 不能在一个只读模式的事务中执行ALTER DATABASEdb_2d91u=# set default_transaction_read_only=off;SETdb_2d91u=# alter database db_2d91u set default_transaction_read_only=off;ALTER DATABASEdb_2d91u=# select * from pg_settings where name='default_transaction_read_only';-[ RECORD 1 ]------------------------------------------------------name | default_transaction_read_onlysetting | offunit |category | Client Connection Defaults / Statement Behaviorshort_desc | Sets the default read-only status of new transactions.extra_desc |context | uservartype | boolsource | databasemin_val |max_val |enumvals |boot_val | offreset_val | offsourcefile |sourceline |
使用桌面重新连接后,发现可以成功删除图层。
阅读全文
0 0
- 处理PostgreSQL Transactoin in Read Only Mode一例
- PostgreSQL's read only transaction mode
- PostgreSQL's read only transaction mode
- not allowed in read-only mode (FlushMode.NEVER) 解决
- Write operations are not allowed in read-only mode
- Write operations are not allowed in read-only mode
- Write operations are not allowed in read-only mode
- operations are not allowed in read-only mode
- operations are not allowed in read-only mode
- Write operations are not allowed in read-only mode
- Write operations are not allowed in read-only mode
- the root filesystem is currently mounted in read-only mode
- Write operations are not allowed in read-only mode
- Write operations are not allowed in read-only mode
- InvalidDataAccessApiUsageException:Write operations are not allowed in read-only mode
- Write operations are not allowed in read-only mode
- Write operations are not allowed in read-only mode错误
- HIVE A read-only user or a user in a read-only database is not permitted to disable read-only mode
- HCRM医院客户管理系统
- 面试笔试准备(1)
- 使用 vue2+Vuex+Router 重写饿了么点餐系统和 vue 插件简析
- C++:指针和引用的区别
- python编程中的if __name__ == 'main': 的作用和原理
- 处理PostgreSQL Transactoin in Read Only Mode一例
- leetcode 5(Longest Palindromic Substring)golang实现
- 【Jquery】关于失焦事件和点击事件发生冲突的解决办法
- LinkedHashMap(按访问顺序的链表)实现Lru
- C/C++:typedef、#define和const
- Quantum Computers
- html5现阶段的使用
- fig913
- 不同类型的可编辑状态