PostgreSQL知识分享-第39期

本期分享3个案例：

• 设置GUC参数可直接更新pg_settings吗？
• 通过LSN获取WAL段文件名为什么失灵？
• MySQL存过一次返回多个结果集，如何迁移？

1.在session会话里可以直接修改pg_settings视图来设置GUC参数值

设置GUC参数值有多种方式，例如：

• ALTER USER … SET …
• ALTER USER … IN DATABASE … SET …
• ALTER DATABASE … SET …
• SET …
• set_config()

但DBA几乎很少去直接修改pg_settings系统视图，不过官方文档描述能在会话级别设置(来自一个客户的新发现)：

In addition, the system view pg_settings can be used to view and change session-local values:

2.获取LSN所在的WAL段文件时需考虑Segment Size

分析一个INSERT语句时发现在等待某个LSN：

... waiting for 8BB/7EC89EE8
复制

于是在本地环境使用pg_walfile_name()函数推算WAL段文件名称：

postgres=# SELECT pg_walfile_name('8BB/7EC89EE8');
     pg_walfile_name      
--------------------------
 00000001000008BB0000007E
(1 row)
复制

但实际环境根本没有该文件，后来查看wal_segment_size参数发现WAL段文件大小发生了变化。用户实际环境能正常计算名称。

微信公众号数据库杂记的何老师有篇文章有详细的分析过程：PostgreSQL中的WAL文件与LSN深入探索和分析

如果pg_walfile_name()函数可以接受显式wal_segment_size参数，那本地环境调用pg_walfile_name()函数也可快速获取WAL段文件名称，而不依赖实际环境。

3.MySQL存储过程一次性返回多个查询结果集，如何迁移？

MySQL可以在存过中使用多个查询语句一次性返回多个记录集，存过示例如下：

DELIMITER //
CREATE PROCEDURE curtest()
BEGIN
    SELECT * FROM tab1 WHERE ...;
    SELECT * FROM tab2 WHERE ...;
END //
DELIMITER ;
复制

迁移到PG，存过需要做一些调整，下面先创建表结构：

CREATE TABLE fiverows(id serial primary key,data text);
INSERT INTO fiverows(data) 
    VALUES ('one'),('two'),('three'),('four'),('five');
复制

存过定义如下:

CREATE OR REPLACE PROCEDURE curtest(
  OUT cur1 REFCURSOR,OUT cur2 REFCURSOR) 
AS $$
BEGIN 
    OPEN cur1 FOR SELECT id,data FROM fiverows WHERE id BETWEEN 1 AND 3;
    OPEN cur2 FOR SELECT id,data FROM fiverows WHERE id BETWEEN 4 AND 5;
END;
$$ LANGUAGE PLPGSQL;
复制

接着在psql里进行测试：

postgres=> START TRANSACTION;
START TRANSACTION
postgres=*> CALL curtest(NULL,NULL);
        cur1        |        cur2        
--------------------+--------------------
 <unnamed portal 1> | <unnamed portal 2>
(1 row)

postgres=*> FETCH ALL "<unnamed portal 1>";
 id | data  
----+-------
  1 | one
  2 | two
  3 | three
(3 rows)

postgres=*> FETCH ALL "<unnamed portal 2>";
 id | data 
----+------
  4 | four
  5 | five
(2 rows)

postgres=*> COMMIT;
COMMIT
复制

在Java里进行调用测试，主要代码如下：

conn.setAutoCommit(false);
CallableStatement stmt = null;
stmt = conn.prepareCall("{call curtest(?,?)}");;
stmt.registerOutParameter(1, Types.REF_CURSOR);
stmt.registerOutParameter(2, Types.REF_CURSOR);
stmt.execute();
ResultSet resultSet = (ResultSet) stmt.getObject(1);
while(resultSet.next()){
      Integer id = (Integer)resultSet.getInt(1);
      String data = (String) resultSet.getString(2);
}    
resultSet = (ResultSet) stmt.getObject(2);
... 
复制

上面在psql里测试时，通过FETCH命令获取数据时需要先获取游标名称，但存过返回的游标名称是自动生成的，名称可能会变, 这一点不太友好。

下面尝试改写为函数：

CREATE OR REPLACE FUNCTION curtest2(cur1 REFCURSOR,cur2 REFCURSOR) 
RETURNS SETOF REFCURSOR
AS $$
BEGIN 
    OPEN cur1 FOR SELECT id,data FROM fiverows WHERE id BETWEEN 1 AND 3;
    RETURN NEXT cur1;

    OPEN cur2 FOR SELECT id,data FROM fiverows WHERE id BETWEEN 4 AND 5;
    RETURN NEXT cur2;
END;
$$ LANGUAGE PLPGSQL;
复制

再使用psql测试

START TRANSACTION;
SELECT curtest2('mycur1', 'mycur2');
FETCH ALL IN "mycur1";
FETCH ALL IN "mycur2";
COMMIT;
复制

此时可以自定义游标的名称，FETCH调用时会更加方便。

本文到此结束，欢迎留言评论，文章如有描述错误也请大家指正。

关联推荐

PostgreSQL知识问答分享-第38期
PostgreSQL知识问答分享-第37期
PostgreSQL知识问答分享-第36期
PostgreSQL知识问答分享-第35期
PostgreSQL知识问答分享-第34期
PostgreSQL知识问答分享-第33期
PostgreSQL知识问答分享-第32期
PostgreSQL知识问答分享-第31期
PostgreSQL知识问答分享-第30期
PostgreSQL知识问答分享-第29期