| blob | 55f6e3711d8442c7a45ad266dfd5656269374f7e |
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 */
17 CREATE 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);
35 CREATE 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;
50 REVOKE ALL ON pg_shadow FROM public;
52 CREATE 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;
60 CREATE 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;
73 CREATE 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);
108 CREATE 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';
118 CREATE 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';
127 CREATE 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');
141 CREATE 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';
154 CREATE 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');
167 CREATE 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';
189 CREATE 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));
254 REVOKE ALL ON pg_statistic FROM public;
256 CREATE 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));
294 CREATE 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);
362 -- unprivileged users may read pg_statistic_ext but not pg_statistic_ext_data
363 REVOKE ALL ON pg_statistic_ext_data FROM public;
365 CREATE 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;
375 CREATE VIEW pg_locks AS
376 SELECT * FROM pg_lock_status() AS L;
378 CREATE VIEW pg_cursors AS
379 SELECT * FROM pg_cursor() AS C;
381 CREATE 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;
387 CREATE 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;
395 CREATE 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;
402 CREATE VIEW pg_prepared_statements AS
403 SELECT * FROM pg_prepared_statement() AS P;
405 CREATE VIEW pg_seclabels AS
406 SELECT
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
419 FROM
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
423 WHERE
424 l.objsubid = 0
425 UNION ALL
426 SELECT
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
435 FROM
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
441 WHERE
442 l.objsubid != 0
443 UNION ALL
444 SELECT
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
457 FROM
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
461 WHERE
462 l.objsubid = 0
463 UNION ALL
464 SELECT
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
474 FROM
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
478 WHERE
479 l.objsubid = 0
480 UNION ALL
481 SELECT
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
487 FROM
488 pg_seclabel l
489 JOIN pg_largeobject_metadata lom ON l.objoid = lom.oid
490 WHERE
491 l.classoid = 'pg_catalog.pg_largeobject'::regclass AND l.objsubid = 0
492 UNION ALL
493 SELECT
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
499 FROM
500 pg_seclabel l
501 JOIN pg_language lan ON l.classoid = lan.tableoid AND l.objoid = lan.oid
502 WHERE
503 l.objsubid = 0
504 UNION ALL
505 SELECT
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
511 FROM
512 pg_seclabel l
513 JOIN pg_namespace nsp ON l.classoid = nsp.tableoid AND l.objoid = nsp.oid
514 WHERE
515 l.objsubid = 0
516 UNION ALL
517 SELECT
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
523 FROM
524 pg_seclabel l
525 JOIN pg_event_trigger evt ON l.classoid = evt.tableoid
526 AND l.objoid = evt.oid
527 WHERE
528 l.objsubid = 0
529 UNION ALL
530 SELECT
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
536 FROM
537 pg_seclabel l
538 JOIN pg_publication p ON l.classoid = p.tableoid AND l.objoid = p.oid
539 WHERE
540 l.objsubid = 0
541 UNION ALL
542 SELECT
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
548 FROM
549 pg_shseclabel l
550 JOIN pg_subscription s ON l.classoid = s.tableoid AND l.objoid = s.oid
551 UNION ALL
552 SELECT
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
558 FROM
559 pg_shseclabel l
560 JOIN pg_database dat ON l.classoid = dat.tableoid AND l.objoid = dat.oid
561 UNION ALL
562 SELECT
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
568 FROM
569 pg_shseclabel l
570 JOIN pg_tablespace spc ON l.classoid = spc.tableoid AND l.objoid = spc.oid
571 UNION ALL
572 SELECT
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
578 FROM
579 pg_shseclabel l
580 JOIN pg_authid rol ON l.classoid = rol.tableoid AND l.objoid = rol.oid;
582 CREATE VIEW pg_settings AS
583 SELECT * FROM pg_show_all_settings() AS A;
585 CREATE 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');
590 CREATE RULE pg_settings_n AS
591 ON UPDATE TO pg_settings
592 DO INSTEAD NOTHING;
594 GRANT SELECT, UPDATE ON pg_settings TO PUBLIC;
596 CREATE VIEW pg_file_settings AS
597 SELECT * FROM pg_show_all_file_settings() AS A;
599 REVOKE ALL ON pg_file_settings FROM PUBLIC;
600 REVOKE EXECUTE ON FUNCTION pg_show_all_file_settings() FROM PUBLIC;
602 CREATE VIEW pg_hba_file_rules AS
603 SELECT * FROM pg_hba_file_rules() AS A;
605 REVOKE ALL ON pg_hba_file_rules FROM PUBLIC;
606 REVOKE EXECUTE ON FUNCTION pg_hba_file_rules() FROM PUBLIC;
608 CREATE VIEW pg_timezone_abbrevs AS
609 SELECT * FROM pg_timezone_abbrevs();
611 CREATE VIEW pg_timezone_names AS
612 SELECT * FROM pg_timezone_names();
614 CREATE VIEW pg_config AS
615 SELECT * FROM pg_config();
617 REVOKE ALL ON pg_config FROM PUBLIC;
618 REVOKE EXECUTE ON FUNCTION pg_config() FROM PUBLIC;
620 CREATE VIEW pg_shmem_allocations AS
621 SELECT * FROM pg_get_shmem_allocations();
623 REVOKE ALL ON pg_shmem_allocations FROM PUBLIC;
624 REVOKE EXECUTE ON FUNCTION pg_get_shmem_allocations() FROM PUBLIC;
626 CREATE VIEW pg_backend_memory_contexts AS
627 SELECT * FROM pg_get_backend_memory_contexts();
629 REVOKE ALL ON pg_backend_memory_contexts FROM PUBLIC;
630 REVOKE EXECUTE ON FUNCTION pg_get_backend_memory_contexts() FROM PUBLIC;
632 -- Statistics views
634 CREATE 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;
666 CREATE 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;
686 CREATE 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';
691 CREATE 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';
696 CREATE 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';
701 CREATE 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';
706 CREATE 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;
731 CREATE 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';
736 CREATE 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';
741 CREATE 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');
757 CREATE 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';
762 CREATE 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';
767 CREATE 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');
783 CREATE 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';
788 CREATE 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';
793 CREATE 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';
805 CREATE 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';
810 CREATE 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';
815 CREATE 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);
843 CREATE 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);
869 CREATE 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;
882 CREATE 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;
902 CREATE 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);
917 CREATE 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;
930 CREATE 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;
939 CREATE 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);
959 CREATE 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
975 CREATE 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;
1015 CREATE 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;
1026 CREATE 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;
1038 CREATE 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;
1050 CREATE 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;
1061 CREATE 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;
1075 CREATE 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;
1088 CREATE 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;
1109 CREATE 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;
1127 CREATE 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;
1154 CREATE 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;
1189 CREATE 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;
1206 CREATE 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;
1225 CREATE 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);
1247 REVOKE ALL ON pg_user_mapping FROM public;
1249 CREATE VIEW pg_replication_origin_status AS
1250 SELECT *
1251 FROM pg_show_replication_origin_status();
1253 REVOKE ALL ON pg_replication_origin_status FROM public;
1255 -- All columns of pg_subscription except subconninfo are publicly readable.
1256 REVOKE ALL ON pg_subscription FROM public;
1257 GRANT SELECT (oid, subdbid, subname, subowner, subenabled, subbinary,
1258 substream, subtwophasestate, subslotname, subsynccommit, subpublications)
1259 ON pg_subscription TO public;