PHP实战中知识总结 / PgSQL - pgsql系统函数解析
一、数据库配置设置函数
| 名称 | 描述 |
|---|---|
| current_setting(setting_name,missing_ok) | 获得配置的值 |
| set_config(setting_name,new_value,is_local) | 设置一个参数并返回新值 |
// 当参数没有missing_ok或者mission_ok不为true时，如果setting_name不存在，则会报错；存在时，将返回该配置值
postgres=# SELECT current_setting('datestyle11');
ERROR: unrecognized configuration parameter "datestyle11"
postgres=# SELECT current_setting('datestyle11',true);
current_setting
-----------------
(1 row)
postgres=# SELECT current_setting('datestyle',true);
current_setting
-----------------
ISO, YMD
(1 row)
二、数据库对象尺寸函数
| 名称 | 返回类型 | 描述 |
|---|---|---|
| pg_column_size(any) | int | 存储一个特定值（可能压缩过）所需的字节数 |
| pg_database_size(oid) | bigint | 指定 OID 的数据库使用的磁盘空间 |
| pg_database_size(name) | bigint | 指定名称的数据库使用的磁盘空间 |
| pg_indexes_size(regclass) | bigint | 附加到指定表的索引所占的总磁盘空间 |
| pg_relation_size(relation regclass,fork text) | bigint | 指定表或索引的指定分叉（main、fsm、vm或init）使用的磁盘空间 |
| pg_relation_size(relation regclass) | bigint | pg_relation_size(..., 'main')的简写 |
| pg_size_bytes(text) | bigint | 把人类可读格式的带有单位的尺寸转换成字节数 |
| pg_size_pretty(bigint) | text | 将表示成一个 64位整数的字节尺寸转换为带尺寸单位的人类可读格式 |
| pg_size_pretty(numeric) | text | 将表示成一个数字值的字节尺寸转换为带尺寸单位的人类可读格式 |
| pg_table_size(regclass) | bigint | 被指定表使用的磁盘空间，排除索引（但包括 TOAST、空闲空间映射和可见性映射） |
| pg_tablespace_size(oid) | bigint | 指定 OID 的表空间使用的磁盘空间 |
| pg_tablespace_size(name) | bigint | 指定名称的表空间使用的磁盘空间 |
| pg_total_relation_size(regclass) | bigint | 指定表所用的总磁盘空间，包括所有的索引和TOAST数据。这个函数等价于pg_table_size+pg_indexes_size |
以下例子可见，pg_indexes_size(regclass)函数效果与各个索引使用pg_relation_size(indexname)函数的总和是一样的。
// 查看指定表的索引
postgres=# select * from pg_indexes where tablename='alerts';
schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+-------------------------------------+------------+--------------------------------------------------------------------------------------------------
public | alerts | index_attack_alarm_attack_result | | CREATE INDEX index_attack_alarm_attack_result ON public.alerts USING btree (attack_result)
public | alerts | index_attack_alarm_attack_type | | CREATE INDEX index_attack_alarm_attack_type ON public.alerts USING btree (attack_type)
public | alerts | index_attack_alarm_suffer_branch_id | | CREATE INDEX index_attack_alarm_suffer_branch_id ON public.alerts USING btree (suffer_branch_id)
public | alerts | index_attack_alarm_last_time | | CREATE INDEX index_attack_alarm_last_time ON public.alerts USING btree (last_time)
public | alerts | index_attack_alarm_priority | | CREATE INDEX index_attack_alarm_priority ON public.alerts USING btree (priority)
public | alerts | index_attack_alarm_reliability | | CREATE INDEX index_attack_alarm_reliability ON public.alerts USING btree (reliability)
public | alerts | index_attack_alarm_rule_id | | CREATE INDEX index_attack_alarm_rule_id ON public.alerts USING hash (rule_id)
public | alerts | index_attack_alarm_sub_attack_type | | CREATE INDEX index_attack_alarm_sub_attack_type ON public.alerts USING btree (sub_attack_type)
public | alerts | attack_alarm_pkey | | CREATE UNIQUE INDEX attack_alarm_pkey ON public.alerts USING btree (id)
public | alerts | index_attack_alarm_event_desc | | CREATE INDEX index_attack_alarm_event_desc ON public.alerts USING gin (event_desc gin_trgm_ops)
(10 rows)
// 查看alerts表的索引所占的总磁盘空间
postgres=# select
pg_size_pretty(pg_indexes_size('alerts')) as indexes,
pg_size_pretty(
pg_relation_size('index_attack_alarm_priority') +
pg_relation_size('index_attack_alarm_last_time') +
pg_relation_size('index_attack_alarm_attack_result') +
pg_relation_size('index_attack_alarm_attack_type') +
pg_relation_size('index_attack_alarm_suffer_branch_id') +
pg_relation_size('index_attack_alarm_reliability') +
pg_relation_size('index_attack_alarm_rule_id') +
pg_relation_size('index_attack_alarm_sub_attack_type') +
pg_relation_size('attack_alarm_pkey') +
pg_relation_size('index_attack_alarm_event_desc')
) as sum;
indexes | sum
---------+--------
149 MB | 149 MB
(1 row)
// 查看数据库的oid
postgres=# select datname,oid from pg_database;
datname | oid
-----------+-------
postgres | 13580
test | 16411
template1 | 1
template0 | 13579
(4 rows)
// 根据数据库oid或者名称查看数据库占用的磁盘空间大小
postgres=# select pg_size_pretty(pg_database_size(13580));
pg_size_pretty
----------------
558 MB
(1 row)
postgres=# select pg_size_pretty(pg_database_size('postgres'));
pg_size_pretty
----------------
558 MB
(1 row)
// pg_total_relation_size等于pg_table_size和pg_indexes_size的总和
postgres=# select pg_size_pretty(pg_table_size('alerts')) as table_size,
pg_size_pretty(pg_indexes_size('alerts')) as index_size,
pg_size_pretty(pg_total_relation_size('alerts')) as total;
table_size | index_size | total
------------+------------+--------
391 MB | 149 MB | 540 MB
(1 row)
三、数据库对象定位函数
| 名称 | 返回类型 | 描述 |
|---|---|---|
| pg_relation_filenode(relation regclass) | oid | 接受一个表、索引、序列或 TOAST 表的 OID 或名称，返回当前分配给它的“filenode”号。 |
| pg_relation_filepath(relation regclass) | text | 与pg_relation_filenode类似，指定关系的文件路径名。 |
| pg_filenode_relation(tablespace oid，filenode oid) | regclass | 查找与给定的表空间和文件节点相关的关系。 |
// 查看alerts表文件的对应的存储位置
postgres=# select pg_relation_filepath('alerts');
pg_relation_filepath
----------------------
base/13580/16391
(1 row)
// 查看alerts表的filenode（relfilenode一般情况下和oid一致）
postgres=# select pg_relation_filenode('alerts');
pg_relation_filenode
----------------------
16391
(1 row)
// pg_filenode_relation是pg_relation_filenode的反向函数。给定一个“tablespace”OID 以及一个“filenode”，它会返回相关关系的 OID。
// 对于一个在数据库的默认表空间中的表，该表空间可以指定为 0
// 表空间的oid可以通过pg_tablespace查看
postgres=# SELECT * FROM pg_tablespace;
oid | spcname | spcowner | spcacl | spcoptions
------+------------+----------+--------+------------
1663 | pg_default | 10 | |
1664 | pg_global | 10 | |
(2 rows)
postgres=# select pg_filenode_relation(1663,16391);
pg_filenode_relation
----------------------
alerts
(1 row)
postgres=# select pg_filenode_relation(0,16391);
pg_filenode_relation
----------------------
alerts
(1 row)
四、数据库分区信息函数
| 名称 | 返回类型 | 描述 |
|---|---|---|
| pg_partition_tree(regclass) | setof record | 列出一个分区树中的表或索引的相关信息。给定的分区表或分区索引，每张表有一行 分区。 提供的信息包括分区的名称，它的直系父级的名称，一个布尔值，表示该分区是否是一个叶子，以及一个整数，表示它在层次结构中的级别。level的值从0开始，表示输入表或索引作为分区树的根，1表示其分区，2表示其分区，以此类推。 |
| pg_partition_ancestors(regclass) | setof regclass | 列出给定分区的祖先关系，包括分区本身。 |
| pg_partition_root(regclass) | regclass | R返回给定关系所属的分区树的最上层父节点。 |
五、通用文件访问函数
这些函数提供了对数据库服务器所在机器上的文件的本地访问。只能访问数据库集簇目录以及log_directory中的文件，除非用户被授予了角色pg_read_server_files。
这些函数中的某些有一个可选的missing_ok参数， 它指定文件或者目录不存在时的行为。如果为true， 函数会返回 NULL （pg_ls_dir除外，它返回一个空 结果集）。如果为false，则发生一个错误。默认是false。
| 名称 | 返回类型 | 描述 |
|---|---|---|
| pg_ls_dir(dirname text [missing_ok boolean,include_dot_dirs boolean]) | setof text | 列出目录中的内容。默认仅限于超级用户使用，但是可以给其他用户授予EXECUTE让他们运行这个函数。 |
| pg_ls_logdir() | setof record | 列出日志目录中文件的名称、尺寸以及最后修改时间。访问被授予给pg_monitor角色的成员，并且可以被授予给其他非超级用户角色。 |
| pg_ls_waldir() | setof record | 列出WAL目录中文件的名称、尺寸以及最后修改时间。访问被授予给pg_monitor角色的成员，并且可以被授予给其他非超级用户角色。 |
| pg_ls_archive_statusdir() | setof record | 列出WAL存档状态目录中文件的名称、大小和最后一次修改时间。访问权限只授予pg_monitor角色的成员，也可以授予其他非超级用户角色。 |
| pg_ls_tmpdir([tablespace oid]) | setof record | 为tablespace列出临时目录中文件的名称、大小和最后一次修改时间。 如果没有提供*tablespace*，则在临时目录中的pg_default表空间被使用。pg_monitor角色的成员可以访问，其他非超级用户角色也可以访问。 |
| pg_read_file(filename text [offset bigint，length bigint [,missing_ok boolean] ]) | text | 返回一个文本文件的内容。默认仅限于超级用户使用，但是可以给其他用户授予EXECUTE让他们运行这个函数。 |
| pg_read_binary_file(filename text [,offset bigint,length bigint[,missing_ok boolean] ]) | bytea | 返回一个文件的内容。默认仅限于超级用户使用，但是可以给其他用户授予EXECUTE让他们运行这个函数。 |
| pg_stat_file(filename text [,missing_ok boolean]) | record | 返回关于一个文件的信息。默认仅限于超级用户使用，但是可以给其他用户授予EXECUTE让他们运行这个函数。 |
（1）pg_ls_dir：返回指定目录中所有文件（以及目录和其他特殊文件） 的名称。include_dot_dirs指示结果集中是否包括“.”和“..”。默认是false，但是 当missing_ok为true时把它们包括在内是有用的，因为可以把一个空目录与一个不存在的目录区分开。
（2）pg_ls_logdir：返回日志目录中每个文件的名称、尺寸以及最后的修改时间（mtime）。默认情况下，只有超级用户以及pg_monitor角色的成员能够使用这个函数。可以使用GRANT把访问授予给其他人。
（3）pg_ls_waldir：返回预写式日志（WAL）目录中每个文件的名称、尺寸以及最后的修改时间（mtime）。默认情况下，只有超级用户以及pg_monitor角色的成员能够使用这个函数。可以使用GRANT把访问授予给其他人。
（4）pg_ls_archive_statusdir：返回WAL归档状态目录pg_wal/archive_status中每个文件的名称、大小和最后一次修改时间（mtime）。默认情况下，只有超级用户和pg_monitor角色的成员才能使用此函数。可使用GRANT授权其他用户访问。
（5）pg_ls_tmpdir：返回指定的tablespace临时文件目录中每个文件的名称、大小和最后一次修改时间（mtime）。 如果没有提供tablespace，则使用pg_default表空间。 默认情况下，只有超级用户和pg_monitor角色的成员才能使用这个函数。 可使用GRANT授权其他用户访问。
（6）pg_read_file：返回一个文本文件的一部分，从给定的offset开始，返回最多length字节（如果先到达文件末尾则会稍短）。如果offset为负，它相对于文件的末尾。如果offset和length被忽略，整个文件都被返回。从文件中读的字节被使用服务器编码解释成一个字符串；如果它们在编码中不合法则抛出一个错误。
（7）pg_read_binary_file：与pg_read_file相似，除了前者的结果是一个bytea值；相应地，不会执行编码检查。通过与convert_from函数结合，这个函数可以用来读取一个指定编码的文件：
SELECT convert_from(pg_read_binary_file('file_in_utf8.txt'), 'UTF8');
（8）pg_stat_file：返回一个记录，其中包含文件尺寸、最后访问时间戳、最后修改时间戳、最后文件状态改变时间戳（只支持 Unix 平台）、文件创建时间戳（只支持 Windows）和一个boolean指示它是否为目录。通常的用法包括:
SELECT * FROM pg_stat_file('filename');
SELECT (pg_stat_file('filename')).modification;