PostgreSQL知识分享-第38期

本期分享三个小案例（更多案例可以查看文章最后的关联推荐）：

• 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。