2. it
  3. postgresql 执行 create table 后的一些分析

postgresql 执行 create table 后的一些分析

mac2024-03-06  26

os: centos 7.4 db: postgresql 10.10

版本

# cat /etc/centos-release CentOS Linux release 7.4.1708 (Core) # # su - postgres Last login: Sat Oct 26 22:55:25 CST 2019 on pts/0 $ $ psql -c "select version();" version ---------------------------------------------------------------------------------------------------------- PostgreSQL 10.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit (1 row)

create table

postgres=# select pg_switch_wal(); pg_switch_wal --------------- 0/3329E28 (1 row) postgres=# checkpoint; CHECKPOINT postgres=# select pg_walfile_name(pg_current_wal_lsn()); pg_walfile_name -------------------------- 000000010000000000000004 (1 row) postgres=# select txid_current(); txid_current -------------- 598 (1 row) postgres=# create table tmp_t0 ( c0 varchar(100),c1 varchar(100),c2 varchar(100)); CREATE TABLE postgres=# select txid_current(); txid_current -------------- 600 (1 row)

pg_waldump

$ /usr/pgsql-10/bin/pg_waldump 000000010000000000000004 rmgr: Transaction len (rec/tot): 34/ 34, tx: 598, lsn: 0/04031A40, prev 0/04031A08, desc: COMMIT 2019-10-28 11:43:29.226653 CST rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/04031A68, prev 0/04031A40, desc: RUNNING_XACTS nextXid 599 latestCompletedXid 598 oldestRunningXid 599 rmgr: Storage len (rec/tot): 42/ 42, tx: 0, lsn: 0/04031AA0, prev 0/04031A68, desc: CREATE base/13808/16517 rmgr: Heap len (rec/tot): 54/ 6738, tx: 599, lsn: 0/04031AD0, prev 0/04031AA0, desc: INSERT off 37, blkref #0: rel 1663/13808/1247 blk 8 FPW rmgr: Btree len (rec/tot): 53/ 8053, tx: 599, lsn: 0/04033540, prev 0/04031AD0, desc: INSERT_LEAF off 398, blkref #0: rel 1663/13808/2703 blk 1 FPW rmgr: Btree len (rec/tot): 53/ 2333, tx: 599, lsn: 0/040354D0, prev 0/04033540, desc: INSERT_LEAF off 40, blkref #0: rel 1663/13808/2704 blk 2 FPW rmgr: Heap len (rec/tot): 54/ 7370, tx: 599, lsn: 0/04035DF0, prev 0/040354D0, desc: INSERT off 213, blkref #0: rel 1663/13808/2608 blk 54 FPW rmgr: Btree len (rec/tot): 53/ 4237, tx: 599, lsn: 0/04037AD8, prev 0/04035DF0, desc: INSERT_LEAF off 126, blkref #0: rel 1663/13808/2673 blk 30 FPW rmgr: Btree len (rec/tot): 53/ 7345, tx: 599, lsn: 0/04038B80, prev 0/04037AD8, desc: INSERT_LEAF off 153, blkref #0: rel 1663/13808/2674 blk 46 FPW rmgr: Heap len (rec/tot): 203/ 203, tx: 599, lsn: 0/0403A850, prev 0/04038B80, desc: INSERT off 38, blkref #0: rel 1663/13808/1247 blk 8 rmgr: Btree len (rec/tot): 64/ 64, tx: 599, lsn: 0/0403A920, prev 0/0403A850, desc: INSERT_LEAF off 398, blkref #0: rel 1663/13808/2703 blk 1 rmgr: Btree len (rec/tot): 53/ 5905, tx: 599, lsn: 0/0403A960, prev 0/0403A920, desc: INSERT_LEAF off 72, blkref #0: rel 1663/13808/2704 blk 1 FPW rmgr: Heap len (rec/tot): 80/ 80, tx: 599, lsn: 0/0403C090, prev 0/0403A960, desc: INSERT off 214, blkref #0: rel 1663/13808/2608 blk 54 rmgr: Btree len (rec/tot): 72/ 72, tx: 599, lsn: 0/0403C0E0, prev 0/0403C090, desc: INSERT_LEAF off 126, blkref #0: rel 1663/13808/2673 blk 30 rmgr: Btree len (rec/tot): 53/ 4825, tx: 599, lsn: 0/0403C128, prev 0/0403C0E0, desc: INSERT_LEAF off 32, blkref #0: rel 1663/13808/2674 blk 44 FPW rmgr: Heap len (rec/tot): 54/ 4058, tx: 599, lsn: 0/0403D408, prev 0/0403C128, desc: INSERT off 8, blkref #0: rel 1663/13808/1259 blk 5 FPW rmgr: Btree len (rec/tot): 53/ 2033, tx: 599, lsn: 0/0403E400, prev 0/0403D408, desc: INSERT_LEAF off 97, blkref #0: rel 1663/13808/2662 blk 2 FPW rmgr: Btree len (rec/tot): 53/ 2493, tx: 599, lsn: 0/0403EBF8, prev 0/0403E400, desc: INSERT_LEAF off 48, blkref #0: rel 1663/13808/2663 blk 2 FPW rmgr: Btree len (rec/tot): 53/ 3713, tx: 599, lsn: 0/0403F5B8, prev 0/0403EBF8, desc: INSERT_LEAF off 177, blkref #0: rel 1663/13808/3455 blk 4 FPW rmgr: Heap len (rec/tot): 54/ 6738, tx: 599, lsn: 0/04040458, prev 0/0403F5B8, desc: INSERT off 45, blkref #0: rel 1663/13808/1249 blk 48 FPW rmgr: Btree len (rec/tot): 53/ 3189, tx: 599, lsn: 0/04041EB0, prev 0/04040458, desc: INSERT_LEAF off 114, blkref #0: rel 1663/13808/2658 blk 13 FPW rmgr: Btree len (rec/tot): 53/ 3413, tx: 599, lsn: 0/04042B40, prev 0/04041EB0, desc: INSERT_LEAF off 166, blkref #0: rel 1663/13808/2659 blk 9 FPW rmgr: Heap len (rec/tot): 171/ 171, tx: 599, lsn: 0/04043898, prev 0/04042B40, desc: INSERT off 46, blkref #0: rel 1663/13808/1249 blk 48 rmgr: Btree len (rec/tot): 64/ 64, tx: 599, lsn: 0/04043948, prev 0/04043898, desc: INSERT_LEAF off 115, blkref #0: rel 1663/13808/2658 blk 13 rmgr: Btree len (rec/tot): 64/ 64, tx: 599, lsn: 0/04043988, prev 0/04043948, desc: INSERT_LEAF off 167, blkref #0: rel 1663/13808/2659 blk 9 rmgr: Heap len (rec/tot): 171/ 171, tx: 599, lsn: 0/040439C8, prev 0/04043988, desc: INSERT off 47, blkref #0: rel 1663/13808/1249 blk 48 rmgr: Btree len (rec/tot): 64/ 64, tx: 599, lsn: 0/04043A78, prev 0/040439C8, desc: INSERT_LEAF off 116, blkref #0: rel 1663/13808/2658 blk 13 rmgr: Btree len (rec/tot): 64/ 64, tx: 599, lsn: 0/04043AB8, prev 0/04043A78, desc: INSERT_LEAF off 168, blkref #0: rel 1663/13808/2659 blk 9 rmgr: Heap len (rec/tot): 171/ 171, tx: 599, lsn: 0/04043AF8, prev 0/04043AB8, desc: INSERT off 48, blkref #0: rel 1663/13808/1249 blk 48 rmgr: Btree len (rec/tot): 72/ 72, tx: 599, lsn: 0/04043BA8, prev 0/04043AF8, desc: INSERT_LEAF off 117, blkref #0: rel 1663/13808/2658 blk 13 rmgr: Btree len (rec/tot): 64/ 64, tx: 599, lsn: 0/04043BF0, prev 0/04043BA8, desc: INSERT_LEAF off 166, blkref #0: rel 1663/13808/2659 blk 9 rmgr: Heap len (rec/tot): 171/ 171, tx: 599, lsn: 0/04043C30, prev 0/04043BF0, desc: INSERT off 49, blkref #0: rel 1663/13808/1249 blk 48 rmgr: Btree len (rec/tot): 72/ 72, tx: 599, lsn: 0/04043CE0, prev 0/04043C30, desc: INSERT_LEAF off 118, blkref #0: rel 1663/13808/2658 blk 13 rmgr: Btree len (rec/tot): 64/ 64, tx: 599, lsn: 0/04043D28, prev 0/04043CE0, desc: INSERT_LEAF off 166, blkref #0: rel 1663/13808/2659 blk 9 rmgr: Heap len (rec/tot): 171/ 171, tx: 599, lsn: 0/04043D68, prev 0/04043D28, desc: INSERT off 50, blkref #0: rel 1663/13808/1249 blk 48 rmgr: Btree len (rec/tot): 72/ 72, tx: 599, lsn: 0/04043E18, prev 0/04043D68, desc: INSERT_LEAF off 117, blkref #0: rel 1663/13808/2658 blk 13 rmgr: Btree len (rec/tot): 64/ 64, tx: 599, lsn: 0/04043E60, prev 0/04043E18, desc: INSERT_LEAF off 166, blkref #0: rel 1663/13808/2659 blk 9 rmgr: Heap len (rec/tot): 171/ 171, tx: 599, lsn: 0/04043EA0, prev 0/04043E60, desc: INSERT off 51, blkref #0: rel 1663/13808/1249 blk 48 rmgr: Btree len (rec/tot): 72/ 72, tx: 599, lsn: 0/04043F50, prev 0/04043EA0, desc: INSERT_LEAF off 119, blkref #0: rel 1663/13808/2658 blk 13 rmgr: Btree len (rec/tot): 64/ 64, tx: 599, lsn: 0/04043F98, prev 0/04043F50, desc: INSERT_LEAF off 166, blkref #0: rel 1663/13808/2659 blk 9 rmgr: Heap len (rec/tot): 171/ 171, tx: 599, lsn: 0/04043FD8, prev 0/04043F98, desc: INSERT off 52, blkref #0: rel 1663/13808/1249 blk 48 rmgr: Btree len (rec/tot): 72/ 72, tx: 599, lsn: 0/040440A0, prev 0/04043FD8, desc: INSERT_LEAF off 117, blkref #0: rel 1663/13808/2658 blk 13 rmgr: Btree len (rec/tot): 64/ 64, tx: 599, lsn: 0/040440E8, prev 0/040440A0, desc: INSERT_LEAF off 166, blkref #0: rel 1663/13808/2659 blk 9 rmgr: Heap len (rec/tot): 171/ 171, tx: 599, lsn: 0/04044128, prev 0/040440E8, desc: INSERT off 53, blkref #0: rel 1663/13808/1249 blk 48 rmgr: Btree len (rec/tot): 72/ 72, tx: 599, lsn: 0/040441D8, prev 0/04044128, desc: INSERT_LEAF off 120, blkref #0: rel 1663/13808/2658 blk 13 rmgr: Btree len (rec/tot): 64/ 64, tx: 599, lsn: 0/04044220, prev 0/040441D8, desc: INSERT_LEAF off 166, blkref #0: rel 1663/13808/2659 blk 9 rmgr: Heap2 len (rec/tot): 72/ 72, tx: 599, lsn: 0/04044260, prev 0/04044220, desc: CLEAN remxid 597, blkref #0: rel 1663/13808/2608 blk 54 rmgr: Heap len (rec/tot): 80/ 80, tx: 599, lsn: 0/040442A8, prev 0/04044260, desc: INSERT off 215, blkref #0: rel 1663/13808/2608 blk 54 rmgr: Btree len (rec/tot): 53/ 7317, tx: 599, lsn: 0/040442F8, prev 0/040442A8, desc: INSERT_LEAF off 144, blkref #0: rel 1663/13808/2673 blk 39 FPW rmgr: Btree len (rec/tot): 53/ 7121, tx: 599, lsn: 0/04045F90, prev 0/040442F8, desc: INSERT_LEAF off 124, blkref #0: rel 1663/13808/2674 blk 26 FPW rmgr: Heap2 len (rec/tot): 126/ 126, tx: 599, lsn: 0/04047B80, prev 0/04045F90, desc: CLEAN remxid 597, blkref #0: rel 1663/13808/1249 blk 48 rmgr: Standby len (rec/tot): 42/ 42, tx: 599, lsn: 0/04047C00, prev 0/04047B80, desc: LOCK xid 599 db 13808 rel 16517 rmgr: Transaction len (rec/tot): 469/ 469, tx: 599, lsn: 0/04047C30, prev 0/04047C00, desc: COMMIT 2019-10-28 11:43:42.468689 CST; inval msgs: catcache 74 catcache 73 catcache 74 catcache 73 catcache 50 catcache 49 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 snapshot 2608 relcache 16517 rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/04047E08, prev 0/04047C30, desc: RUNNING_XACTS nextXid 600 latestCompletedXid 599 oldestRunningXid 600 rmgr: Transaction len (rec/tot): 34/ 34, tx: 600, lsn: 0/04047E40, prev 0/04047E08, desc: COMMIT 2019-10-28 11:43:54.831572 CST rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/04047E68, prev 0/04047E40, desc: RUNNING_XACTS nextXid 601 latestCompletedXid 600 oldestRunningXid 601

