PostgreSQL creates a table using a data dictionary without the need to create a table

PostgreSQL creates a table using a data dictionary without the need to create a table

Today, I have a simple understanding of postgresql's data dictionary. The postgre table structure and other information are stored in the data dictionary, can we simply insert data into the data dictionary to create a table?
The following is a simple experiment:

1. Create a table in the PostgreSQL instance
Create Table Test (a int );

Check the following data dictionaries. The results are as follows:
Pg_class:
Select OID, * From pg_class where relname = 'test'; -- table name
Oid | relname | relnamespace | reltype | relowner | relam | relfilenode | reltablespace | relpages | metrics | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | metrics | relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass | relfrozenxid | relacl | reloptions
------- + --------- + ------------ + --------- + ---------- + ------- + Hour + ---------- + ----------- + hour + ----------- + ---------- + ----------- + hour + ---------- + ------------ + --------- + ------------ + ------------- + ---------------- + -------------- + -------- + ------------
73728 | test | 2200 | 73729 | 10 | 0 | 73728 | 0 | 0 | 0 | 0 | f | r | 1 | 0 | 0 | 0 | 0 | 0 | 0 | f | 814 |

-- The following is a query using the oId of the test table.
Pg_attribute:
Select * From pg_attribute where attrelid = '000000 ';
Attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | identifier | atttypmod | attbyval | attstorage | attalign | attnotnull | atthasdef | attisdropped | attislocal | attinhcount
---------- + -------- + ---------- + ----------- + ---------- + ------------ + ----------- + -------------- + -------------
73728 | tableoid | 26 | 0 | 4 |-7 | 0 |-1 |-1 | T | p | I | T | f | T | 0
73728 | Cmax | 29 | 0 | 4 |-6 | 0 |-1 |-1 | T | p | I | T | f | T | 0
73728 | xmax | 28 | 0 | 4 |-5 | 0 |-1 |-1 | T | p | I | T | f | T | 0
73728 | Cmin | 29 | 0 | 4 |-4 | 0 |-1 |-1 | T | p | I | T | f | T | 0
73728 | xmin | 28 | 0 | 4 |-3 | 0 |-1 |-1 | T | p | I | T | f | T | 0
73728 | ctid | 27 | 0 | 6 |-1 | 0 |-1 |-1 | f | p | S | T | f | T | 0
73728 | A | 23 |-1 | 4 | 1 | 0 |-1 |-1 | T | p | I | f | T | 0

Pg_depend:
Select * From pg_depend where refobjid = '201312 ';
Classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
--------- + ------- + ---------- + ------------ + ---------- + ------------- + ---------
1247 | 73729 | 0 | 1259 | 73728 | 0 | I

