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,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,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','f','r','1','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','-1','c','t',','record_in','record_out','record_recv','record_send','-','d','x','',''); 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','4','-7','p','i','0'); insert into pg_attribute(attrelid,'cmax','29','-6','xmax','28','-5','cmin','-4','xmin','-3','ctid','27','6','s','a','23','0'); insert into pg_depend(classid,objid,objsubid,refclassid,refobjid,refobjsubid,deptype) values('1247','1259','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)
做了一个简单的测试,是可以的
使用这种方法要对数据字典非常数据,我们刚刚只是建了一个简单的表,所涉及的数据字典比较少,如果有分区表,索引表等就会非常复杂了。 (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|