postgresql 通過資料字典建表，不用create TABLE

postgresql 通過資料字典建表，不用create TABLE

今天對postgresql的資料字典有個簡單的瞭解，postgre的表結構等資訊都是放在資料字典中的，那我們能不能簡單的往資料字典中插入資料來實現建表的功能呢？
以下做了一個簡單的實驗：

1.在postgresql的執行個體中建立一個表
create table test(a int);

然後去查以下幾個資料字典，結果如下
pg_class:
select oid,* from pg_class where relname ='test';--表名
  oid  | relname | relnamespace | reltype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass | relfrozenxid | relacl | reloptions
-------+---------+--------------+---------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+--------------+--------+------------
 73728 | test    |         2200 |   73729 |       10 |     0 |       73728 |             0 |        0 |         0 |             0 |             0 | f           | f           | r       |        1 |         0 |           0 |        0 |        0 |       0 | f          | f          | f           | f              |          814 |        |

--以下通過test表的oid查詢
pg_attribute:
select * from pg_attribute  where attrelid ='73728';
 attrelid | attname  | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attalign | attnotnull | atthasdef | attisdropped | attislocal | attinhcount
----------+----------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+-----------+--------------+------------+-------------
    73728 | tableoid |       26 |             0 |      4 |     -7 |        0 |          -1 |        -1 | t        | p          | i        | t          | f         | f            | t          |           0
    73728 | cmax     |       29 |             0 |      4 |     -6 |        0 |          -1 |        -1 | t        | p          | i        | t          | f         | f            | t          |           0
    73728 | xmax     |       28 |             0 |      4 |     -5 |        0 |          -1 |        -1 | t        | p          | i        | t          | f         | f            | t          |           0
    73728 | cmin     |       29 |             0 |      4 |     -4 |        0 |          -1 |        -1 | t        | p          | i        | t          | f         | f            | t          |           0
    73728 | xmin     |       28 |             0 |      4 |     -3 |        0 |          -1 |        -1 | t        | p          | i        | t          | f         | f            | t          |           0
    73728 | ctid     |       27 |             0 |      6 |     -1 |        0 |          -1 |        -1 | f        | p          | s        | t          | f         | f            | t          |           0
    73728 | a        |       23 |            -1 |      4 |      1 |        0 |          -1 |        -1 | t        | p          | i        | f          | f         | f            | t          |           0

pg_depend:
select * from pg_depend where refobjid ='73728';
 classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
---------+-------+----------+------------+----------+-------------+---------
    1247 | 73729 |        0 |       1259 |    73728 |           0 | i

pg_type:
select oid,* from pg_type where typrelid = '73728';
  oid  | typname | typnamespace | typowner | typlen | typbyval | typtype | typisdefined | typdelim | typrelid | typelem | typinput  | typoutput  | typreceive  |   typsend   | typanalyze | typalign | typstorage | typnotnull | typbasetype | typtypmod | typndims | typdefaultbin | typdefault
-------+---------+--------------+----------+--------+----------+---------+--------------+----------+----------+---------+-----------+------------+-------------+-------------+------------+----------+------------+------------+-------------+-----------+----------+---------------+------------
 73729 | test    |         2200 |       10 |     -1 | f        | c       | t            | ,        |    73728 |       0 | record_in | record_out | record_recv | record_send | -          | d        | x          | f          |           0 |        -1 |        0 |               |

tableoid,cmax,xmax,cmin,xmin,ctid都是這個表的隱藏欄位，select的時候指定，是可以找到的
mysql=# select tableoid,cmax,xmax,cmin,xmin,ctid,a from test;
 tableoid | cmax | xmax | cmin | xmin | ctid | a
----------+------+------+------+------+------+---
(0 rows)

mysql=# insert into test values(1);
INSERT 0 1
mysql=#  select tableoid,cmax,xmax,cmin,xmin,ctid,a from test;
 tableoid | cmax | xmax | cmin | xmin | ctid  | a
----------+------+------+------+------+-------+---
    73728 |    0 |    0 |    0 |  842 | (0,1) | 1
   

可以看到，資料庫中這個資料檔案的大小是0
[mysql@pttest4 data]$ ll ./base/16386/73728
-rw------- 1 mysql mysql 0 Dec  1 22:11 ./base/16386/73728

2.我們已經知道上述四個資料字典的資料了，這樣子我們模仿，給這四個資料字典插入資料，是不是我們也可以建立一張表呢。
下面我們就建立一張 test_cxf的表，字典跟上面的一樣。

a.首先往pg_class中插入資料：
mysql=# insert into pg_class(relname,relnamespace,reltype,relowner,relam,relfilenode,reltablespace,relpages,reltuples,reltoastrelid,reltoastidxid,relhasindex,relisshared,relkind,relnatts,relchecks,reltriggers,relukeys,relfkeys,relrefs,relhasoids,relhaspkey,relhasrules,relhassubclass,relfrozenxid,relacl,reloptions) values('test_cxf','2200','73731','10','0','73730','0','0','0','0','0','f','f','r','1','0','0','0','0','0','f','f','f','f','814','{}','{}');
INSERT 73730 1

