Re: Questions about PostgreSQL implementation details

Julien Delplanque <julien(dot)delplanque(at)inria(dot)fr> writes:
> I have a few questions about the internals of PostgreSQL and I think they require experts knowledge.

> 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 ?

The system catalogs are reality as far as the effects of DDL go. In the
particular case of pg_class, there is additional reality, which is that
(most) pg_class rows represent one or more data files on-disk. You could
in fact simulate many DDL operations by manual inserts/deletes/updates on
system catalogs; but that would not result in any actions on the data
files, so it falls down for the specific cases of CREATE/DROP TABLE,
CREATE/DROP INDEX, etc.

> 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?

Underneath, many of those operations are just catalog manipulations,
so yes up to the point where you need to do something that impacts
user data storage.

(In practice, getting all the details right from a SQL client
would be a pretty painful thing, so I'm not sure I see the point.
Usually, modifying the PG C code or writing an extension would be
a saner approach to modifying the system's behavior.)

> Q2. Are PostgreSQL's "meta-constraints" (i.e. constraints related to database structure such as "a table can only have a single primary key") implemented in C code or via data constraints on PostgreSQL's meta-description tables?

Mostly code. The only real constraints on the system catalogs, in the
sense of something that would reject an ill-advised low-level update,
are the unique indexes. There are also NOT NULL markers on many of
the catalogs' columns, but those are only enforced against manual
SQL updates not updates made by C code. This is all somewhat historical,
I suppose, but it's worked well enough for us.

> Thanks in advance for answering my questions, any help or pointers to existing documentation will be appreciated.

There's no substitute for reading the source code. Unlike some other
systems, PG was developed as open source from the beginning, so there
is not much in the way of a "theory of operations manual" or other
separate internals documentation --- people with these sorts of
questions are expected to go read the code. You could perhaps start
by skimming

https://www.postgresql.org/docs/devel/internals.html

and then go look for README files in the portions of the source
tree that interest you.

regards, tom lane