相关信息

postgres=# select oid,* from pg_tablespace; oid | spcname | spcowner | spcacl | spcoptions ------+------------+----------+--------+------------ 1663 | pg_default | 10 | | 1664 | pg_global | 10 | | (2 rows) ; postgres=# with tmp_file as ( select 'base/13808/1247' as file_path union all select 'base/13808/2703' as file_path union all select 'base/13808/2704' as file_path union all select 'base/13808/2608' as file_path union all select 'base/13808/2673' as file_path union all select 'base/13808/2674' as file_path union all select 'base/13808/1259' as file_path union all select 'base/13808/2662' as file_path union all select 'base/13808/2663' as file_path union all select 'base/13808/3455' as file_path union all select 'base/13808/1249' as file_path union all select 'base/13808/2658' as file_path union all select 'base/13808/2659' ) select oid,relname,relkind,relfilenode,pg_relation_filepath(pc.oid) from pg_class pc where 1=1 and pg_relation_filepath(pc.oid) in ( select * from tmp_file ) order by pc.relkind ; oid | relname | relkind | relfilenode | pg_relation_filepath ------+-----------------------------------+---------+-------------+---------------------- 2704 | pg_type_typname_nsp_index | i | 0 | base/13808/2704 2658 | pg_attribute_relid_attnam_index | i | 0 | base/13808/2658 2659 | pg_attribute_relid_attnum_index | i | 0 | base/13808/2659 2662 | pg_class_oid_index | i | 0 | base/13808/2662 2663 | pg_class_relname_nsp_index | i | 0 | base/13808/2663 3455 | pg_class_tblspc_relfilenode_index | i | 0 | base/13808/3455 2673 | pg_depend_depender_index | i | 2673 | base/13808/2673 2674 | pg_depend_reference_index | i | 2674 | base/13808/2674 2703 | pg_type_oid_index | i | 0 | base/13808/2703 2608 | pg_depend | r | 2608 | base/13808/2608 1249 | pg_attribute | r | 0 | base/13808/1249 1259 | pg_class | r | 0 | base/13808/1259 1247 | pg_type | r | 0 | base/13808/1247 (13 rows)

relkind=i 的为索引(rmgr: Btree)，可以忽略。涉及到relkind=r 的 pg_depend、pg_attribute、pg_class、pg_type 这四个系统表(rmgr: Heap)。

这几个表可以参考： http://postgres.cn/docs/10/catalog-pg-depend.html http://postgres.cn/docs/10/catalog-pg-attribute.html http://postgres.cn/docs/10/catalog-pg-class.html http://postgres.cn/docs/10/catalog-pg-type.html

最新回复(0)