可以看到，這一行的oid為73730

b.接著往pg_type中插入資料：
mysql=# insert into pg_type(typname,typnamespace,typowner,typlen,typbyval,typtype,typisdefined,typdelim,typrelid,typelem,typinput,typoutput,typreceive,typsend,typanalyze,typalign,typstorage,typnotnull,typbasetype,typtypmod,typndims,typdefaultbin,typdefault) values('test_cxf','2200','10','-1','f','c','t',',','73730','0','record_in','record_out','record_recv','record_send','-','d','x','f','0','-1','0','','');
INSERT 73731 1

c.將pg_class中的reltype資訊更新
UPDATE pg_class SET reltype=73731 WHERE oid = 73730;

d.往pg_attribute跟pg_depend中插入資料
insert into pg_attribute(attrelid , attname  , atttypid , attstattarget , attlen , attnum , attndims , attcacheoff , atttypmod , attbyval , attstorage , attalign , attnotnull , atthasdef , attisdropped , attislocal , attinhcount ) values('73730','tableoid','26','0','4','-7','0','-1','-1','t','p','i','t','f','f','t','0');
insert into pg_attribute(attrelid , attname  , atttypid , attstattarget , attlen , attnum , attndims , attcacheoff , atttypmod , attbyval , attstorage , attalign , attnotnull , atthasdef , attisdropped , attislocal , attinhcount ) values('73730','cmax','29','0','4','-6','0','-1','-1','t','p','i','t','f','f','t','0');
insert into pg_attribute(attrelid , attname  , atttypid , attstattarget , attlen , attnum , attndims , attcacheoff , atttypmod , attbyval , attstorage , attalign , attnotnull , atthasdef , attisdropped , attislocal , attinhcount ) values('73730','xmax','28','0','4','-5','0','-1','-1','t','p','i','t','f','f','t','0');
insert into pg_attribute(attrelid , attname  , atttypid , attstattarget , attlen , attnum , attndims , attcacheoff , atttypmod , attbyval , attstorage , attalign , attnotnull , atthasdef , attisdropped , attislocal , attinhcount ) values('73730','cmin','29','0','4','-4','0','-1','-1','t','p','i','t','f','f','t','0');
insert into pg_attribute(attrelid , attname  , atttypid , attstattarget , attlen , attnum , attndims , attcacheoff , atttypmod , attbyval , attstorage , attalign , attnotnull , atthasdef , attisdropped , attislocal , attinhcount ) values('73730','xmin','28','0','4','-3','0','-1','-1','t','p','i','t','f','f','t','0');
insert into pg_attribute(attrelid , attname  , atttypid , attstattarget , attlen , attnum , attndims , attcacheoff , atttypmod , attbyval , attstorage , attalign , attnotnull , atthasdef , attisdropped , attislocal , attinhcount ) values('73730','ctid','27','0','6','-1','0','-1','-1','f','p','s','t','f','f','t','0');
insert into pg_attribute(attrelid , attname  , atttypid , attstattarget , attlen , attnum , attndims , attcacheoff , atttypmod , attbyval , attstorage , attalign , attnotnull , atthasdef , attisdropped , attislocal , attinhcount ) values('73730','a','23','-1','4','1','0','-1','-1','t','p','i','f','f','f','t','0');
insert into pg_depend(classid,objid,objsubid,refclassid,refobjid,refobjsubid,deptype) values('1247','73731','0','1259','73730','0','i');

e.查詢資料
mysql=# select * from test_cxf;
ERROR:  could not open relation 1663/16386/73800: No such file or directory
報錯，因為沒有資料檔案，我們在base目錄下touch一個空檔案
[mysql@pttest4 base]$ cd 16386
[mysql@pttest4 16386]$ touch 73800
[mysql@pttest4 16386]$ ll 73800
-rw-rw-r-- 1 mysql mysql 0 Dec  1 23:27 73800

簡單查詢一下資料，做一些資料操作
mysql=# /d test_cxf                                                                              
   Table "public.test_cxf"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | integer |

mysql=# select * from test_cxf;
 a
---
(0 rows)
mysql=# insert into test_cxf values(124);
INSERT 0 1
mysql=# insert into test_cxf select * from test_cxf;
INSERT 0 1
mysql=# insert into test_cxf select * from test_cxf;
INSERT 0 2
mysql=# insert into test_cxf select * from test_cxf;
INSERT 0 4
mysql=# select * from test_cxf;
  a 
-----
 124
 124
 124
 124
 124
 124
 124
 124
(8 rows)

mysql=# create index a_index on test_cxf(a);
CREATE INDEX
mysql=# /d test_cxf        
   Table "public.test_cxf"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | integer |
Indexes:
    "a_index" btree (a)

做了一個簡單的測試，是可以的

使用這種方法要對資料字典非常資料，我們剛剛只是建了一個簡單的表，所涉及的資料字典比較少，如果有分區表，索引表等就會非常複雜了。