1/* 2 * PostgreSQL System Views 3 * 4 * Copyright (c) 1996-2021, PostgreSQL Global Development Group 5 * 6 * src/backend/catalog/system_views.sql 7 * 8 * Note: this file is read in single-user -j mode, which means that the 9 * command terminator is semicolon-newline-newline; whenever the backend 10 * sees that, it stops and executes what it's got. If you write a lot of 11 * statements without empty lines between, they'll all get quoted to you 12 * in any error message about one of them, so don't do that. Also, you 13 * cannot write a semicolon immediately followed by an empty line in a 14 * string literal (including a function body!) or a multiline comment. 15 */ 16 17CREATE VIEW pg_roles AS 18 SELECT 19 rolname, 20 rolsuper, 21 rolinherit, 22 rolcreaterole, 23 rolcreatedb, 24 rolcanlogin, 25 rolreplication, 26 rolconnlimit, 27 '********'::text as rolpassword, 28 rolvaliduntil, 29 rolbypassrls, 30 setconfig as rolconfig, 31 pg_authid.oid 32 FROM pg_authid LEFT JOIN pg_db_role_setting s 33 ON (pg_authid.oid = setrole AND setdatabase = 0); 34 35CREATE VIEW pg_shadow AS 36 SELECT 37 rolname AS usename, 38 pg_authid.oid AS usesysid, 39 rolcreatedb AS usecreatedb, 40 rolsuper AS usesuper, 41 rolreplication AS userepl, 42 rolbypassrls AS usebypassrls, 43 rolpassword AS passwd, 44 rolvaliduntil AS valuntil, 45 setconfig AS useconfig 46 FROM pg_authid LEFT JOIN pg_db_role_setting s 47 ON (pg_authid.oid = setrole AND setdatabase = 0) 48 WHERE rolcanlogin; 49 50REVOKE ALL ON pg_shadow FROM public; 51 52CREATE VIEW pg_group AS 53 SELECT 54 rolname AS groname, 55 oid AS grosysid, 56 ARRAY(SELECT member FROM pg_auth_members WHERE roleid = oid) AS grolist 57 FROM pg_authid 58 WHERE NOT rolcanlogin; 59 60CREATE VIEW pg_user AS 61 SELECT 62 usename, 63 usesysid, 64 usecreatedb, 65 usesuper, 66 userepl, 67 usebypassrls, 68 '********'::text as passwd, 69 valuntil, 70 useconfig 71 FROM pg_shadow; 72 73CREATE VIEW pg_policies AS 74 SELECT 75 N.nspname AS schemaname, 76 C.relname AS tablename, 77 pol.polname AS policyname, 78 CASE 79 WHEN pol.polpermissive THEN 80 'PERMISSIVE' 81 ELSE 82 'RESTRICTIVE' 83 END AS permissive, 84 CASE 85 WHEN pol.polroles = '{0}' THEN 86 string_to_array('public', '') 87 ELSE 88 ARRAY 89 ( 90 SELECT rolname 91 FROM pg_catalog.pg_authid 92 WHERE oid = ANY (pol.polroles) ORDER BY 1 93 ) 94 END AS roles, 95 CASE pol.polcmd 96 WHEN 'r' THEN 'SELECT' 97 WHEN 'a' THEN 'INSERT' 98 WHEN 'w' THEN 'UPDATE' 99 WHEN 'd' THEN 'DELETE' 100 WHEN '*' THEN 'ALL' 101 END AS cmd, 102 pg_catalog.pg_get_expr(pol.polqual, pol.polrelid) AS qual, 103 pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid) AS with_check 104 FROM pg_catalog.pg_policy pol 105 JOIN pg_catalog.pg_class C ON (C.oid = pol.polrelid) 106 LEFT JOIN pg_catalog.pg_namespace N ON (N.oid = C.relnamespace); 107 108CREATE VIEW pg_rules AS 109 SELECT 110 N.nspname AS schemaname, 111 C.relname AS tablename, 112 R.rulename AS rulename, 113 pg_get_ruledef(R.oid) AS definition 114 FROM (pg_rewrite R JOIN pg_class C ON (C.oid = R.ev_class)) 115 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 116 WHERE R.rulename != '_RETURN'; 117 118CREATE VIEW pg_views AS 119 SELECT 120 N.nspname AS schemaname, 121 C.relname AS viewname, 122 pg_get_userbyid(C.relowner) AS viewowner, 123 pg_get_viewdef(C.oid) AS definition 124 FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 125 WHERE C.relkind = 'v'; 126 127CREATE VIEW pg_tables AS 128 SELECT 129 N.nspname AS schemaname, 130 C.relname AS tablename, 131 pg_get_userbyid(C.relowner) AS tableowner, 132 T.spcname AS tablespace, 133 C.relhasindex AS hasindexes, 134 C.relhasrules AS hasrules, 135 C.relhastriggers AS hastriggers, 136 C.relrowsecurity AS rowsecurity 137 FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 138 LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace) 139 WHERE C.relkind IN ('r', 'p'); 140 141CREATE VIEW pg_matviews AS 142 SELECT 143 N.nspname AS schemaname, 144 C.relname AS matviewname, 145 pg_get_userbyid(C.relowner) AS matviewowner, 146 T.spcname AS tablespace, 147 C.relhasindex AS hasindexes, 148 C.relispopulated AS ispopulated, 149 pg_get_viewdef(C.oid) AS definition 150 FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 151 LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace) 152 WHERE C.relkind = 'm'; 153 154CREATE VIEW pg_indexes AS 155 SELECT 156 N.nspname AS schemaname, 157 C.relname AS tablename, 158 I.relname AS indexname, 159 T.spcname AS tablespace, 160 pg_get_indexdef(I.oid) AS indexdef 161 FROM pg_index X JOIN pg_class C ON (C.oid = X.indrelid) 162 JOIN pg_class I ON (I.oid = X.indexrelid) 163 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 164 LEFT JOIN pg_tablespace T ON (T.oid = I.reltablespace) 165 WHERE C.relkind IN ('r', 'm', 'p') AND I.relkind IN ('i', 'I'); 166 167CREATE VIEW pg_sequences AS 168 SELECT 169 N.nspname AS schemaname, 170 C.relname AS sequencename, 171 pg_get_userbyid(C.relowner) AS sequenceowner, 172 S.seqtypid::regtype AS data_type, 173 S.seqstart AS start_value, 174 S.seqmin AS min_value, 175 S.seqmax AS max_value, 176 S.seqincrement AS increment_by, 177 S.seqcycle AS cycle, 178 S.seqcache AS cache_size, 179 CASE 180 WHEN has_sequence_privilege(C.oid, 'SELECT,USAGE'::text) 181 THEN pg_sequence_last_value(C.oid) 182 ELSE NULL 183 END AS last_value 184 FROM pg_sequence S JOIN pg_class C ON (C.oid = S.seqrelid) 185 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 186 WHERE NOT pg_is_other_temp_schema(N.oid) 187 AND relkind = 'S'; 188 189CREATE VIEW pg_stats WITH (security_barrier) AS 190 SELECT 191 nspname AS schemaname, 192 relname AS tablename, 193 attname AS attname, 194 stainherit AS inherited, 195 stanullfrac AS null_frac, 196 stawidth AS avg_width, 197 stadistinct AS n_distinct, 198 CASE 199 WHEN stakind1 = 1 THEN stavalues1 200 WHEN stakind2 = 1 THEN stavalues2 201 WHEN stakind3 = 1 THEN stavalues3 202 WHEN stakind4 = 1 THEN stavalues4 203 WHEN stakind5 = 1 THEN stavalues5 204 END AS most_common_vals, 205 CASE 206 WHEN stakind1 = 1 THEN stanumbers1 207 WHEN stakind2 = 1 THEN stanumbers2 208 WHEN stakind3 = 1 THEN stanumbers3 209 WHEN stakind4 = 1 THEN stanumbers4 210 WHEN stakind5 = 1 THEN stanumbers5 211 END AS most_common_freqs, 212 CASE 213 WHEN stakind1 = 2 THEN stavalues1 214 WHEN stakind2 = 2 THEN stavalues2 215 WHEN stakind3 = 2 THEN stavalues3 216 WHEN stakind4 = 2 THEN stavalues4 217 WHEN stakind5 = 2 THEN stavalues5 218 END AS histogram_bounds, 219 CASE 220 WHEN stakind1 = 3 THEN stanumbers1[1] 221 WHEN stakind2 = 3 THEN stanumbers2[1] 222 WHEN stakind3 = 3 THEN stanumbers3[1] 223 WHEN stakind4 = 3 THEN stanumbers4[1] 224 WHEN stakind5 = 3 THEN stanumbers5[1] 225 END AS correlation, 226 CASE 227 WHEN stakind1 = 4 THEN stavalues1 228 WHEN stakind2 = 4 THEN stavalues2 229 WHEN stakind3 = 4 THEN stavalues3 230 WHEN stakind4 = 4 THEN stavalues4 231 WHEN stakind5 = 4 THEN stavalues5 232 END AS most_common_elems, 233 CASE 234 WHEN stakind1 = 4 THEN stanumbers1 235 WHEN stakind2 = 4 THEN stanumbers2 236 WHEN stakind3 = 4 THEN stanumbers3 237 WHEN stakind4 = 4 THEN stanumbers4 238 WHEN stakind5 = 4 THEN stanumbers5 239 END AS most_common_elem_freqs, 240 CASE 241 WHEN stakind1 = 5 THEN stanumbers1 242 WHEN stakind2 = 5 THEN stanumbers2 243 WHEN stakind3 = 5 THEN stanumbers3 244 WHEN stakind4 = 5 THEN stanumbers4 245 WHEN stakind5 = 5 THEN stanumbers5 246 END AS elem_count_histogram 247 FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid) 248 JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum) 249 LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace) 250 WHERE NOT attisdropped 251 AND has_column_privilege(c.oid, a.attnum, 'select') 252 AND (c.relrowsecurity = false OR NOT row_security_active(c.oid)); 253 254REVOKE ALL ON pg_statistic FROM public; 255 256CREATE VIEW pg_stats_ext WITH (security_barrier) AS 257 SELECT cn.nspname AS schemaname, 258 c.relname AS tablename, 259 sn.nspname AS statistics_schemaname, 260 s.stxname AS statistics_name, 261 pg_get_userbyid(s.stxowner) AS statistics_owner, 262 ( SELECT array_agg(a.attname ORDER BY a.attnum) 263 FROM unnest(s.stxkeys) k 264 JOIN pg_attribute a 265 ON (a.attrelid = s.stxrelid AND a.attnum = k) 266 ) AS attnames, 267 pg_get_statisticsobjdef_expressions(s.oid) as exprs, 268 s.stxkind AS kinds, 269 sd.stxdndistinct AS n_distinct, 270 sd.stxddependencies AS dependencies, 271 m.most_common_vals, 272 m.most_common_val_nulls, 273 m.most_common_freqs, 274 m.most_common_base_freqs 275 FROM pg_statistic_ext s JOIN pg_class c ON (c.oid = s.stxrelid) 276 JOIN pg_statistic_ext_data sd ON (s.oid = sd.stxoid) 277 LEFT JOIN pg_namespace cn ON (cn.oid = c.relnamespace) 278 LEFT JOIN pg_namespace sn ON (sn.oid = s.stxnamespace) 279 LEFT JOIN LATERAL 280 ( SELECT array_agg(values) AS most_common_vals, 281 array_agg(nulls) AS most_common_val_nulls, 282 array_agg(frequency) AS most_common_freqs, 283 array_agg(base_frequency) AS most_common_base_freqs 284 FROM pg_mcv_list_items(sd.stxdmcv) 285 ) m ON sd.stxdmcv IS NOT NULL 286 WHERE NOT EXISTS 287 ( SELECT 1 288 FROM unnest(stxkeys) k 289 JOIN pg_attribute a 290 ON (a.attrelid = s.stxrelid AND a.attnum = k) 291 WHERE NOT has_column_privilege(c.oid, a.attnum, 'select') ) 292 AND (c.relrowsecurity = false OR NOT row_security_active(c.oid)); 293 294CREATE VIEW pg_stats_ext_exprs WITH (security_barrier) AS 295 SELECT cn.nspname AS schemaname, 296 c.relname AS tablename, 297 sn.nspname AS statistics_schemaname, 298 s.stxname AS statistics_name, 299 pg_get_userbyid(s.stxowner) AS statistics_owner, 300 stat.expr, 301 (stat.a).stanullfrac AS null_frac, 302 (stat.a).stawidth AS avg_width, 303 (stat.a).stadistinct AS n_distinct, 304 (CASE 305 WHEN (stat.a).stakind1 = 1 THEN (stat.a).stavalues1 306 WHEN (stat.a).stakind2 = 1 THEN (stat.a).stavalues2 307 WHEN (stat.a).stakind3 = 1 THEN (stat.a).stavalues3 308 WHEN (stat.a).stakind4 = 1 THEN (stat.a).stavalues4 309 WHEN (stat.a).stakind5 = 1 THEN (stat.a).stavalues5 310 END) AS most_common_vals, 311 (CASE 312 WHEN (stat.a).stakind1 = 1 THEN (stat.a).stanumbers1 313 WHEN (stat.a).stakind2 = 1 THEN (stat.a).stanumbers2 314 WHEN (stat.a).stakind3 = 1 THEN (stat.a).stanumbers3 315 WHEN (stat.a).stakind4 = 1 THEN (stat.a).stanumbers4 316 WHEN (stat.a).stakind5 = 1 THEN (stat.a).stanumbers5 317 END) AS most_common_freqs, 318 (CASE 319 WHEN (stat.a).stakind1 = 2 THEN (stat.a).stavalues1 320 WHEN (stat.a).stakind2 = 2 THEN (stat.a).stavalues2 321 WHEN (stat.a).stakind3 = 2 THEN (stat.a).stavalues3 322 WHEN (stat.a).stakind4 = 2 THEN (stat.a).stavalues4 323 WHEN (stat.a).stakind5 = 2 THEN (stat.a).stavalues5 324 END) AS histogram_bounds, 325 (CASE 326 WHEN (stat.a).stakind1 = 3 THEN (stat.a).stanumbers1[1] 327 WHEN (stat.a).stakind2 = 3 THEN (stat.a).stanumbers2[1] 328 WHEN (stat.a).stakind3 = 3 THEN (stat.a).stanumbers3[1] 329 WHEN (stat.a).stakind4 = 3 THEN (stat.a).stanumbers4[1] 330 WHEN (stat.a).stakind5 = 3 THEN (stat.a).stanumbers5[1] 331 END) correlation, 332 (CASE 333 WHEN (stat.a).stakind1 = 4 THEN (stat.a).stavalues1 334 WHEN (stat.a).stakind2 = 4 THEN (stat.a).stavalues2 335 WHEN (stat.a).stakind3 = 4 THEN (stat.a).stavalues3 336 WHEN (stat.a).stakind4 = 4 THEN (stat.a).stavalues4 337 WHEN (stat.a).stakind5 = 4 THEN (stat.a).stavalues5 338 END) AS most_common_elems, 339 (CASE 340 WHEN (stat.a).stakind1 = 4 THEN (stat.a).stanumbers1 341 WHEN (stat.a).stakind2 = 4 THEN (stat.a).stanumbers2 342 WHEN (stat.a).stakind3 = 4 THEN (stat.a).stanumbers3 343 WHEN (stat.a).stakind4 = 4 THEN (stat.a).stanumbers4 344 WHEN (stat.a).stakind5 = 4 THEN (stat.a).stanumbers5 345 END) AS most_common_elem_freqs, 346 (CASE 347 WHEN (stat.a).stakind1 = 5 THEN (stat.a).stanumbers1 348 WHEN (stat.a).stakind2 = 5 THEN (stat.a).stanumbers2 349 WHEN (stat.a).stakind3 = 5 THEN (stat.a).stanumbers3 350 WHEN (stat.a).stakind4 = 5 THEN (stat.a).stanumbers4 351 WHEN (stat.a).stakind5 = 5 THEN (stat.a).stanumbers5 352 END) AS elem_count_histogram 353 FROM pg_statistic_ext s JOIN pg_class c ON (c.oid = s.stxrelid) 354 LEFT JOIN pg_statistic_ext_data sd ON (s.oid = sd.stxoid) 355 LEFT JOIN pg_namespace cn ON (cn.oid = c.relnamespace) 356 LEFT JOIN pg_namespace sn ON (sn.oid = s.stxnamespace) 357 JOIN LATERAL ( 358 SELECT unnest(pg_get_statisticsobjdef_expressions(s.oid)) AS expr, 359 unnest(sd.stxdexpr)::pg_statistic AS a 360 ) stat ON (stat.expr IS NOT NULL); 361 362-- unprivileged users may read pg_statistic_ext but not pg_statistic_ext_data 363REVOKE ALL ON pg_statistic_ext_data FROM public; 364 365CREATE VIEW pg_publication_tables AS 366 SELECT 367 P.pubname AS pubname, 368 N.nspname AS schemaname, 369 C.relname AS tablename 370 FROM pg_publication P, 371 LATERAL pg_get_publication_tables(P.pubname) GPT, 372 pg_class C JOIN pg_namespace N ON (N.oid = C.relnamespace) 373 WHERE C.oid = GPT.relid; 374 375CREATE VIEW pg_locks AS 376 SELECT * FROM pg_lock_status() AS L; 377 378CREATE VIEW pg_cursors AS 379 SELECT * FROM pg_cursor() AS C; 380 381CREATE VIEW pg_available_extensions AS 382 SELECT E.name, E.default_version, X.extversion AS installed_version, 383 E.comment 384 FROM pg_available_extensions() AS E 385 LEFT JOIN pg_extension AS X ON E.name = X.extname; 386 387CREATE VIEW pg_available_extension_versions AS 388 SELECT E.name, E.version, (X.extname IS NOT NULL) AS installed, 389 E.superuser, E.trusted, E.relocatable, 390 E.schema, E.requires, E.comment 391 FROM pg_available_extension_versions() AS E 392 LEFT JOIN pg_extension AS X 393 ON E.name = X.extname AND E.version = X.extversion; 394 395CREATE VIEW pg_prepared_xacts AS 396 SELECT P.transaction, P.gid, P.prepared, 397 U.rolname AS owner, D.datname AS database 398 FROM pg_prepared_xact() AS P 399 LEFT JOIN pg_authid U ON P.ownerid = U.oid 400 LEFT JOIN pg_database D ON P.dbid = D.oid; 401 402CREATE VIEW pg_prepared_statements AS 403 SELECT * FROM pg_prepared_statement() AS P; 404 405CREATE VIEW pg_seclabels AS 406SELECT 407 l.objoid, l.classoid, l.objsubid, 408 CASE WHEN rel.relkind IN ('r', 'p') THEN 'table'::text 409 WHEN rel.relkind = 'v' THEN 'view'::text 410 WHEN rel.relkind = 'm' THEN 'materialized view'::text 411 WHEN rel.relkind = 'S' THEN 'sequence'::text 412 WHEN rel.relkind = 'f' THEN 'foreign table'::text END AS objtype, 413 rel.relnamespace AS objnamespace, 414 CASE WHEN pg_table_is_visible(rel.oid) 415 THEN quote_ident(rel.relname) 416 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(rel.relname) 417 END AS objname, 418 l.provider, l.label 419FROM 420 pg_seclabel l 421 JOIN pg_class rel ON l.classoid = rel.tableoid AND l.objoid = rel.oid 422 JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid 423WHERE 424 l.objsubid = 0 425UNION ALL 426SELECT 427 l.objoid, l.classoid, l.objsubid, 428 'column'::text AS objtype, 429 rel.relnamespace AS objnamespace, 430 CASE WHEN pg_table_is_visible(rel.oid) 431 THEN quote_ident(rel.relname) 432 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(rel.relname) 433 END || '.' || att.attname AS objname, 434 l.provider, l.label 435FROM 436 pg_seclabel l 437 JOIN pg_class rel ON l.classoid = rel.tableoid AND l.objoid = rel.oid 438 JOIN pg_attribute att 439 ON rel.oid = att.attrelid AND l.objsubid = att.attnum 440 JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid 441WHERE 442 l.objsubid != 0 443UNION ALL 444SELECT 445 l.objoid, l.classoid, l.objsubid, 446 CASE pro.prokind 447 WHEN 'a' THEN 'aggregate'::text 448 WHEN 'f' THEN 'function'::text 449 WHEN 'p' THEN 'procedure'::text 450 WHEN 'w' THEN 'window'::text END AS objtype, 451 pro.pronamespace AS objnamespace, 452 CASE WHEN pg_function_is_visible(pro.oid) 453 THEN quote_ident(pro.proname) 454 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(pro.proname) 455 END || '(' || pg_catalog.pg_get_function_arguments(pro.oid) || ')' AS objname, 456 l.provider, l.label 457FROM 458 pg_seclabel l 459 JOIN pg_proc pro ON l.classoid = pro.tableoid AND l.objoid = pro.oid 460 JOIN pg_namespace nsp ON pro.pronamespace = nsp.oid 461WHERE 462 l.objsubid = 0 463UNION ALL 464SELECT 465 l.objoid, l.classoid, l.objsubid, 466 CASE WHEN typ.typtype = 'd' THEN 'domain'::text 467 ELSE 'type'::text END AS objtype, 468 typ.typnamespace AS objnamespace, 469 CASE WHEN pg_type_is_visible(typ.oid) 470 THEN quote_ident(typ.typname) 471 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(typ.typname) 472 END AS objname, 473 l.provider, l.label 474FROM 475 pg_seclabel l 476 JOIN pg_type typ ON l.classoid = typ.tableoid AND l.objoid = typ.oid 477 JOIN pg_namespace nsp ON typ.typnamespace = nsp.oid 478WHERE 479 l.objsubid = 0 480UNION ALL 481SELECT 482 l.objoid, l.classoid, l.objsubid, 483 'large object'::text AS objtype, 484 NULL::oid AS objnamespace, 485 l.objoid::text AS objname, 486 l.provider, l.label 487FROM 488 pg_seclabel l 489 JOIN pg_largeobject_metadata lom ON l.objoid = lom.oid 490WHERE 491 l.classoid = 'pg_catalog.pg_largeobject'::regclass AND l.objsubid = 0 492UNION ALL 493SELECT 494 l.objoid, l.classoid, l.objsubid, 495 'language'::text AS objtype, 496 NULL::oid AS objnamespace, 497 quote_ident(lan.lanname) AS objname, 498 l.provider, l.label 499FROM 500 pg_seclabel l 501 JOIN pg_language lan ON l.classoid = lan.tableoid AND l.objoid = lan.oid 502WHERE 503 l.objsubid = 0 504UNION ALL 505SELECT 506 l.objoid, l.classoid, l.objsubid, 507 'schema'::text AS objtype, 508 nsp.oid AS objnamespace, 509 quote_ident(nsp.nspname) AS objname, 510 l.provider, l.label 511FROM 512 pg_seclabel l 513 JOIN pg_namespace nsp ON l.classoid = nsp.tableoid AND l.objoid = nsp.oid 514WHERE 515 l.objsubid = 0 516UNION ALL 517SELECT 518 l.objoid, l.classoid, l.objsubid, 519 'event trigger'::text AS objtype, 520 NULL::oid AS objnamespace, 521 quote_ident(evt.evtname) AS objname, 522 l.provider, l.label 523FROM 524 pg_seclabel l 525 JOIN pg_event_trigger evt ON l.classoid = evt.tableoid 526 AND l.objoid = evt.oid 527WHERE 528 l.objsubid = 0 529UNION ALL 530SELECT 531 l.objoid, l.classoid, l.objsubid, 532 'publication'::text AS objtype, 533 NULL::oid AS objnamespace, 534 quote_ident(p.pubname) AS objname, 535 l.provider, l.label 536FROM 537 pg_seclabel l 538 JOIN pg_publication p ON l.classoid = p.tableoid AND l.objoid = p.oid 539WHERE 540 l.objsubid = 0 541UNION ALL 542SELECT 543 l.objoid, l.classoid, 0::int4 AS objsubid, 544 'subscription'::text AS objtype, 545 NULL::oid AS objnamespace, 546 quote_ident(s.subname) AS objname, 547 l.provider, l.label 548FROM 549 pg_shseclabel l 550 JOIN pg_subscription s ON l.classoid = s.tableoid AND l.objoid = s.oid 551UNION ALL 552SELECT 553 l.objoid, l.classoid, 0::int4 AS objsubid, 554 'database'::text AS objtype, 555 NULL::oid AS objnamespace, 556 quote_ident(dat.datname) AS objname, 557 l.provider, l.label 558FROM 559 pg_shseclabel l 560 JOIN pg_database dat ON l.classoid = dat.tableoid AND l.objoid = dat.oid 561UNION ALL 562SELECT 563 l.objoid, l.classoid, 0::int4 AS objsubid, 564 'tablespace'::text AS objtype, 565 NULL::oid AS objnamespace, 566 quote_ident(spc.spcname) AS objname, 567 l.provider, l.label 568FROM 569 pg_shseclabel l 570 JOIN pg_tablespace spc ON l.classoid = spc.tableoid AND l.objoid = spc.oid 571UNION ALL 572SELECT 573 l.objoid, l.classoid, 0::int4 AS objsubid, 574 'role'::text AS objtype, 575 NULL::oid AS objnamespace, 576 quote_ident(rol.rolname) AS objname, 577 l.provider, l.label 578FROM 579 pg_shseclabel l 580 JOIN pg_authid rol ON l.classoid = rol.tableoid AND l.objoid = rol.oid; 581 582CREATE VIEW pg_settings AS 583 SELECT * FROM pg_show_all_settings() AS A; 584 585CREATE RULE pg_settings_u AS 586 ON UPDATE TO pg_settings 587 WHERE new.name = old.name DO 588 SELECT set_config(old.name, new.setting, 'f'); 589 590CREATE RULE pg_settings_n AS 591 ON UPDATE TO pg_settings 592 DO INSTEAD NOTHING; 593 594GRANT SELECT, UPDATE ON pg_settings TO PUBLIC; 595 596CREATE VIEW pg_file_settings AS 597 SELECT * FROM pg_show_all_file_settings() AS A; 598 599REVOKE ALL ON pg_file_settings FROM PUBLIC; 600REVOKE EXECUTE ON FUNCTION pg_show_all_file_settings() FROM PUBLIC; 601 602CREATE VIEW pg_hba_file_rules AS 603 SELECT * FROM pg_hba_file_rules() AS A; 604 605REVOKE ALL ON pg_hba_file_rules FROM PUBLIC; 606REVOKE EXECUTE ON FUNCTION pg_hba_file_rules() FROM PUBLIC; 607 608CREATE VIEW pg_timezone_abbrevs AS 609 SELECT * FROM pg_timezone_abbrevs(); 610 611CREATE VIEW pg_timezone_names AS 612 SELECT * FROM pg_timezone_names(); 613 614CREATE VIEW pg_config AS 615 SELECT * FROM pg_config(); 616 617REVOKE ALL ON pg_config FROM PUBLIC; 618REVOKE EXECUTE ON FUNCTION pg_config() FROM PUBLIC; 619 620CREATE VIEW pg_shmem_allocations AS 621 SELECT * FROM pg_get_shmem_allocations(); 622 623REVOKE ALL ON pg_shmem_allocations FROM PUBLIC; 624REVOKE EXECUTE ON FUNCTION pg_get_shmem_allocations() FROM PUBLIC; 625 626CREATE VIEW pg_backend_memory_contexts AS 627 SELECT * FROM pg_get_backend_memory_contexts(); 628 629REVOKE ALL ON pg_backend_memory_contexts FROM PUBLIC; 630REVOKE EXECUTE ON FUNCTION pg_get_backend_memory_contexts() FROM PUBLIC; 631 632-- Statistics views 633 634CREATE VIEW pg_stat_all_tables AS 635 SELECT 636 C.oid AS relid, 637 N.nspname AS schemaname, 638 C.relname AS relname, 639 pg_stat_get_numscans(C.oid) AS seq_scan, 640 pg_stat_get_tuples_returned(C.oid) AS seq_tup_read, 641 sum(pg_stat_get_numscans(I.indexrelid))::bigint AS idx_scan, 642 sum(pg_stat_get_tuples_fetched(I.indexrelid))::bigint + 643 pg_stat_get_tuples_fetched(C.oid) AS idx_tup_fetch, 644 pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins, 645 pg_stat_get_tuples_updated(C.oid) AS n_tup_upd, 646 pg_stat_get_tuples_deleted(C.oid) AS n_tup_del, 647 pg_stat_get_tuples_hot_updated(C.oid) AS n_tup_hot_upd, 648 pg_stat_get_live_tuples(C.oid) AS n_live_tup, 649 pg_stat_get_dead_tuples(C.oid) AS n_dead_tup, 650 pg_stat_get_mod_since_analyze(C.oid) AS n_mod_since_analyze, 651 pg_stat_get_ins_since_vacuum(C.oid) AS n_ins_since_vacuum, 652 pg_stat_get_last_vacuum_time(C.oid) as last_vacuum, 653 pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum, 654 pg_stat_get_last_analyze_time(C.oid) as last_analyze, 655 pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze, 656 pg_stat_get_vacuum_count(C.oid) AS vacuum_count, 657 pg_stat_get_autovacuum_count(C.oid) AS autovacuum_count, 658 pg_stat_get_analyze_count(C.oid) AS analyze_count, 659 pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count 660 FROM pg_class C LEFT JOIN 661 pg_index I ON C.oid = I.indrelid 662 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 663 WHERE C.relkind IN ('r', 't', 'm', 'p') 664 GROUP BY C.oid, N.nspname, C.relname; 665 666CREATE VIEW pg_stat_xact_all_tables AS 667 SELECT 668 C.oid AS relid, 669 N.nspname AS schemaname, 670 C.relname AS relname, 671 pg_stat_get_xact_numscans(C.oid) AS seq_scan, 672 pg_stat_get_xact_tuples_returned(C.oid) AS seq_tup_read, 673 sum(pg_stat_get_xact_numscans(I.indexrelid))::bigint AS idx_scan, 674 sum(pg_stat_get_xact_tuples_fetched(I.indexrelid))::bigint + 675 pg_stat_get_xact_tuples_fetched(C.oid) AS idx_tup_fetch, 676 pg_stat_get_xact_tuples_inserted(C.oid) AS n_tup_ins, 677 pg_stat_get_xact_tuples_updated(C.oid) AS n_tup_upd, 678 pg_stat_get_xact_tuples_deleted(C.oid) AS n_tup_del, 679 pg_stat_get_xact_tuples_hot_updated(C.oid) AS n_tup_hot_upd 680 FROM pg_class C LEFT JOIN 681 pg_index I ON C.oid = I.indrelid 682 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 683 WHERE C.relkind IN ('r', 't', 'm', 'p') 684 GROUP BY C.oid, N.nspname, C.relname; 685 686CREATE VIEW pg_stat_sys_tables AS 687 SELECT * FROM pg_stat_all_tables 688 WHERE schemaname IN ('pg_catalog', 'information_schema') OR 689 schemaname ~ '^pg_toast'; 690 691CREATE VIEW pg_stat_xact_sys_tables AS 692 SELECT * FROM pg_stat_xact_all_tables 693 WHERE schemaname IN ('pg_catalog', 'information_schema') OR 694 schemaname ~ '^pg_toast'; 695 696CREATE VIEW pg_stat_user_tables AS 697 SELECT * FROM pg_stat_all_tables 698 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND 699 schemaname !~ '^pg_toast'; 700 701CREATE VIEW pg_stat_xact_user_tables AS 702 SELECT * FROM pg_stat_xact_all_tables 703 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND 704 schemaname !~ '^pg_toast'; 705 706CREATE VIEW pg_statio_all_tables AS 707 SELECT 708 C.oid AS relid, 709 N.nspname AS schemaname, 710 C.relname AS relname, 711 pg_stat_get_blocks_fetched(C.oid) - 712 pg_stat_get_blocks_hit(C.oid) AS heap_blks_read, 713 pg_stat_get_blocks_hit(C.oid) AS heap_blks_hit, 714 sum(pg_stat_get_blocks_fetched(I.indexrelid) - 715 pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_read, 716 sum(pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_hit, 717 pg_stat_get_blocks_fetched(T.oid) - 718 pg_stat_get_blocks_hit(T.oid) AS toast_blks_read, 719 pg_stat_get_blocks_hit(T.oid) AS toast_blks_hit, 720 pg_stat_get_blocks_fetched(X.indexrelid) - 721 pg_stat_get_blocks_hit(X.indexrelid) AS tidx_blks_read, 722 pg_stat_get_blocks_hit(X.indexrelid) AS tidx_blks_hit 723 FROM pg_class C LEFT JOIN 724 pg_index I ON C.oid = I.indrelid LEFT JOIN 725 pg_class T ON C.reltoastrelid = T.oid LEFT JOIN 726 pg_index X ON T.oid = X.indrelid 727 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 728 WHERE C.relkind IN ('r', 't', 'm') 729 GROUP BY C.oid, N.nspname, C.relname, T.oid, X.indexrelid; 730 731CREATE VIEW pg_statio_sys_tables AS 732 SELECT * FROM pg_statio_all_tables 733 WHERE schemaname IN ('pg_catalog', 'information_schema') OR 734 schemaname ~ '^pg_toast'; 735 736CREATE VIEW pg_statio_user_tables AS 737 SELECT * FROM pg_statio_all_tables 738 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND 739 schemaname !~ '^pg_toast'; 740 741CREATE VIEW pg_stat_all_indexes AS 742 SELECT 743 C.oid AS relid, 744 I.oid AS indexrelid, 745 N.nspname AS schemaname, 746 C.relname AS relname, 747 I.relname AS indexrelname, 748 pg_stat_get_numscans(I.oid) AS idx_scan, 749 pg_stat_get_tuples_returned(I.oid) AS idx_tup_read, 750 pg_stat_get_tuples_fetched(I.oid) AS idx_tup_fetch 751 FROM pg_class C JOIN 752 pg_index X ON C.oid = X.indrelid JOIN 753 pg_class I ON I.oid = X.indexrelid 754 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 755 WHERE C.relkind IN ('r', 't', 'm'); 756 757CREATE VIEW pg_stat_sys_indexes AS 758 SELECT * FROM pg_stat_all_indexes 759 WHERE schemaname IN ('pg_catalog', 'information_schema') OR 760 schemaname ~ '^pg_toast'; 761 762CREATE VIEW pg_stat_user_indexes AS 763 SELECT * FROM pg_stat_all_indexes 764 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND 765 schemaname !~ '^pg_toast'; 766 767CREATE VIEW pg_statio_all_indexes AS 768 SELECT 769 C.oid AS relid, 770 I.oid AS indexrelid, 771 N.nspname AS schemaname, 772 C.relname AS relname, 773 I.relname AS indexrelname, 774 pg_stat_get_blocks_fetched(I.oid) - 775 pg_stat_get_blocks_hit(I.oid) AS idx_blks_read, 776 pg_stat_get_blocks_hit(I.oid) AS idx_blks_hit 777 FROM pg_class C JOIN 778 pg_index X ON C.oid = X.indrelid JOIN 779 pg_class I ON I.oid = X.indexrelid 780 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 781 WHERE C.relkind IN ('r', 't', 'm'); 782 783CREATE VIEW pg_statio_sys_indexes AS 784 SELECT * FROM pg_statio_all_indexes 785 WHERE schemaname IN ('pg_catalog', 'information_schema') OR 786 schemaname ~ '^pg_toast'; 787 788CREATE VIEW pg_statio_user_indexes AS 789 SELECT * FROM pg_statio_all_indexes 790 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND 791 schemaname !~ '^pg_toast'; 792 793CREATE VIEW pg_statio_all_sequences AS 794 SELECT 795 C.oid AS relid, 796 N.nspname AS schemaname, 797 C.relname AS relname, 798 pg_stat_get_blocks_fetched(C.oid) - 799 pg_stat_get_blocks_hit(C.oid) AS blks_read, 800 pg_stat_get_blocks_hit(C.oid) AS blks_hit 801 FROM pg_class C 802 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 803 WHERE C.relkind = 'S'; 804 805CREATE VIEW pg_statio_sys_sequences AS 806 SELECT * FROM pg_statio_all_sequences 807 WHERE schemaname IN ('pg_catalog', 'information_schema') OR 808 schemaname ~ '^pg_toast'; 809 810CREATE VIEW pg_statio_user_sequences AS 811 SELECT * FROM pg_statio_all_sequences 812 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND 813 schemaname !~ '^pg_toast'; 814 815CREATE VIEW pg_stat_activity AS 816 SELECT 817 S.datid AS datid, 818 D.datname AS datname, 819 S.pid, 820 S.leader_pid, 821 S.usesysid, 822 U.rolname AS usename, 823 S.application_name, 824 S.client_addr, 825 S.client_hostname, 826 S.client_port, 827 S.backend_start, 828 S.xact_start, 829 S.query_start, 830 S.state_change, 831 S.wait_event_type, 832 S.wait_event, 833 S.state, 834 S.backend_xid, 835 s.backend_xmin, 836 S.query_id, 837 S.query, 838 S.backend_type 839 FROM pg_stat_get_activity(NULL) AS S 840 LEFT JOIN pg_database AS D ON (S.datid = D.oid) 841 LEFT JOIN pg_authid AS U ON (S.usesysid = U.oid); 842 843CREATE VIEW pg_stat_replication AS 844 SELECT 845 S.pid, 846 S.usesysid, 847 U.rolname AS usename, 848 S.application_name, 849 S.client_addr, 850 S.client_hostname, 851 S.client_port, 852 S.backend_start, 853 S.backend_xmin, 854 W.state, 855 W.sent_lsn, 856 W.write_lsn, 857 W.flush_lsn, 858 W.replay_lsn, 859 W.write_lag, 860 W.flush_lag, 861 W.replay_lag, 862 W.sync_priority, 863 W.sync_state, 864 W.reply_time 865 FROM pg_stat_get_activity(NULL) AS S 866 JOIN pg_stat_get_wal_senders() AS W ON (S.pid = W.pid) 867 LEFT JOIN pg_authid AS U ON (S.usesysid = U.oid); 868 869CREATE VIEW pg_stat_slru AS 870 SELECT 871 s.name, 872 s.blks_zeroed, 873 s.blks_hit, 874 s.blks_read, 875 s.blks_written, 876 s.blks_exists, 877 s.flushes, 878 s.truncates, 879 s.stats_reset 880 FROM pg_stat_get_slru() s; 881 882CREATE VIEW pg_stat_wal_receiver AS 883 SELECT 884 s.pid, 885 s.status, 886 s.receive_start_lsn, 887 s.receive_start_tli, 888 s.written_lsn, 889 s.flushed_lsn, 890 s.received_tli, 891 s.last_msg_send_time, 892 s.last_msg_receipt_time, 893 s.latest_end_lsn, 894 s.latest_end_time, 895 s.slot_name, 896 s.sender_host, 897 s.sender_port, 898 s.conninfo 899 FROM pg_stat_get_wal_receiver() s 900 WHERE s.pid IS NOT NULL; 901 902CREATE VIEW pg_stat_subscription AS 903 SELECT 904 su.oid AS subid, 905 su.subname, 906 st.pid, 907 st.relid, 908 st.received_lsn, 909 st.last_msg_send_time, 910 st.last_msg_receipt_time, 911 st.latest_end_lsn, 912 st.latest_end_time 913 FROM pg_subscription su 914 LEFT JOIN pg_stat_get_subscription(NULL) st 915 ON (st.subid = su.oid); 916 917CREATE VIEW pg_stat_ssl AS 918 SELECT 919 S.pid, 920 S.ssl, 921 S.sslversion AS version, 922 S.sslcipher AS cipher, 923 S.sslbits AS bits, 924 S.ssl_client_dn AS client_dn, 925 S.ssl_client_serial AS client_serial, 926 S.ssl_issuer_dn AS issuer_dn 927 FROM pg_stat_get_activity(NULL) AS S 928 WHERE S.client_port IS NOT NULL; 929 930CREATE VIEW pg_stat_gssapi AS 931 SELECT 932 S.pid, 933 S.gss_auth AS gss_authenticated, 934 S.gss_princ AS principal, 935 S.gss_enc AS encrypted 936 FROM pg_stat_get_activity(NULL) AS S 937 WHERE S.client_port IS NOT NULL; 938 939CREATE VIEW pg_replication_slots AS 940 SELECT 941 L.slot_name, 942 L.plugin, 943 L.slot_type, 944 L.datoid, 945 D.datname AS database, 946 L.temporary, 947 L.active, 948 L.active_pid, 949 L.xmin, 950 L.catalog_xmin, 951 L.restart_lsn, 952 L.confirmed_flush_lsn, 953 L.wal_status, 954 L.safe_wal_size, 955 L.two_phase 956 FROM pg_get_replication_slots() AS L 957 LEFT JOIN pg_database D ON (L.datoid = D.oid); 958 959CREATE VIEW pg_stat_replication_slots AS 960 SELECT 961 s.slot_name, 962 s.spill_txns, 963 s.spill_count, 964 s.spill_bytes, 965 s.stream_txns, 966 s.stream_count, 967 s.stream_bytes, 968 s.total_txns, 969 s.total_bytes, 970 s.stats_reset 971 FROM pg_replication_slots as r, 972 LATERAL pg_stat_get_replication_slot(slot_name) as s 973 WHERE r.datoid IS NOT NULL; -- excluding physical slots 974 975CREATE VIEW pg_stat_database AS 976 SELECT 977 D.oid AS datid, 978 D.datname AS datname, 979 CASE 980 WHEN (D.oid = (0)::oid) THEN 0 981 ELSE pg_stat_get_db_numbackends(D.oid) 982 END AS numbackends, 983 pg_stat_get_db_xact_commit(D.oid) AS xact_commit, 984 pg_stat_get_db_xact_rollback(D.oid) AS xact_rollback, 985 pg_stat_get_db_blocks_fetched(D.oid) - 986 pg_stat_get_db_blocks_hit(D.oid) AS blks_read, 987 pg_stat_get_db_blocks_hit(D.oid) AS blks_hit, 988 pg_stat_get_db_tuples_returned(D.oid) AS tup_returned, 989 pg_stat_get_db_tuples_fetched(D.oid) AS tup_fetched, 990 pg_stat_get_db_tuples_inserted(D.oid) AS tup_inserted, 991 pg_stat_get_db_tuples_updated(D.oid) AS tup_updated, 992 pg_stat_get_db_tuples_deleted(D.oid) AS tup_deleted, 993 pg_stat_get_db_conflict_all(D.oid) AS conflicts, 994 pg_stat_get_db_temp_files(D.oid) AS temp_files, 995 pg_stat_get_db_temp_bytes(D.oid) AS temp_bytes, 996 pg_stat_get_db_deadlocks(D.oid) AS deadlocks, 997 pg_stat_get_db_checksum_failures(D.oid) AS checksum_failures, 998 pg_stat_get_db_checksum_last_failure(D.oid) AS checksum_last_failure, 999 pg_stat_get_db_blk_read_time(D.oid) AS blk_read_time, 1000 pg_stat_get_db_blk_write_time(D.oid) AS blk_write_time, 1001 pg_stat_get_db_session_time(D.oid) AS session_time, 1002 pg_stat_get_db_active_time(D.oid) AS active_time, 1003 pg_stat_get_db_idle_in_transaction_time(D.oid) AS idle_in_transaction_time, 1004 pg_stat_get_db_sessions(D.oid) AS sessions, 1005 pg_stat_get_db_sessions_abandoned(D.oid) AS sessions_abandoned, 1006 pg_stat_get_db_sessions_fatal(D.oid) AS sessions_fatal, 1007 pg_stat_get_db_sessions_killed(D.oid) AS sessions_killed, 1008 pg_stat_get_db_stat_reset_time(D.oid) AS stats_reset 1009 FROM ( 1010 SELECT 0 AS oid, NULL::name AS datname 1011 UNION ALL 1012 SELECT oid, datname FROM pg_database 1013 ) D; 1014 1015CREATE VIEW pg_stat_database_conflicts AS 1016 SELECT 1017 D.oid AS datid, 1018 D.datname AS datname, 1019 pg_stat_get_db_conflict_tablespace(D.oid) AS confl_tablespace, 1020 pg_stat_get_db_conflict_lock(D.oid) AS confl_lock, 1021 pg_stat_get_db_conflict_snapshot(D.oid) AS confl_snapshot, 1022 pg_stat_get_db_conflict_bufferpin(D.oid) AS confl_bufferpin, 1023 pg_stat_get_db_conflict_startup_deadlock(D.oid) AS confl_deadlock 1024 FROM pg_database D; 1025 1026CREATE VIEW pg_stat_user_functions AS 1027 SELECT 1028 P.oid AS funcid, 1029 N.nspname AS schemaname, 1030 P.proname AS funcname, 1031 pg_stat_get_function_calls(P.oid) AS calls, 1032 pg_stat_get_function_total_time(P.oid) AS total_time, 1033 pg_stat_get_function_self_time(P.oid) AS self_time 1034 FROM pg_proc P LEFT JOIN pg_namespace N ON (N.oid = P.pronamespace) 1035 WHERE P.prolang != 12 -- fast check to eliminate built-in functions 1036 AND pg_stat_get_function_calls(P.oid) IS NOT NULL; 1037 1038CREATE VIEW pg_stat_xact_user_functions AS 1039 SELECT 1040 P.oid AS funcid, 1041 N.nspname AS schemaname, 1042 P.proname AS funcname, 1043 pg_stat_get_xact_function_calls(P.oid) AS calls, 1044 pg_stat_get_xact_function_total_time(P.oid) AS total_time, 1045 pg_stat_get_xact_function_self_time(P.oid) AS self_time 1046 FROM pg_proc P LEFT JOIN pg_namespace N ON (N.oid = P.pronamespace) 1047 WHERE P.prolang != 12 -- fast check to eliminate built-in functions 1048 AND pg_stat_get_xact_function_calls(P.oid) IS NOT NULL; 1049 1050CREATE VIEW pg_stat_archiver AS 1051 SELECT 1052 s.archived_count, 1053 s.last_archived_wal, 1054 s.last_archived_time, 1055 s.failed_count, 1056 s.last_failed_wal, 1057 s.last_failed_time, 1058 s.stats_reset 1059 FROM pg_stat_get_archiver() s; 1060 1061CREATE VIEW pg_stat_bgwriter AS 1062 SELECT 1063 pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, 1064 pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, 1065 pg_stat_get_checkpoint_write_time() AS checkpoint_write_time, 1066 pg_stat_get_checkpoint_sync_time() AS checkpoint_sync_time, 1067 pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint, 1068 pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, 1069 pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, 1070 pg_stat_get_buf_written_backend() AS buffers_backend, 1071 pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync, 1072 pg_stat_get_buf_alloc() AS buffers_alloc, 1073 pg_stat_get_bgwriter_stat_reset_time() AS stats_reset; 1074 1075CREATE VIEW pg_stat_wal AS 1076 SELECT 1077 w.wal_records, 1078 w.wal_fpi, 1079 w.wal_bytes, 1080 w.wal_buffers_full, 1081 w.wal_write, 1082 w.wal_sync, 1083 w.wal_write_time, 1084 w.wal_sync_time, 1085 w.stats_reset 1086 FROM pg_stat_get_wal() w; 1087 1088CREATE VIEW pg_stat_progress_analyze AS 1089 SELECT 1090 S.pid AS pid, S.datid AS datid, D.datname AS datname, 1091 CAST(S.relid AS oid) AS relid, 1092 CASE S.param1 WHEN 0 THEN 'initializing' 1093 WHEN 1 THEN 'acquiring sample rows' 1094 WHEN 2 THEN 'acquiring inherited sample rows' 1095 WHEN 3 THEN 'computing statistics' 1096 WHEN 4 THEN 'computing extended statistics' 1097 WHEN 5 THEN 'finalizing analyze' 1098 END AS phase, 1099 S.param2 AS sample_blks_total, 1100 S.param3 AS sample_blks_scanned, 1101 S.param4 AS ext_stats_total, 1102 S.param5 AS ext_stats_computed, 1103 S.param6 AS child_tables_total, 1104 S.param7 AS child_tables_done, 1105 CAST(S.param8 AS oid) AS current_child_table_relid 1106 FROM pg_stat_get_progress_info('ANALYZE') AS S 1107 LEFT JOIN pg_database D ON S.datid = D.oid; 1108 1109CREATE VIEW pg_stat_progress_vacuum AS 1110 SELECT 1111 S.pid AS pid, S.datid AS datid, D.datname AS datname, 1112 S.relid AS relid, 1113 CASE S.param1 WHEN 0 THEN 'initializing' 1114 WHEN 1 THEN 'scanning heap' 1115 WHEN 2 THEN 'vacuuming indexes' 1116 WHEN 3 THEN 'vacuuming heap' 1117 WHEN 4 THEN 'cleaning up indexes' 1118 WHEN 5 THEN 'truncating heap' 1119 WHEN 6 THEN 'performing final cleanup' 1120 END AS phase, 1121 S.param2 AS heap_blks_total, S.param3 AS heap_blks_scanned, 1122 S.param4 AS heap_blks_vacuumed, S.param5 AS index_vacuum_count, 1123 S.param6 AS max_dead_tuples, S.param7 AS num_dead_tuples 1124 FROM pg_stat_get_progress_info('VACUUM') AS S 1125 LEFT JOIN pg_database D ON S.datid = D.oid; 1126 1127CREATE VIEW pg_stat_progress_cluster AS 1128 SELECT 1129 S.pid AS pid, 1130 S.datid AS datid, 1131 D.datname AS datname, 1132 S.relid AS relid, 1133 CASE S.param1 WHEN 1 THEN 'CLUSTER' 1134 WHEN 2 THEN 'VACUUM FULL' 1135 END AS command, 1136 CASE S.param2 WHEN 0 THEN 'initializing' 1137 WHEN 1 THEN 'seq scanning heap' 1138 WHEN 2 THEN 'index scanning heap' 1139 WHEN 3 THEN 'sorting tuples' 1140 WHEN 4 THEN 'writing new heap' 1141 WHEN 5 THEN 'swapping relation files' 1142 WHEN 6 THEN 'rebuilding index' 1143 WHEN 7 THEN 'performing final cleanup' 1144 END AS phase, 1145 CAST(S.param3 AS oid) AS cluster_index_relid, 1146 S.param4 AS heap_tuples_scanned, 1147 S.param5 AS heap_tuples_written, 1148 S.param6 AS heap_blks_total, 1149 S.param7 AS heap_blks_scanned, 1150 S.param8 AS index_rebuild_count 1151 FROM pg_stat_get_progress_info('CLUSTER') AS S 1152 LEFT JOIN pg_database D ON S.datid = D.oid; 1153 1154CREATE VIEW pg_stat_progress_create_index AS 1155 SELECT 1156 S.pid AS pid, S.datid AS datid, D.datname AS datname, 1157 S.relid AS relid, 1158 CAST(S.param7 AS oid) AS index_relid, 1159 CASE S.param1 WHEN 1 THEN 'CREATE INDEX' 1160 WHEN 2 THEN 'CREATE INDEX CONCURRENTLY' 1161 WHEN 3 THEN 'REINDEX' 1162 WHEN 4 THEN 'REINDEX CONCURRENTLY' 1163 END AS command, 1164 CASE S.param10 WHEN 0 THEN 'initializing' 1165 WHEN 1 THEN 'waiting for writers before build' 1166 WHEN 2 THEN 'building index' || 1167 COALESCE((': ' || pg_indexam_progress_phasename(S.param9::oid, S.param11)), 1168 '') 1169 WHEN 3 THEN 'waiting for writers before validation' 1170 WHEN 4 THEN 'index validation: scanning index' 1171 WHEN 5 THEN 'index validation: sorting tuples' 1172 WHEN 6 THEN 'index validation: scanning table' 1173 WHEN 7 THEN 'waiting for old snapshots' 1174 WHEN 8 THEN 'waiting for readers before marking dead' 1175 WHEN 9 THEN 'waiting for readers before dropping' 1176 END as phase, 1177 S.param4 AS lockers_total, 1178 S.param5 AS lockers_done, 1179 S.param6 AS current_locker_pid, 1180 S.param16 AS blocks_total, 1181 S.param17 AS blocks_done, 1182 S.param12 AS tuples_total, 1183 S.param13 AS tuples_done, 1184 S.param14 AS partitions_total, 1185 S.param15 AS partitions_done 1186 FROM pg_stat_get_progress_info('CREATE INDEX') AS S 1187 LEFT JOIN pg_database D ON S.datid = D.oid; 1188 1189CREATE VIEW pg_stat_progress_basebackup AS 1190 SELECT 1191 S.pid AS pid, 1192 CASE S.param1 WHEN 0 THEN 'initializing' 1193 WHEN 1 THEN 'waiting for checkpoint to finish' 1194 WHEN 2 THEN 'estimating backup size' 1195 WHEN 3 THEN 'streaming database files' 1196 WHEN 4 THEN 'waiting for wal archiving to finish' 1197 WHEN 5 THEN 'transferring wal files' 1198 END AS phase, 1199 CASE S.param2 WHEN -1 THEN NULL ELSE S.param2 END AS backup_total, 1200 S.param3 AS backup_streamed, 1201 S.param4 AS tablespaces_total, 1202 S.param5 AS tablespaces_streamed 1203 FROM pg_stat_get_progress_info('BASEBACKUP') AS S; 1204 1205 1206CREATE VIEW pg_stat_progress_copy AS 1207 SELECT 1208 S.pid AS pid, S.datid AS datid, D.datname AS datname, 1209 S.relid AS relid, 1210 CASE S.param5 WHEN 1 THEN 'COPY FROM' 1211 WHEN 2 THEN 'COPY TO' 1212 END AS command, 1213 CASE S.param6 WHEN 1 THEN 'FILE' 1214 WHEN 2 THEN 'PROGRAM' 1215 WHEN 3 THEN 'PIPE' 1216 WHEN 4 THEN 'CALLBACK' 1217 END AS "type", 1218 S.param1 AS bytes_processed, 1219 S.param2 AS bytes_total, 1220 S.param3 AS tuples_processed, 1221 S.param4 AS tuples_excluded 1222 FROM pg_stat_get_progress_info('COPY') AS S 1223 LEFT JOIN pg_database D ON S.datid = D.oid; 1224 1225CREATE VIEW pg_user_mappings AS 1226 SELECT 1227 U.oid AS umid, 1228 S.oid AS srvid, 1229 S.srvname AS srvname, 1230 U.umuser AS umuser, 1231 CASE WHEN U.umuser = 0 THEN 1232 'public' 1233 ELSE 1234 A.rolname 1235 END AS usename, 1236 CASE WHEN (U.umuser <> 0 AND A.rolname = current_user 1237 AND (pg_has_role(S.srvowner, 'USAGE') 1238 OR has_server_privilege(S.oid, 'USAGE'))) 1239 OR (U.umuser = 0 AND pg_has_role(S.srvowner, 'USAGE')) 1240 OR (SELECT rolsuper FROM pg_authid WHERE rolname = current_user) 1241 THEN U.umoptions 1242 ELSE NULL END AS umoptions 1243 FROM pg_user_mapping U 1244 JOIN pg_foreign_server S ON (U.umserver = S.oid) 1245 LEFT JOIN pg_authid A ON (A.oid = U.umuser); 1246 1247REVOKE ALL ON pg_user_mapping FROM public; 1248 1249CREATE VIEW pg_replication_origin_status AS 1250 SELECT * 1251 FROM pg_show_replication_origin_status(); 1252 1253REVOKE ALL ON pg_replication_origin_status FROM public; 1254 1255-- All columns of pg_subscription except subconninfo are publicly readable. 1256REVOKE ALL ON pg_subscription FROM public; 1257GRANT SELECT (oid, subdbid, subname, subowner, subenabled, subbinary, 1258 substream, subslotname, subsynccommit, subpublications) 1259 ON pg_subscription TO public; 1260