09 Занятие 09: Организация данных / Системный каталог

основная схема: pg_catalog
альтернативное представление: information_schema (стандарт SQL)

просмотр: SELECT
изменение: CREATE, ALTER, DROP

pg_database.datname

=> CREATE DATABASE data_catalog;

CREATE DATABASE

=> \c data_catalog

You are now connected to database "data_catalog" as user "student".

=> CREATE TABLE employees(
  id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text,
  manager integer
);

CREATE TABLE

=> CREATE VIEW top_managers AS
  SELECT * FROM employees WHERE manager IS NULL;

CREATE VIEW

=> CREATE TEMP TABLE emp_salaries(
  employee integer,
  salary numeric
);

CREATE TABLE

=> SELECT * FROM pg_database WHERE datname = 'data_catalog' \gx

-[ RECORD 1 ]--+-------------
oid            | 16386
datname        | data_catalog
datdba         | 16384
encoding       | 6
datlocprovider | c
datistemplate  | f
datallowconn   | t
datconnlimit   | -1
datfrozenxid   | 722
datminmxid     | 1
dattablespace  | 1663
datcollate     | en_US.UTF-8
datctype       | en_US.UTF-8
daticulocale   |
daticurules    |
datcollversion | 2.39
datacl         |

=> SELECT * FROM pg_namespace WHERE nspname = 'public' \gx

-[ RECORD 1 ]-----------------------------------------------------------
oid      | 2200
nspname  | public
nspowner | 6171
nspacl   | {pg_database_owner=UC/pg_database_owner,=U/pg_database_owner}

=> SELECT relname, relkind, relnamespace, relfilenode, relowner, relpersistence
FROM pg_class WHERE relname ~ '^(emp|top)';

     relname      | relkind | relnamespace | relfilenode | relowner | relpersistence
------------------+---------+--------------+-------------+----------+----------------
 employees_id_seq | S       |         2200 |       16387 |    16384 | p
 employees        | r       |         2200 |       16388 |    16384 | p
 employees_pkey   | i       |         2200 |       16393 |    16384 | p
 top_managers     | v       |         2200 |           0 |    16384 | p
 emp_salaries     | r       |        16399 |       16401 |    16384 | t
(5 rows)

=> SELECT schemaname, tablename, tableowner
FROM pg_tables WHERE schemaname ~ '(public|pg_temp.+)';

 schemaname |  tablename   | tableowner
------------+--------------+------------
 public     | employees    | student
 pg_temp_4  | emp_salaries | student
(2 rows)

=> SELECT *
FROM pg_views WHERE schemaname = 'public';

 schemaname |   viewname   | viewowner |         definition
------------+--------------+-----------+----------------------------
 public     | top_managers | student   |  SELECT id,               +
            |              |           |     name,                 +
            |              |           |     manager               +
            |              |           |    FROM employees         +
            |              |           |   WHERE (manager IS NULL);
(1 row)

=> \dt

             List of relations
  Schema   |     Name     | Type  |  Owner
-----------+--------------+-------+---------
 pg_temp_4 | emp_salaries | table | student
 public    | employees    | table | student
(2 rows)

=> \dv public.*

           List of relations
 Schema |     Name     | Type |  Owner
--------+--------------+------+---------
 public | top_managers | view | student
(1 row)

=> \dtvis

                       List of relations
  Schema   |       Name       |   Type   |  Owner  |   Table
-----------+------------------+----------+---------+-----------
 pg_temp_4 | emp_salaries     | table    | student |
 public    | employees        | table    | student |
 public    | employees_id_seq | sequence | student |
 public    | employees_pkey   | index    | student | employees
 public    | top_managers     | view     | student |
(5 rows)

=> \dt+

                                          List of relations
  Schema   |     Name     | Type  |  Owner  | Persistence | Access method |    Size    |
Description
-----------+--------------+-------+---------+-------------+---------------+------------+--
-----------
 pg_temp_4 | emp_salaries | table | student | temporary   | heap          | 8192 bytes |
 public    | employees    | table | student | permanent   | heap          | 8192 bytes |
