Shulou(Shulou.com)06/02 Report--

This article mainly explains how to define PostgreSQL system table and its TOAST. Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how PostgreSQL system tables and their TOAST are defined.

This article only talks about how PG defines system tables, rather than modifying system tables or even defining their own system tables.

PG system tables, such as pg_class, pg_attribute, pg_type, etc.

These tables are related to each other, and the latter two need to record their own table definitions in pg_class, while pg_class needs to record their own fields and types in the latter two, which looks like a dead end.

If you are interested in how they are defined, you can read:

System Catalog Declarations and Initial Contents

It is more detailed and clear than previous documents, and the way dat files are defined is clearer than before.

1. System table data structure definition, open pg_class.h

CATALOG (pg_class,1259,RelationRelationId) BKI_BOOTSTRAP BKI_ROWTYPE_OID (83 dint RelationRelationalization Rowtypewriter ID) BKI_SCHEMA_MACRO {NameData relname; / * class name * / Oid relnamespace; / * OID of namespace containing this class * / Oid reltype; / * OID of entry in pg_type for table's * implicit row type * / Oid reloftype; / * OID of entry in pg_type for underlying * composite type * /..

Pg_class table structure

Flying=#\ d pg_class Table "pg_catalog.pg_class" Column | Type | Collation | Nullable | Default-+- relname | name | | not null | relnamespace | oid | | not null | reltype | oid | | not null | reloftype | oid | | not null |.

Whether it is an one-to-one correspondence, in fact, the table-building script is extracted and generated here, which is why they are so consistent.

2. Src/backend/catalog/genbki.pl script

This Perl script is responsible for extracting the definition and generating postgres.bki, which can be understood in conjunction with the above documentation if you are interested.

BKI script is supported by a separate syntax engine, not what we often call SQL, it is under bootstrap, you can see for yourself if you are interested, and we'll talk about it later.

3. Code

We're just looking at what PG does here, not modifying it. Each definition has its own code, which is under commands and catalog.

Later, I will write a separate article on how to add my own system fields.

4. TOAST table

Fields like the text type require toast storage, and the OID of the system table is preset and not allowed to be modified, so their TOAST also needs to be preset.

Let's take a look at src/include/catalog/toasting.h. Take pg_proc as an example:

DECLARE_TOAST (pg_proc, 2836, 2837)

The preprocessor is defined like this:

# define DECLARE_TOAST (name,toastoid,indexoid) extern int no_such_variable

It seems pointless, right? in fact, it's not really for PG code, it's genbki, and you can see what's going on when you look at the code.

5. Index

System tables also need to be indexed, defined in src/include/catalog/indexing.h or taking pg_proc as an example:

DECLARE_UNIQUE_INDEX (pg_proc_oid_index, 2690, on pg_proc using btree (oid oid_ops)); # define ProcedureOidIndexId 2690DECLARE_UNIQUE_INDEX (pg_proc_proname_args_nsp_index, 2691, on pg_proc using btree (proname name_ops, proargtypes oidvector_ops, pronamespace oid_ops)); # define ProcedureNameArgsNspIndexId 2691

It has two indexes, and the definitions above include: name, OID, am, and field.

These preprocessors are also not for C code, only genbki can be used.

6. Initial data

They are defined in a dat file with the same name, such as pg_proc.dat.

7. Optional values for fields

This is not a table definition, but it is also put together. For example, pg_proc 's provolatile only allows:

# define PROVOLATILE_IMMUTABLE 'i' / * never changes for given input * / # define PROVOLATILE_STABLE 's'/ * does not change within a scan * / # define PROVOLATILE_VOLATILE 'v' / * can change even within a scan * / now that you have a better understanding of "PostgreSQL system table and its TOAST definition", you might as well do it in practice! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.