【openGauss】openGauss清空表在回收站记录，彻底释放磁盘空间

---

问题描述

数据库只读模式，只能查询，做其他dml操作都报错：
ERROR: cannot execute XXXXXX in a read-only transaction

一、查参数

select name,setting,unit,context from pg_settings where name ~* 'read_only';

name	setting	unit	context
default_transaction_read_only	on		user
transaction_read_only	on		user

二、设置参数default_transaction_read_only参数值为off

set default_transaction_read_only = off;

三、truncate清空一些不常用的但占用磁盘空间很大的表，比如日志表

truncate tzq_log_t;  -- 日志表

四、openGauss清空表在回收站记录，彻底释放磁盘空间

4.1、针对truncate

--  针对truncate
select concat('PURGE TABLE ',rcyoriginname,';')  from gs_recyclebin 
 where rcyoriginname in(
select -- t2.nspname,
       t1.relname as tablename_in_recyclebin
from pg_class t1
join pg_namespace t2 on (t1.relnamespace=t2.oid)
where t1.relkind = 'r'
   and t1.relname ~* '^bin'
   and t2.nspname = '模式名'
order by 1
);

4.2、针对drop

-- 针对drop
select concat('PURGE TABLE ',rcyoriginname,';') from gs_recyclebin where rcyname in(
select --t2.nspname,
       t1.relname as tablename_in_recyclebin
from pg_class t1
join pg_namespace t2 on (t1.relnamespace=t2.oid)
where t1.relkind = 'r'
      and t1.relname ~* '^bin'
	  and t2.nspname = '模式名'
order by 1);