Generic File Access Functions:
普通文件的访问方法:
digoal=# select pg_ls_dir('.'); pg_ls_dir ----------------- pg_multixact pg_notify postmaster.pid pg_serial pg_twophase postgresql.conf pg_ident.conf pg_xlog pg_snapshots pg_tblspc global pg_log pg_subtrans PG_VERSION pg_clog postmaster.opts base recovery.done pg_hba.conf pg_stat_tmp pg_stat (21 rows) digoal=# select pg_read_file('./pg_hba.conf'); pg_read_file -------------------------------------------------------------------------- # PostgreSQL Client Authentication Configuration File + # =================================================== + # + # Refer to the "Client Authentication" section in the PostgreSQL + # documentation for a complete description of this file. A short + # synopsis follows. + ..... digoal=# select pg_read_file('./pg_hba.conf') into tbtbmp;--将文件内容插入到一个新表中 SELECT 1 digoal=# select (pg_stat_file('./pg_hba.conf')).modification; modification ------------------------ 2014-04-08 20:05:30+08 digoal=# select pg_stat_file('./pg_hba.conf'); pg_stat_file -------------------------------------------------------------------------------------- (4548,"2014-05-21 16:20:19+08","2014-04-08 20:05:30+08","2014-04-08 20:05:30+08",,f) (1 row) digoal=# SELECT convert_from(pg_read_binary_file('./pg_hba.conf'), 'UTF8'); convert_from -------------------------------------------------------------------------- # PostgreSQL Client Authentication Configuration File + # =================================================== + # + # Refer to the "Client Authentication" section in the PostgreSQL + # documentation for a complete description of this file. A short + ......
Snapshot Synchronization Functions:
sessionB:digoal=# select * from foo;
fooid | foosubid | fooname | col1 | col2 | col3 | col4 | col5
-------+----------+---------+------+------+------+------+------
4 | 5 | six | | 90 | 22 | 202 |
5 | 5 | two | | 90 | 22 | 202 |
7 | 7 | rrr | | 90 | 22 | 202 |
6 | 6 | vvvvv | | 90 | 22 | 202 |
1 | 2 | test | | 90 | 22 | 202 |
(5 rows)digoal=# BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN digoal=# SELECT pg_export_snapshot(); pg_export_snapshot -------------------- 000007A4-1 (1 row) digoal=#
sessionC:digoal=# select * from foo; fooid | foosubid | fooname | col1 | col2 | col3 | col4 | col5 -------+----------+---------+------+------+------+------+------ 4 | 5 | six | | 90 | 22 | 202 | 5 | 5 | two | | 90 | 22 | 202 | 7 | 7 | rrr | | 90 | 22 | 202 | 6 | 6 | vvvvv | | 90 | 22 | 202 | 1 | 2 | test | | 90 | 22 | 202 | (5 rows) digoal=# update foo set fooname='nnnn' where fooid=1; UPDATE 1 digoal=# BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN digoal=# SET TRANSACTION SNAPSHOT '000007A4-1'; SET digoal=# select * from foo; fooid | foosubid | fooname | col1 | col2 | col3 | col4 | col5 -------+----------+---------+------+------+------+------+------ 4 | 5 | six | | 90 | 22 | 202 | 5 | 5 | two | | 90 | 22 | 202 | 7 | 7 | rrr | | 90 | 22 | 202 | 6 | 6 | vvvvv | | 90 | 22 | 202 | 1 | 2 | test | | 90 | 22 | 202 | (5 rows) digoal=#
digoal=# select * from foo; fooid | foosubid | fooname | col1 | col2 | col3 | col4 | col5 -------+----------+---------+------+------+------+------+------ 4 | 5 | six | | 90 | 22 | 202 | 5 | 5 | two | | 90 | 22 | 202 | 7 | 7 | rrr | | 90 | 22 | 202 | 6 | 6 | vvvvv | | 90 | 22 | 202 | 1 | 2 | nnnn | | 90 | 22 | 202 | (5 rows)
数据库系统函数:
pg_xlogfile_name_offset:将事务location转换为实际存储的文件名和此location在此文件中的偏移量digoal=# select pg_switch_xlog (); pg_switch_xlog ---------------- 0/629ACC0 (1 row) digoal=# select pg_switch_xlog (); pg_switch_xlog ---------------- 0/7000000 (1 row) digoal=# select pg_xlogfile_name('0/7000000'); pg_xlogfile_name -------------------------- 000000010000000000000006 (1 row) digoal=# select pg_xlogfile_name('0/629ACC0'); pg_xlogfile_name -------------------------- 000000010000000000000006 (1 row)
pg_xlog_location_diff:计算两个location的距离digoal=# select pg_xlogfile_name_offset('0/629ACC0'); pg_xlogfile_name_offset ------------------------------------ (000000010000000000000006,2731200) (1 row) digoal=# select pg_xlogfile_name_offset('0/7000000'); pg_xlogfile_name_offset ------------------------------ (000000010000000000000006,0) (1 row) digoal=# select pg_switch_xlog (); pg_switch_xlog ---------------- 0/70005E8 (1 row) digoal=# select pg_xlogfile_name_offset('0/70005E8'); pg_xlogfile_name_offset --------------------------------- (000000010000000000000007,1512) (1 row)
pg_current_xlog_location :得到当前xlog的transaction的locationdigoal=# select pg_xlog_location_diff('0/70005E8','0/7000000'); pg_xlog_location_diff ----------------------- 1512 (1 row)
digoal=# select pg_current_xlog_location(); pg_current_xlog_location -------------------------- 0/9001118 (1 row)