查看锁的信息:
postgres=# select * from pg_locks;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
------------±---------±---------±-----±------±-----------±--------------±--------±------±---------±-------------------±------±----------------±--------±---------
relation | 13325 | 11695 | | | | | | | | 9/13 | 36697 | AccessShareLock | t | t
virtualxid | | | | | 9/13 | | | | | 9/13 | 36697 | ExclusiveLock | t | t
(2 rows)
产生锁
postgres=# \set AUTOCOMMIT off
postgres=# insert into test values(8);
INSERT 0 1
postgres=# select * from pg_locks;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
---------------±---------±---------±-----±------±-----------±--------------±--------±------±---------±-------------------±------±-----------------±--------±---------
relation | 13325 | 11695 | | | | | | | | 9/14 | 36697 | AccessShareLock | t | t
relation | 13325 | 16385 | | | | | | | | 9/14 | 36697 | RowExclusiveLock | t | t
virtualxid | | | | | 9/14 | | | | | 9/14 | 36697 | ExclusiveLock | t | t
transactionid | | | | | | 1768 | | | | 9/14 | 36697 | ExclusiveLock | t | f
(4 rows)
查看表的位置:
postgres=# select pg_relation_filepath(oid),relpages from pg_class where relname = ‘test’;
ERROR: current transaction is aborted, commands ignored until end of transaction block #表示现在有锁,有事务存在,要先提交;
postgres=# commit;
ROLLBACK
postgres=# select pg_relation_filepath(oid),relpages from pg_class where relname = ‘test’;
pg_relation_filepath | relpages
----------------------±---------
base/13325/16385 | 0
(1 row)
[root@node201 13325]# pwd
/var/lib/pgsql/9.6/data/base/13325
[root@node201 13325]# ll |grep 16385
-rw------- 1 postgres postgres 8192 Apr 3 22:40 16385
查最大的表
postgres=# select relname,relpages from pg_class order by relpages desc;
relname | relpages
-----------------------------------------±---------
pg_proc | 72 —》最大的表是这个
pg_depend | 55
pg_toast_2618 | 47
pg_attribute | 45
pg_depend_reference_index | 43
pg_depend_depender_index | 38
pg_description | 34
pg_proc_proname_args_nsp_index | 31
pg_collation | 29
pg_description_o_c_o_index | 21
pg_statistic | 16
。。。