(2 rows)

=> \d top_managers

             View "public.top_managers"
 Column  |  Type   | Collation | Nullable | Default
---------+---------+-----------+----------+---------
 id      | integer |           |          |
 name    | text    |           |          |
 manager | integer |           |          |

=> \d+ top_managers

                         View "public.top_managers"
 Column  |  Type   | Collation | Nullable | Default | Storage  | Description
---------+---------+-----------+----------+---------+----------+-------------
 id      | integer |           |          |         | plain    |
 name    | text    |           |          |         | extended |
 manager | integer |           |          |         | plain    |
View definition:
 SELECT id,
    name,
    manager
   FROM employees
  WHERE manager IS NULL;

=> \dfS pg*size

                                  List of functions
   Schema   |          Name          | Result data type | Argument data types | Type
------------+------------------------+------------------+---------------------+------
 pg_catalog | pg_column_size         | integer          | "any"               | func
 pg_catalog | pg_database_size       | bigint           | name                | func
 pg_catalog | pg_database_size       | bigint           | oid                 | func
 pg_catalog | pg_indexes_size        | bigint           | regclass            | func
 pg_catalog | pg_relation_size       | bigint           | regclass            | func
 pg_catalog | pg_relation_size       | bigint           | regclass, text      | func
 pg_catalog | pg_table_size          | bigint           | regclass            | func
 pg_catalog | pg_tablespace_size     | bigint           | name                | func
 pg_catalog | pg_tablespace_size     | bigint           | oid                 | func
 pg_catalog | pg_total_relation_size | bigint           | regclass            | func
(10 rows)

=> \sf pg_catalog.pg_database_size(oid)

CREATE OR REPLACE FUNCTION pg_catalog.pg_database_size(oid)
 RETURNS bigint
 LANGUAGE internal
 PARALLEL SAFE STRICT
AS $function$pg_database_size_oid$function$

=> \set ECHO_HIDDEN on

=> \dt employees

********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized
view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 't' THEN 'TOAST table' WHEN 'f'
THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index'
END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','t','s','')
  AND c.relname OPERATOR(pg_catalog.~) '^(employees)$' COLLATE pg_catalog.default
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

          List of relations
 Schema |   Name    | Type  |  Owner
--------+-----------+-------+---------
 public | employees | table | student
(1 row)

=> \unset ECHO_HIDDEN

=> \d pg_attribute

               Table "pg_catalog.pg_attribute"
     Column     |   Type    | Collation | Nullable | Default
----------------+-----------+-----------+----------+---------
 attrelid       | oid       |           | not null |
 attname        | name      |           | not null |
 atttypid       | oid       |           | not null |
 attlen         | smallint  |           | not null |
 attnum         | smallint  |           | not null |
 attcacheoff    | integer   |           | not null |
 atttypmod      | integer   |           | not null |
 attndims       | smallint  |           | not null |
 attbyval       | boolean   |           | not null |
 attalign       | "char"    |           | not null |
 attstorage     | "char"    |           | not null |
 attcompression | "char"    |           | not null |
 attnotnull     | boolean   |           | not null |
 atthasdef      | boolean   |           | not null |
 atthasmissing  | boolean   |           | not null |
 attidentity    | "char"    |           | not null |
 attgenerated   | "char"    |           | not null |
 attisdropped   | boolean   |           | not null |
 attislocal     | boolean   |           | not null |
 attinhcount    | smallint  |           | not null |
 attstattarget  | smallint  |           | not null |
 attcollation   | oid       |           | not null |
 attacl         | aclitem[] |           |          |
 attoptions     | text[]    | C         |          |
 attfdwoptions  | text[]    | C         |          |
 attmissingval  | anyarray  |           |          |
Indexes:
    "pg_attribute_relid_attnum_index" PRIMARY KEY, btree (attrelid, attnum)
    "pg_attribute_relid_attnam_index" UNIQUE CONSTRAINT, btree (attrelid, attname)

