PostgreSQL问答-第20221202期

Table of Contents

• Q1. PostgresSQL中to_number()函数的具体用法？
• 问题描述
• 问题解答
• Q2. 元命令\dt+ 查出的大小包含哪些对象？
• 问题描述
• 问题解答
• Q3. contrib_regression库的用途？
• 问题描述
• 问题解答
• Q4. 怎样获取表上依赖于该表的视图
• 问题描述
• 问题解答
• Q5. 临时文件在什么条件下会生成？是如何命名的？
• 问题描述
• 问题解答

Q1. PostgresSQL中to_number()函数的具体用法？

问题描述

在使用to_number(char,fmt)函数时，其中第二个参数fmt格式化中的9和G分别代表什么含义，如何理解？

问题解答

to_number()函数：

• 作用：把字符串转换成数字

• 格式：to_number（char,fmt），括号里第一个参数是字符串，第二个参数是待匹配的格式

• 实例：

  • 9 - 匹配数字
  • G - 分组分隔符（G 是 ,，匹配到数字中的千位分隔符）

  postgres=# select to_number('123,456,789','999G999') ;
   to_number 
  -----------
      123456
  (1 row)
  

  从左至右，9匹配到三个数字123，G匹配到,即前面三个为一组，后面三个9匹配到456，所以结果是123456。

  这是完全匹配的例子。看个不完全匹配：

  postgres=# select to_number('123,456,789','9G9G999' ) ;
   to_number 
  -----------
        1234
  (1 row)
  

  G没有匹配到，G失效；9匹配到,

  9表示数位，那么其他其他数字呢？

  postgres=# select to_number('123,456,789','919G999' ) ;
   to_number 
  -----------
       13456
  (1 row)
  

  1匹配到123,456,789中的2，则删除（第二个参数中的数字1-8相似）

• 附录：

Q2. 元命令\dt+ 查出的大小包含哪些对象？

问题描述

PostgreSQL中的\dt+ tablename元命令查出的大小是数据+索引的大小，还是说只是数据的大小？

问题解答

表的数据文件+vm文件+fsm+init+相关toast表的大小

执行\set ECHO_HIDDEN on，显示某一个命令实际执行的SQL。
再执行\dt+ table_name可以看到pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size"
下面这张图显示与表大小相关的函数及其对应的查询的表相关对象的大小：

• pg_table_size: 获取表的大小，包括 TOAST，但不包括索引
• pg_relation_size: 获取表的大小。一个参数是main主数据文件的大小，可以增加一个参数（ fork text）指定返回某个分支文件的大小
• pg_total_relation_size：获取表的大小，包括索引和TOAST

Q3. contrib_regression库的用途？

问题描述

PostgreSQL数据库中，发现contrib_regression数据库，这个库的用途？

问题解答

contrib_regression是用来做回归测试验证用的

例：

在pg_stat_statement插件的源码目录执行 make installcheck

[postgres@node4 pg_stat_statements]$ pwd
/opt/software/postgresql-15.0/contrib/pg_stat_statements
[postgres@node4 pg_stat_statements]$ make installcheck 
make -C ../../src/backend generated-headers
make[1]: Entering directory `/opt/software/postgresql-15.0/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/opt/software/postgresql-15.0/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/opt/software/postgresql-15.0/src/backend/catalog'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/opt/software/postgresql-15.0/src/backend/utils'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/opt/software/postgresql-15.0/src/backend/utils'
make[1]: Leaving directory `/opt/software/postgresql-15.0/src/backend'
[postgres@node4 pg_stat_statements]$ psql -c "select datname from pg_database where datname= 'contrib_regression';"
      datname       
--------------------
 contrib_regression
(1 row)

Q4. 怎样获取表上依赖于该表的视图

问题描述

在PostgreSQL中，要获取依赖某张表创建的所有的视图，有相应的SQL语句么？

问题解答

select relnamespace::regnamespace,relname as view_name,relkind from pg_class where oid in(          
           select c.ev_class
      from pg_depend a,pg_depend b,pg_class pc,pg_rewrite c
      where a.refclassid=1259      
      and b.deptype='i'
      and a.classid=2618
      and a.objid=b.objid
      and a.classid=b.classid
      and a.refclassid=b.refclassid
      and a.refobjid<>b.refobjid
      and pc.oid=a.refobjid     
      and c.oid=b.objid
      and pc.relname='t');

例：

postgres=# \dt t
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | t    | table | postgres
(1 row)

postgres=# create view v_t as select * from t ;
CREATE VIEW

postgres=# select relnamespace::regnamespace,relname as view_name,relkind from pg_class where oid in(          
           select c.ev_class
      from pg_depend a,pg_depend b,pg_class pc,pg_rewrite c
      where a.refclassid=1259      
      and b.deptype='i'
      and a.classid=2618
      and a.objid=b.objid
      and a.classid=b.classid
      and a.refclassid=b.refclassid
      and a.refobjid<>b.refobjid
      and pc.oid=a.refobjid     
      and c.oid=b.objid
      and pc.relname='t');
 relnamespace | view_name | relkind 
--------------+-----------+---------
 public       | v_t       | v
(1 row)

Q5. 临时文件在什么条件下会生成？是如何命名的？

问题描述

PostgreSQL中，在$PGDATA/base目录下有个pgsql_tmp文件夹来存储生成的临时文件。临时文件什么情况下会生成？有什么命名规范？

问题解答

• 命名：pgsql_tmpxxx.yyy
  • xxx - 进程号(pid)
  • yyy - 段号。默认每1GB分一个段，从0开始
• 生成：当执行sort排序、HASH JOIN、中间结果存储、聚合等会用到临时文件，超过了work_mem就会溢出到磁盘，在事务查询和结束后会自动回收