COMP9315-week1-lecture4

继续学习week1第四次课

COMP9315 19T2 Week 1 Lecture 4_哔哩哔哩_bilibili

PostgreSQL: Documentation: 11: PostgreSQL 11.22 Documentation

PostgreSQL: Documentation: 11: Chapter 52. System Catalogs

Catalogs

Database Objects 65/100

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, all have OIDs).
How are the different types of objects represented?
How do we go from a name (or OID) to bytes stored on disk?

-------------------------------------------------------------------------------------

Catalogs 66/100
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

-------------------------------------------------------------------------------------

... Catalogs 67/100
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';

file:///Users/jas/srvr/apps/cs9315/19T2/lectures/week01/notes.html 

-------------------------------------------------------------------------------------

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)

-------------------------------------------------------------------------------------

... Catalogs 69/100
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

-------------------------------------------------------------------------------------

... Catalogs 70/100
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        

-------------------------------------------------------------------------------------

... Catalogs 71/100
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

file:///Users/jas/srvr/apps/cs9315/19T2/lectures/week01/notes.html 

-------------------------------------------------------------------------------------

Representing Databases 72/100
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

-------------------------------------------------------------------------------------

... Representing Databases 73/100
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

-------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------