Pg_type:
Select OID, * From pg_type where typrelid = '000000 ';
Oid | typname | typnamespace | typowner | typlen | percent | typtype | percent | typelem | typinput | percent | typreceive | typsend | typanalyze | typalign | typstorage | percent | typbasetype typtypmod | typndims | typdefaultbin | typdefault
------- + --------- + Begin + ---------- + -------- + ---------- + --------- + accept + ------------ + accept + ----------- + ---------- + --------------- + ------------
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, and ctid are all hidden fields in the table. They can be found if specified during select.
MySQL = # select tableoid, Cmax, xmax, Cmin, xmin, ctid, A from test;
Tableoid | Cmax | xmax | Cmin | xmin | ctid |
---------- + ------ + ---
(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 |
---------- + ------ + ------- + ---
73728 | 0 | 0 | 0 | 842 | (0, 1) | 1

The size of the data file in the database is 0.
[MySQL @ pttest4 data] $ ll./base/16386/73728
-RW ------- 1 MySQL 0 Dec 1./base/16386/73728

2. We already know the data of the above four data dictionaries. In this way, we can simulate and insert data to these four data dictionaries. Can we also create a new table.
Next we will create a new table named test_cxf. The dictionary is the same as above.

A. First insert data to pg_class:
MySQL = # insert into pg_class (relname, relnamespace, reltype, relowner, relam, relfilenode, reltablespace, relpages, reltuples, tables, indexes, relhasindex, relisshared, kind, relnatts, relchecks, reltriggers, relukeys, relfkeys, relrefs, relhasoids, relhaspkey, relhasrules, relhassubclass, metrics, relacl, reloptions) values ('test _ cxf', '200', '100 ', '10', '0', '000000', '0', '0', '0', '0', 'F', 'F ', 'R', '1', '0', '0', '0', '0', '0', 'F ', 'F', '000000 ','{}','{}');
Insert 73730 1

We can see that the oId of this row is 73730

B. insert data to pg_type:
MySQL = # insert into pg_type (typname, typnamespace, typowner, typlen, kernel, typtype, kernel, typelem, typinput, typoutput, typreceive, typsend, typanalyze, typal, typstorage, typnotnull, typbasetype, typtypmod, typndims, typdefaultbin, typdefault) values ('test _ cxf', '000000', '10', '-1', 'F ', 'C', 't', ',', '000000', '0', 'record _ in', 'record _ out', 'record _ rec ', 'record _ send', '-', 'D', 'x', 'F', '0', '-1', '0 ','', '');
Insert 73731 1

C. Update the reltype information in pg_class.
Update pg_class set reltype = 73731 where OID = 73730;

D. insert data to pg_attribute and pg_depend.
Insert into pg_attribute (values, attname, atttypid, attstattarget, attlen, attnum, attndims, identifier, atttypmod, identifier, attstorage, attalign, attnotnull, atthasdef, identifier, attislocal, attinhcount) values ('20140901', 'tableid', '26', '0', '4', '-7', '0','-1 ', '-1', 't', 'P',' I ', 't', 'F', 'F', 't', '0 ');
Insert into pg_attribute (values, attname, atttypid, attstattarget, attlen, attnum, attndims, identifier, atttypmod, identifier, attstorage, attalign, attnotnull, atthasdef, identifier, attislocal, attinhcount) values ('123', 'cmax ', '29', '0', '4','-6', '0', '-1 ', '-1', 't', 'P',' I ', 't', 'F', 'F', 't', '0 ');
Insert into pg_attribute (values, attname, atttypid, attstattarget, attlen, attnum, attndims, identifier, atttypmod, identifier, attstorage, attalign, attnotnull, atthasdef, identifier, attislocal, attinhcount) values ('123', 'xmax ', '28', '0', '4','-5', '0', '-1 ', '-1', 't', 'P',' I ', 't', 'F', 'F', 't', '0 ');
Insert into pg_attribute (values, attname, atttypid, attstattarget, attlen, attnum, attndims, identifier, atttypmod, identifier, attstorage, attalign, attnotnull, atthasdef, identifier, attislocal, attinhcount) values ('20140901', 'limit', '29', '0', '4', '-4', '0','-1 ', '-1', 't', 'P',' I ', 't', 'F', 'F', 't', '0 ');
Insert into pg_attribute (values, attname, atttypid, attstattarget, attlen, attnum, attndims, identifier, atttypmod, identifier, attstorage, attalign, attnotnull, atthasdef, identifier, attislocal, attinhcount) values ('20140901', 'xmin', '28', '0', '4', '-3', '0','-1 ', '-1', 't', 'P',' I ', 't', 'F', 'F', 't', '0 ');
Insert into pg_attribute (values, attname, atttypid, attstattarget, attlen, attnum, attndims, identifier, atttypmod, identifier, attstorage, attalign, attnotnull, atthasdef, identifier, attislocal, attinhcount) values ('20170', 'tid', '27', '0', '6', '-1', '0','-1 ', '-1', 'F', 'P','s', 't', 'F', 'F', 't', '0 ');
Insert into pg_attribute (values, attname, atttypid, attstattarget, attlen, attnum, attndims, identifier, atttypmod, identifier, attstorage, attalign, attnotnull, atthasdef, identifier, attislocal, attinhcount) values ('100', 'A', '23', '-1', '4', '1', '0','-1 ', '-1', 't', 'P',' I ', 'F', 't', '0 ');
Insert into pg_depend (classid, objid, objsubid, refclassid, refobjid, refobjsubid, deptype) values ('20170', '20160', '20160', '20160 ', '0', 'I ');

E. query data
MySQL = # select * From test_cxf;
Error: cocould not open relation 1663/16386/73800: no such file or directory
An error is reported. Because there is no data file, we touch an empty file in the base directory.
[MySQL @ pttest4 base] $ CD 16386
[MySQL @ pttest4 16386] $ touch 73800
[MySQL @ pttest4 16386] $ ll 73800
-RW-r -- 1 MySQL 0 Dec 1 23:27 73800

Simply query the data and perform some data operations
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 ();
Create Index
MySQL = #/d test_cxf
Table "public. test_cxf"
Column | type | Modifiers
-------- + --------- + -----------
A | integer |
Indexes:
"A_index" btree ()

A simple test is available.

This method requires that the data dictionary is very data. We just created a simple table that involves a small number of data dictionaries. If there is a partitioned table, index tables and so on will be very complicated.