Julien Delplanque <julien.delplan...@inria.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
