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; ```