codekingpro/portable-devtools
116k
1/*2 * PostgreSQL System Views3 *4 * Copyright (c) 1996-2023, PostgreSQL Global Development Group5 *6 * src/backend/catalog/system_views.sql7 *8 * Note: this file is read in single-user -j mode, which means that the9 * command terminator is semicolon-newline-newline; whenever the backend10 * sees that, it stops and executes what it's got. If you write a lot of11 * statements without empty lines between, they'll all get quoted to you12 * in any error message about one of them, so don't do that. Also, you13 * cannot write a semicolon immediately followed by an empty line in a14 * string literal (including a function body!) or a multiline comment.15 */16 17CREATE VIEW pg_roles AS18 SELECT19 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.oid32 FROM pg_authid LEFT JOIN pg_db_role_setting s33 ON (pg_authid.oid = setrole AND setdatabase = 0);34 35CREATE VIEW pg_shadow AS36 SELECT37 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 useconfig46 FROM pg_authid LEFT JOIN pg_db_role_setting s47 ON (pg_authid.oid = setrole AND setdatabase = 0)48 WHERE rolcanlogin;49 50REVOKE ALL ON pg_shadow FROM public;51 52CREATE VIEW pg_group AS53 SELECT54 rolname AS groname,55 oid AS grosysid,56 ARRAY(SELECT member FROM pg_auth_members WHERE roleid = pg_authid.oid) AS grolist57 FROM pg_authid58 WHERE NOT rolcanlogin;59 60CREATE VIEW pg_user AS61 SELECT62 usename,63 usesysid,64 usecreatedb,65 usesuper,66 userepl,67 usebypassrls,68 '********'::text as passwd,69 valuntil,70 useconfig71 FROM pg_shadow;72 73CREATE VIEW pg_policies AS74 SELECT75 N.nspname AS schemaname,76 C.relname AS tablename,77 pol.polname AS policyname,78 CASE79 WHEN pol.polpermissive THEN80 'PERMISSIVE'81 ELSE82 'RESTRICTIVE'83 END AS permissive,84 CASE85 WHEN pol.polroles = '{0}' THEN86 string_to_array('public', '')87 ELSE88 ARRAY89 (90 SELECT rolname91 FROM pg_catalog.pg_authid92 WHERE oid = ANY (pol.polroles) ORDER BY 193 )94 END AS roles,95 CASE pol.polcmd96 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_check104 FROM pg_catalog.pg_policy pol105 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 AS109 SELECT110 N.nspname AS schemaname,111 C.relname AS tablename,112 R.rulename AS rulename,113 pg_get_ruledef(R.oid) AS definition114 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 AS119 SELECT120 N.nspname AS schemaname,121 C.relname AS viewname,122 pg_get_userbyid(C.relowner) AS viewowner,123 pg_get_viewdef(C.oid) AS definition124 FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)125 WHERE C.relkind = 'v';126 127CREATE VIEW pg_tables AS128 SELECT129 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 rowsecurity137 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 AS142 SELECT143 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 definition150 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 AS155 SELECT156 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 indexdef161 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 AS168 SELECT169 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 CASE180 WHEN has_sequence_privilege(C.oid, 'SELECT,USAGE'::text)181 THEN pg_sequence_last_value(C.oid)182 ELSE NULL183 END AS last_value184 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) AS190 SELECT191 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 CASE199 WHEN stakind1 = 1 THEN stavalues1200 WHEN stakind2 = 1 THEN stavalues2201 WHEN stakind3 = 1 THEN stavalues3202 WHEN stakind4 = 1 THEN stavalues4203 WHEN stakind5 = 1 THEN stavalues5204 END AS most_common_vals,205 CASE206 WHEN stakind1 = 1 THEN stanumbers1207 WHEN stakind2 = 1 THEN stanumbers2208 WHEN stakind3 = 1 THEN stanumbers3209 WHEN stakind4 = 1 THEN stanumbers4210 WHEN stakind5 = 1 THEN stanumbers5211 END AS most_common_freqs,212 CASE213 WHEN stakind1 = 2 THEN stavalues1214 WHEN stakind2 = 2 THEN stavalues2215 WHEN stakind3 = 2 THEN stavalues3216 WHEN stakind4 = 2 THEN stavalues4217 WHEN stakind5 = 2 THEN stavalues5218 END AS histogram_bounds,219 CASE220 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 CASE227 WHEN stakind1 = 4 THEN stavalues1228 WHEN stakind2 = 4 THEN stavalues2229 WHEN stakind3 = 4 THEN stavalues3230 WHEN stakind4 = 4 THEN stavalues4231 WHEN stakind5 = 4 THEN stavalues5232 END AS most_common_elems,233 CASE234 WHEN stakind1 = 4 THEN stanumbers1235 WHEN stakind2 = 4 THEN stanumbers2236 WHEN stakind3 = 4 THEN stanumbers3237 WHEN stakind4 = 4 THEN stanumbers4238 WHEN stakind5 = 4 THEN stanumbers5239 END AS most_common_elem_freqs,240 CASE241 WHEN stakind1 = 5 THEN stanumbers1242 WHEN stakind2 = 5 THEN stanumbers2243 WHEN stakind3 = 5 THEN stanumbers3244 WHEN stakind4 = 5 THEN stanumbers4245 WHEN stakind5 = 5 THEN stanumbers5246 END AS elem_count_histogram247 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 attisdropped251 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) AS257 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) k264 JOIN pg_attribute a265 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.stxdinherit AS inherited,270 sd.stxdndistinct AS n_distinct,271 sd.stxddependencies AS dependencies,272 m.most_common_vals,273 m.most_common_val_nulls,274 m.most_common_freqs,275 m.most_common_base_freqs276 FROM pg_statistic_ext s JOIN pg_class c ON (c.oid = s.stxrelid)277 JOIN pg_statistic_ext_data sd ON (s.oid = sd.stxoid)278 LEFT JOIN pg_namespace cn ON (cn.oid = c.relnamespace)279 LEFT JOIN pg_namespace sn ON (sn.oid = s.stxnamespace)280 LEFT JOIN LATERAL281 ( SELECT array_agg(values) AS most_common_vals,282 array_agg(nulls) AS most_common_val_nulls,283 array_agg(frequency) AS most_common_freqs,284 array_agg(base_frequency) AS most_common_base_freqs285 FROM pg_mcv_list_items(sd.stxdmcv)286 ) m ON sd.stxdmcv IS NOT NULL287 WHERE pg_has_role(c.relowner, 'USAGE')288 AND (c.relrowsecurity = false OR NOT row_security_active(c.oid));289 290CREATE VIEW pg_stats_ext_exprs WITH (security_barrier) AS291 SELECT cn.nspname AS schemaname,292 c.relname AS tablename,293 sn.nspname AS statistics_schemaname,294 s.stxname AS statistics_name,295 pg_get_userbyid(s.stxowner) AS statistics_owner,296 stat.expr,297 sd.stxdinherit AS inherited,298 (stat.a).stanullfrac AS null_frac,299 (stat.a).stawidth AS avg_width,300 (stat.a).stadistinct AS n_distinct,301 (CASE302 WHEN (stat.a).stakind1 = 1 THEN (stat.a).stavalues1303 WHEN (stat.a).stakind2 = 1 THEN (stat.a).stavalues2304 WHEN (stat.a).stakind3 = 1 THEN (stat.a).stavalues3305 WHEN (stat.a).stakind4 = 1 THEN (stat.a).stavalues4306 WHEN (stat.a).stakind5 = 1 THEN (stat.a).stavalues5307 END) AS most_common_vals,308 (CASE309 WHEN (stat.a).stakind1 = 1 THEN (stat.a).stanumbers1310 WHEN (stat.a).stakind2 = 1 THEN (stat.a).stanumbers2311 WHEN (stat.a).stakind3 = 1 THEN (stat.a).stanumbers3312 WHEN (stat.a).stakind4 = 1 THEN (stat.a).stanumbers4313 WHEN (stat.a).stakind5 = 1 THEN (stat.a).stanumbers5314 END) AS most_common_freqs,315 (CASE316 WHEN (stat.a).stakind1 = 2 THEN (stat.a).stavalues1317 WHEN (stat.a).stakind2 = 2 THEN (stat.a).stavalues2318 WHEN (stat.a).stakind3 = 2 THEN (stat.a).stavalues3319 WHEN (stat.a).stakind4 = 2 THEN (stat.a).stavalues4320 WHEN (stat.a).stakind5 = 2 THEN (stat.a).stavalues5321 END) AS histogram_bounds,322 (CASE323 WHEN (stat.a).stakind1 = 3 THEN (stat.a).stanumbers1[1]324 WHEN (stat.a).stakind2 = 3 THEN (stat.a).stanumbers2[1]325 WHEN (stat.a).stakind3 = 3 THEN (stat.a).stanumbers3[1]326 WHEN (stat.a).stakind4 = 3 THEN (stat.a).stanumbers4[1]327 WHEN (stat.a).stakind5 = 3 THEN (stat.a).stanumbers5[1]328 END) correlation,329 (CASE330 WHEN (stat.a).stakind1 = 4 THEN (stat.a).stavalues1331 WHEN (stat.a).stakind2 = 4 THEN (stat.a).stavalues2332 WHEN (stat.a).stakind3 = 4 THEN (stat.a).stavalues3333 WHEN (stat.a).stakind4 = 4 THEN (stat.a).stavalues4334 WHEN (stat.a).stakind5 = 4 THEN (stat.a).stavalues5335 END) AS most_common_elems,336 (CASE337 WHEN (stat.a).stakind1 = 4 THEN (stat.a).stanumbers1338 WHEN (stat.a).stakind2 = 4 THEN (stat.a).stanumbers2339 WHEN (stat.a).stakind3 = 4 THEN (stat.a).stanumbers3340 WHEN (stat.a).stakind4 = 4 THEN (stat.a).stanumbers4341 WHEN (stat.a).stakind5 = 4 THEN (stat.a).stanumbers5342 END) AS most_common_elem_freqs,343 (CASE344 WHEN (stat.a).stakind1 = 5 THEN (stat.a).stanumbers1345 WHEN (stat.a).stakind2 = 5 THEN (stat.a).stanumbers2346 WHEN (stat.a).stakind3 = 5 THEN (stat.a).stanumbers3347 WHEN (stat.a).stakind4 = 5 THEN (stat.a).stanumbers4348 WHEN (stat.a).stakind5 = 5 THEN (stat.a).stanumbers5349 END) AS elem_count_histogram350 FROM pg_statistic_ext s JOIN pg_class c ON (c.oid = s.stxrelid)351 LEFT JOIN pg_statistic_ext_data sd ON (s.oid = sd.stxoid)352 LEFT JOIN pg_namespace cn ON (cn.oid = c.relnamespace)353 LEFT JOIN pg_namespace sn ON (sn.oid = s.stxnamespace)354 JOIN LATERAL (355 SELECT unnest(pg_get_statisticsobjdef_expressions(s.oid)) AS expr,356 unnest(sd.stxdexpr)::pg_statistic AS a357 ) stat ON (stat.expr IS NOT NULL)358 WHERE pg_has_role(c.relowner, 'USAGE')359 AND (c.relrowsecurity = false OR NOT row_security_active(c.oid));360 361-- unprivileged users may read pg_statistic_ext but not pg_statistic_ext_data362REVOKE ALL ON pg_statistic_ext_data FROM public;363 364CREATE VIEW pg_publication_tables AS365 SELECT366 P.pubname AS pubname,367 N.nspname AS schemaname,368 C.relname AS tablename,369 ( SELECT array_agg(a.attname ORDER BY a.attnum)370 FROM pg_attribute a371 WHERE a.attrelid = GPT.relid AND372 a.attnum = ANY(GPT.attrs)373 ) AS attnames,374 pg_get_expr(GPT.qual, GPT.relid) AS rowfilter375 FROM pg_publication P,376 LATERAL pg_get_publication_tables(P.pubname) GPT,377 pg_class C JOIN pg_namespace N ON (N.oid = C.relnamespace)378 WHERE C.oid = GPT.relid;379 380CREATE VIEW pg_locks AS381 SELECT * FROM pg_lock_status() AS L;382 383CREATE VIEW pg_cursors AS384 SELECT * FROM pg_cursor() AS C;385 386CREATE VIEW pg_available_extensions AS387 SELECT E.name, E.default_version, X.extversion AS installed_version,388 E.comment389 FROM pg_available_extensions() AS E390 LEFT JOIN pg_extension AS X ON E.name = X.extname;391 392CREATE VIEW pg_available_extension_versions AS393 SELECT E.name, E.version, (X.extname IS NOT NULL) AS installed,394 E.superuser, E.trusted, E.relocatable,395 E.schema, E.requires, E.comment396 FROM pg_available_extension_versions() AS E397 LEFT JOIN pg_extension AS X398 ON E.name = X.extname AND E.version = X.extversion;399 400CREATE VIEW pg_prepared_xacts AS401 SELECT P.transaction, P.gid, P.prepared,402 U.rolname AS owner, D.datname AS database403 FROM pg_prepared_xact() AS P404 LEFT JOIN pg_authid U ON P.ownerid = U.oid405 LEFT JOIN pg_database D ON P.dbid = D.oid;406 407CREATE VIEW pg_prepared_statements AS408 SELECT * FROM pg_prepared_statement() AS P;409 410CREATE VIEW pg_seclabels AS411SELECT412 l.objoid, l.classoid, l.objsubid,413 CASE WHEN rel.relkind IN ('r', 'p') THEN 'table'::text414 WHEN rel.relkind = 'v' THEN 'view'::text415 WHEN rel.relkind = 'm' THEN 'materialized view'::text416 WHEN rel.relkind = 'S' THEN 'sequence'::text417 WHEN rel.relkind = 'f' THEN 'foreign table'::text END AS objtype,418 rel.relnamespace AS objnamespace,419 CASE WHEN pg_table_is_visible(rel.oid)420 THEN quote_ident(rel.relname)421 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(rel.relname)422 END AS objname,423 l.provider, l.label424FROM425 pg_seclabel l426 JOIN pg_class rel ON l.classoid = rel.tableoid AND l.objoid = rel.oid427 JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid428WHERE429 l.objsubid = 0430UNION ALL431SELECT432 l.objoid, l.classoid, l.objsubid,433 'column'::text AS objtype,434 rel.relnamespace AS objnamespace,435 CASE WHEN pg_table_is_visible(rel.oid)436 THEN quote_ident(rel.relname)437 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(rel.relname)438 END || '.' || att.attname AS objname,439 l.provider, l.label440FROM441 pg_seclabel l442 JOIN pg_class rel ON l.classoid = rel.tableoid AND l.objoid = rel.oid443 JOIN pg_attribute att444 ON rel.oid = att.attrelid AND l.objsubid = att.attnum445 JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid446WHERE447 l.objsubid != 0448UNION ALL449SELECT450 l.objoid, l.classoid, l.objsubid,451 CASE pro.prokind452 WHEN 'a' THEN 'aggregate'::text453 WHEN 'f' THEN 'function'::text454 WHEN 'p' THEN 'procedure'::text455 WHEN 'w' THEN 'window'::text END AS objtype,456 pro.pronamespace AS objnamespace,457 CASE WHEN pg_function_is_visible(pro.oid)458 THEN quote_ident(pro.proname)459 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(pro.proname)460 END || '(' || pg_catalog.pg_get_function_arguments(pro.oid) || ')' AS objname,461 l.provider, l.label462FROM463 pg_seclabel l464 JOIN pg_proc pro ON l.classoid = pro.tableoid AND l.objoid = pro.oid465 JOIN pg_namespace nsp ON pro.pronamespace = nsp.oid466WHERE467 l.objsubid = 0468UNION ALL469SELECT470 l.objoid, l.classoid, l.objsubid,471 CASE WHEN typ.typtype = 'd' THEN 'domain'::text472 ELSE 'type'::text END AS objtype,473 typ.typnamespace AS objnamespace,474 CASE WHEN pg_type_is_visible(typ.oid)475 THEN quote_ident(typ.typname)476 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(typ.typname)477 END AS objname,478 l.provider, l.label479FROM480 pg_seclabel l481 JOIN pg_type typ ON l.classoid = typ.tableoid AND l.objoid = typ.oid482 JOIN pg_namespace nsp ON typ.typnamespace = nsp.oid483WHERE484 l.objsubid = 0485UNION ALL486SELECT487 l.objoid, l.classoid, l.objsubid,488 'large object'::text AS objtype,489 NULL::oid AS objnamespace,490 l.objoid::text AS objname,491 l.provider, l.label492FROM493 pg_seclabel l494 JOIN pg_largeobject_metadata lom ON l.objoid = lom.oid495WHERE496 l.classoid = 'pg_catalog.pg_largeobject'::regclass AND l.objsubid = 0497UNION ALL498SELECT499 l.objoid, l.classoid, l.objsubid,500 'language'::text AS objtype,501 NULL::oid AS objnamespace,502 quote_ident(lan.lanname) AS objname,503 l.provider, l.label504FROM505 pg_seclabel l506 JOIN pg_language lan ON l.classoid = lan.tableoid AND l.objoid = lan.oid507WHERE508 l.objsubid = 0509UNION ALL510SELECT511 l.objoid, l.classoid, l.objsubid,512 'schema'::text AS objtype,513 nsp.oid AS objnamespace,514 quote_ident(nsp.nspname) AS objname,515 l.provider, l.label516FROM517 pg_seclabel l518 JOIN pg_namespace nsp ON l.classoid = nsp.tableoid AND l.objoid = nsp.oid519WHERE520 l.objsubid = 0521UNION ALL522SELECT523 l.objoid, l.classoid, l.objsubid,524 'event trigger'::text AS objtype,525 NULL::oid AS objnamespace,526 quote_ident(evt.evtname) AS objname,527 l.provider, l.label528FROM529 pg_seclabel l530 JOIN pg_event_trigger evt ON l.classoid = evt.tableoid531 AND l.objoid = evt.oid532WHERE533 l.objsubid = 0534UNION ALL535SELECT536 l.objoid, l.classoid, l.objsubid,537 'publication'::text AS objtype,538 NULL::oid AS objnamespace,539 quote_ident(p.pubname) AS objname,540 l.provider, l.label541FROM542 pg_seclabel l543 JOIN pg_publication p ON l.classoid = p.tableoid AND l.objoid = p.oid544WHERE545 l.objsubid = 0546UNION ALL547SELECT548 l.objoid, l.classoid, 0::int4 AS objsubid,549 'subscription'::text AS objtype,550 NULL::oid AS objnamespace,551 quote_ident(s.subname) AS objname,552 l.provider, l.label553FROM554 pg_shseclabel l555 JOIN pg_subscription s ON l.classoid = s.tableoid AND l.objoid = s.oid556UNION ALL557SELECT558 l.objoid, l.classoid, 0::int4 AS objsubid,559 'database'::text AS objtype,560 NULL::oid AS objnamespace,561 quote_ident(dat.datname) AS objname,562 l.provider, l.label563FROM564 pg_shseclabel l565 JOIN pg_database dat ON l.classoid = dat.tableoid AND l.objoid = dat.oid566UNION ALL567SELECT568 l.objoid, l.classoid, 0::int4 AS objsubid,569 'tablespace'::text AS objtype,570 NULL::oid AS objnamespace,571 quote_ident(spc.spcname) AS objname,572 l.provider, l.label573FROM574 pg_shseclabel l575 JOIN pg_tablespace spc ON l.classoid = spc.tableoid AND l.objoid = spc.oid576UNION ALL577SELECT578 l.objoid, l.classoid, 0::int4 AS objsubid,579 'role'::text AS objtype,580 NULL::oid AS objnamespace,581 quote_ident(rol.rolname) AS objname,582 l.provider, l.label583FROM584 pg_shseclabel l585 JOIN pg_authid rol ON l.classoid = rol.tableoid AND l.objoid = rol.oid;586 587CREATE VIEW pg_settings AS588 SELECT * FROM pg_show_all_settings() AS A;589 590CREATE RULE pg_settings_u AS591 ON UPDATE TO pg_settings592 WHERE new.name = old.name DO593 SELECT set_config(old.name, new.setting, 'f');594 595CREATE RULE pg_settings_n AS596 ON UPDATE TO pg_settings597 DO INSTEAD NOTHING;598 599GRANT SELECT, UPDATE ON pg_settings TO PUBLIC;600 601CREATE VIEW pg_file_settings AS602 SELECT * FROM pg_show_all_file_settings() AS A;603 604REVOKE ALL ON pg_file_settings FROM PUBLIC;605REVOKE EXECUTE ON FUNCTION pg_show_all_file_settings() FROM PUBLIC;606 607CREATE VIEW pg_hba_file_rules AS608 SELECT * FROM pg_hba_file_rules() AS A;609 610REVOKE ALL ON pg_hba_file_rules FROM PUBLIC;611REVOKE EXECUTE ON FUNCTION pg_hba_file_rules() FROM PUBLIC;612 613CREATE VIEW pg_ident_file_mappings AS614 SELECT * FROM pg_ident_file_mappings() AS A;615 616REVOKE ALL ON pg_ident_file_mappings FROM PUBLIC;617REVOKE EXECUTE ON FUNCTION pg_ident_file_mappings() FROM PUBLIC;618 619CREATE VIEW pg_timezone_abbrevs AS620 SELECT * FROM pg_timezone_abbrevs();621 622CREATE VIEW pg_timezone_names AS623 SELECT * FROM pg_timezone_names();624 625CREATE VIEW pg_config AS626 SELECT * FROM pg_config();627 628REVOKE ALL ON pg_config FROM PUBLIC;629REVOKE EXECUTE ON FUNCTION pg_config() FROM PUBLIC;630 631CREATE VIEW pg_shmem_allocations AS632 SELECT * FROM pg_get_shmem_allocations();633 634REVOKE ALL ON pg_shmem_allocations FROM PUBLIC;635GRANT SELECT ON pg_shmem_allocations TO pg_read_all_stats;636REVOKE EXECUTE ON FUNCTION pg_get_shmem_allocations() FROM PUBLIC;637GRANT EXECUTE ON FUNCTION pg_get_shmem_allocations() TO pg_read_all_stats;638 639CREATE VIEW pg_backend_memory_contexts AS640 SELECT * FROM pg_get_backend_memory_contexts();641 642REVOKE ALL ON pg_backend_memory_contexts FROM PUBLIC;643GRANT SELECT ON pg_backend_memory_contexts TO pg_read_all_stats;644REVOKE EXECUTE ON FUNCTION pg_get_backend_memory_contexts() FROM PUBLIC;645GRANT EXECUTE ON FUNCTION pg_get_backend_memory_contexts() TO pg_read_all_stats;646 647-- Statistics views648 649CREATE VIEW pg_stat_all_tables AS650 SELECT651 C.oid AS relid,652 N.nspname AS schemaname,653 C.relname AS relname,654 pg_stat_get_numscans(C.oid) AS seq_scan,655 pg_stat_get_lastscan(C.oid) AS last_seq_scan,656 pg_stat_get_tuples_returned(C.oid) AS seq_tup_read,657 sum(pg_stat_get_numscans(I.indexrelid))::bigint AS idx_scan,658 max(pg_stat_get_lastscan(I.indexrelid)) AS last_idx_scan,659 sum(pg_stat_get_tuples_fetched(I.indexrelid))::bigint +660 pg_stat_get_tuples_fetched(C.oid) AS idx_tup_fetch,661 pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins,662 pg_stat_get_tuples_updated(C.oid) AS n_tup_upd,663 pg_stat_get_tuples_deleted(C.oid) AS n_tup_del,664 pg_stat_get_tuples_hot_updated(C.oid) AS n_tup_hot_upd,665 pg_stat_get_tuples_newpage_updated(C.oid) AS n_tup_newpage_upd,666 pg_stat_get_live_tuples(C.oid) AS n_live_tup,667 pg_stat_get_dead_tuples(C.oid) AS n_dead_tup,668 pg_stat_get_mod_since_analyze(C.oid) AS n_mod_since_analyze,669 pg_stat_get_ins_since_vacuum(C.oid) AS n_ins_since_vacuum,670 pg_stat_get_last_vacuum_time(C.oid) as last_vacuum,671 pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum,672 pg_stat_get_last_analyze_time(C.oid) as last_analyze,673 pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze,674 pg_stat_get_vacuum_count(C.oid) AS vacuum_count,675 pg_stat_get_autovacuum_count(C.oid) AS autovacuum_count,676 pg_stat_get_analyze_count(C.oid) AS analyze_count,677 pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count678 FROM pg_class C LEFT JOIN679 pg_index I ON C.oid = I.indrelid680 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)681 WHERE C.relkind IN ('r', 't', 'm', 'p')682 GROUP BY C.oid, N.nspname, C.relname;683 684CREATE VIEW pg_stat_xact_all_tables AS685 SELECT686 C.oid AS relid,687 N.nspname AS schemaname,688 C.relname AS relname,689 pg_stat_get_xact_numscans(C.oid) AS seq_scan,690 pg_stat_get_xact_tuples_returned(C.oid) AS seq_tup_read,691 sum(pg_stat_get_xact_numscans(I.indexrelid))::bigint AS idx_scan,692 sum(pg_stat_get_xact_tuples_fetched(I.indexrelid))::bigint +693 pg_stat_get_xact_tuples_fetched(C.oid) AS idx_tup_fetch,694 pg_stat_get_xact_tuples_inserted(C.oid) AS n_tup_ins,695 pg_stat_get_xact_tuples_updated(C.oid) AS n_tup_upd,696 pg_stat_get_xact_tuples_deleted(C.oid) AS n_tup_del,697 pg_stat_get_xact_tuples_hot_updated(C.oid) AS n_tup_hot_upd,698 pg_stat_get_xact_tuples_newpage_updated(C.oid) AS n_tup_newpage_upd699 FROM pg_class C LEFT JOIN700 pg_index I ON C.oid = I.indrelid701 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)702 WHERE C.relkind IN ('r', 't', 'm', 'p')703 GROUP BY C.oid, N.nspname, C.relname;704 705CREATE VIEW pg_stat_sys_tables AS706 SELECT * FROM pg_stat_all_tables707 WHERE schemaname IN ('pg_catalog', 'information_schema') OR708 schemaname ~ '^pg_toast';709 710CREATE VIEW pg_stat_xact_sys_tables AS711 SELECT * FROM pg_stat_xact_all_tables712 WHERE schemaname IN ('pg_catalog', 'information_schema') OR713 schemaname ~ '^pg_toast';714 715CREATE VIEW pg_stat_user_tables AS716 SELECT * FROM pg_stat_all_tables717 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND718 schemaname !~ '^pg_toast';719 720CREATE VIEW pg_stat_xact_user_tables AS721 SELECT * FROM pg_stat_xact_all_tables722 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND723 schemaname !~ '^pg_toast';724 725CREATE VIEW pg_statio_all_tables AS726 SELECT727 C.oid AS relid,728 N.nspname AS schemaname,729 C.relname AS relname,730 pg_stat_get_blocks_fetched(C.oid) -731 pg_stat_get_blocks_hit(C.oid) AS heap_blks_read,732 pg_stat_get_blocks_hit(C.oid) AS heap_blks_hit,733 I.idx_blks_read AS idx_blks_read,734 I.idx_blks_hit AS idx_blks_hit,735 pg_stat_get_blocks_fetched(T.oid) -736 pg_stat_get_blocks_hit(T.oid) AS toast_blks_read,737 pg_stat_get_blocks_hit(T.oid) AS toast_blks_hit,738 X.idx_blks_read AS tidx_blks_read,739 X.idx_blks_hit AS tidx_blks_hit740 FROM pg_class C LEFT JOIN741 pg_class T ON C.reltoastrelid = T.oid742 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)743 LEFT JOIN LATERAL (744 SELECT sum(pg_stat_get_blocks_fetched(indexrelid) -745 pg_stat_get_blocks_hit(indexrelid))::bigint746 AS idx_blks_read,747 sum(pg_stat_get_blocks_hit(indexrelid))::bigint748 AS idx_blks_hit749 FROM pg_index WHERE indrelid = C.oid ) I ON true750 LEFT JOIN LATERAL (751 SELECT sum(pg_stat_get_blocks_fetched(indexrelid) -752 pg_stat_get_blocks_hit(indexrelid))::bigint753 AS idx_blks_read,754 sum(pg_stat_get_blocks_hit(indexrelid))::bigint755 AS idx_blks_hit756 FROM pg_index WHERE indrelid = T.oid ) X ON true757 WHERE C.relkind IN ('r', 't', 'm');758 759CREATE VIEW pg_statio_sys_tables AS760 SELECT * FROM pg_statio_all_tables761 WHERE schemaname IN ('pg_catalog', 'information_schema') OR762 schemaname ~ '^pg_toast';763 764CREATE VIEW pg_statio_user_tables AS765 SELECT * FROM pg_statio_all_tables766 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND767 schemaname !~ '^pg_toast';768 769CREATE VIEW pg_stat_all_indexes AS770 SELECT771 C.oid AS relid,772 I.oid AS indexrelid,773 N.nspname AS schemaname,774 C.relname AS relname,775 I.relname AS indexrelname,776 pg_stat_get_numscans(I.oid) AS idx_scan,777 pg_stat_get_lastscan(I.oid) AS last_idx_scan,778 pg_stat_get_tuples_returned(I.oid) AS idx_tup_read,779 pg_stat_get_tuples_fetched(I.oid) AS idx_tup_fetch780 FROM pg_class C JOIN781 pg_index X ON C.oid = X.indrelid JOIN782 pg_class I ON I.oid = X.indexrelid783 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)784 WHERE C.relkind IN ('r', 't', 'm');785 786CREATE VIEW pg_stat_sys_indexes AS787 SELECT * FROM pg_stat_all_indexes788 WHERE schemaname IN ('pg_catalog', 'information_schema') OR789 schemaname ~ '^pg_toast';790 791CREATE VIEW pg_stat_user_indexes AS792 SELECT * FROM pg_stat_all_indexes793 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND794 schemaname !~ '^pg_toast';795 796CREATE VIEW pg_statio_all_indexes AS797 SELECT798 C.oid AS relid,799 I.oid AS indexrelid,800 N.nspname AS schemaname,801 C.relname AS relname,802 I.relname AS indexrelname,803 pg_stat_get_blocks_fetched(I.oid) -804 pg_stat_get_blocks_hit(I.oid) AS idx_blks_read,805 pg_stat_get_blocks_hit(I.oid) AS idx_blks_hit806 FROM pg_class C JOIN807 pg_index X ON C.oid = X.indrelid JOIN808 pg_class I ON I.oid = X.indexrelid809 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)810 WHERE C.relkind IN ('r', 't', 'm');811 812CREATE VIEW pg_statio_sys_indexes AS813 SELECT * FROM pg_statio_all_indexes814 WHERE schemaname IN ('pg_catalog', 'information_schema') OR815 schemaname ~ '^pg_toast';816 817CREATE VIEW pg_statio_user_indexes AS818 SELECT * FROM pg_statio_all_indexes819 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND820 schemaname !~ '^pg_toast';821 822CREATE VIEW pg_statio_all_sequences AS823 SELECT824 C.oid AS relid,825 N.nspname AS schemaname,826 C.relname AS relname,827 pg_stat_get_blocks_fetched(C.oid) -828 pg_stat_get_blocks_hit(C.oid) AS blks_read,829 pg_stat_get_blocks_hit(C.oid) AS blks_hit830 FROM pg_class C831 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)832 WHERE C.relkind = 'S';833 834CREATE VIEW pg_statio_sys_sequences AS835 SELECT * FROM pg_statio_all_sequences836 WHERE schemaname IN ('pg_catalog', 'information_schema') OR837 schemaname ~ '^pg_toast';838 839CREATE VIEW pg_statio_user_sequences AS840 SELECT * FROM pg_statio_all_sequences841 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND842 schemaname !~ '^pg_toast';843 844CREATE VIEW pg_stat_activity AS845 SELECT846 S.datid AS datid,847 D.datname AS datname,848 S.pid,849 S.leader_pid,850 S.usesysid,851 U.rolname AS usename,852 S.application_name,853 S.client_addr,854 S.client_hostname,855 S.client_port,856 S.backend_start,857 S.xact_start,858 S.query_start,859 S.state_change,860 S.wait_event_type,861 S.wait_event,862 S.state,863 S.backend_xid,864 s.backend_xmin,865 S.query_id,866 S.query,867 S.backend_type868 FROM pg_stat_get_activity(NULL) AS S869 LEFT JOIN pg_database AS D ON (S.datid = D.oid)870 LEFT JOIN pg_authid AS U ON (S.usesysid = U.oid);871 872CREATE VIEW pg_stat_replication AS873 SELECT874 S.pid,875 S.usesysid,876 U.rolname AS usename,877 S.application_name,878 S.client_addr,879 S.client_hostname,880 S.client_port,881 S.backend_start,882 S.backend_xmin,883 W.state,884 W.sent_lsn,885 W.write_lsn,886 W.flush_lsn,887 W.replay_lsn,888 W.write_lag,889 W.flush_lag,890 W.replay_lag,891 W.sync_priority,892 W.sync_state,893 W.reply_time894 FROM pg_stat_get_activity(NULL) AS S895 JOIN pg_stat_get_wal_senders() AS W ON (S.pid = W.pid)896 LEFT JOIN pg_authid AS U ON (S.usesysid = U.oid);897 898CREATE VIEW pg_stat_slru AS899 SELECT900 s.name,901 s.blks_zeroed,902 s.blks_hit,903 s.blks_read,904 s.blks_written,905 s.blks_exists,906 s.flushes,907 s.truncates,908 s.stats_reset909 FROM pg_stat_get_slru() s;910 911CREATE VIEW pg_stat_wal_receiver AS912 SELECT913 s.pid,914 s.status,915 s.receive_start_lsn,916 s.receive_start_tli,917 s.written_lsn,918 s.flushed_lsn,919 s.received_tli,920 s.last_msg_send_time,921 s.last_msg_receipt_time,922 s.latest_end_lsn,923 s.latest_end_time,924 s.slot_name,925 s.sender_host,926 s.sender_port,927 s.conninfo928 FROM pg_stat_get_wal_receiver() s929 WHERE s.pid IS NOT NULL;930 931CREATE VIEW pg_stat_recovery_prefetch AS932 SELECT933 s.stats_reset,934 s.prefetch,935 s.hit,936 s.skip_init,937 s.skip_new,938 s.skip_fpw,939 s.skip_rep,940 s.wal_distance,941 s.block_distance,942 s.io_depth943 FROM pg_stat_get_recovery_prefetch() s;944 945CREATE VIEW pg_stat_subscription AS946 SELECT947 su.oid AS subid,948 su.subname,949 st.pid,950 st.leader_pid,951 st.relid,952 st.received_lsn,953 st.last_msg_send_time,954 st.last_msg_receipt_time,955 st.latest_end_lsn,956 st.latest_end_time957 FROM pg_subscription su958 LEFT JOIN pg_stat_get_subscription(NULL) st959 ON (st.subid = su.oid);960 961CREATE VIEW pg_stat_ssl AS962 SELECT963 S.pid,964 S.ssl,965 S.sslversion AS version,966 S.sslcipher AS cipher,967 S.sslbits AS bits,968 S.ssl_client_dn AS client_dn,969 S.ssl_client_serial AS client_serial,970 S.ssl_issuer_dn AS issuer_dn971 FROM pg_stat_get_activity(NULL) AS S972 WHERE S.client_port IS NOT NULL;973 974CREATE VIEW pg_stat_gssapi AS975 SELECT976 S.pid,977 S.gss_auth AS gss_authenticated,978 S.gss_princ AS principal,979 S.gss_enc AS encrypted,980 S.gss_delegation AS credentials_delegated981 FROM pg_stat_get_activity(NULL) AS S982 WHERE S.client_port IS NOT NULL;983 984CREATE VIEW pg_replication_slots AS985 SELECT986 L.slot_name,987 L.plugin,988 L.slot_type,989 L.datoid,990 D.datname AS database,991 L.temporary,992 L.active,993 L.active_pid,994 L.xmin,995 L.catalog_xmin,996 L.restart_lsn,997 L.confirmed_flush_lsn,998 L.wal_status,999 L.safe_wal_size,1000 L.two_phase,1001 L.conflicting1002 FROM pg_get_replication_slots() AS L1003 LEFT JOIN pg_database D ON (L.datoid = D.oid);1004 1005CREATE VIEW pg_stat_replication_slots AS1006 SELECT1007 s.slot_name,1008 s.spill_txns,1009 s.spill_count,1010 s.spill_bytes,1011 s.stream_txns,1012 s.stream_count,1013 s.stream_bytes,1014 s.total_txns,1015 s.total_bytes,1016 s.stats_reset1017 FROM pg_replication_slots as r,1018 LATERAL pg_stat_get_replication_slot(slot_name) as s1019 WHERE r.datoid IS NOT NULL; -- excluding physical slots1020 1021CREATE VIEW pg_stat_database AS1022 SELECT1023 D.oid AS datid,1024 D.datname AS datname,1025 CASE1026 WHEN (D.oid = (0)::oid) THEN 01027 ELSE pg_stat_get_db_numbackends(D.oid)1028 END AS numbackends,1029 pg_stat_get_db_xact_commit(D.oid) AS xact_commit,1030 pg_stat_get_db_xact_rollback(D.oid) AS xact_rollback,1031 pg_stat_get_db_blocks_fetched(D.oid) -1032 pg_stat_get_db_blocks_hit(D.oid) AS blks_read,1033 pg_stat_get_db_blocks_hit(D.oid) AS blks_hit,1034 pg_stat_get_db_tuples_returned(D.oid) AS tup_returned,1035 pg_stat_get_db_tuples_fetched(D.oid) AS tup_fetched,1036 pg_stat_get_db_tuples_inserted(D.oid) AS tup_inserted,1037 pg_stat_get_db_tuples_updated(D.oid) AS tup_updated,1038 pg_stat_get_db_tuples_deleted(D.oid) AS tup_deleted,1039 pg_stat_get_db_conflict_all(D.oid) AS conflicts,1040 pg_stat_get_db_temp_files(D.oid) AS temp_files,1041 pg_stat_get_db_temp_bytes(D.oid) AS temp_bytes,1042 pg_stat_get_db_deadlocks(D.oid) AS deadlocks,1043 pg_stat_get_db_checksum_failures(D.oid) AS checksum_failures,1044 pg_stat_get_db_checksum_last_failure(D.oid) AS checksum_last_failure,1045 pg_stat_get_db_blk_read_time(D.oid) AS blk_read_time,1046 pg_stat_get_db_blk_write_time(D.oid) AS blk_write_time,1047 pg_stat_get_db_session_time(D.oid) AS session_time,1048 pg_stat_get_db_active_time(D.oid) AS active_time,1049 pg_stat_get_db_idle_in_transaction_time(D.oid) AS idle_in_transaction_time,1050 pg_stat_get_db_sessions(D.oid) AS sessions,1051 pg_stat_get_db_sessions_abandoned(D.oid) AS sessions_abandoned,1052 pg_stat_get_db_sessions_fatal(D.oid) AS sessions_fatal,1053 pg_stat_get_db_sessions_killed(D.oid) AS sessions_killed,1054 pg_stat_get_db_stat_reset_time(D.oid) AS stats_reset1055 FROM (1056 SELECT 0 AS oid, NULL::name AS datname1057 UNION ALL1058 SELECT oid, datname FROM pg_database1059 ) D;1060 1061CREATE VIEW pg_stat_database_conflicts AS1062 SELECT1063 D.oid AS datid,1064 D.datname AS datname,1065 pg_stat_get_db_conflict_tablespace(D.oid) AS confl_tablespace,1066 pg_stat_get_db_conflict_lock(D.oid) AS confl_lock,1067 pg_stat_get_db_conflict_snapshot(D.oid) AS confl_snapshot,1068 pg_stat_get_db_conflict_bufferpin(D.oid) AS confl_bufferpin,1069 pg_stat_get_db_conflict_startup_deadlock(D.oid) AS confl_deadlock,1070 pg_stat_get_db_conflict_logicalslot(D.oid) AS confl_active_logicalslot1071 FROM pg_database D;1072 1073CREATE VIEW pg_stat_user_functions AS1074 SELECT1075 P.oid AS funcid,1076 N.nspname AS schemaname,1077 P.proname AS funcname,1078 pg_stat_get_function_calls(P.oid) AS calls,1079 pg_stat_get_function_total_time(P.oid) AS total_time,1080 pg_stat_get_function_self_time(P.oid) AS self_time1081 FROM pg_proc P LEFT JOIN pg_namespace N ON (N.oid = P.pronamespace)1082 WHERE P.prolang != 12 -- fast check to eliminate built-in functions1083 AND pg_stat_get_function_calls(P.oid) IS NOT NULL;1084 1085CREATE VIEW pg_stat_xact_user_functions AS1086 SELECT1087 P.oid AS funcid,1088 N.nspname AS schemaname,1089 P.proname AS funcname,1090 pg_stat_get_xact_function_calls(P.oid) AS calls,1091 pg_stat_get_xact_function_total_time(P.oid) AS total_time,1092 pg_stat_get_xact_function_self_time(P.oid) AS self_time1093 FROM pg_proc P LEFT JOIN pg_namespace N ON (N.oid = P.pronamespace)1094 WHERE P.prolang != 12 -- fast check to eliminate built-in functions1095 AND pg_stat_get_xact_function_calls(P.oid) IS NOT NULL;1096 1097CREATE VIEW pg_stat_archiver AS1098 SELECT1099 s.archived_count,1100 s.last_archived_wal,1101 s.last_archived_time,1102 s.failed_count,1103 s.last_failed_wal,1104 s.last_failed_time,1105 s.stats_reset1106 FROM pg_stat_get_archiver() s;1107 1108CREATE VIEW pg_stat_bgwriter AS1109 SELECT1110 pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,1111 pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req,1112 pg_stat_get_checkpoint_write_time() AS checkpoint_write_time,1113 pg_stat_get_checkpoint_sync_time() AS checkpoint_sync_time,1114 pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint,1115 pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,1116 pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,1117 pg_stat_get_buf_written_backend() AS buffers_backend,1118 pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync,1119 pg_stat_get_buf_alloc() AS buffers_alloc,1120 pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;1121 1122CREATE VIEW pg_stat_io AS1123SELECT1124 b.backend_type,1125 b.object,1126 b.context,1127 b.reads,1128 b.read_time,1129 b.writes,1130 b.write_time,1131 b.writebacks,1132 b.writeback_time,1133 b.extends,1134 b.extend_time,1135 b.op_bytes,1136 b.hits,1137 b.evictions,1138 b.reuses,1139 b.fsyncs,1140 b.fsync_time,1141 b.stats_reset1142FROM pg_stat_get_io() b;1143 1144CREATE VIEW pg_stat_wal AS1145 SELECT1146 w.wal_records,1147 w.wal_fpi,1148 w.wal_bytes,1149 w.wal_buffers_full,1150 w.wal_write,1151 w.wal_sync,1152 w.wal_write_time,1153 w.wal_sync_time,1154 w.stats_reset1155 FROM pg_stat_get_wal() w;1156 1157CREATE VIEW pg_stat_progress_analyze AS1158 SELECT1159 S.pid AS pid, S.datid AS datid, D.datname AS datname,1160 CAST(S.relid AS oid) AS relid,1161 CASE S.param1 WHEN 0 THEN 'initializing'1162 WHEN 1 THEN 'acquiring sample rows'1163 WHEN 2 THEN 'acquiring inherited sample rows'1164 WHEN 3 THEN 'computing statistics'1165 WHEN 4 THEN 'computing extended statistics'1166 WHEN 5 THEN 'finalizing analyze'1167 END AS phase,1168 S.param2 AS sample_blks_total,1169 S.param3 AS sample_blks_scanned,1170 S.param4 AS ext_stats_total,1171 S.param5 AS ext_stats_computed,1172 S.param6 AS child_tables_total,1173 S.param7 AS child_tables_done,1174 CAST(S.param8 AS oid) AS current_child_table_relid1175 FROM pg_stat_get_progress_info('ANALYZE') AS S1176 LEFT JOIN pg_database D ON S.datid = D.oid;1177 1178CREATE VIEW pg_stat_progress_vacuum AS1179 SELECT1180 S.pid AS pid, S.datid AS datid, D.datname AS datname,1181 S.relid AS relid,1182 CASE S.param1 WHEN 0 THEN 'initializing'1183 WHEN 1 THEN 'scanning heap'1184 WHEN 2 THEN 'vacuuming indexes'1185 WHEN 3 THEN 'vacuuming heap'1186 WHEN 4 THEN 'cleaning up indexes'1187 WHEN 5 THEN 'truncating heap'1188 WHEN 6 THEN 'performing final cleanup'1189 END AS phase,1190 S.param2 AS heap_blks_total, S.param3 AS heap_blks_scanned,1191 S.param4 AS heap_blks_vacuumed, S.param5 AS index_vacuum_count,1192 S.param6 AS max_dead_tuples, S.param7 AS num_dead_tuples1193 FROM pg_stat_get_progress_info('VACUUM') AS S1194 LEFT JOIN pg_database D ON S.datid = D.oid;1195 1196CREATE VIEW pg_stat_progress_cluster AS1197 SELECT1198 S.pid AS pid,1199 S.datid AS datid,1200 D.datname AS datname,