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