1/* 2 * PostgreSQL System Views 3 * 4 * Copyright (c) 1996-2019, 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 OR REPLACE 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 s.stxkind AS kinds, 268 sd.stxdndistinct AS n_distinct, 269 sd.stxddependencies AS dependencies, 270 m.most_common_vals, 271 m.most_common_val_nulls, 272 m.most_common_freqs, 273 m.most_common_base_freqs 274 FROM pg_statistic_ext s JOIN pg_class c ON (c.oid = s.stxrelid) 275 JOIN pg_statistic_ext_data sd ON (s.oid = sd.stxoid) 276 LEFT JOIN pg_namespace cn ON (cn.oid = c.relnamespace) 277 LEFT JOIN pg_namespace sn ON (sn.oid = s.stxnamespace) 278 LEFT JOIN LATERAL 279 ( SELECT array_agg(values) AS most_common_vals, 280 array_agg(nulls) AS most_common_val_nulls, 281 array_agg(frequency) AS most_common_freqs, 282 array_agg(base_frequency) AS most_common_base_freqs 283 FROM pg_mcv_list_items(sd.stxdmcv) 284 ) m ON sd.stxdmcv IS NOT NULL 285 WHERE NOT EXISTS 286 ( SELECT 1 287 FROM unnest(stxkeys) k 288 JOIN pg_attribute a 289 ON (a.attrelid = s.stxrelid AND a.attnum = k) 290 WHERE NOT has_column_privilege(c.oid, a.attnum, 'select') ) 291 AND (c.relrowsecurity = false OR NOT row_security_active(c.oid)); 292 293-- unprivileged users may read pg_statistic_ext but not pg_statistic_ext_data 294REVOKE ALL on pg_statistic_ext_data FROM public; 295 296CREATE VIEW pg_publication_tables AS 297 SELECT 298 P.pubname AS pubname, 299 N.nspname AS schemaname, 300 C.relname AS tablename 301 FROM pg_publication P, 302 LATERAL pg_get_publication_tables(P.pubname) GPT, 303 pg_class C JOIN pg_namespace N ON (N.oid = C.relnamespace) 304 WHERE C.oid = GPT.relid; 305 306CREATE VIEW pg_locks AS 307 SELECT * FROM pg_lock_status() AS L; 308 309CREATE VIEW pg_cursors AS 310 SELECT * FROM pg_cursor() AS C; 311 312CREATE VIEW pg_available_extensions AS 313 SELECT E.name, E.default_version, X.extversion AS installed_version, 314 E.comment 315 FROM pg_available_extensions() AS E 316 LEFT JOIN pg_extension AS X ON E.name = X.extname; 317 318CREATE VIEW pg_available_extension_versions AS 319 SELECT E.name, E.version, (X.extname IS NOT NULL) AS installed, 320 E.superuser, E.relocatable, E.schema, E.requires, E.comment 321 FROM pg_available_extension_versions() AS E 322 LEFT JOIN pg_extension AS X 323 ON E.name = X.extname AND E.version = X.extversion; 324 325CREATE VIEW pg_prepared_xacts AS 326 SELECT P.transaction, P.gid, P.prepared, 327 U.rolname AS owner, D.datname AS database 328 FROM pg_prepared_xact() AS P 329 LEFT JOIN pg_authid U ON P.ownerid = U.oid 330 LEFT JOIN pg_database D ON P.dbid = D.oid; 331 332CREATE VIEW pg_prepared_statements AS 333 SELECT * FROM pg_prepared_statement() AS P; 334 335CREATE VIEW pg_seclabels AS 336SELECT 337 l.objoid, l.classoid, l.objsubid, 338 CASE WHEN rel.relkind IN ('r', 'p') THEN 'table'::text 339 WHEN rel.relkind = 'v' THEN 'view'::text 340 WHEN rel.relkind = 'm' THEN 'materialized view'::text 341 WHEN rel.relkind = 'S' THEN 'sequence'::text 342 WHEN rel.relkind = 'f' THEN 'foreign table'::text END AS objtype, 343 rel.relnamespace AS objnamespace, 344 CASE WHEN pg_table_is_visible(rel.oid) 345 THEN quote_ident(rel.relname) 346 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(rel.relname) 347 END AS objname, 348 l.provider, l.label 349FROM 350 pg_seclabel l 351 JOIN pg_class rel ON l.classoid = rel.tableoid AND l.objoid = rel.oid 352 JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid 353WHERE 354 l.objsubid = 0 355UNION ALL 356SELECT 357 l.objoid, l.classoid, l.objsubid, 358 'column'::text AS objtype, 359 rel.relnamespace AS objnamespace, 360 CASE WHEN pg_table_is_visible(rel.oid) 361 THEN quote_ident(rel.relname) 362 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(rel.relname) 363 END || '.' || att.attname AS objname, 364 l.provider, l.label 365FROM 366 pg_seclabel l 367 JOIN pg_class rel ON l.classoid = rel.tableoid AND l.objoid = rel.oid 368 JOIN pg_attribute att 369 ON rel.oid = att.attrelid AND l.objsubid = att.attnum 370 JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid 371WHERE 372 l.objsubid != 0 373UNION ALL 374SELECT 375 l.objoid, l.classoid, l.objsubid, 376 CASE pro.prokind 377 WHEN 'a' THEN 'aggregate'::text 378 WHEN 'f' THEN 'function'::text 379 WHEN 'p' THEN 'procedure'::text 380 WHEN 'w' THEN 'window'::text END AS objtype, 381 pro.pronamespace AS objnamespace, 382 CASE WHEN pg_function_is_visible(pro.oid) 383 THEN quote_ident(pro.proname) 384 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(pro.proname) 385 END || '(' || pg_catalog.pg_get_function_arguments(pro.oid) || ')' AS objname, 386 l.provider, l.label 387FROM 388 pg_seclabel l 389 JOIN pg_proc pro ON l.classoid = pro.tableoid AND l.objoid = pro.oid 390 JOIN pg_namespace nsp ON pro.pronamespace = nsp.oid 391WHERE 392 l.objsubid = 0 393UNION ALL 394SELECT 395 l.objoid, l.classoid, l.objsubid, 396 CASE WHEN typ.typtype = 'd' THEN 'domain'::text 397 ELSE 'type'::text END AS objtype, 398 typ.typnamespace AS objnamespace, 399 CASE WHEN pg_type_is_visible(typ.oid) 400 THEN quote_ident(typ.typname) 401 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(typ.typname) 402 END AS objname, 403 l.provider, l.label 404FROM 405 pg_seclabel l 406 JOIN pg_type typ ON l.classoid = typ.tableoid AND l.objoid = typ.oid 407 JOIN pg_namespace nsp ON typ.typnamespace = nsp.oid 408WHERE 409 l.objsubid = 0 410UNION ALL 411SELECT 412 l.objoid, l.classoid, l.objsubid, 413 'large object'::text AS objtype, 414 NULL::oid AS objnamespace, 415 l.objoid::text AS objname, 416 l.provider, l.label 417FROM 418 pg_seclabel l 419 JOIN pg_largeobject_metadata lom ON l.objoid = lom.oid 420WHERE 421 l.classoid = 'pg_catalog.pg_largeobject'::regclass AND l.objsubid = 0 422UNION ALL 423SELECT 424 l.objoid, l.classoid, l.objsubid, 425 'language'::text AS objtype, 426 NULL::oid AS objnamespace, 427 quote_ident(lan.lanname) AS objname, 428 l.provider, l.label 429FROM 430 pg_seclabel l 431 JOIN pg_language lan ON l.classoid = lan.tableoid AND l.objoid = lan.oid 432WHERE 433 l.objsubid = 0 434UNION ALL 435SELECT 436 l.objoid, l.classoid, l.objsubid, 437 'schema'::text AS objtype, 438 nsp.oid AS objnamespace, 439 quote_ident(nsp.nspname) AS objname, 440 l.provider, l.label 441FROM 442 pg_seclabel l 443 JOIN pg_namespace nsp ON l.classoid = nsp.tableoid AND l.objoid = nsp.oid 444WHERE 445 l.objsubid = 0 446UNION ALL 447SELECT 448 l.objoid, l.classoid, l.objsubid, 449 'event trigger'::text AS objtype, 450 NULL::oid AS objnamespace, 451 quote_ident(evt.evtname) AS objname, 452 l.provider, l.label 453FROM 454 pg_seclabel l 455 JOIN pg_event_trigger evt ON l.classoid = evt.tableoid 456 AND l.objoid = evt.oid 457WHERE 458 l.objsubid = 0 459UNION ALL 460SELECT 461 l.objoid, l.classoid, l.objsubid, 462 'publication'::text AS objtype, 463 NULL::oid AS objnamespace, 464 quote_ident(p.pubname) AS objname, 465 l.provider, l.label 466FROM 467 pg_seclabel l 468 JOIN pg_publication p ON l.classoid = p.tableoid AND l.objoid = p.oid 469WHERE 470 l.objsubid = 0 471UNION ALL 472SELECT 473 l.objoid, l.classoid, 0::int4 AS objsubid, 474 'subscription'::text AS objtype, 475 NULL::oid AS objnamespace, 476 quote_ident(s.subname) AS objname, 477 l.provider, l.label 478FROM 479 pg_shseclabel l 480 JOIN pg_subscription s ON l.classoid = s.tableoid AND l.objoid = s.oid 481UNION ALL 482SELECT 483 l.objoid, l.classoid, 0::int4 AS objsubid, 484 'database'::text AS objtype, 485 NULL::oid AS objnamespace, 486 quote_ident(dat.datname) AS objname, 487 l.provider, l.label 488FROM 489 pg_shseclabel l 490 JOIN pg_database dat ON l.classoid = dat.tableoid AND l.objoid = dat.oid 491UNION ALL 492SELECT 493 l.objoid, l.classoid, 0::int4 AS objsubid, 494 'tablespace'::text AS objtype, 495 NULL::oid AS objnamespace, 496 quote_ident(spc.spcname) AS objname, 497 l.provider, l.label 498FROM 499 pg_shseclabel l 500 JOIN pg_tablespace spc ON l.classoid = spc.tableoid AND l.objoid = spc.oid 501UNION ALL 502SELECT 503 l.objoid, l.classoid, 0::int4 AS objsubid, 504 'role'::text AS objtype, 505 NULL::oid AS objnamespace, 506 quote_ident(rol.rolname) AS objname, 507 l.provider, l.label 508FROM 509 pg_shseclabel l 510 JOIN pg_authid rol ON l.classoid = rol.tableoid AND l.objoid = rol.oid; 511 512CREATE VIEW pg_settings AS 513 SELECT * FROM pg_show_all_settings() AS A; 514 515CREATE RULE pg_settings_u AS 516 ON UPDATE TO pg_settings 517 WHERE new.name = old.name DO 518 SELECT set_config(old.name, new.setting, 'f'); 519 520CREATE RULE pg_settings_n AS 521 ON UPDATE TO pg_settings 522 DO INSTEAD NOTHING; 523 524GRANT SELECT, UPDATE ON pg_settings TO PUBLIC; 525 526CREATE VIEW pg_file_settings AS 527 SELECT * FROM pg_show_all_file_settings() AS A; 528 529REVOKE ALL on pg_file_settings FROM PUBLIC; 530REVOKE EXECUTE ON FUNCTION pg_show_all_file_settings() FROM PUBLIC; 531 532CREATE VIEW pg_hba_file_rules AS 533 SELECT * FROM pg_hba_file_rules() AS A; 534 535REVOKE ALL on pg_hba_file_rules FROM PUBLIC; 536REVOKE EXECUTE ON FUNCTION pg_hba_file_rules() FROM PUBLIC; 537 538CREATE VIEW pg_timezone_abbrevs AS 539 SELECT * FROM pg_timezone_abbrevs(); 540 541CREATE VIEW pg_timezone_names AS 542 SELECT * FROM pg_timezone_names(); 543 544CREATE VIEW pg_config AS 545 SELECT * FROM pg_config(); 546 547REVOKE ALL on pg_config FROM PUBLIC; 548REVOKE EXECUTE ON FUNCTION pg_config() FROM PUBLIC; 549 550-- Statistics views 551 552CREATE VIEW pg_stat_all_tables AS 553 SELECT 554 C.oid AS relid, 555 N.nspname AS schemaname, 556 C.relname AS relname, 557 pg_stat_get_numscans(C.oid) AS seq_scan, 558 pg_stat_get_tuples_returned(C.oid) AS seq_tup_read, 559 sum(pg_stat_get_numscans(I.indexrelid))::bigint AS idx_scan, 560 sum(pg_stat_get_tuples_fetched(I.indexrelid))::bigint + 561 pg_stat_get_tuples_fetched(C.oid) AS idx_tup_fetch, 562 pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins, 563 pg_stat_get_tuples_updated(C.oid) AS n_tup_upd, 564 pg_stat_get_tuples_deleted(C.oid) AS n_tup_del, 565 pg_stat_get_tuples_hot_updated(C.oid) AS n_tup_hot_upd, 566 pg_stat_get_live_tuples(C.oid) AS n_live_tup, 567 pg_stat_get_dead_tuples(C.oid) AS n_dead_tup, 568 pg_stat_get_mod_since_analyze(C.oid) AS n_mod_since_analyze, 569 pg_stat_get_last_vacuum_time(C.oid) as last_vacuum, 570 pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum, 571 pg_stat_get_last_analyze_time(C.oid) as last_analyze, 572 pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze, 573 pg_stat_get_vacuum_count(C.oid) AS vacuum_count, 574 pg_stat_get_autovacuum_count(C.oid) AS autovacuum_count, 575 pg_stat_get_analyze_count(C.oid) AS analyze_count, 576 pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count 577 FROM pg_class C LEFT JOIN 578 pg_index I ON C.oid = I.indrelid 579 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 580 WHERE C.relkind IN ('r', 't', 'm') 581 GROUP BY C.oid, N.nspname, C.relname; 582 583CREATE VIEW pg_stat_xact_all_tables AS 584 SELECT 585 C.oid AS relid, 586 N.nspname AS schemaname, 587 C.relname AS relname, 588 pg_stat_get_xact_numscans(C.oid) AS seq_scan, 589 pg_stat_get_xact_tuples_returned(C.oid) AS seq_tup_read, 590 sum(pg_stat_get_xact_numscans(I.indexrelid))::bigint AS idx_scan, 591 sum(pg_stat_get_xact_tuples_fetched(I.indexrelid))::bigint + 592 pg_stat_get_xact_tuples_fetched(C.oid) AS idx_tup_fetch, 593 pg_stat_get_xact_tuples_inserted(C.oid) AS n_tup_ins, 594 pg_stat_get_xact_tuples_updated(C.oid) AS n_tup_upd, 595 pg_stat_get_xact_tuples_deleted(C.oid) AS n_tup_del, 596 pg_stat_get_xact_tuples_hot_updated(C.oid) AS n_tup_hot_upd 597 FROM pg_class C LEFT JOIN 598 pg_index I ON C.oid = I.indrelid 599 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 600 WHERE C.relkind IN ('r', 't', 'm') 601 GROUP BY C.oid, N.nspname, C.relname; 602 603CREATE VIEW pg_stat_sys_tables AS 604 SELECT * FROM pg_stat_all_tables 605 WHERE schemaname IN ('pg_catalog', 'information_schema') OR 606 schemaname ~ '^pg_toast'; 607 608CREATE VIEW pg_stat_xact_sys_tables AS 609 SELECT * FROM pg_stat_xact_all_tables 610 WHERE schemaname IN ('pg_catalog', 'information_schema') OR 611 schemaname ~ '^pg_toast'; 612 613CREATE VIEW pg_stat_user_tables AS 614 SELECT * FROM pg_stat_all_tables 615 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND 616 schemaname !~ '^pg_toast'; 617 618CREATE VIEW pg_stat_xact_user_tables AS 619 SELECT * FROM pg_stat_xact_all_tables 620 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND 621 schemaname !~ '^pg_toast'; 622 623CREATE VIEW pg_statio_all_tables AS 624 SELECT 625 C.oid AS relid, 626 N.nspname AS schemaname, 627 C.relname AS relname, 628 pg_stat_get_blocks_fetched(C.oid) - 629 pg_stat_get_blocks_hit(C.oid) AS heap_blks_read, 630 pg_stat_get_blocks_hit(C.oid) AS heap_blks_hit, 631 sum(pg_stat_get_blocks_fetched(I.indexrelid) - 632 pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_read, 633 sum(pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_hit, 634 pg_stat_get_blocks_fetched(T.oid) - 635 pg_stat_get_blocks_hit(T.oid) AS toast_blks_read, 636 pg_stat_get_blocks_hit(T.oid) AS toast_blks_hit, 637 sum(pg_stat_get_blocks_fetched(X.indexrelid) - 638 pg_stat_get_blocks_hit(X.indexrelid))::bigint AS tidx_blks_read, 639 sum(pg_stat_get_blocks_hit(X.indexrelid))::bigint AS tidx_blks_hit 640 FROM pg_class C LEFT JOIN 641 pg_index I ON C.oid = I.indrelid LEFT JOIN 642 pg_class T ON C.reltoastrelid = T.oid LEFT JOIN 643 pg_index X ON T.oid = X.indrelid 644 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 645 WHERE C.relkind IN ('r', 't', 'm') 646 GROUP BY C.oid, N.nspname, C.relname, T.oid, X.indrelid; 647 648CREATE VIEW pg_statio_sys_tables AS 649 SELECT * FROM pg_statio_all_tables 650 WHERE schemaname IN ('pg_catalog', 'information_schema') OR 651 schemaname ~ '^pg_toast'; 652 653CREATE VIEW pg_statio_user_tables AS 654 SELECT * FROM pg_statio_all_tables 655 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND 656 schemaname !~ '^pg_toast'; 657 658CREATE VIEW pg_stat_all_indexes AS 659 SELECT 660 C.oid AS relid, 661 I.oid AS indexrelid, 662 N.nspname AS schemaname, 663 C.relname AS relname, 664 I.relname AS indexrelname, 665 pg_stat_get_numscans(I.oid) AS idx_scan, 666 pg_stat_get_tuples_returned(I.oid) AS idx_tup_read, 667 pg_stat_get_tuples_fetched(I.oid) AS idx_tup_fetch 668 FROM pg_class C JOIN 669 pg_index X ON C.oid = X.indrelid JOIN 670 pg_class I ON I.oid = X.indexrelid 671 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 672 WHERE C.relkind IN ('r', 't', 'm'); 673 674CREATE VIEW pg_stat_sys_indexes AS 675 SELECT * FROM pg_stat_all_indexes 676 WHERE schemaname IN ('pg_catalog', 'information_schema') OR 677 schemaname ~ '^pg_toast'; 678 679CREATE VIEW pg_stat_user_indexes AS 680 SELECT * FROM pg_stat_all_indexes 681 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND 682 schemaname !~ '^pg_toast'; 683 684CREATE VIEW pg_statio_all_indexes AS 685 SELECT 686 C.oid AS relid, 687 I.oid AS indexrelid, 688 N.nspname AS schemaname, 689 C.relname AS relname, 690 I.relname AS indexrelname, 691 pg_stat_get_blocks_fetched(I.oid) - 692 pg_stat_get_blocks_hit(I.oid) AS idx_blks_read, 693 pg_stat_get_blocks_hit(I.oid) AS idx_blks_hit 694 FROM pg_class C JOIN 695 pg_index X ON C.oid = X.indrelid JOIN 696 pg_class I ON I.oid = X.indexrelid 697 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 698 WHERE C.relkind IN ('r', 't', 'm'); 699 700CREATE VIEW pg_statio_sys_indexes AS 701 SELECT * FROM pg_statio_all_indexes 702 WHERE schemaname IN ('pg_catalog', 'information_schema') OR 703 schemaname ~ '^pg_toast'; 704 705CREATE VIEW pg_statio_user_indexes AS 706 SELECT * FROM pg_statio_all_indexes 707 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND 708 schemaname !~ '^pg_toast'; 709 710CREATE VIEW pg_statio_all_sequences AS 711 SELECT 712 C.oid AS relid, 713 N.nspname AS schemaname, 714 C.relname AS relname, 715 pg_stat_get_blocks_fetched(C.oid) - 716 pg_stat_get_blocks_hit(C.oid) AS blks_read, 717 pg_stat_get_blocks_hit(C.oid) AS blks_hit 718 FROM pg_class C 719 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 720 WHERE C.relkind = 'S'; 721 722CREATE VIEW pg_statio_sys_sequences AS 723 SELECT * FROM pg_statio_all_sequences 724 WHERE schemaname IN ('pg_catalog', 'information_schema') OR 725 schemaname ~ '^pg_toast'; 726 727CREATE VIEW pg_statio_user_sequences AS 728 SELECT * FROM pg_statio_all_sequences 729 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND 730 schemaname !~ '^pg_toast'; 731 732CREATE VIEW pg_stat_activity AS 733 SELECT 734 S.datid AS datid, 735 D.datname AS datname, 736 S.pid, 737 S.usesysid, 738 U.rolname AS usename, 739 S.application_name, 740 S.client_addr, 741 S.client_hostname, 742 S.client_port, 743 S.backend_start, 744 S.xact_start, 745 S.query_start, 746 S.state_change, 747 S.wait_event_type, 748 S.wait_event, 749 S.state, 750 S.backend_xid, 751 s.backend_xmin, 752 S.query, 753 S.backend_type 754 FROM pg_stat_get_activity(NULL) AS S 755 LEFT JOIN pg_database AS D ON (S.datid = D.oid) 756 LEFT JOIN pg_authid AS U ON (S.usesysid = U.oid); 757 758CREATE VIEW pg_stat_replication AS 759 SELECT 760 S.pid, 761 S.usesysid, 762 U.rolname AS usename, 763 S.application_name, 764 S.client_addr, 765 S.client_hostname, 766 S.client_port, 767 S.backend_start, 768 S.backend_xmin, 769 W.state, 770 W.sent_lsn, 771 W.write_lsn, 772 W.flush_lsn, 773 W.replay_lsn, 774 W.write_lag, 775 W.flush_lag, 776 W.replay_lag, 777 W.sync_priority, 778 W.sync_state, 779 W.reply_time 780 FROM pg_stat_get_activity(NULL) AS S 781 JOIN pg_stat_get_wal_senders() AS W ON (S.pid = W.pid) 782 LEFT JOIN pg_authid AS U ON (S.usesysid = U.oid); 783 784CREATE VIEW pg_stat_wal_receiver AS 785 SELECT 786 s.pid, 787 s.status, 788 s.receive_start_lsn, 789 s.receive_start_tli, 790 s.received_lsn, 791 s.received_tli, 792 s.last_msg_send_time, 793 s.last_msg_receipt_time, 794 s.latest_end_lsn, 795 s.latest_end_time, 796 s.slot_name, 797 s.sender_host, 798 s.sender_port, 799 s.conninfo 800 FROM pg_stat_get_wal_receiver() s 801 WHERE s.pid IS NOT NULL; 802 803CREATE VIEW pg_stat_subscription AS 804 SELECT 805 su.oid AS subid, 806 su.subname, 807 st.pid, 808 st.relid, 809 st.received_lsn, 810 st.last_msg_send_time, 811 st.last_msg_receipt_time, 812 st.latest_end_lsn, 813 st.latest_end_time 814 FROM pg_subscription su 815 LEFT JOIN pg_stat_get_subscription(NULL) st 816 ON (st.subid = su.oid); 817 818CREATE VIEW pg_stat_ssl AS 819 SELECT 820 S.pid, 821 S.ssl, 822 S.sslversion AS version, 823 S.sslcipher AS cipher, 824 S.sslbits AS bits, 825 S.sslcompression AS compression, 826 S.ssl_client_dn AS client_dn, 827 S.ssl_client_serial AS client_serial, 828 S.ssl_issuer_dn AS issuer_dn 829 FROM pg_stat_get_activity(NULL) AS S; 830 831CREATE VIEW pg_stat_gssapi AS 832 SELECT 833 S.pid, 834 S.gss_auth AS gss_authenticated, 835 S.gss_princ AS principal, 836 S.gss_enc AS encrypted 837 FROM pg_stat_get_activity(NULL) AS S; 838 839CREATE VIEW pg_replication_slots AS 840 SELECT 841 L.slot_name, 842 L.plugin, 843 L.slot_type, 844 L.datoid, 845 D.datname AS database, 846 L.temporary, 847 L.active, 848 L.active_pid, 849 L.xmin, 850 L.catalog_xmin, 851 L.restart_lsn, 852 L.confirmed_flush_lsn 853 FROM pg_get_replication_slots() AS L 854 LEFT JOIN pg_database D ON (L.datoid = D.oid); 855 856CREATE VIEW pg_stat_database AS 857 SELECT 858 D.oid AS datid, 859 D.datname AS datname, 860 CASE 861 WHEN (D.oid = (0)::oid) THEN 0 862 ELSE pg_stat_get_db_numbackends(D.oid) 863 END AS numbackends, 864 pg_stat_get_db_xact_commit(D.oid) AS xact_commit, 865 pg_stat_get_db_xact_rollback(D.oid) AS xact_rollback, 866 pg_stat_get_db_blocks_fetched(D.oid) - 867 pg_stat_get_db_blocks_hit(D.oid) AS blks_read, 868 pg_stat_get_db_blocks_hit(D.oid) AS blks_hit, 869 pg_stat_get_db_tuples_returned(D.oid) AS tup_returned, 870 pg_stat_get_db_tuples_fetched(D.oid) AS tup_fetched, 871 pg_stat_get_db_tuples_inserted(D.oid) AS tup_inserted, 872 pg_stat_get_db_tuples_updated(D.oid) AS tup_updated, 873 pg_stat_get_db_tuples_deleted(D.oid) AS tup_deleted, 874 pg_stat_get_db_conflict_all(D.oid) AS conflicts, 875 pg_stat_get_db_temp_files(D.oid) AS temp_files, 876 pg_stat_get_db_temp_bytes(D.oid) AS temp_bytes, 877 pg_stat_get_db_deadlocks(D.oid) AS deadlocks, 878 pg_stat_get_db_checksum_failures(D.oid) AS checksum_failures, 879 pg_stat_get_db_checksum_last_failure(D.oid) AS checksum_last_failure, 880 pg_stat_get_db_blk_read_time(D.oid) AS blk_read_time, 881 pg_stat_get_db_blk_write_time(D.oid) AS blk_write_time, 882 pg_stat_get_db_stat_reset_time(D.oid) AS stats_reset 883 FROM ( 884 SELECT 0 AS oid, NULL::name AS datname 885 UNION ALL 886 SELECT oid, datname FROM pg_database 887 ) D; 888 889CREATE VIEW pg_stat_database_conflicts AS 890 SELECT 891 D.oid AS datid, 892 D.datname AS datname, 893 pg_stat_get_db_conflict_tablespace(D.oid) AS confl_tablespace, 894 pg_stat_get_db_conflict_lock(D.oid) AS confl_lock, 895 pg_stat_get_db_conflict_snapshot(D.oid) AS confl_snapshot, 896 pg_stat_get_db_conflict_bufferpin(D.oid) AS confl_bufferpin, 897 pg_stat_get_db_conflict_startup_deadlock(D.oid) AS confl_deadlock 898 FROM pg_database D; 899 900CREATE VIEW pg_stat_user_functions AS 901 SELECT 902 P.oid AS funcid, 903 N.nspname AS schemaname, 904 P.proname AS funcname, 905 pg_stat_get_function_calls(P.oid) AS calls, 906 pg_stat_get_function_total_time(P.oid) AS total_time, 907 pg_stat_get_function_self_time(P.oid) AS self_time 908 FROM pg_proc P LEFT JOIN pg_namespace N ON (N.oid = P.pronamespace) 909 WHERE P.prolang != 12 -- fast check to eliminate built-in functions 910 AND pg_stat_get_function_calls(P.oid) IS NOT NULL; 911 912CREATE VIEW pg_stat_xact_user_functions AS 913 SELECT 914 P.oid AS funcid, 915 N.nspname AS schemaname, 916 P.proname AS funcname, 917 pg_stat_get_xact_function_calls(P.oid) AS calls, 918 pg_stat_get_xact_function_total_time(P.oid) AS total_time, 919 pg_stat_get_xact_function_self_time(P.oid) AS self_time 920 FROM pg_proc P LEFT JOIN pg_namespace N ON (N.oid = P.pronamespace) 921 WHERE P.prolang != 12 -- fast check to eliminate built-in functions 922 AND pg_stat_get_xact_function_calls(P.oid) IS NOT NULL; 923 924CREATE VIEW pg_stat_archiver AS 925 SELECT 926 s.archived_count, 927 s.last_archived_wal, 928 s.last_archived_time, 929 s.failed_count, 930 s.last_failed_wal, 931 s.last_failed_time, 932 s.stats_reset 933 FROM pg_stat_get_archiver() s; 934 935CREATE VIEW pg_stat_bgwriter AS 936 SELECT 937 pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, 938 pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, 939 pg_stat_get_checkpoint_write_time() AS checkpoint_write_time, 940 pg_stat_get_checkpoint_sync_time() AS checkpoint_sync_time, 941 pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint, 942 pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, 943 pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, 944 pg_stat_get_buf_written_backend() AS buffers_backend, 945 pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync, 946 pg_stat_get_buf_alloc() AS buffers_alloc, 947 pg_stat_get_bgwriter_stat_reset_time() AS stats_reset; 948 949CREATE VIEW pg_stat_progress_vacuum AS 950 SELECT 951 S.pid AS pid, S.datid AS datid, D.datname AS datname, 952 S.relid AS relid, 953 CASE S.param1 WHEN 0 THEN 'initializing' 954 WHEN 1 THEN 'scanning heap' 955 WHEN 2 THEN 'vacuuming indexes' 956 WHEN 3 THEN 'vacuuming heap' 957 WHEN 4 THEN 'cleaning up indexes' 958 WHEN 5 THEN 'truncating heap' 959 WHEN 6 THEN 'performing final cleanup' 960 END AS phase, 961 S.param2 AS heap_blks_total, S.param3 AS heap_blks_scanned, 962 S.param4 AS heap_blks_vacuumed, S.param5 AS index_vacuum_count, 963 S.param6 AS max_dead_tuples, S.param7 AS num_dead_tuples 964 FROM pg_stat_get_progress_info('VACUUM') AS S 965 LEFT JOIN pg_database D ON S.datid = D.oid; 966 967CREATE VIEW pg_stat_progress_cluster AS 968 SELECT 969 S.pid AS pid, 970 S.datid AS datid, 971 D.datname AS datname, 972 S.relid AS relid, 973 CASE S.param1 WHEN 1 THEN 'CLUSTER' 974 WHEN 2 THEN 'VACUUM FULL' 975 END AS command, 976 CASE S.param2 WHEN 0 THEN 'initializing' 977 WHEN 1 THEN 'seq scanning heap' 978 WHEN 2 THEN 'index scanning heap' 979 WHEN 3 THEN 'sorting tuples' 980 WHEN 4 THEN 'writing new heap' 981 WHEN 5 THEN 'swapping relation files' 982 WHEN 6 THEN 'rebuilding index' 983 WHEN 7 THEN 'performing final cleanup' 984 END AS phase, 985 CAST(S.param3 AS oid) AS cluster_index_relid, 986 S.param4 AS heap_tuples_scanned, 987 S.param5 AS heap_tuples_written, 988 S.param6 AS heap_blks_total, 989 S.param7 AS heap_blks_scanned, 990 S.param8 AS index_rebuild_count 991 FROM pg_stat_get_progress_info('CLUSTER') AS S 992 LEFT JOIN pg_database D ON S.datid = D.oid; 993 994CREATE VIEW pg_stat_progress_create_index AS 995 SELECT 996 S.pid AS pid, S.datid AS datid, D.datname AS datname, 997 S.relid AS relid, 998 CAST(S.param7 AS oid) AS index_relid, 999 CASE S.param1 WHEN 1 THEN 'CREATE INDEX' 1000 WHEN 2 THEN 'CREATE INDEX CONCURRENTLY' 1001 WHEN 3 THEN 'REINDEX' 1002 WHEN 4 THEN 'REINDEX CONCURRENTLY' 1003 END AS command, 1004 CASE S.param10 WHEN 0 THEN 'initializing' 1005 WHEN 1 THEN 'waiting for writers before build' 1006 WHEN 2 THEN 'building index' || 1007 COALESCE((': ' || pg_indexam_progress_phasename(S.param9::oid, S.param11)), 1008 '') 1009 WHEN 3 THEN 'waiting for writers before validation' 1010 WHEN 4 THEN 'index validation: scanning index' 1011 WHEN 5 THEN 'index validation: sorting tuples' 1012 WHEN 6 THEN 'index validation: scanning table' 1013 WHEN 7 THEN 'waiting for old snapshots' 1014 WHEN 8 THEN 'waiting for readers before marking dead' 1015 WHEN 9 THEN 'waiting for readers before dropping' 1016 END as phase, 1017 S.param4 AS lockers_total, 1018 S.param5 AS lockers_done, 1019 S.param6 AS current_locker_pid, 1020 S.param16 AS blocks_total, 1021 S.param17 AS blocks_done, 1022 S.param12 AS tuples_total, 1023 S.param13 AS tuples_done, 1024 S.param14 AS partitions_total, 1025 S.param15 AS partitions_done 1026 FROM pg_stat_get_progress_info('CREATE INDEX') AS S 1027 LEFT JOIN pg_database D ON S.datid = D.oid; 1028 1029CREATE VIEW pg_user_mappings AS 1030 SELECT 1031 U.oid AS umid, 1032 S.oid AS srvid, 1033 S.srvname AS srvname, 1034 U.umuser AS umuser, 1035 CASE WHEN U.umuser = 0 THEN 1036 'public' 1037 ELSE 1038 A.rolname 1039 END AS usename, 1040 CASE WHEN (U.umuser <> 0 AND A.rolname = current_user 1041 AND (pg_has_role(S.srvowner, 'USAGE') 1042 OR has_server_privilege(S.oid, 'USAGE'))) 1043 OR (U.umuser = 0 AND pg_has_role(S.srvowner, 'USAGE')) 1044 OR (SELECT rolsuper FROM pg_authid WHERE rolname = current_user) 1045 THEN U.umoptions 1046 ELSE NULL END AS umoptions 1047 FROM pg_user_mapping U 1048 JOIN pg_foreign_server S ON (U.umserver = S.oid) 1049 LEFT JOIN pg_authid A ON (A.oid = U.umuser); 1050 1051REVOKE ALL on pg_user_mapping FROM public; 1052 1053CREATE VIEW pg_replication_origin_status AS 1054 SELECT * 1055 FROM pg_show_replication_origin_status(); 1056 1057REVOKE ALL ON pg_replication_origin_status FROM public; 1058 1059-- All columns of pg_subscription except subconninfo are readable. 1060REVOKE ALL ON pg_subscription FROM public; 1061GRANT SELECT (subdbid, subname, subowner, subenabled, subslotname, subpublications) 1062 ON pg_subscription TO public; 1063 1064 1065-- 1066-- We have a few function definitions in here, too. 1067-- At some point there might be enough to justify breaking them out into 1068-- a separate "system_functions.sql" file. 1069-- 1070 1071-- Tsearch debug function. Defined here because it'd be pretty unwieldy 1072-- to put it into pg_proc.h 1073 1074CREATE FUNCTION ts_debug(IN config regconfig, IN document text, 1075 OUT alias text, 1076 OUT description text, 1077 OUT token text, 1078 OUT dictionaries regdictionary[], 1079 OUT dictionary regdictionary, 1080 OUT lexemes text[]) 1081RETURNS SETOF record AS 1082$$ 1083SELECT 1084 tt.alias AS alias, 1085 tt.description AS description, 1086 parse.token AS token, 1087 ARRAY ( SELECT m.mapdict::pg_catalog.regdictionary 1088 FROM pg_catalog.pg_ts_config_map AS m 1089 WHERE m.mapcfg = $1 AND m.maptokentype = parse.tokid 1090 ORDER BY m.mapseqno ) 1091 AS dictionaries, 1092 ( SELECT mapdict::pg_catalog.regdictionary 1093 FROM pg_catalog.pg_ts_config_map AS m 1094 WHERE m.mapcfg = $1 AND m.maptokentype = parse.tokid 1095 ORDER BY pg_catalog.ts_lexize(mapdict, parse.token) IS NULL, m.mapseqno 1096 LIMIT 1 1097 ) AS dictionary, 1098 ( SELECT pg_catalog.ts_lexize(mapdict, parse.token) 1099 FROM pg_catalog.pg_ts_config_map AS m 1100 WHERE m.mapcfg = $1 AND m.maptokentype = parse.tokid 1101 ORDER BY pg_catalog.ts_lexize(mapdict, parse.token) IS NULL, m.mapseqno 1102 LIMIT 1 1103 ) AS lexemes 1104FROM pg_catalog.ts_parse( 1105 (SELECT cfgparser FROM pg_catalog.pg_ts_config WHERE oid = $1 ), $2 1106 ) AS parse, 1107 pg_catalog.ts_token_type( 1108 (SELECT cfgparser FROM pg_catalog.pg_ts_config WHERE oid = $1 ) 1109 ) AS tt 1110WHERE tt.tokid = parse.tokid 1111$$ 1112LANGUAGE SQL STRICT STABLE PARALLEL SAFE; 1113 1114COMMENT ON FUNCTION ts_debug(regconfig,text) IS 1115 'debug function for text search configuration'; 1116 1117CREATE FUNCTION ts_debug(IN document text, 1118 OUT alias text, 1119 OUT description text, 1120 OUT token text, 1121 OUT dictionaries regdictionary[], 1122 OUT dictionary regdictionary, 1123 OUT lexemes text[]) 1124RETURNS SETOF record AS 1125$$ 1126 SELECT * FROM pg_catalog.ts_debug( pg_catalog.get_current_ts_config(), $1); 1127$$ 1128LANGUAGE SQL STRICT STABLE PARALLEL SAFE; 1129 1130COMMENT ON FUNCTION ts_debug(text) IS 1131 'debug function for current text search configuration'; 1132 1133-- 1134-- Redeclare built-in functions that need default values attached to their 1135-- arguments. It's impractical to set those up directly in pg_proc.h because 1136-- of the complexity and platform-dependency of the expression tree 1137-- representation. (Note that internal functions still have to have entries 1138-- in pg_proc.h; we are merely causing their proargnames and proargdefaults 1139-- to get filled in.) 1140-- 1141 1142CREATE OR REPLACE FUNCTION 1143 pg_start_backup(label text, fast boolean DEFAULT false, exclusive boolean DEFAULT true) 1144 RETURNS pg_lsn STRICT VOLATILE LANGUAGE internal AS 'pg_start_backup' 1145 PARALLEL RESTRICTED; 1146 1147CREATE OR REPLACE FUNCTION pg_stop_backup ( 1148 exclusive boolean, wait_for_archive boolean DEFAULT true, 1149 OUT lsn pg_lsn, OUT labelfile text, OUT spcmapfile text) 1150 RETURNS SETOF record STRICT VOLATILE LANGUAGE internal as 'pg_stop_backup_v2' 1151 PARALLEL RESTRICTED; 1152 1153CREATE OR REPLACE FUNCTION 1154 pg_promote(wait boolean DEFAULT true, wait_seconds integer DEFAULT 60) 1155 RETURNS boolean STRICT VOLATILE LANGUAGE INTERNAL AS 'pg_promote' 1156 PARALLEL SAFE; 1157 1158-- legacy definition for compatibility with 9.3 1159CREATE OR REPLACE FUNCTION 1160 json_populate_record(base anyelement, from_json json, use_json_as_text boolean DEFAULT false) 1161 RETURNS anyelement LANGUAGE internal STABLE AS 'json_populate_record' PARALLEL SAFE; 1162 1163-- legacy definition for compatibility with 9.3 1164CREATE OR REPLACE FUNCTION 1165 json_populate_recordset(base anyelement, from_json json, use_json_as_text boolean DEFAULT false) 1166 RETURNS SETOF anyelement LANGUAGE internal STABLE ROWS 100 AS 'json_populate_recordset' PARALLEL SAFE; 1167 1168CREATE OR REPLACE FUNCTION pg_logical_slot_get_changes( 1169 IN slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}', 1170 OUT lsn pg_lsn, OUT xid xid, OUT data text) 1171RETURNS SETOF RECORD 1172LANGUAGE INTERNAL 1173VOLATILE ROWS 1000 COST 1000 1174AS 'pg_logical_slot_get_changes'; 1175 1176CREATE OR REPLACE FUNCTION pg_logical_slot_peek_changes( 1177 IN slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}', 1178 OUT lsn pg_lsn, OUT xid xid, OUT data text) 1179RETURNS SETOF RECORD 1180LANGUAGE INTERNAL 1181VOLATILE ROWS 1000 COST 1000 1182AS 'pg_logical_slot_peek_changes'; 1183 1184CREATE OR REPLACE FUNCTION pg_logical_slot_get_binary_changes( 1185 IN slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}', 1186 OUT lsn pg_lsn, OUT xid xid, OUT data bytea) 1187RETURNS SETOF RECORD 1188LANGUAGE INTERNAL 1189VOLATILE ROWS 1000 COST 1000 1190AS 'pg_logical_slot_get_binary_changes'; 1191 1192CREATE OR REPLACE FUNCTION pg_logical_slot_peek_binary_changes( 1193 IN slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}', 1194 OUT lsn pg_lsn, OUT xid xid, OUT data bytea) 1195RETURNS SETOF RECORD 1196LANGUAGE INTERNAL 1197VOLATILE ROWS 1000 COST 1000 1198AS 'pg_logical_slot_peek_binary_changes'; 1199 1200CREATE OR REPLACE FUNCTION pg_create_physical_replication_slot( 1201 IN slot_name name, IN immediately_reserve boolean DEFAULT false, 1202 IN temporary boolean DEFAULT false, 1203 OUT slot_name name, OUT lsn pg_lsn) 1204RETURNS RECORD 1205LANGUAGE INTERNAL 1206STRICT VOLATILE 1207AS 'pg_create_physical_replication_slot'; 1208 1209CREATE OR REPLACE FUNCTION pg_create_logical_replication_slot( 1210 IN slot_name name, IN plugin name, 1211 IN temporary boolean DEFAULT false, 1212 OUT slot_name name, OUT lsn pg_lsn) 1213RETURNS RECORD 1214LANGUAGE INTERNAL 1215STRICT VOLATILE 1216AS 'pg_create_logical_replication_slot'; 1217 1218CREATE OR REPLACE FUNCTION 1219 make_interval(years int4 DEFAULT 0, months int4 DEFAULT 0, weeks int4 DEFAULT 0, 1220 days int4 DEFAULT 0, hours int4 DEFAULT 0, mins int4 DEFAULT 0, 1221 secs double precision DEFAULT 0.0) 1222RETURNS interval 1223LANGUAGE INTERNAL 1224STRICT IMMUTABLE PARALLEL SAFE 1225AS 'make_interval'; 1226 1227CREATE OR REPLACE FUNCTION 1228 jsonb_set(jsonb_in jsonb, path text[] , replacement jsonb, 1229 create_if_missing boolean DEFAULT true) 1230RETURNS jsonb 1231LANGUAGE INTERNAL 1232STRICT IMMUTABLE PARALLEL SAFE 1233AS 'jsonb_set'; 1234 1235CREATE OR REPLACE FUNCTION 1236 parse_ident(str text, strict boolean DEFAULT true) 1237RETURNS text[] 1238LANGUAGE INTERNAL 1239STRICT IMMUTABLE PARALLEL SAFE 1240AS 'parse_ident'; 1241 1242CREATE OR REPLACE FUNCTION 1243 jsonb_insert(jsonb_in jsonb, path text[] , replacement jsonb, 1244 insert_after boolean DEFAULT false) 1245RETURNS jsonb 1246LANGUAGE INTERNAL 1247STRICT IMMUTABLE PARALLEL SAFE 1248AS 'jsonb_insert'; 1249 1250CREATE OR REPLACE FUNCTION 1251 jsonb_path_exists(target jsonb, path jsonpath, vars jsonb DEFAULT '{}', 1252 silent boolean DEFAULT false) 1253RETURNS boolean 1254LANGUAGE INTERNAL 1255STRICT IMMUTABLE PARALLEL SAFE 1256AS 'jsonb_path_exists'; 1257 1258CREATE OR REPLACE FUNCTION 1259 jsonb_path_match(target jsonb, path jsonpath, vars jsonb DEFAULT '{}', 1260 silent boolean DEFAULT false) 1261RETURNS boolean 1262LANGUAGE INTERNAL 1263STRICT IMMUTABLE PARALLEL SAFE 1264AS 'jsonb_path_match'; 1265 1266CREATE OR REPLACE FUNCTION 1267 jsonb_path_query(target jsonb, path jsonpath, vars jsonb DEFAULT '{}', 1268 silent boolean DEFAULT false) 1269RETURNS SETOF jsonb 1270LANGUAGE INTERNAL 1271STRICT IMMUTABLE PARALLEL SAFE 1272AS 'jsonb_path_query'; 1273 1274CREATE OR REPLACE FUNCTION 1275 jsonb_path_query_array(target jsonb, path jsonpath, vars jsonb DEFAULT '{}', 1276 silent boolean DEFAULT false) 1277RETURNS jsonb 1278LANGUAGE INTERNAL 1279STRICT IMMUTABLE PARALLEL SAFE 1280AS 'jsonb_path_query_array'; 1281 1282CREATE OR REPLACE FUNCTION 1283 jsonb_path_query_first(target jsonb, path jsonpath, vars jsonb DEFAULT '{}', 1284 silent boolean DEFAULT false) 1285RETURNS jsonb 1286LANGUAGE INTERNAL 1287STRICT IMMUTABLE PARALLEL SAFE 1288AS 'jsonb_path_query_first'; 1289 1290-- 1291-- The default permissions for functions mean that anyone can execute them. 1292-- A number of functions shouldn't be executable by just anyone, but rather 1293-- than use explicit 'superuser()' checks in those functions, we use the GRANT 1294-- system to REVOKE access to those functions at initdb time. Administrators 1295-- can later change who can access these functions, or leave them as only 1296-- available to superuser / cluster owner, if they choose. 1297-- 1298REVOKE EXECUTE ON FUNCTION pg_start_backup(text, boolean, boolean) FROM public; 1299REVOKE EXECUTE ON FUNCTION pg_stop_backup() FROM public; 1300REVOKE EXECUTE ON FUNCTION pg_stop_backup(boolean, boolean) FROM public; 1301REVOKE EXECUTE ON FUNCTION pg_create_restore_point(text) FROM public; 1302REVOKE EXECUTE ON FUNCTION pg_switch_wal() FROM public; 1303REVOKE EXECUTE ON FUNCTION pg_wal_replay_pause() FROM public; 1304REVOKE EXECUTE ON FUNCTION pg_wal_replay_resume() FROM public; 1305REVOKE EXECUTE ON FUNCTION pg_rotate_logfile() FROM public; 1306REVOKE EXECUTE ON FUNCTION pg_reload_conf() FROM public; 1307REVOKE EXECUTE ON FUNCTION pg_current_logfile() FROM public; 1308REVOKE EXECUTE ON FUNCTION pg_current_logfile(text) FROM public; 1309REVOKE EXECUTE ON FUNCTION pg_promote(boolean, integer) FROM public; 1310 1311REVOKE EXECUTE ON FUNCTION pg_stat_reset() FROM public; 1312REVOKE EXECUTE ON FUNCTION pg_stat_reset_shared(text) FROM public; 1313REVOKE EXECUTE ON FUNCTION pg_stat_reset_single_table_counters(oid) FROM public; 1314REVOKE EXECUTE ON FUNCTION pg_stat_reset_single_function_counters(oid) FROM public; 1315 1316REVOKE EXECUTE ON FUNCTION lo_import(text) FROM public; 1317REVOKE EXECUTE ON FUNCTION lo_import(text, oid) FROM public; 1318REVOKE EXECUTE ON FUNCTION lo_export(oid, text) FROM public; 1319 1320REVOKE EXECUTE ON FUNCTION pg_ls_logdir() FROM public; 1321REVOKE EXECUTE ON FUNCTION pg_ls_waldir() FROM public; 1322REVOKE EXECUTE ON FUNCTION pg_ls_archive_statusdir() FROM public; 1323REVOKE EXECUTE ON FUNCTION pg_ls_tmpdir() FROM public; 1324REVOKE EXECUTE ON FUNCTION pg_ls_tmpdir(oid) FROM public; 1325 1326REVOKE EXECUTE ON FUNCTION pg_read_file(text) FROM public; 1327REVOKE EXECUTE ON FUNCTION pg_read_file(text,bigint,bigint) FROM public; 1328REVOKE EXECUTE ON FUNCTION pg_read_file(text,bigint,bigint,boolean) FROM public; 1329 1330REVOKE EXECUTE ON FUNCTION pg_read_binary_file(text) FROM public; 1331REVOKE EXECUTE ON FUNCTION pg_read_binary_file(text,bigint,bigint) FROM public; 1332REVOKE EXECUTE ON FUNCTION pg_read_binary_file(text,bigint,bigint,boolean) FROM public; 1333 1334REVOKE EXECUTE ON FUNCTION pg_stat_file(text) FROM public; 1335REVOKE EXECUTE ON FUNCTION pg_stat_file(text,boolean) FROM public; 1336 1337REVOKE EXECUTE ON FUNCTION pg_ls_dir(text) FROM public; 1338REVOKE EXECUTE ON FUNCTION pg_ls_dir(text,boolean,boolean) FROM public; 1339 1340-- 1341-- We also set up some things as accessible to standard roles. 1342-- 1343GRANT EXECUTE ON FUNCTION pg_ls_logdir() TO pg_monitor; 1344GRANT EXECUTE ON FUNCTION pg_ls_waldir() TO pg_monitor; 1345GRANT EXECUTE ON FUNCTION pg_ls_archive_statusdir() TO pg_monitor; 1346GRANT EXECUTE ON FUNCTION pg_ls_tmpdir() TO pg_monitor; 1347GRANT EXECUTE ON FUNCTION pg_ls_tmpdir(oid) TO pg_monitor; 1348 1349GRANT pg_read_all_settings TO pg_monitor; 1350GRANT pg_read_all_stats TO pg_monitor; 1351GRANT pg_stat_scan_tables TO pg_monitor; 1352