=> SELECT *
FROM   pg_get_catalog_foreign_keys()
WHERE  fktable = 'pg_attribute'::regclass;

   fktable    |     fkcols     |   pktable    | pkcols | is_array | is_opt
--------------+----------------+--------------+--------+----------+--------
 pg_attribute | {attrelid}     | pg_class     | {oid}  | f        | f
 pg_attribute | {atttypid}     | pg_type      | {oid}  | f        | t
 pg_attribute | {attcollation} | pg_collation | {oid}  | f        | t
(3 rows)

столбец oid обеспечивает уникальность в таблицах системного каталога

псевдонимы oid для некоторых таблиц системного каталога
(regclass для pg_class и т. п.)

приведение текстового имени объекта к типу oid и обратно

=> SELECT a.attname, a.atttypid
FROM pg_attribute a
WHERE a.attrelid = (
  SELECT oid FROM pg_class WHERE relname = 'employees'
)
AND a.attnum > 0;

 attname | atttypid
---------+----------
 id      |       23
 name    |       25
 manager |       23
(3 rows)

=> SELECT a.attname, a.atttypid
FROM pg_attribute a
WHERE a.attrelid = 'employees'::regclass
AND a.attnum > 0;

 attname | atttypid
---------+----------
 id      |       23
 name    |       25
 manager |       23
(3 rows)

=> SELECT a.attname, a.atttypid::regtype
FROM pg_attribute a
WHERE a.attrelid = 'employees'::regclass
AND a.attnum > 0;

 attname | atttypid
---------+----------
 id      | integer
 name    | text
 manager | integer
(3 rows)

=> \dT reg*

                        List of data types
   Schema   |     Name      |             Description
------------+---------------+--------------------------------------
 pg_catalog | regclass      | registered class
 pg_catalog | regcollation  | registered collation
 pg_catalog | regconfig     | registered text search configuration
 pg_catalog | regdictionary | registered text search dictionary
 pg_catalog | regnamespace  | registered namespace
 pg_catalog | regoper       | registered operator
 pg_catalog | regoperator   | registered operator (with args)
 pg_catalog | regproc       | registered procedure
 pg_catalog | regprocedure  | registered procedure (with args)
 pg_catalog | regrole       | registered role
 pg_catalog | regtype       | registered type
(11 rows)

=> \d pg_class

                     Table "pg_catalog.pg_class"
       Column        |     Type     | Collation | Nullable | Default
---------------------+--------------+-----------+----------+---------
 oid                 | oid          |           | not null |
 relname             | name         |           | not null |
 relnamespace        | oid          |           | not null |
 reltype             | oid          |           | not null |
 reloftype           | oid          |           | not null |
 relowner            | oid          |           | not null |
 relam               | oid          |           | not null |
 relfilenode         | oid          |           | not null |
 reltablespace       | oid          |           | not null |
 relpages            | integer      |           | not null |
 reltuples           | real         |           | not null |
 relallvisible       | integer      |           | not null |
 reltoastrelid       | oid          |           | not null |
 relhasindex         | boolean      |           | not null |
 relisshared         | boolean      |           | not null |
 relpersistence      | "char"       |           | not null |
 relkind             | "char"       |           | not null |
 relnatts            | smallint     |           | not null |
 relchecks           | smallint     |           | not null |
 relhasrules         | boolean      |           | not null |
 relhastriggers      | boolean      |           | not null |
 relhassubclass      | boolean      |           | not null |
 relrowsecurity      | boolean      |           | not null |
 relforcerowsecurity | boolean      |           | not null |
 relispopulated      | boolean      |           | not null |
 relreplident        | "char"       |           | not null |
 relispartition      | boolean      |           | not null |
 relrewrite          | oid          |           | not null |
 relfrozenxid        | xid          |           | not null |
 relminmxid          | xid          |           | not null |
 relacl              | aclitem[]    |           |          |
 reloptions          | text[]       | C         |          |
 relpartbound        | pg_node_tree | C         |          |
