src/backend/catalog/system_views.sql

   1 /*
   2  * PostgreSQL System Views
   3  *
   4  * Copyright (c) 1996-2009, PostgreSQL Global Development Group
   5  *
   6  * $PostgreSQL$
   7  */
   8
   9 CREATE VIEW pg_roles AS
  10     SELECT
  11         rolname,
  12         rolsuper,
  13         rolinherit,
  14         rolcreaterole,
  15         rolcreatedb,
  16         rolcatupdate,
  17         rolcanlogin,
  18         rolconnlimit,
  19         '********'::text as rolpassword,
  20         rolvaliduntil,
  21         rolconfig,
  22         oid
  23     FROM pg_authid;
  24
  25 CREATE VIEW pg_shadow AS
  26     SELECT
  27         rolname AS usename,
  28         oid AS usesysid,
  29         rolcreatedb AS usecreatedb,
  30         rolsuper AS usesuper,
  31         rolcatupdate AS usecatupd,
  32         rolpassword AS passwd,
  33         rolvaliduntil::abstime AS valuntil,
  34         rolconfig AS useconfig
  35     FROM pg_authid
  36     WHERE rolcanlogin;
  37
  38 REVOKE ALL on pg_shadow FROM public;
  39
  40 CREATE VIEW pg_group AS
  41     SELECT
  42         rolname AS groname,
  43         oid AS grosysid,
  44         ARRAY(SELECT member FROM pg_auth_members WHERE roleid = oid) AS grolist
  45     FROM pg_authid
  46     WHERE NOT rolcanlogin;
  47
  48 CREATE VIEW pg_user AS
  49     SELECT
  50         usename,
  51         usesysid,
  52         usecreatedb,
  53         usesuper,
  54         usecatupd,
  55         '********'::text as passwd,
  56         valuntil,
  57         useconfig
  58     FROM pg_shadow;
  59
  60 CREATE VIEW pg_rules AS
  61     SELECT
  62         N.nspname AS schemaname,
  63         C.relname AS tablename,
  64         R.rulename AS rulename,
  65         pg_get_ruledef(R.oid) AS definition
  66     FROM (pg_rewrite R JOIN pg_class C ON (C.oid = R.ev_class))
  67         LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  68     WHERE R.rulename != '_RETURN';
  69
  70 CREATE VIEW pg_views AS
  71     SELECT
  72         N.nspname AS schemaname,
  73         C.relname AS viewname,
  74         pg_get_userbyid(C.relowner) AS viewowner,
  75         pg_get_viewdef(C.oid) AS definition
  76     FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  77     WHERE C.relkind = 'v';
  78
  79 CREATE VIEW pg_tables AS
  80     SELECT
  81         N.nspname AS schemaname,
  82         C.relname AS tablename,
  83         pg_get_userbyid(C.relowner) AS tableowner,
  84         T.spcname AS tablespace,
  85         C.relhasindex AS hasindexes,
  86         C.relhasrules AS hasrules,
  87         C.relhastriggers AS hastriggers
  88     FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  89          LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace)
  90     WHERE C.relkind = 'r';
  91
  92 CREATE VIEW pg_indexes AS
  93     SELECT
  94         N.nspname AS schemaname,
  95         C.relname AS tablename,
  96         I.relname AS indexname,
  97         T.spcname AS tablespace,
  98         pg_get_indexdef(I.oid) AS indexdef
  99     FROM pg_index X JOIN pg_class C ON (C.oid = X.indrelid)
 100          JOIN pg_class I ON (I.oid = X.indexrelid)
 101          LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 102          LEFT JOIN pg_tablespace T ON (T.oid = I.reltablespace)
 103     WHERE C.relkind = 'r' AND I.relkind = 'i';
 104
 105 CREATE VIEW pg_stats AS
 106     SELECT
 107         nspname AS schemaname,
 108         relname AS tablename,
 109         attname AS attname,
 110         stanullfrac AS null_frac,
 111         stawidth AS avg_width,
 112         stadistinct AS n_distinct,
 113         CASE
 114             WHEN stakind1 IN (1, 4) THEN stavalues1
 115             WHEN stakind2 IN (1, 4) THEN stavalues2
 116             WHEN stakind3 IN (1, 4) THEN stavalues3
 117             WHEN stakind4 IN (1, 4) THEN stavalues4
 118         END AS most_common_vals,
 119         CASE
 120             WHEN stakind1 IN (1, 4) THEN stanumbers1
 121             WHEN stakind2 IN (1, 4) THEN stanumbers2
 122             WHEN stakind3 IN (1, 4) THEN stanumbers3
 123             WHEN stakind4 IN (1, 4) THEN stanumbers4
 124         END AS most_common_freqs,
 125         CASE
 126             WHEN stakind1 = 2 THEN stavalues1
 127             WHEN stakind2 = 2 THEN stavalues2
 128             WHEN stakind3 = 2 THEN stavalues3
 129             WHEN stakind4 = 2 THEN stavalues4
 130         END AS histogram_bounds,
 131         CASE
 132             WHEN stakind1 = 3 THEN stanumbers1[1]
 133             WHEN stakind2 = 3 THEN stanumbers2[1]
 134             WHEN stakind3 = 3 THEN stanumbers3[1]
 135             WHEN stakind4 = 3 THEN stanumbers4[1]
 136         END AS correlation
 137     FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid)
 138          JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum)
 139          LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
 140     WHERE NOT attisdropped AND has_column_privilege(c.oid, a.attnum, 'select');
 141
 142 REVOKE ALL on pg_statistic FROM public;
 143
 144 CREATE VIEW pg_locks AS
 145     SELECT * FROM pg_lock_status() AS L;
 146
 147 CREATE VIEW pg_cursors AS
 148     SELECT * FROM pg_cursor() AS C;
 149
 150 CREATE VIEW pg_prepared_xacts AS
 151     SELECT P.transaction, P.gid, P.prepared,
 152            U.rolname AS owner, D.datname AS database
 153     FROM pg_prepared_xact() AS P
 154          LEFT JOIN pg_authid U ON P.ownerid = U.oid
 155          LEFT JOIN pg_database D ON P.dbid = D.oid;
 156
 157 CREATE VIEW pg_prepared_statements AS
 158     SELECT * FROM pg_prepared_statement() AS P;
 159
 160 CREATE VIEW pg_settings AS
 161     SELECT * FROM pg_show_all_settings() AS A;
 162
 163 CREATE RULE pg_settings_u AS
 164     ON UPDATE TO pg_settings
 165     WHERE new.name = old.name DO
 166     SELECT set_config(old.name, new.setting, 'f');
 167
 168 CREATE RULE pg_settings_n AS
 169     ON UPDATE TO pg_settings
 170     DO INSTEAD NOTHING;
 171
 172 GRANT SELECT, UPDATE ON pg_settings TO PUBLIC;
 173
 174 CREATE VIEW pg_timezone_abbrevs AS
 175     SELECT * FROM pg_timezone_abbrevs();
 176
 177 CREATE VIEW pg_timezone_names AS
 178     SELECT * FROM pg_timezone_names();
 179
 180 -- Statistics views
 181
 182 CREATE VIEW pg_stat_all_tables AS
 183     SELECT
 184             C.oid AS relid,
 185             N.nspname AS schemaname,
 186             C.relname AS relname,
 187             pg_stat_get_numscans(C.oid) AS seq_scan,
 188             pg_stat_get_tuples_returned(C.oid) AS seq_tup_read,
 189             sum(pg_stat_get_numscans(I.indexrelid))::bigint AS idx_scan,
 190             sum(pg_stat_get_tuples_fetched(I.indexrelid))::bigint +
 191             pg_stat_get_tuples_fetched(C.oid) AS idx_tup_fetch,
 192             pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins,
 193             pg_stat_get_tuples_updated(C.oid) AS n_tup_upd,
 194             pg_stat_get_tuples_deleted(C.oid) AS n_tup_del,
 195             pg_stat_get_tuples_hot_updated(C.oid) AS n_tup_hot_upd,
 196             pg_stat_get_live_tuples(C.oid) AS n_live_tup,
 197             pg_stat_get_dead_tuples(C.oid) AS n_dead_tup,
 198             pg_stat_get_last_vacuum_time(C.oid) as last_vacuum,
 199             pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum,
 200             pg_stat_get_last_analyze_time(C.oid) as last_analyze,
 201             pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze
 202     FROM pg_class C LEFT JOIN
 203          pg_index I ON C.oid = I.indrelid
 204          LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 205     WHERE C.relkind IN ('r', 't')
 206     GROUP BY C.oid, N.nspname, C.relname;
 207
 208 CREATE VIEW pg_stat_sys_tables AS
 209     SELECT * FROM pg_stat_all_tables
 210     WHERE schemaname IN ('pg_catalog', 'information_schema') OR
 211           schemaname ~ '^pg_toast';
 212
 213 CREATE VIEW pg_stat_user_tables AS
 214     SELECT * FROM pg_stat_all_tables
 215     WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
 216           schemaname !~ '^pg_toast';
 217
 218 CREATE VIEW pg_statio_all_tables AS
 219     SELECT
 220             C.oid AS relid,
 221             N.nspname AS schemaname,
 222             C.relname AS relname,
 223             pg_stat_get_blocks_fetched(C.oid) -
 224                     pg_stat_get_blocks_hit(C.oid) AS heap_blks_read,
 225             pg_stat_get_blocks_hit(C.oid) AS heap_blks_hit,
 226             sum(pg_stat_get_blocks_fetched(I.indexrelid) -
 227                     pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_read,
 228             sum(pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_hit,
 229             pg_stat_get_blocks_fetched(T.oid) -
 230                     pg_stat_get_blocks_hit(T.oid) AS toast_blks_read,
 231             pg_stat_get_blocks_hit(T.oid) AS toast_blks_hit,
 232             pg_stat_get_blocks_fetched(X.oid) -
 233                     pg_stat_get_blocks_hit(X.oid) AS tidx_blks_read,
 234             pg_stat_get_blocks_hit(X.oid) AS tidx_blks_hit
 235     FROM pg_class C LEFT JOIN
 236             pg_index I ON C.oid = I.indrelid LEFT JOIN
 237             pg_class T ON C.reltoastrelid = T.oid LEFT JOIN
 238             pg_class X ON T.reltoastidxid = X.oid
 239             LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 240     WHERE C.relkind IN ('r', 't')
 241     GROUP BY C.oid, N.nspname, C.relname, T.oid, X.oid;
 242
 243 CREATE VIEW pg_statio_sys_tables AS
 244     SELECT * FROM pg_statio_all_tables
 245     WHERE schemaname IN ('pg_catalog', 'information_schema') OR
 246           schemaname ~ '^pg_toast';
 247
 248 CREATE VIEW pg_statio_user_tables AS
 249     SELECT * FROM pg_statio_all_tables
 250     WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
 251           schemaname !~ '^pg_toast';
 252
 253 CREATE VIEW pg_stat_all_indexes AS
 254     SELECT
 255             C.oid AS relid,
 256             I.oid AS indexrelid,
 257             N.nspname AS schemaname,
 258             C.relname AS relname,
 259             I.relname AS indexrelname,
 260             pg_stat_get_numscans(I.oid) AS idx_scan,
 261             pg_stat_get_tuples_returned(I.oid) AS idx_tup_read,
 262             pg_stat_get_tuples_fetched(I.oid) AS idx_tup_fetch
 263     FROM pg_class C JOIN
 264             pg_index X ON C.oid = X.indrelid JOIN
 265             pg_class I ON I.oid = X.indexrelid
 266             LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 267     WHERE C.relkind IN ('r', 't');
 268
 269 CREATE VIEW pg_stat_sys_indexes AS
 270     SELECT * FROM pg_stat_all_indexes
 271     WHERE schemaname IN ('pg_catalog', 'information_schema') OR
 272           schemaname ~ '^pg_toast';
 273
 274 CREATE VIEW pg_stat_user_indexes AS
 275     SELECT * FROM pg_stat_all_indexes
 276     WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
 277           schemaname !~ '^pg_toast';
 278
 279 CREATE VIEW pg_statio_all_indexes AS
 280     SELECT
 281             C.oid AS relid,
 282             I.oid AS indexrelid,
 283             N.nspname AS schemaname,
 284             C.relname AS relname,
 285             I.relname AS indexrelname,
 286             pg_stat_get_blocks_fetched(I.oid) -
 287                     pg_stat_get_blocks_hit(I.oid) AS idx_blks_read,
 288             pg_stat_get_blocks_hit(I.oid) AS idx_blks_hit
 289     FROM pg_class C JOIN
 290             pg_index X ON C.oid = X.indrelid JOIN
 291             pg_class I ON I.oid = X.indexrelid
 292             LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 293     WHERE C.relkind IN ('r', 't');
 294
 295 CREATE VIEW pg_statio_sys_indexes AS
 296     SELECT * FROM pg_statio_all_indexes
 297     WHERE schemaname IN ('pg_catalog', 'information_schema') OR
 298           schemaname ~ '^pg_toast';
 299
 300 CREATE VIEW pg_statio_user_indexes AS
 301     SELECT * FROM pg_statio_all_indexes
 302     WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
 303           schemaname !~ '^pg_toast';
 304
 305 CREATE VIEW pg_statio_all_sequences AS
 306     SELECT
 307             C.oid AS relid,
 308             N.nspname AS schemaname,
 309             C.relname AS relname,
 310             pg_stat_get_blocks_fetched(C.oid) -
 311                     pg_stat_get_blocks_hit(C.oid) AS blks_read,
 312             pg_stat_get_blocks_hit(C.oid) AS blks_hit
 313     FROM pg_class C
 314             LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 315     WHERE C.relkind = 'S';
 316
 317 CREATE VIEW pg_statio_sys_sequences AS
 318     SELECT * FROM pg_statio_all_sequences
 319     WHERE schemaname IN ('pg_catalog', 'information_schema') OR
 320           schemaname ~ '^pg_toast';
 321
 322 CREATE VIEW pg_statio_user_sequences AS
 323     SELECT * FROM pg_statio_all_sequences
 324     WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
 325           schemaname !~ '^pg_toast';
 326
 327 CREATE VIEW pg_stat_activity AS
 328     SELECT
 329             S.datid AS datid,
 330             D.datname AS datname,
 331             S.procpid,
 332             S.usesysid,
 333             U.rolname AS usename,
 334             S.current_query,
 335             S.waiting,
 336             S.xact_start,
 337             S.query_start,
 338             S.backend_start,
 339             S.client_addr,
 340             S.client_port
 341     FROM pg_database D, pg_stat_get_activity(NULL) AS S, pg_authid U
 342     WHERE S.datid = D.oid AND
 343             S.usesysid = U.oid;
 344
 345 CREATE VIEW pg_stat_database AS
 346     SELECT
 347             D.oid AS datid,
 348             D.datname AS datname,
 349             pg_stat_get_db_numbackends(D.oid) AS numbackends,
 350             pg_stat_get_db_xact_commit(D.oid) AS xact_commit,
 351             pg_stat_get_db_xact_rollback(D.oid) AS xact_rollback,
 352             pg_stat_get_db_blocks_fetched(D.oid) -
 353                     pg_stat_get_db_blocks_hit(D.oid) AS blks_read,
 354             pg_stat_get_db_blocks_hit(D.oid) AS blks_hit,
 355             pg_stat_get_db_tuples_returned(D.oid) AS tup_returned,
 356             pg_stat_get_db_tuples_fetched(D.oid) AS tup_fetched,
 357             pg_stat_get_db_tuples_inserted(D.oid) AS tup_inserted,
 358             pg_stat_get_db_tuples_updated(D.oid) AS tup_updated,
 359             pg_stat_get_db_tuples_deleted(D.oid) AS tup_deleted
 360     FROM pg_database D;
 361
 362 CREATE VIEW pg_stat_user_functions AS
 363     SELECT
 364             P.oid AS funcid,
 365             N.nspname AS schemaname,
 366             P.proname AS funcname,
 367             pg_stat_get_function_calls(P.oid) AS calls,
 368             pg_stat_get_function_time(P.oid) / 1000 AS total_time,
 369             pg_stat_get_function_self_time(P.oid) / 1000 AS self_time
 370     FROM pg_proc P LEFT JOIN pg_namespace N ON (N.oid = P.pronamespace)
 371     WHERE P.prolang != 12  -- fast check to eliminate built-in functions
 372           AND pg_stat_get_function_calls(P.oid) IS NOT NULL;
 373
 374 CREATE VIEW pg_stat_bgwriter AS
 375     SELECT
 376         pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,
 377         pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req,
 378         pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint,
 379         pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,
 380         pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
 381         pg_stat_get_buf_written_backend() AS buffers_backend,
 382         pg_stat_get_buf_alloc() AS buffers_alloc;
 383
 384 CREATE VIEW pg_user_mappings AS
 385     SELECT
 386         U.oid       AS umid,
 387         S.oid       AS srvid,
 388         S.srvname   AS srvname,
 389         U.umuser    AS umuser,
 390         CASE WHEN U.umuser = 0 THEN
 391             'public'
 392         ELSE
 393             A.rolname
 394         END AS usename,
 395         CASE WHEN pg_has_role(S.srvowner, 'USAGE') OR has_server_privilege(S.oid, 'USAGE') THEN
 396             U.umoptions
 397         ELSE
 398             NULL
 399         END AS umoptions
 400     FROM pg_user_mapping U
 401          LEFT JOIN pg_authid A ON (A.oid = U.umuser) JOIN
 402         pg_foreign_server S ON (U.umserver = S.oid);
 403
 404 REVOKE ALL on pg_user_mapping FROM public;
 405
 406 --
 407 -- We have a few function definitions in here, too.
 408 -- At some point there might be enough to justify breaking them out into
 409 -- a separate "system_functions.sql" file.
 410 --
 411
 412 -- Tsearch debug function.  Defined here because it'd be pretty unwieldy
 413 -- to put it into pg_proc.h
 414
 415 CREATE FUNCTION ts_debug(IN config regconfig, IN document text,
 416     OUT alias text,
 417     OUT description text,
 418     OUT token text,
 419     OUT dictionaries regdictionary[],
 420     OUT dictionary regdictionary,
 421     OUT lexemes text[])
 422 RETURNS SETOF record AS
 423 $$
 424 SELECT
 425     tt.alias AS alias,
 426     tt.description AS description,
 427     parse.token AS token,
 428     ARRAY ( SELECT m.mapdict::pg_catalog.regdictionary
 429             FROM pg_catalog.pg_ts_config_map AS m
 430             WHERE m.mapcfg = $1 AND m.maptokentype = parse.tokid
 431             ORDER BY m.mapseqno )
 432     AS dictionaries,
 433     ( SELECT mapdict::pg_catalog.regdictionary
 434       FROM pg_catalog.pg_ts_config_map AS m
 435       WHERE m.mapcfg = $1 AND m.maptokentype = parse.tokid
 436       ORDER BY pg_catalog.ts_lexize(mapdict, parse.token) IS NULL, m.mapseqno
 437       LIMIT 1
 438     ) AS dictionary,
 439     ( SELECT pg_catalog.ts_lexize(mapdict, parse.token)
 440       FROM pg_catalog.pg_ts_config_map AS m
 441       WHERE m.mapcfg = $1 AND m.maptokentype = parse.tokid
 442       ORDER BY pg_catalog.ts_lexize(mapdict, parse.token) IS NULL, m.mapseqno
 443       LIMIT 1
 444     ) AS lexemes
 445 FROM pg_catalog.ts_parse(
 446         (SELECT cfgparser FROM pg_catalog.pg_ts_config WHERE oid = $1 ), $2
 447     ) AS parse,
 448      pg_catalog.ts_token_type(
 449         (SELECT cfgparser FROM pg_catalog.pg_ts_config WHERE oid = $1 )
 450     ) AS tt
 451 WHERE tt.tokid = parse.tokid
 452 $$
 453 LANGUAGE SQL STRICT STABLE;
 454
 455 COMMENT ON FUNCTION ts_debug(regconfig,text) IS
 456     'debug function for text search configuration';
 457
 458 CREATE FUNCTION ts_debug(IN document text,
 459     OUT alias text,
 460     OUT description text,
 461     OUT token text,
 462     OUT dictionaries regdictionary[],
 463     OUT dictionary regdictionary,
 464     OUT lexemes text[])
 465 RETURNS SETOF record AS
 466 $$
 467     SELECT * FROM pg_catalog.ts_debug( pg_catalog.get_current_ts_config(), $1);
 468 $$
 469 LANGUAGE SQL STRICT STABLE;
 470
 471 COMMENT ON FUNCTION ts_debug(text) IS
 472     'debug function for current text search configuration';
 473
 474 --
 475 -- Redeclare built-in functions that need default values attached to their
 476 -- arguments.  It's impractical to set those up directly in pg_proc.h because
 477 -- of the complexity and platform-dependency of the expression tree
 478 -- representation.  (Note that internal functions still have to have entries
 479 -- in pg_proc.h; we are merely causing their proargnames and proargdefaults
 480 -- to get filled in.)
 481 --
 482
 483 CREATE OR REPLACE FUNCTION
 484   pg_start_backup(label text, fast boolean DEFAULT false)
 485   RETURNS text STRICT VOLATILE LANGUAGE internal AS 'pg_start_backup';