COMP9315-week1-lecture4

张某人的胡思乱想

已于 2024-08-29 20:35:25 修改

阅读量839

点赞数 8

分类专栏： database 文章标签：数据库

于 2024-08-27 12:42:55 首次发布

本文链接：https://blog.youkuaiyun.com/hb_zxl/article/details/141599753

版权

database 专栏收录该内容

10 篇文章

订阅专栏

继续学习week1第四次课

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

PostgreSQL: Documentation: 11: PostgreSQL 11.22 Documentation

PostgreSQL: Documentation: 11: Chapter 52. System Catalogs

COMP9315 Introduction (unsw.edu.au)

C:\python\COMP9315-master\19T2\Lecture Exercises\week01 --pizza database test

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

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

Representing Tables 74/100
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, ...

psql beer

\d pg_class

select relname from pg_class ;

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

... Representing Tables 75/100
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, ...

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

Exercise 4: Table Statistics 76/100
Using the PostgreSQL catalog, write a PLpgSQL function
to return table name and #tuples in table
for all tables in the public schema
create type TableInfo as (table text, ntuples int);
create function pop() returns setof TableInfo ...
Hints:
table is a reserved word
you will need to use dynamically-generated queries

excercises/week02/excercises/catalog

vim pop.sql

psql beer

\dS 检查pg_tables

psql beer

\i pop.sql

CREATE FUNCTION

\df

select * from pop();

select * from likes;

delete from likes where drinker='Justin';

select * from pop();

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

PLpgSQL function:

function schema() returns setof text
giving a list of table schemas in the public schema

It should behave as follows:
db=# select * from schema();
tables
---------------------------
table1(x, y, z)
table2(a, b)
table3(id, name, address)

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

Exercise 6: Enumerated Types 78/100
PostgreSQL allows you to define enumerated types, e.g.
create type Mood as enum ('sad', 'happy');
Creates a type with two ordered values 'sad' < 'happy'
What is created in the catalog for the above definition?

pg_type(oid, typname, typelen, typetype, ...)
pg_enum(oid, enumtypid, enumlabel)

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

PostgreSQL Architecture 80/100
Client/server architecture: