本期分享三个小案例(更多案例可以查看文章最后的关联推荐):
- char类型如何走索引
- PL过程执行DDL报错问题
- openGauss物化视图名称发生变化
一、char字段使用bpchar转型走索引
下面的示例创建了一个包含2个字段的表。
CREATE TABLE t(a integer, b char(10));
第一个字段包含1到100000之间的数字。第二个字段包含除不可打印字符之外的各种ASCII字符。最
INSERT INTO t(a,b)
SELECT s.id, chr((32+random()*94)::integer)
FROM generate_series(1,100000) as s(id)
ORDER BY random();
对b字段创建btree索引
CREATE INDEX on t(b);
再插入一条"PostgreSQL"关键字的数据
INSERT INTO t(a,b) values(100001,'PostgreSQL');
收集表的统计信息
ANALYZE t;
下面的查询语句从执行计划看可以走索引
postgres=> EXPLAIN (costs off) SELECT * FROM t WHERE b = 'PostgreSQL';
QUERY PLAN
-------------------------------------
Index Scan using t_b_idx on t
Index Cond: (b = 'PostgreSQL'::bpchar)
(3 rows)
但如果b字段的赋值是由字符串拼接或者有变量运算,则不会走索引:
postgres=> EXPLAIN (costs off) SELECT * FROM t WHERE b ='Postgre' || 'SQL';
QUERY PLAN
---------------------------------------
Seq Scan on t
Filter: ((b)::text = 'PostgreSQL'::text)
(2 rows)
不过可以借助类型转换,使用bpchar转型走索引:
postgres=> EXPLAIN (costs off) SELECT * FROM t WHERE b =('Postgre' || 'SQL')::bpchar;
QUERY PLAN
-------------------------------------
Index Scan using t_b_idx on t
Index Cond: (b = 'PostgreSQL'::bpchar)
(3 rows)
注:bpchar(blank-padded char)是character类型的内部别名,使用char、bpchar类型,数据库都会转换为character类型。
二、PL过程中游标未关闭之前不能执行DDL
在PL过程中执行TRUNCATE TABLE时报错如下:
ERROR: cannot TRUNCATE "tab_cursor_trunc" because it is being used by active queries in this session
CONTEXT: SQL statement "TRUNCATE TABLE tab_cursor_trunc"
模拟表结构如下:
CREATE TABLE tab_cursor_trunc(a int);
测试代码如下:
DO $$
DECLARE
c CURSOR IS SELECT * FROM tab_cursor_trunc;
x INT;
BEGIN
OPEN c;
LOOP
FETCH c INTO x;
EXIT WHEN NOT FOUND;
END LOOP;
EXECUTE 'TRUNCATE TABLE tab_cursor_trunc';
END $$;
同一个transaction中,PL过程里的游标未关闭之前,被引用的表不能立即执行DDL操作。
游标使用完需要及时显式close,或者执行动态语句之前先commit,也能自动关闭游标。
因此该问题对应如下两种解法:
方案一:及时关闭游标
DO $$
DECLARE
c CURSOR IS SELECT * FROM tab_cursor_trunc;
x INT;
BEGIN
OPEN c;
LOOP
FETCH c INTO x;
EXIT WHEN NOT FOUND;
END LOOP;
CLOSE c;
EXECUTE 'TRUNCATE TABLE tab_cursor_trunc';
END $$;
方案二:先commit再执行DDL
DO $$
DECLARE
c CURSOR IS SELECT * FROM tab_cursor_trunc;
x INT;
BEGIN
OPEN c;
LOOP
FETCH c INTO x;
EXIT WHEN NOT FOUND;
END LOOP;
COMMIT;
EXECUTE 'TRUNCATE TABLE tab_cursor_trunc';
END $$;
三、openGauss物化视图名称发生变化
业务应用连接openGauss使用可视化报表工具查询时遇到如下报错:
org.postgresql.util.PSQLException: ERROR: relation "pg_catalog.pg_matviews" does not exist on dn_6001_6002_6003
从错误提示信息的对象名,分析可能与物化视图的系统字典查询有关。物化视图是9.3内核提供的功能,9.3开始支持pg_matviews系统视图获取物化视图的模式以及定义等信息。在openGauss数据库里,物化视图采用新的系统视图名gs_matviews。
openGauss系数据库,请使用gs_matviews替代pg_matviews来查询物化视图信息。
关联推荐
- PostgreSQL知识问答分享-第38期
- PostgreSQL知识问答分享-第37期
- PostgreSQL知识问答分享-第36期
- PostgreSQL知识问答分享-第35期
- PostgreSQL知识问答分享-第34期
- PostgreSQL知识问答分享-第33期
- PostgreSQL知识问答分享-第32期
- PostgreSQL知识问答分享-第31期
- PostgreSQL知识问答分享-第30期
- PostgreSQL知识问答分享-第29期
如果有任何问题需要讨论交流的朋友,欢迎添加本人微信号skypkmoon。