pgsql系统函数解析 · PHP知识总结

[TOC] # **数据库配置设置函数** | 名称 | 描述 | | --- | --- | --- | |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让他们运行这个函数。 | * `pg_ls_dir`返回指定目录中所有文件（以及目录和其他特殊文件）的名称。`include_dot_dirs`指示结果集中是否包括“.”和“..”。默认是false，但是当missing_ok为true时把它们包括在内是有用的，因为可以把一个空目录与一个不存在的目录区分开。 * `pg_ls_logdir`返回日志目录中每个文件的名称、尺寸以及最后的修改时间（mtime）。默认情况下，只有超级用户以及`pg_monitor`角色的成员能够使用这个函数。可以使用GRANT把访问授予给其他人。 * `pg_ls_waldir`返回预写式日志（WAL）目录中每个文件的名称、尺寸以及最后的修改时间（mtime）。默认情况下，只有超级用户以及`pg_monitor`角色的成员能够使用这个函数。可以使用GRANT把访问授予给其他人。 * `pg_ls_archive_statusdir`返回WAL归档状态目录`pg_wal/archive_status`中每个文件的名称、大小和最后一次修改时间（mtime）。默认情况下，只有超级用户和`pg_monitor`角色的成员才能使用此函数。可使用GRANT授权其他用户访问。 * `pg_ls_tmpdir`返回指定的`tablespace`临时文件目录中每个文件的名称、大小和最后一次修改时间（mtime）。如果没有提供`tablespace`，则使用`pg_default`表空间。默认情况下，只有超级用户和`pg_monitor`角色的成员才能使用这个函数。可使用`GRANT`授权其他用户访问。 * `pg_read_file`返回一个文本文件的一部分，从给定的`offset`开始，返回最多`length`字节（如果先到达文件末尾则会稍短）。如果`offset`为负，它相对于文件的末尾。如果`offset`和`length`被忽略，整个文件都被返回。从文件中读的字节被使用服务器编码解释成一个字符串；如果它们在编码中不合法则抛出一个错误。 * `pg_read_binary_file`与`pg_read_file`相似，除了前者的结果是一个`bytea`值；相应地，不会执行编码检查。通过与`convert_from`函数结合，这个函数可以用来读取一个指定编码的文件： ``` SELECT convert_from(pg_read_binary_file('file_in_utf8.txt'), 'UTF8'); ``` * `pg_stat_file`返回一个记录，其中包含文件尺寸、最后访问时间戳、最后修改时间戳、最后文件状态改变时间戳（只支持 Unix 平台）、文件创建时间戳（只支持 Windows）和一个`boolean`指示它是否为目录。通常的用法包括: ``` SELECT * FROM pg_stat_file('filename'); SELECT (pg_stat_file('filename')).modification; ```