Indexes:
    "pg_class_oid_index" PRIMARY KEY, btree (oid)
    "pg_class_relname_nsp_index" UNIQUE CONSTRAINT, btree (relname, relnamespace)
    "pg_class_tblspc_relfilenode_index" btree (reltablespace, relfilenode)

=> \d+ pg_tables

                          View "pg_catalog.pg_tables"
   Column    |  Type   | Collation | Nullable | Default | Storage | Description
-------------+---------+-----------+----------+---------+---------+-------------
 schemaname  | name    |           |          |         | plain   |
 tablename   | name    |           |          |         | plain   |
 tableowner  | name    |           |          |         | plain   |
 tablespace  | name    |           |          |         | plain   |
 hasindexes  | boolean |           |          |         | plain   |
 hasrules    | boolean |           |          |         | plain   |
 hastriggers | boolean |           |          |         | plain   |
 rowsecurity | boolean |           |          |         | plain   |
View definition:
 SELECT n.nspname AS schemaname,
    c.relname AS tablename,
    pg_get_userbyid(c.relowner) AS tableowner,
    t.spcname AS tablespace,
    c.relhasindex AS hasindexes,
    c.relhasrules AS hasrules,
    c.relhastriggers AS hastriggers,
    c.relrowsecurity AS rowsecurity
   FROM pg_class c
     LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
  WHERE c.relkind = ANY (ARRAY['r'::"char", 'p'::"char"]);

=> CREATE DATABASE data_catalog;

CREATE DATABASE

=> \c data_catalog

You are now connected to database "data_catalog" as user "student".

=> CREATE TEMP TABLE t(n integer);

CREATE TABLE

=> \dnS

            List of schemas
        Name        |       Owner
--------------------+-------------------
 information_schema | postgres
 pg_catalog         | postgres
 pg_temp_4          | postgres
 pg_toast           | postgres
 pg_toast_temp_4    | postgres
 public             | pg_database_owner
(6 rows)

=> SELECT pg_my_temp_schema()::regnamespace;

 pg_my_temp_schema
-------------------
 pg_temp_4
(1 row)

=> SELECT * FROM pg_temp.t;

 n
---
(0 rows)

=> \dv information_schema.*

                              List of relations
       Schema       |                 Name                  | Type |  Owner
