Re: Questions about PostgreSQL implementation details

Hi Julien!

On 09/12/2019 17:35, Julien Delplanque wrote:
> 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 ?

Yes, the catalog tables are the authoritative source. The system uses
those tables internally to get the information too.

Some of the pg_* relations are just views over other catalog tables, though.

> 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).
>
> 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.
>
> Q1.2 If it is possible and not done, what is the reason?

The C code for the DDL commands do some things in addition to modifying
the catalog tables. Notably for CREATE TABLE, it creates the relation
file in the data directory, where all the data is stored. It also
handles locking, invalidating various caches, firing event triggers etc.
Except for creating relation files, those other things happen just in
memory, though.

It is not supported, and please don't do it in production, but you could
try it out. Set "allow_system_table_mods=on", and insert to pg_class,
pg_attribute, etc. See how well it works. Beware that there are internal
caches, called "syscaches", in backends over the catalog tables, so if
you modify them directly, you may need to restart for the changes to
take effect.

> 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?
>
> I guess, again, they are implemented in C code, but I might be wrong.

In C code.

> Q2.1 If they are not implemented via data constraints on meta-description tables, why ?

I think there are some restrictions that cannot easily be represented as
constraints. Also, we've never supported constraints on catalog tables,
so no one's given much thought to what it would look like if we did.

> Q2.2 Is there somewhere in the documentation a list of such "meta-constraints" implemented by PostgreSQL?

Hmm, I don't think there is. Your best bet is to just look at the C
code, I'm afraid.

- Heikki