Re: Questions about PostgreSQL implementation details

On 12/9/19 7:35 AM, Julien Delplanque wrote:
> Hello PostgreSQL hackers,
> 
> I hope I am posting on the right mailing-list.
> 
> I am actually doing a PhD related to relational databases and software 
> engineering.
> 
> I use PostgreSQL for my research.
> 
> I have a few questions about the internals of PostgreSQL and I think 
> they require experts knowledge.
> 
> I could not find documentation about that in the nice PostgreSQL 
> documentation but maybe I missed something? Tell me if it is the case.
> 
> My Questions:
> 
> Q1. Are PostgreSQL's meta-description tables (such as pg_class) the 
> "reality" concerning the state of the DB or are they just a virtual 
> representation ?

Not all of them are real tables; some of the pg_catalog relations are
views over others of them.  But many of them are real tables with C
structs that back them.  Take a look in src/include/catalog/pg_class.h
and you'll see the C struct definition, somewhat obscured by some
macros that make it less obvious to people not familiar with the
postgresql sources.

On line 29:

CATALOG(pg_class,1259,RelationRelationId) BKI_BOOTSTRAP 
BKI_ROWTYPE_OID(83,RelationRelation_Rowtype_Id) BKI_SCHEMA_MACRO
{
...
}

That's a typedef.  See genbki.h where it defines the macro:

#define CATALOG(name,oid,oidmacro)    typedef struct CppConcat(FormData_,name)

> What I would like to know with this question is: would it be possible to 
> implement DDL queries (e.g. CREATE TABLE, DROP TABLE, CREATE VIEW, ALTER 
> TABLE, etc.) as DML queries that modify the meta-data stored in 
> meta-description tables?
> 
> For example, something like:
> 
> INSERT INTO pg_class [...];
> 
> To create a new table (instead of the CREATE TABLE DDL query).

You are not allowed to insert into the pg_class table directly.  There
are good reasons for that.  Simply inserting a row into this table would
not cause all the infrastructure that backs a table to pop into
existence.  So you have to use the DDL commands.

> 
> Q1.1 If it is possible, is what is done in reality? I have the feeling 
> that it is not the case and that DDL queries are implemented in C directly.

See src/backend/commands/tablecmds.c, function DefineRelation.



-- 
Mark Dilger