--------------------+---------------------------------------+------+----------
 information_schema | _pg_foreign_data_wrappers             | view | postgres
 information_schema | _pg_foreign_servers                   | view | postgres
 information_schema | _pg_foreign_table_columns             | view | postgres
 information_schema | _pg_foreign_tables                    | view | postgres
 information_schema | _pg_user_mappings                     | view | postgres
 information_schema | administrable_role_authorizations     | view | postgres
 information_schema | applicable_roles                      | view | postgres
 information_schema | attributes                            | view | postgres
 information_schema | character_sets                        | view | postgres
 information_schema | check_constraint_routine_usage        | view | postgres
 information_schema | check_constraints                     | view | postgres
 information_schema | collation_character_set_applicability | view | postgres
 information_schema | collations                            | view | postgres
 information_schema | column_column_usage                   | view | postgres
 information_schema | column_domain_usage                   | view | postgres
 information_schema | column_options                        | view | postgres
 information_schema | column_privileges                     | view | postgres
 information_schema | column_udt_usage                      | view | postgres
 information_schema | columns                               | view | postgres
 information_schema | constraint_column_usage               | view | postgres
 information_schema | constraint_table_usage                | view | postgres
 information_schema | data_type_privileges                  | view | postgres
 information_schema | domain_constraints                    | view | postgres
 information_schema | domain_udt_usage                      | view | postgres
 information_schema | domains                               | view | postgres
 information_schema | element_types                         | view | postgres
 information_schema | enabled_roles                         | view | postgres
 information_schema | foreign_data_wrapper_options          | view | postgres
 information_schema | foreign_data_wrappers                 | view | postgres
 information_schema | foreign_server_options                | view | postgres
 information_schema | foreign_servers                       | view | postgres
 information_schema | foreign_table_options                 | view | postgres
 information_schema | foreign_tables                        | view | postgres
 information_schema | information_schema_catalog_name       | view | postgres
 information_schema | key_column_usage                      | view | postgres
 information_schema | parameters                            | view | postgres
 information_schema | referential_constraints               | view | postgres
 information_schema | role_column_grants                    | view | postgres
 information_schema | role_routine_grants                   | view | postgres
 information_schema | role_table_grants                     | view | postgres
 information_schema | role_udt_grants                       | view | postgres
 information_schema | role_usage_grants                     | view | postgres
 information_schema | routine_column_usage                  | view | postgres
 information_schema | routine_privileges                    | view | postgres
 information_schema | routine_routine_usage                 | view | postgres
 information_schema | routine_sequence_usage                | view | postgres
 information_schema | routine_table_usage                   | view | postgres
 information_schema | routines                              | view | postgres
 information_schema | schemata                              | view | postgres
 information_schema | sequences                             | view | postgres
 information_schema | table_constraints                     | view | postgres
 information_schema | table_privileges                      | view | postgres
 information_schema | tables                                | view | postgres
 information_schema | transforms                            | view | postgres
 information_schema | triggered_update_columns              | view | postgres
 information_schema | triggers                              | view | postgres
 information_schema | udt_privileges                        | view | postgres
 information_schema | usage_privileges                      | view | postgres
 information_schema | user_defined_types                    | view | postgres
 information_schema | user_mapping_options                  | view | postgres
 information_schema | user_mappings                         | view | postgres
 information_schema | view_column_usage                     | view | postgres
 information_schema | view_routine_usage                    | view | postgres
 information_schema | view_table_usage                      | view | postgres
 information_schema | views                                 | view | postgres
(65 rows)

=> \set ECHO_HIDDEN on

=> \d+ pg_views

********* QUERY **********
SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname OPERATOR(pg_catalog.~) '^(pg_views)$' COLLATE pg_catalog.default
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**************************

********* QUERY **********
SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers,
c.relrowsecurity, c.relforcerowsecurity, false AS relhasoids, c.relispartition,
pg_catalog.array_to_string(c.reloptions || array(select 'toast.' || x from
pg_catalog.unnest(tc.reloptions) x), ', ')
, c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE
c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence, c.relreplident,
am.amname
FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
LEFT JOIN pg_catalog.pg_am am ON (c.relam = am.oid)
WHERE c.oid = '12028';
**************************

********* QUERY **********
SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid, true)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull,
  (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
   WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <>
t.typcollation) AS attcollation,
  a.attidentity,
  a.attgenerated,
  a.attstorage,
  pg_catalog.col_description(a.attrelid, a.attnum)
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '12028' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;
**************************

********* QUERY **********
SELECT pg_catalog.pg_get_viewdef('12028'::pg_catalog.oid, true);
**************************

********* QUERY **********
SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))
FROM pg_catalog.pg_rewrite r
WHERE r.ev_class = '12028' AND r.rulename != '_RETURN' ORDER BY 1;
**************************

                         View "pg_catalog.pg_views"
   Column   | Type | Collation | Nullable | Default | Storage  | Description
------------+------+-----------+----------+---------+----------+-------------
 schemaname | name |           |          |         | plain    |
 viewname   | name |           |          |         | plain    |
 viewowner  | name |           |          |         | plain    |
 definition | text |           |          |         | extended |
View definition:
 SELECT n.nspname AS schemaname,
    c.relname AS viewname,
    pg_get_userbyid(c.relowner) AS viewowner,
    pg_get_viewdef(c.oid) AS definition
   FROM pg_class c
     LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE c.relkind = 'v'::"char";

=> \set ECHO_HIDDEN off