Search code examples
postgresqlcatalog

What is OID of the system catalog in PostgreSQL : column classid in pg_depend

During implementation of one task I found column classid in pg_depend table. Documentation(https://www.postgresql.org/docs/12/catalog-pg-depend.html) says : "classid - the OID of the system catalog the dependent object is in". As I know, usually term "catalog" = "database" in PostgreSQL; however once I've checked pg_database then I was surprised : in my PostgreSQL there just 4 values for oid's in pg_database and non of it is equal to 21 values of classid in pg_depend.

Would someone clarify what is classid in pg_depend and what is "system catalog" in this context ?

Solution

  • A “catalog” is not a database. In PostgreSQL parlance, a catalog is a metadata table — one of the tables where PostgreSQL stores the information what tables there are in the database (pg_catalog.pg_class), what columns there are in the tables (pg_catalog.pg_attribute) and so on.

    Each of these tables (like all other tables in PostgreSQL) has an object ID, a number that uniquely identifies them and remains unchanged, even if you rename the table. pg_depend is the catalog that tracks relationships between database objects. For example, each table depends on the table's schema. This information is used to prevent the user from dropping a schema as long as it is still needed by a table. In other cases, the dependency is used to automatically drop dependent objects when an object is dropped (for example, all constraints of a table that you drop vanish automatically.

    The dependencies are stored in pg_depend as follows:

    • classid is the object ID of the catalog table the contains the dependent object (for a table that would be pg_class, whose object ID is always 1259)
    • objid is the object ID of the dependent object (if classid is 1259, that will be the object ID of a table)
    • refclassid and refobjid are as above for the referenced object
    • deptype is the type of dependency

    You might wonder why objid is needed. The reason is that object IDs are not globally unique, but only within the same type of object. For example, no two tables, views or sequences could have the same object ID, but a function or database can have the same object ID as a table.