继续学习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
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------