>>
Catalogs
• Database Objects
• Catalogs
• Representing Databases
• Representing Tables
COMP9315 21T1 ♢ Catalogs ♢ [0/11]
∧ >>
❖ Database Objects
RDBMSs manage different kinds of objects
• databases, schemas, tablespaces
• relations/tables, attributes, tuples/records
• constraints, assertions
• views, stored procedures, triggers, rules
Many objects have names (and, in PostgreSQL, some have OIDs).
How are the different types of objects represented?
How do we go from a name (or OID) to bytes stored on disk?
COMP9315 21T1 ♢ Catalogs ♢ [1/11]
<< ∧ >>
❖ Catalogs
Consider what information the RDBMS needs about relations:
• name, owner, primary key of each relation
• name, data type, constraints for each attribute
• authorisation for operations on each relation
Similarly for other DBMS objects (e.g. views, functions, triggers, …)
This information is stored in the system catalog tables
Standard for catalogs in SQL:2003: INFORMATION_SCHEMA
COMP9315 21T1 ♢ Catalogs ♢ [2/11]
<< ∧ >>
❖ Catalogs (cont)
The catalog is affected by several types of SQL operations:
• create Object as Definition
• drop Object …
• alter Object Changes
• grant Privilege on Object
where Object is one of table, view, function, trigger, schema, …
E.g. drop table Groups; produces something like
delete from Tables
where schema = ‘public’ and name = ‘groups’;
COMP9315 21T1 ♢ Catalogs ♢ [3/11]
<< ∧ >>
❖ Catalogs (cont)
In PostgreSQL, the system catalog is available to users via:
• special commands in the psql shell (e.g. \d)
• SQL standard information_schema
e.g. select * from information_schema.tables;
The low-level representation is available to sysadmins via:
• a global schema called pg_catalog
• a set of tables/views in that schema (e.g. pg_tables)
COMP9315 21T1 ♢ Catalogs ♢ [4/11]
<< ∧ >>
❖ Catalogs (cont)
You can explore the PostgreSQl catalog via psql commands
• \d gives a list of all tables and views
• \d Table gives a schema for Table
• \df gives a list of user-defined functions
• \df+ Function gives details of Function
• \ef Function allows you to edit Function
• \dv gives a list of user-defined views
• \d+ View gives definition of View
You can also explore via SQL on the catalog tables
COMP9315 21T1 ♢ Catalogs ♢ [5/11]
<< ∧ >>
❖ Catalogs (cont)
A PostgreSQL installation (cluster) typically has many DBs
Some catalog information is global, e.g.
• catalog tables defining: databases, users, …
• one copy of each such table for the whole PostgreSQL installation
• shared by all databases in the cluster (in PGDATA/pg_global)
Other catalog information is local to each database, e.g
• schemas, tables, attributes, functions, types, …
• separate copy of each “local” table in each database
• a copy of many “global” tables is made on database creation
COMP9315 21T1 ♢ Catalogs ♢ [6/11]
<< ∧ >>
❖ Catalogs (cont)
Side-note: PostgreSQL tuples contain
• owner-specified attributes (from create table)
• system-defined attributes
• oid
•
• unique identifying number for tuple (optional)
• tableoid
•
• which table this tuple belongs to
• xmin/xmax
•
• which transaction created/deleted tuple (for MVCC)
•
OIDs are used as primary keys in many of the catalog tables.
COMP9315 21T1 ♢ Catalogs ♢ [7/11]
<< ∧ >>
❖ Representing Databases
Above the level of individual DB schemata, we have:
• databases … represented by pg_database
• schemas … represented by pg_namespace
• table spaces … represented by pg_tablespace
These tables are global to each PostgreSQL cluster.
Keys are names (strings) and must be unique within cluster.
COMP9315 21T1 ♢ Catalogs ♢ [8/11]
<< ∧ >>
❖ Representing Databases (cont)
pg_database contains information about databases:
• oid, datname, datdba, datacl[], encoding, …
pg_namespace contains information about schemata:
• oid, nspname, nspowner, nspacl[]
pg_tablespace contains information about tablespaces:
• oid, spcname, spcowner, spcacl[]
PostgreSQL represents access via array of access items:
Role=Privileges/Grantor
where Privileges is a string enumerating privileges, e.g.
jas=arwdRxt/jas,fred=r/jas,joe=rwad/jas
COMP9315 21T1 ♢ Catalogs ♢ [9/11]
<< ∧ >>
❖ Representing Tables
Representing one table needs tuples in several catalog tables.
Due to O-O heritage, base table for tables is called pg_class.
The pg_class table also handles other “table-like” objects:
• views … represents attributes/domains of view
• composite (tuple) types … from CREATE TYPE AS
• sequences, indexes (top-level defn), other “special” objects
All tuples in pg_class have an OID, used as primary key.
Some fields from the pg_class table:
• oid, relname, relnamespace, reltype, relowner
• relkind, reltuples, relnatts, relhaspkey, relacl, …
COMP9315 21T1 ♢ Catalogs ♢ [10/11]
<< ∧
❖ Representing Tables (cont)
Details of catalog tables representing database tables
pg_class holds core information about tables
• relname, relnamespace, reltype, relowner, ...
• relkind, relnatts, relhaspkey, relacl[], ...
pg_attribute contains information about attributes
• attrelid, attname, atttypid, attnum, ...
pg_type contains information about types
• typname, typnamespace, typowner, typlen, ...
• typtype, typrelid, typinput, typoutput, ...
COMP9315 21T1 ♢ Catalogs ♢ [11/11]
Produced: 15 Feb 2021