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 +
......
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=#
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=# 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=#
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 | nnnn | | 90 | 22 | 202 | (5 rows)
数据库系统函数:
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_xlogfile_name_offset:将事务location转换为实际存储的文件名和此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_xlog_location_diff:计算两个location的距离
digoal=# select pg_xlog_location_diff('0/70005E8','0/7000000');
pg_xlog_location_diff
-----------------------
1512
(1 row)
pg_current_xlog_location :得到当前xlog的transaction的location
digoal=# select pg_current_xlog_location();
pg_current_xlog_location
--------------------------
0/9001118
(1 row)