Thread: Questions about PostgreSQL implementation details

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
itis 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
theyjust a virtual representation ?
 

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
implementedin C directly.
 

Q1.2 If it is possible and not done, what is the reason?

--

Q2. Are PostgreSQL's "meta-constraints" (i.e. constraints related to database structure such as "a table can only have
asingle 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.

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

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


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

Cheers,

Julien Delplanque

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

Julien Delplanque <julien.delplanque@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
theyjust 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,
DROPTABLE, 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
havea 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

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
theyjust 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,
DROPTABLE, 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
areimplemented 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
havea 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

On 12/9/19 7:52 AM, Mark Dilger wrote:
>>
>> 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.

I realize I could be a bit more helpful, here.  For a SQL command
like "CREATE TABLE", you can first look in src/backend/parser/gram.y
for the grammar rule.  In this case, searching for CREATE TABLE
leads you to a banner comment around line 3132.  The rule for
CreateStmt creates a node of type CreateStmt.  That leads you
to a struct of the same name in src/include/nodes/parsenodes.h.
You can see all the fields of that struct, and reconcile those
against what the code in gram.y is doing to populate those
fields.  You can then look in src/backend/tcop/utility.c for
the T_CreateStmt nodeTag, and you'll find that it appears in
a few switch statements.  One of those in ProcessUtilitySlow
calls DefineRelation along with a bunch of other stuff.

That's how you can trace these commands.  Let's take DROP TABLE
as a second example.  This one is harder, because "DROP TABLE"
doesn't exist exactly.  It's part of a larger grammar production
for DropStmt that includes other DROP commands.  All the same,
you can see there is a rule for DropStmt that creates a node of
type DropStmt.  Looking in src/include/nodes/parsenodes.h you
can see the struct of the same name.  Looking in tcop/utility.c
you see T_DropStmt is handled in a few switch statements, and
seem to run through ProcessUtilitySlow and ExecDropStmt.  The
function ExecDropStmt has a switch over stmt->removeType, which
was populated back in gram.y to the value OBJECT_TABLE.  That
now serves to select the RemoveRelations function.

This sort of analysis is fairly useful for getting from a SQL
statement to the code in src/backend/commands/ that implements
the guts of that statement.

I hope this helps.

-- 
Mark Dilger

Mark Dilger <hornschnorter@gmail.com> writes:
> [ useful tips about finding the code that implements a SQL command ]

BTW, if it wasn't obvious already, you *really* want to have some kind
of tool that easily finds the definition of a particular C symbol.
You can fall back on "grep -r" or "git grep", but lots of people use
ctags or etags or some other C-aware indexing tool.

            regards, tom lane

Thanks for your answer, I will dive into the C code then.

Le 9/12/19 à 16:52, Mark Dilger a écrit :
>
> 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. 

Indeed, I should have say it in my email, I saw in the documentation 
that some of them are views. :-)

Cheers,

Julien

Le 9/12/19 à 16:52, Mark Dilger a écrit :
>
>
> 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.
>
>
>