Debian Codegraph @

src/backend/catalog/system_views.sql

raw
1 /*
2 * PostgreSQL System Views
3 *
4 * Copyright (c) 1996-2025, 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
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);
34
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;
49
50 REVOKE ALL ON pg_shadow FROM public;
51
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 = pg_authid.oid) AS grolist
57 FROM pg_authid
58 WHERE NOT rolcanlogin;
59
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;
72
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);
107
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';
117
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';
126
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');
140
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';
153
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');
166
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 pg_sequence_last_value(C.oid) AS last_value
180 FROM pg_sequence S JOIN pg_class C ON (C.oid = S.seqrelid)
181 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
182 WHERE NOT pg_is_other_temp_schema(N.oid)
183 AND relkind = 'S';
184
185 CREATE VIEW pg_stats WITH (security_barrier) AS
186 SELECT
187 nspname AS schemaname,
188 relname AS tablename,
189 attname AS attname,
190 stainherit AS inherited,
191 stanullfrac AS null_frac,
192 stawidth AS avg_width,
193 stadistinct AS n_distinct,
194 CASE
195 WHEN stakind1 = 1 THEN stavalues1
196 WHEN stakind2 = 1 THEN stavalues2
197 WHEN stakind3 = 1 THEN stavalues3
198 WHEN stakind4 = 1 THEN stavalues4
199 WHEN stakind5 = 1 THEN stavalues5
200 END AS most_common_vals,
201 CASE
202 WHEN stakind1 = 1 THEN stanumbers1
203 WHEN stakind2 = 1 THEN stanumbers2
204 WHEN stakind3 = 1 THEN stanumbers3
205 WHEN stakind4 = 1 THEN stanumbers4
206 WHEN stakind5 = 1 THEN stanumbers5
207 END AS most_common_freqs,
208 CASE
209 WHEN stakind1 = 2 THEN stavalues1
210 WHEN stakind2 = 2 THEN stavalues2
211 WHEN stakind3 = 2 THEN stavalues3
212 WHEN stakind4 = 2 THEN stavalues4
213 WHEN stakind5 = 2 THEN stavalues5
214 END AS histogram_bounds,
215 CASE
216 WHEN stakind1 = 3 THEN stanumbers1[1]
217 WHEN stakind2 = 3 THEN stanumbers2[1]
218 WHEN stakind3 = 3 THEN stanumbers3[1]
219 WHEN stakind4 = 3 THEN stanumbers4[1]
220 WHEN stakind5 = 3 THEN stanumbers5[1]
221 END AS correlation,
222 CASE
223 WHEN stakind1 = 4 THEN stavalues1
224 WHEN stakind2 = 4 THEN stavalues2
225 WHEN stakind3 = 4 THEN stavalues3
226 WHEN stakind4 = 4 THEN stavalues4
227 WHEN stakind5 = 4 THEN stavalues5
228 END AS most_common_elems,
229 CASE
230 WHEN stakind1 = 4 THEN stanumbers1
231 WHEN stakind2 = 4 THEN stanumbers2
232 WHEN stakind3 = 4 THEN stanumbers3
233 WHEN stakind4 = 4 THEN stanumbers4
234 WHEN stakind5 = 4 THEN stanumbers5
235 END AS most_common_elem_freqs,
236 CASE
237 WHEN stakind1 = 5 THEN stanumbers1
238 WHEN stakind2 = 5 THEN stanumbers2
239 WHEN stakind3 = 5 THEN stanumbers3
240 WHEN stakind4 = 5 THEN stanumbers4
241 WHEN stakind5 = 5 THEN stanumbers5
242 END AS elem_count_histogram,
243 CASE
244 WHEN stakind1 = 6 THEN stavalues1
245 WHEN stakind2 = 6 THEN stavalues2
246 WHEN stakind3 = 6 THEN stavalues3
247 WHEN stakind4 = 6 THEN stavalues4
248 WHEN stakind5 = 6 THEN stavalues5
249 END AS range_length_histogram,
250 CASE
251 WHEN stakind1 = 6 THEN stanumbers1[1]
252 WHEN stakind2 = 6 THEN stanumbers2[1]
253 WHEN stakind3 = 6 THEN stanumbers3[1]
254 WHEN stakind4 = 6 THEN stanumbers4[1]
255 WHEN stakind5 = 6 THEN stanumbers5[1]
256 END AS range_empty_frac,
257 CASE
258 WHEN stakind1 = 7 THEN stavalues1
259 WHEN stakind2 = 7 THEN stavalues2
260 WHEN stakind3 = 7 THEN stavalues3
261 WHEN stakind4 = 7 THEN stavalues4
262 WHEN stakind5 = 7 THEN stavalues5
263 END AS range_bounds_histogram
264 FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid)
265 JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum)
266 LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
267 WHERE NOT attisdropped
268 AND has_column_privilege(c.oid, a.attnum, 'select')
269 AND (c.relrowsecurity = false OR NOT row_security_active(c.oid));
270
271 REVOKE ALL ON pg_statistic FROM public;
272
273 CREATE VIEW pg_stats_ext WITH (security_barrier) AS
274 SELECT cn.nspname AS schemaname,
275 c.relname AS tablename,
276 sn.nspname AS statistics_schemaname,
277 s.stxname AS statistics_name,
278 pg_get_userbyid(s.stxowner) AS statistics_owner,
279 ( SELECT array_agg(a.attname ORDER BY a.attnum)
280 FROM unnest(s.stxkeys) k
281 JOIN pg_attribute a
282 ON (a.attrelid = s.stxrelid AND a.attnum = k)
283 ) AS attnames,
284 pg_get_statisticsobjdef_expressions(s.oid) as exprs,
285 s.stxkind AS kinds,
286 sd.stxdinherit AS inherited,
287 sd.stxdndistinct AS n_distinct,
288 sd.stxddependencies AS dependencies,
289 m.most_common_vals,
290 m.most_common_val_nulls,
291 m.most_common_freqs,
292 m.most_common_base_freqs
293 FROM pg_statistic_ext s JOIN pg_class c ON (c.oid = s.stxrelid)
294 JOIN pg_statistic_ext_data sd ON (s.oid = sd.stxoid)
295 LEFT JOIN pg_namespace cn ON (cn.oid = c.relnamespace)
296 LEFT JOIN pg_namespace sn ON (sn.oid = s.stxnamespace)
297 LEFT JOIN LATERAL
298 ( SELECT array_agg(values) AS most_common_vals,
299 array_agg(nulls) AS most_common_val_nulls,
300 array_agg(frequency) AS most_common_freqs,
301 array_agg(base_frequency) AS most_common_base_freqs
302 FROM pg_mcv_list_items(sd.stxdmcv)
303 ) m ON sd.stxdmcv IS NOT NULL
304 WHERE pg_has_role(c.relowner, 'USAGE')
305 AND (c.relrowsecurity = false OR NOT row_security_active(c.oid));
306
307 CREATE VIEW pg_stats_ext_exprs WITH (security_barrier) AS
308 SELECT cn.nspname AS schemaname,
309 c.relname AS tablename,
310 sn.nspname AS statistics_schemaname,
311 s.stxname AS statistics_name,
312 pg_get_userbyid(s.stxowner) AS statistics_owner,
313 stat.expr,
314 sd.stxdinherit AS inherited,
315 (stat.a).stanullfrac AS null_frac,
316 (stat.a).stawidth AS avg_width,
317 (stat.a).stadistinct AS n_distinct,
318 (CASE
319 WHEN (stat.a).stakind1 = 1 THEN (stat.a).stavalues1
320 WHEN (stat.a).stakind2 = 1 THEN (stat.a).stavalues2
321 WHEN (stat.a).stakind3 = 1 THEN (stat.a).stavalues3
322 WHEN (stat.a).stakind4 = 1 THEN (stat.a).stavalues4
323 WHEN (stat.a).stakind5 = 1 THEN (stat.a).stavalues5
324 END) AS most_common_vals,
325 (CASE
326 WHEN (stat.a).stakind1 = 1 THEN (stat.a).stanumbers1
327 WHEN (stat.a).stakind2 = 1 THEN (stat.a).stanumbers2
328 WHEN (stat.a).stakind3 = 1 THEN (stat.a).stanumbers3
329 WHEN (stat.a).stakind4 = 1 THEN (stat.a).stanumbers4
330 WHEN (stat.a).stakind5 = 1 THEN (stat.a).stanumbers5
331 END) AS most_common_freqs,
332 (CASE
333 WHEN (stat.a).stakind1 = 2 THEN (stat.a).stavalues1
334 WHEN (stat.a).stakind2 = 2 THEN (stat.a).stavalues2
335 WHEN (stat.a).stakind3 = 2 THEN (stat.a).stavalues3
336 WHEN (stat.a).stakind4 = 2 THEN (stat.a).stavalues4
337 WHEN (stat.a).stakind5 = 2 THEN (stat.a).stavalues5
338 END) AS histogram_bounds,
339 (CASE
340 WHEN (stat.a).stakind1 = 3 THEN (stat.a).stanumbers1[1]
341 WHEN (stat.a).stakind2 = 3 THEN (stat.a).stanumbers2[1]
342 WHEN (stat.a).stakind3 = 3 THEN (stat.a).stanumbers3[1]
343 WHEN (stat.a).stakind4 = 3 THEN (stat.a).stanumbers4[1]
344 WHEN (stat.a).stakind5 = 3 THEN (stat.a).stanumbers5[1]
345 END) correlation,
346 (CASE
347 WHEN (stat.a).stakind1 = 4 THEN (stat.a).stavalues1
348 WHEN (stat.a).stakind2 = 4 THEN (stat.a).stavalues2
349 WHEN (stat.a).stakind3 = 4 THEN (stat.a).stavalues3
350 WHEN (stat.a).stakind4 = 4 THEN (stat.a).stavalues4
351 WHEN (stat.a).stakind5 = 4 THEN (stat.a).stavalues5
352 END) AS most_common_elems,
353 (CASE
354 WHEN (stat.a).stakind1 = 4 THEN (stat.a).stanumbers1
355 WHEN (stat.a).stakind2 = 4 THEN (stat.a).stanumbers2
356 WHEN (stat.a).stakind3 = 4 THEN (stat.a).stanumbers3
357 WHEN (stat.a).stakind4 = 4 THEN (stat.a).stanumbers4
358 WHEN (stat.a).stakind5 = 4 THEN (stat.a).stanumbers5
359 END) AS most_common_elem_freqs,
360 (CASE
361 WHEN (stat.a).stakind1 = 5 THEN (stat.a).stanumbers1
362 WHEN (stat.a).stakind2 = 5 THEN (stat.a).stanumbers2
363 WHEN (stat.a).stakind3 = 5 THEN (stat.a).stanumbers3
364 WHEN (stat.a).stakind4 = 5 THEN (stat.a).stanumbers4
365 WHEN (stat.a).stakind5 = 5 THEN (stat.a).stanumbers5
366 END) AS elem_count_histogram
367 FROM pg_statistic_ext s JOIN pg_class c ON (c.oid = s.stxrelid)
368 LEFT JOIN pg_statistic_ext_data sd ON (s.oid = sd.stxoid)
369 LEFT JOIN pg_namespace cn ON (cn.oid = c.relnamespace)
370 LEFT JOIN pg_namespace sn ON (sn.oid = s.stxnamespace)
371 JOIN LATERAL (
372 SELECT unnest(pg_get_statisticsobjdef_expressions(s.oid)) AS expr,
373 unnest(sd.stxdexpr)::pg_statistic AS a
374 ) stat ON (stat.expr IS NOT NULL)
375 WHERE pg_has_role(c.relowner, 'USAGE')
376 AND (c.relrowsecurity = false OR NOT row_security_active(c.oid));
377
378 -- unprivileged users may read pg_statistic_ext but not pg_statistic_ext_data
379 REVOKE ALL ON pg_statistic_ext_data FROM public;
380
381 CREATE VIEW pg_publication_tables AS
382 SELECT
383 P.pubname AS pubname,
384 N.nspname AS schemaname,
385 C.relname AS tablename,
386 ( SELECT array_agg(a.attname ORDER BY a.attnum)
387 FROM pg_attribute a
388 WHERE a.attrelid = GPT.relid AND
389 a.attnum = ANY(GPT.attrs)
390 ) AS attnames,
391 pg_get_expr(GPT.qual, GPT.relid) AS rowfilter
392 FROM pg_publication P,
393 LATERAL pg_get_publication_tables(P.pubname) GPT,
394 pg_class C JOIN pg_namespace N ON (N.oid = C.relnamespace)
395 WHERE C.oid = GPT.relid;
396
397 CREATE VIEW pg_locks AS
398 SELECT * FROM pg_lock_status() AS L;
399
400 CREATE VIEW pg_cursors AS
401 SELECT * FROM pg_cursor() AS C;
402
403 CREATE VIEW pg_available_extensions AS
404 SELECT E.name, E.default_version, X.extversion AS installed_version,
405 E.comment
406 FROM pg_available_extensions() AS E
407 LEFT JOIN pg_extension AS X ON E.name = X.extname;
408
409 CREATE VIEW pg_available_extension_versions AS
410 SELECT E.name, E.version, (X.extname IS NOT NULL) AS installed,
411 E.superuser, E.trusted, E.relocatable,
412 E.schema, E.requires, E.comment
413 FROM pg_available_extension_versions() AS E
414 LEFT JOIN pg_extension AS X
415 ON E.name = X.extname AND E.version = X.extversion;
416
417 CREATE VIEW pg_prepared_xacts AS
418 SELECT P.transaction, P.gid, P.prepared,
419 U.rolname AS owner, D.datname AS database
420 FROM pg_prepared_xact() AS P
421 LEFT JOIN pg_authid U ON P.ownerid = U.oid
422 LEFT JOIN pg_database D ON P.dbid = D.oid;
423
424 CREATE VIEW pg_prepared_statements AS
425 SELECT * FROM pg_prepared_statement() AS P;
426
427 CREATE VIEW pg_seclabels AS
428 SELECT
429 l.objoid, l.classoid, l.objsubid,
430 CASE WHEN rel.relkind IN ('r', 'p') THEN 'table'::text
431 WHEN rel.relkind = 'v' THEN 'view'::text
432 WHEN rel.relkind = 'm' THEN 'materialized view'::text
433 WHEN rel.relkind = 'S' THEN 'sequence'::text
434 WHEN rel.relkind = 'f' THEN 'foreign table'::text END AS objtype,
435 rel.relnamespace AS objnamespace,
436 CASE WHEN pg_table_is_visible(rel.oid)
437 THEN quote_ident(rel.relname)
438 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(rel.relname)
439 END AS objname,
440 l.provider, l.label
441 FROM
442 pg_seclabel l
443 JOIN pg_class rel ON l.classoid = rel.tableoid AND l.objoid = rel.oid
444 JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid
445 WHERE
446 l.objsubid = 0
447 UNION ALL
448 SELECT
449 l.objoid, l.classoid, l.objsubid,
450 'column'::text AS objtype,
451 rel.relnamespace AS objnamespace,
452 CASE WHEN pg_table_is_visible(rel.oid)
453 THEN quote_ident(rel.relname)
454 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(rel.relname)
455 END || '.' || att.attname AS objname,
456 l.provider, l.label
457 FROM
458 pg_seclabel l
459 JOIN pg_class rel ON l.classoid = rel.tableoid AND l.objoid = rel.oid
460 JOIN pg_attribute att
461 ON rel.oid = att.attrelid AND l.objsubid = att.attnum
462 JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid
463 WHERE
464 l.objsubid != 0
465 UNION ALL
466 SELECT
467 l.objoid, l.classoid, l.objsubid,
468 CASE pro.prokind
469 WHEN 'a' THEN 'aggregate'::text
470 WHEN 'f' THEN 'function'::text
471 WHEN 'p' THEN 'procedure'::text
472 WHEN 'w' THEN 'window'::text END AS objtype,
473 pro.pronamespace AS objnamespace,
474 CASE WHEN pg_function_is_visible(pro.oid)
475 THEN quote_ident(pro.proname)
476 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(pro.proname)
477 END || '(' || pg_catalog.pg_get_function_arguments(pro.oid) || ')' AS objname,
478 l.provider, l.label
479 FROM
480 pg_seclabel l
481 JOIN pg_proc pro ON l.classoid = pro.tableoid AND l.objoid = pro.oid
482 JOIN pg_namespace nsp ON pro.pronamespace = nsp.oid
483 WHERE
484 l.objsubid = 0
485 UNION ALL
486 SELECT
487 l.objoid, l.classoid, l.objsubid,
488 CASE WHEN typ.typtype = 'd' THEN 'domain'::text
489 ELSE 'type'::text END AS objtype,
490 typ.typnamespace AS objnamespace,
491 CASE WHEN pg_type_is_visible(typ.oid)
492 THEN quote_ident(typ.typname)
493 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(typ.typname)
494 END AS objname,
495 l.provider, l.label
496 FROM
497 pg_seclabel l
498 JOIN pg_type typ ON l.classoid = typ.tableoid AND l.objoid = typ.oid
499 JOIN pg_namespace nsp ON typ.typnamespace = nsp.oid
500 WHERE
501 l.objsubid = 0
502 UNION ALL
503 SELECT
504 l.objoid, l.classoid, l.objsubid,
505 'large object'::text AS objtype,
506 NULL::oid AS objnamespace,
507 l.objoid::text AS objname,
508 l.provider, l.label
509 FROM
510 pg_seclabel l
511 JOIN pg_largeobject_metadata lom ON l.objoid = lom.oid
512 WHERE
513 l.classoid = 'pg_catalog.pg_largeobject'::regclass AND l.objsubid = 0
514 UNION ALL
515 SELECT
516 l.objoid, l.classoid, l.objsubid,
517 'language'::text AS objtype,
518 NULL::oid AS objnamespace,
519 quote_ident(lan.lanname) AS objname,
520 l.provider, l.label
521 FROM
522 pg_seclabel l
523 JOIN pg_language lan ON l.classoid = lan.tableoid AND l.objoid = lan.oid
524 WHERE
525 l.objsubid = 0
526 UNION ALL
527 SELECT
528 l.objoid, l.classoid, l.objsubid,
529 'schema'::text AS objtype,
530 nsp.oid AS objnamespace,
531 quote_ident(nsp.nspname) AS objname,
532 l.provider, l.label
533 FROM
534 pg_seclabel l
535 JOIN pg_namespace nsp ON l.classoid = nsp.tableoid AND l.objoid = nsp.oid
536 WHERE
537 l.objsubid = 0
538 UNION ALL
539 SELECT
540 l.objoid, l.classoid, l.objsubid,
541 'event trigger'::text AS objtype,
542 NULL::oid AS objnamespace,
543 quote_ident(evt.evtname) AS objname,
544 l.provider, l.label
545 FROM
546 pg_seclabel l
547 JOIN pg_event_trigger evt ON l.classoid = evt.tableoid
548 AND l.objoid = evt.oid
549 WHERE
550 l.objsubid = 0
551 UNION ALL
552 SELECT
553 l.objoid, l.classoid, l.objsubid,
554 'publication'::text AS objtype,
555 NULL::oid AS objnamespace,
556 quote_ident(p.pubname) AS objname,
557 l.provider, l.label
558 FROM
559 pg_seclabel l
560 JOIN pg_publication p ON l.classoid = p.tableoid AND l.objoid = p.oid
561 WHERE
562 l.objsubid = 0
563 UNION ALL
564 SELECT
565 l.objoid, l.classoid, 0::int4 AS objsubid,
566 'subscription'::text AS objtype,
567 NULL::oid AS objnamespace,
568 quote_ident(s.subname) AS objname,
569 l.provider, l.label
570 FROM
571 pg_shseclabel l
572 JOIN pg_subscription s ON l.classoid = s.tableoid AND l.objoid = s.oid
573 UNION ALL
574 SELECT
575 l.objoid, l.classoid, 0::int4 AS objsubid,
576 'database'::text AS objtype,
577 NULL::oid AS objnamespace,
578 quote_ident(dat.datname) AS objname,
579 l.provider, l.label
580 FROM
581 pg_shseclabel l
582 JOIN pg_database dat ON l.classoid = dat.tableoid AND l.objoid = dat.oid
583 UNION ALL
584 SELECT
585 l.objoid, l.classoid, 0::int4 AS objsubid,
586 'tablespace'::text AS objtype,
587 NULL::oid AS objnamespace,
588 quote_ident(spc.spcname) AS objname,
589 l.provider, l.label
590 FROM
591 pg_shseclabel l
592 JOIN pg_tablespace spc ON l.classoid = spc.tableoid AND l.objoid = spc.oid
593 UNION ALL
594 SELECT
595 l.objoid, l.classoid, 0::int4 AS objsubid,
596 'role'::text AS objtype,
597 NULL::oid AS objnamespace,
598 quote_ident(rol.rolname) AS objname,
599 l.provider, l.label
600 FROM
601 pg_shseclabel l
602 JOIN pg_authid rol ON l.classoid = rol.tableoid AND l.objoid = rol.oid;
603
604 CREATE VIEW pg_settings AS
605 SELECT * FROM pg_show_all_settings() AS A;
606
607 CREATE RULE pg_settings_u AS
608 ON UPDATE TO pg_settings
609 WHERE new.name = old.name DO
610 SELECT set_config(old.name, new.setting, 'f');
611
612 CREATE RULE pg_settings_n AS
613 ON UPDATE TO pg_settings
614 DO INSTEAD NOTHING;
615
616 GRANT SELECT, UPDATE ON pg_settings TO PUBLIC;
617
618 CREATE VIEW pg_file_settings AS
619 SELECT * FROM pg_show_all_file_settings() AS A;
620
621 REVOKE ALL ON pg_file_settings FROM PUBLIC;
622 REVOKE EXECUTE ON FUNCTION pg_show_all_file_settings() FROM PUBLIC;
623
624 CREATE VIEW pg_hba_file_rules AS
625 SELECT * FROM pg_hba_file_rules() AS A;
626
627 REVOKE ALL ON pg_hba_file_rules FROM PUBLIC;
628 REVOKE EXECUTE ON FUNCTION pg_hba_file_rules() FROM PUBLIC;
629
630 CREATE VIEW pg_ident_file_mappings AS
631 SELECT * FROM pg_ident_file_mappings() AS A;
632
633 REVOKE ALL ON pg_ident_file_mappings FROM PUBLIC;
634 REVOKE EXECUTE ON FUNCTION pg_ident_file_mappings() FROM PUBLIC;
635
636 CREATE VIEW pg_timezone_abbrevs AS
637 SELECT * FROM pg_timezone_abbrevs_zone() z
638 UNION ALL
639 (SELECT * FROM pg_timezone_abbrevs_abbrevs() a
640 WHERE NOT EXISTS (SELECT 1 FROM pg_timezone_abbrevs_zone() z2
641 WHERE z2.abbrev = a.abbrev))
642 ORDER BY abbrev;
643
644 CREATE VIEW pg_timezone_names AS
645 SELECT * FROM pg_timezone_names();
646
647 CREATE VIEW pg_config AS
648 SELECT * FROM pg_config();
649
650 REVOKE ALL ON pg_config FROM PUBLIC;
651 REVOKE EXECUTE ON FUNCTION pg_config() FROM PUBLIC;
652
653 CREATE VIEW pg_shmem_allocations AS
654 SELECT * FROM pg_get_shmem_allocations();
655
656 REVOKE ALL ON pg_shmem_allocations FROM PUBLIC;
657 GRANT SELECT ON pg_shmem_allocations TO pg_read_all_stats;
658 REVOKE EXECUTE ON FUNCTION pg_get_shmem_allocations() FROM PUBLIC;
659 GRANT EXECUTE ON FUNCTION pg_get_shmem_allocations() TO pg_read_all_stats;
660
661 CREATE VIEW pg_shmem_allocations_numa AS
662 SELECT * FROM pg_get_shmem_allocations_numa();
663
664 REVOKE ALL ON pg_shmem_allocations_numa FROM PUBLIC;
665 GRANT SELECT ON pg_shmem_allocations_numa TO pg_read_all_stats;
666 REVOKE EXECUTE ON FUNCTION pg_get_shmem_allocations_numa() FROM PUBLIC;
667 GRANT EXECUTE ON FUNCTION pg_get_shmem_allocations_numa() TO pg_read_all_stats;
668
669 CREATE VIEW pg_backend_memory_contexts AS
670 SELECT * FROM pg_get_backend_memory_contexts();
671
672 REVOKE ALL ON pg_backend_memory_contexts FROM PUBLIC;
673 GRANT SELECT ON pg_backend_memory_contexts TO pg_read_all_stats;
674 REVOKE EXECUTE ON FUNCTION pg_get_backend_memory_contexts() FROM PUBLIC;
675 GRANT EXECUTE ON FUNCTION pg_get_backend_memory_contexts() TO pg_read_all_stats;
676
677 -- Statistics views
678
679 CREATE VIEW pg_stat_all_tables AS
680 SELECT
681 C.oid AS relid,
682 N.nspname AS schemaname,
683 C.relname AS relname,
684 pg_stat_get_numscans(C.oid) AS seq_scan,
685 pg_stat_get_lastscan(C.oid) AS last_seq_scan,
686 pg_stat_get_tuples_returned(C.oid) AS seq_tup_read,
687 sum(pg_stat_get_numscans(I.indexrelid))::bigint AS idx_scan,
688 max(pg_stat_get_lastscan(I.indexrelid)) AS last_idx_scan,
689 sum(pg_stat_get_tuples_fetched(I.indexrelid))::bigint +
690 pg_stat_get_tuples_fetched(C.oid) AS idx_tup_fetch,
691 pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins,
692 pg_stat_get_tuples_updated(C.oid) AS n_tup_upd,
693 pg_stat_get_tuples_deleted(C.oid) AS n_tup_del,
694 pg_stat_get_tuples_hot_updated(C.oid) AS n_tup_hot_upd,
695 pg_stat_get_tuples_newpage_updated(C.oid) AS n_tup_newpage_upd,
696 pg_stat_get_live_tuples(C.oid) AS n_live_tup,
697 pg_stat_get_dead_tuples(C.oid) AS n_dead_tup,
698 pg_stat_get_mod_since_analyze(C.oid) AS n_mod_since_analyze,
699 pg_stat_get_ins_since_vacuum(C.oid) AS n_ins_since_vacuum,
700 pg_stat_get_last_vacuum_time(C.oid) as last_vacuum,
701 pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum,
702 pg_stat_get_last_analyze_time(C.oid) as last_analyze,
703 pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze,
704 pg_stat_get_vacuum_count(C.oid) AS vacuum_count,
705 pg_stat_get_autovacuum_count(C.oid) AS autovacuum_count,
706 pg_stat_get_analyze_count(C.oid) AS analyze_count,
707 pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count,
708 pg_stat_get_total_vacuum_time(C.oid) AS total_vacuum_time,
709 pg_stat_get_total_autovacuum_time(C.oid) AS total_autovacuum_time,
710 pg_stat_get_total_analyze_time(C.oid) AS total_analyze_time,
711 pg_stat_get_total_autoanalyze_time(C.oid) AS total_autoanalyze_time
712 FROM pg_class C LEFT JOIN
713 pg_index I ON C.oid = I.indrelid
714 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
715 WHERE C.relkind IN ('r', 't', 'm', 'p')
716 GROUP BY C.oid, N.nspname, C.relname;
717
718 CREATE VIEW pg_stat_xact_all_tables AS
719 SELECT
720 C.oid AS relid,
721 N.nspname AS schemaname,
722 C.relname AS relname,
723 pg_stat_get_xact_numscans(C.oid) AS seq_scan,
724 pg_stat_get_xact_tuples_returned(C.oid) AS seq_tup_read,
725 sum(pg_stat_get_xact_numscans(I.indexrelid))::bigint AS idx_scan,
726 sum(pg_stat_get_xact_tuples_fetched(I.indexrelid))::bigint +
727 pg_stat_get_xact_tuples_fetched(C.oid) AS idx_tup_fetch,
728 pg_stat_get_xact_tuples_inserted(C.oid) AS n_tup_ins,
729 pg_stat_get_xact_tuples_updated(C.oid) AS n_tup_upd,
730 pg_stat_get_xact_tuples_deleted(C.oid) AS n_tup_del,
731 pg_stat_get_xact_tuples_hot_updated(C.oid) AS n_tup_hot_upd,
732 pg_stat_get_xact_tuples_newpage_updated(C.oid) AS n_tup_newpage_upd
733 FROM pg_class C LEFT JOIN
734 pg_index I ON C.oid = I.indrelid
735 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
736 WHERE C.relkind IN ('r', 't', 'm', 'p')
737 GROUP BY C.oid, N.nspname, C.relname;
738
739 CREATE VIEW pg_stat_sys_tables AS
740 SELECT * FROM pg_stat_all_tables
741 WHERE schemaname IN ('pg_catalog', 'information_schema') OR
742 schemaname ~ '^pg_toast';
743
744 CREATE VIEW pg_stat_xact_sys_tables AS
745 SELECT * FROM pg_stat_xact_all_tables
746 WHERE schemaname IN ('pg_catalog', 'information_schema') OR
747 schemaname ~ '^pg_toast';
748
749 CREATE VIEW pg_stat_user_tables AS
750 SELECT * FROM pg_stat_all_tables
751 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
752 schemaname !~ '^pg_toast';
753
754 CREATE VIEW pg_stat_xact_user_tables AS
755 SELECT * FROM pg_stat_xact_all_tables
756 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
757 schemaname !~ '^pg_toast';
758
759 CREATE VIEW pg_statio_all_tables AS
760 SELECT
761 C.oid AS relid,
762 N.nspname AS schemaname,
763 C.relname AS relname,
764 pg_stat_get_blocks_fetched(C.oid) -
765 pg_stat_get_blocks_hit(C.oid) AS heap_blks_read,
766 pg_stat_get_blocks_hit(C.oid) AS heap_blks_hit,
767 I.idx_blks_read AS idx_blks_read,
768 I.idx_blks_hit AS idx_blks_hit,
769 pg_stat_get_blocks_fetched(T.oid) -
770 pg_stat_get_blocks_hit(T.oid) AS toast_blks_read,
771 pg_stat_get_blocks_hit(T.oid) AS toast_blks_hit,
772 X.idx_blks_read AS tidx_blks_read,
773 X.idx_blks_hit AS tidx_blks_hit
774 FROM pg_class C LEFT JOIN
775 pg_class T ON C.reltoastrelid = T.oid
776 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
777 LEFT JOIN LATERAL (
778 SELECT sum(pg_stat_get_blocks_fetched(indexrelid) -
779 pg_stat_get_blocks_hit(indexrelid))::bigint
780 AS idx_blks_read,
781 sum(pg_stat_get_blocks_hit(indexrelid))::bigint
782 AS idx_blks_hit
783 FROM pg_index WHERE indrelid = C.oid ) I ON true
784 LEFT JOIN LATERAL (
785 SELECT sum(pg_stat_get_blocks_fetched(indexrelid) -
786 pg_stat_get_blocks_hit(indexrelid))::bigint
787 AS idx_blks_read,
788 sum(pg_stat_get_blocks_hit(indexrelid))::bigint
789 AS idx_blks_hit
790 FROM pg_index WHERE indrelid = T.oid ) X ON true
791 WHERE C.relkind IN ('r', 't', 'm');
792
793 CREATE VIEW pg_statio_sys_tables AS
794 SELECT * FROM pg_statio_all_tables
795 WHERE schemaname IN ('pg_catalog', 'information_schema') OR
796 schemaname ~ '^pg_toast';
797
798 CREATE VIEW pg_statio_user_tables AS
799 SELECT * FROM pg_statio_all_tables
800 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
801 schemaname !~ '^pg_toast';
802
803 CREATE VIEW pg_stat_all_indexes AS
804 SELECT
805 C.oid AS relid,
806 I.oid AS indexrelid,
807 N.nspname AS schemaname,
808 C.relname AS relname,
809 I.relname AS indexrelname,
810 pg_stat_get_numscans(I.oid) AS idx_scan,
811 pg_stat_get_lastscan(I.oid) AS last_idx_scan,
812 pg_stat_get_tuples_returned(I.oid) AS idx_tup_read,
813 pg_stat_get_tuples_fetched(I.oid) AS idx_tup_fetch
814 FROM pg_class C JOIN
815 pg_index X ON C.oid = X.indrelid JOIN
816 pg_class I ON I.oid = X.indexrelid
817 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
818 WHERE C.relkind IN ('r', 't', 'm');
819
820 CREATE VIEW pg_stat_sys_indexes AS
821 SELECT * FROM pg_stat_all_indexes
822 WHERE schemaname IN ('pg_catalog', 'information_schema') OR
823 schemaname ~ '^pg_toast';
824
825 CREATE VIEW pg_stat_user_indexes AS
826 SELECT * FROM pg_stat_all_indexes
827 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
828 schemaname !~ '^pg_toast';
829
830 CREATE VIEW pg_statio_all_indexes AS
831 SELECT
832 C.oid AS relid,
833 I.oid AS indexrelid,
834 N.nspname AS schemaname,
835 C.relname AS relname,
836 I.relname AS indexrelname,
837 pg_stat_get_blocks_fetched(I.oid) -
838 pg_stat_get_blocks_hit(I.oid) AS idx_blks_read,
839 pg_stat_get_blocks_hit(I.oid) AS idx_blks_hit
840 FROM pg_class C JOIN
841 pg_index X ON C.oid = X.indrelid JOIN
842 pg_class I ON I.oid = X.indexrelid
843 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
844 WHERE C.relkind IN ('r', 't', 'm');
845
846 CREATE VIEW pg_statio_sys_indexes AS
847 SELECT * FROM pg_statio_all_indexes
848 WHERE schemaname IN ('pg_catalog', 'information_schema') OR
849 schemaname ~ '^pg_toast';
850
851 CREATE VIEW pg_statio_user_indexes AS
852 SELECT * FROM pg_statio_all_indexes
853 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
854 schemaname !~ '^pg_toast';
855
856 CREATE VIEW pg_statio_all_sequences AS
857 SELECT
858 C.oid AS relid,
859 N.nspname AS schemaname,
860 C.relname AS relname,
861 pg_stat_get_blocks_fetched(C.oid) -
862 pg_stat_get_blocks_hit(C.oid) AS blks_read,
863 pg_stat_get_blocks_hit(C.oid) AS blks_hit
864 FROM pg_class C
865 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
866 WHERE C.relkind = 'S';
867
868 CREATE VIEW pg_statio_sys_sequences AS
869 SELECT * FROM pg_statio_all_sequences
870 WHERE schemaname IN ('pg_catalog', 'information_schema') OR
871 schemaname ~ '^pg_toast';
872
873 CREATE VIEW pg_statio_user_sequences AS
874 SELECT * FROM pg_statio_all_sequences
875 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
876 schemaname !~ '^pg_toast';
877
878 CREATE VIEW pg_stat_activity AS
879 SELECT
880 S.datid AS datid,
881 D.datname AS datname,
882 S.pid,
883 S.leader_pid,
884 S.usesysid,
885 U.rolname AS usename,
886 S.application_name,
887 S.client_addr,
888 S.client_hostname,
889 S.client_port,
890 S.backend_start,
891 S.xact_start,
892 S.query_start,
893 S.state_change,
894 S.wait_event_type,
895 S.wait_event,
896 S.state,
897 S.backend_xid,
898 s.backend_xmin,
899 S.query_id,
900 S.query,
901 S.backend_type
902 FROM pg_stat_get_activity(NULL) AS S
903 LEFT JOIN pg_database AS D ON (S.datid = D.oid)
904 LEFT JOIN pg_authid AS U ON (S.usesysid = U.oid);
905
906 CREATE VIEW pg_stat_replication AS
907 SELECT
908 S.pid,
909 S.usesysid,
910 U.rolname AS usename,
911 S.application_name,
912 S.client_addr,
913 S.client_hostname,
914 S.client_port,
915 S.backend_start,
916 S.backend_xmin,
917 W.state,
918 W.sent_lsn,
919 W.write_lsn,
920 W.flush_lsn,
921 W.replay_lsn,
922 W.write_lag,
923 W.flush_lag,
924 W.replay_lag,
925 W.sync_priority,
926 W.sync_state,
927 W.reply_time
928 FROM pg_stat_get_activity(NULL) AS S
929 JOIN pg_stat_get_wal_senders() AS W ON (S.pid = W.pid)
930 LEFT JOIN pg_authid AS U ON (S.usesysid = U.oid);
931
932 CREATE VIEW pg_stat_slru AS
933 SELECT
934 s.name,
935 s.blks_zeroed,
936 s.blks_hit,
937 s.blks_read,
938 s.blks_written,
939 s.blks_exists,
940 s.flushes,
941 s.truncates,
942 s.stats_reset
943 FROM pg_stat_get_slru() s;
944
945 CREATE VIEW pg_stat_wal_receiver AS
946 SELECT
947 s.pid,
948 s.status,
949 s.receive_start_lsn,
950 s.receive_start_tli,
951 s.written_lsn,
952 s.flushed_lsn,
953 s.received_tli,
954 s.last_msg_send_time,
955 s.last_msg_receipt_time,
956 s.latest_end_lsn,
957 s.latest_end_time,
958 s.slot_name,
959 s.sender_host,
960 s.sender_port,
961 s.conninfo
962 FROM pg_stat_get_wal_receiver() s
963 WHERE s.pid IS NOT NULL;
964
965 CREATE VIEW pg_stat_recovery_prefetch AS
966 SELECT
967 s.stats_reset,
968 s.prefetch,
969 s.hit,
970 s.skip_init,
971 s.skip_new,
972 s.skip_fpw,
973 s.skip_rep,
974 s.wal_distance,
975 s.block_distance,
976 s.io_depth
977 FROM pg_stat_get_recovery_prefetch() s;
978
979 CREATE VIEW pg_stat_subscription AS
980 SELECT
981 su.oid AS subid,
982 su.subname,
983 st.worker_type,
984 st.pid,
985 st.leader_pid,
986 st.relid,
987 st.received_lsn,
988 st.last_msg_send_time,
989 st.last_msg_receipt_time,
990 st.latest_end_lsn,
991 st.latest_end_time
992 FROM pg_subscription su
993 LEFT JOIN pg_stat_get_subscription(NULL) st
994 ON (st.subid = su.oid);
995
996 CREATE VIEW pg_stat_ssl AS
997 SELECT
998 S.pid,
999 S.ssl,
1000 S.sslversion AS version,
1001 S.sslcipher AS cipher,
1002 S.sslbits AS bits,
1003 S.ssl_client_dn AS client_dn,
1004 S.ssl_client_serial AS client_serial,
1005 S.ssl_issuer_dn AS issuer_dn
1006 FROM pg_stat_get_activity(NULL) AS S
1007 WHERE S.client_port IS NOT NULL;
1008
1009 CREATE VIEW pg_stat_gssapi AS
1010 SELECT
1011 S.pid,
1012 S.gss_auth AS gss_authenticated,
1013 S.gss_princ AS principal,
1014 S.gss_enc AS encrypted,
1015 S.gss_delegation AS credentials_delegated
1016 FROM pg_stat_get_activity(NULL) AS S
1017 WHERE S.client_port IS NOT NULL;
1018
1019 CREATE VIEW pg_replication_slots AS
1020 SELECT
1021 L.slot_name,
1022 L.plugin,
1023 L.slot_type,
1024 L.datoid,
1025 D.datname AS database,
1026 L.temporary,
1027 L.active,
1028 L.active_pid,
1029 L.xmin,
1030 L.catalog_xmin,
1031 L.restart_lsn,
1032 L.confirmed_flush_lsn,
1033 L.wal_status,
1034 L.safe_wal_size,
1035 L.two_phase,
1036 L.two_phase_at,
1037 L.inactive_since,
1038 L.conflicting,
1039 L.invalidation_reason,
1040 L.failover,
1041 L.synced
1042 FROM pg_get_replication_slots() AS L
1043 LEFT JOIN pg_database D ON (L.datoid = D.oid);
1044
1045 CREATE VIEW pg_stat_replication_slots AS
1046 SELECT
1047 s.slot_name,
1048 s.spill_txns,
1049 s.spill_count,
1050 s.spill_bytes,
1051 s.stream_txns,
1052 s.stream_count,
1053 s.stream_bytes,
1054 s.total_txns,
1055 s.total_bytes,
1056 s.stats_reset
1057 FROM pg_replication_slots as r,
1058 LATERAL pg_stat_get_replication_slot(slot_name) as s
1059 WHERE r.datoid IS NOT NULL; -- excluding physical slots
1060
1061 CREATE VIEW pg_stat_database AS
1062 SELECT
1063 D.oid AS datid,
1064 D.datname AS datname,
1065 CASE
1066 WHEN (D.oid = (0)::oid) THEN 0
1067 ELSE pg_stat_get_db_numbackends(D.oid)
1068 END AS numbackends,
1069 pg_stat_get_db_xact_commit(D.oid) AS xact_commit,
1070 pg_stat_get_db_xact_rollback(D.oid) AS xact_rollback,
1071 pg_stat_get_db_blocks_fetched(D.oid) -
1072 pg_stat_get_db_blocks_hit(D.oid) AS blks_read,
1073 pg_stat_get_db_blocks_hit(D.oid) AS blks_hit,
1074 pg_stat_get_db_tuples_returned(D.oid) AS tup_returned,
1075 pg_stat_get_db_tuples_fetched(D.oid) AS tup_fetched,
1076 pg_stat_get_db_tuples_inserted(D.oid) AS tup_inserted,
1077 pg_stat_get_db_tuples_updated(D.oid) AS tup_updated,
1078 pg_stat_get_db_tuples_deleted(D.oid) AS tup_deleted,
1079 pg_stat_get_db_conflict_all(D.oid) AS conflicts,
1080 pg_stat_get_db_temp_files(D.oid) AS temp_files,
1081 pg_stat_get_db_temp_bytes(D.oid) AS temp_bytes,
1082 pg_stat_get_db_deadlocks(D.oid) AS deadlocks,
1083 pg_stat_get_db_checksum_failures(D.oid) AS checksum_failures,
1084 pg_stat_get_db_checksum_last_failure(D.oid) AS checksum_last_failure,
1085 pg_stat_get_db_blk_read_time(D.oid) AS blk_read_time,
1086 pg_stat_get_db_blk_write_time(D.oid) AS blk_write_time,
1087 pg_stat_get_db_session_time(D.oid) AS session_time,
1088 pg_stat_get_db_active_time(D.oid) AS active_time,
1089 pg_stat_get_db_idle_in_transaction_time(D.oid) AS idle_in_transaction_time,
1090 pg_stat_get_db_sessions(D.oid) AS sessions,
1091 pg_stat_get_db_sessions_abandoned(D.oid) AS sessions_abandoned,
1092 pg_stat_get_db_sessions_fatal(D.oid) AS sessions_fatal,
1093 pg_stat_get_db_sessions_killed(D.oid) AS sessions_killed,
1094 pg_stat_get_db_parallel_workers_to_launch(D.oid) as parallel_workers_to_launch,
1095 pg_stat_get_db_parallel_workers_launched(D.oid) as parallel_workers_launched,
1096 pg_stat_get_db_stat_reset_time(D.oid) AS stats_reset
1097 FROM (
1098 SELECT 0 AS oid, NULL::name AS datname
1099 UNION ALL
1100 SELECT oid, datname FROM pg_database
1101 ) D;
1102
1103 CREATE VIEW pg_stat_database_conflicts AS
1104 SELECT
1105 D.oid AS datid,
1106 D.datname AS datname,
1107 pg_stat_get_db_conflict_tablespace(D.oid) AS confl_tablespace,
1108 pg_stat_get_db_conflict_lock(D.oid) AS confl_lock,
1109 pg_stat_get_db_conflict_snapshot(D.oid) AS confl_snapshot,
1110 pg_stat_get_db_conflict_bufferpin(D.oid) AS confl_bufferpin,
1111 pg_stat_get_db_conflict_startup_deadlock(D.oid) AS confl_deadlock,
1112 pg_stat_get_db_conflict_logicalslot(D.oid) AS confl_active_logicalslot
1113 FROM pg_database D;
1114
1115 CREATE VIEW pg_stat_user_functions AS
1116 SELECT
1117 P.oid AS funcid,
1118 N.nspname AS schemaname,
1119 P.proname AS funcname,
1120 pg_stat_get_function_calls(P.oid) AS calls,
1121 pg_stat_get_function_total_time(P.oid) AS total_time,
1122 pg_stat_get_function_self_time(P.oid) AS self_time
1123 FROM pg_proc P LEFT JOIN pg_namespace N ON (N.oid = P.pronamespace)
1124 WHERE P.prolang != 12 -- fast check to eliminate built-in functions
1125 AND pg_stat_get_function_calls(P.oid) IS NOT NULL;
1126
1127 CREATE VIEW pg_stat_xact_user_functions AS
1128 SELECT
1129 P.oid AS funcid,
1130 N.nspname AS schemaname,
1131 P.proname AS funcname,
1132 pg_stat_get_xact_function_calls(P.oid) AS calls,
1133 pg_stat_get_xact_function_total_time(P.oid) AS total_time,
1134 pg_stat_get_xact_function_self_time(P.oid) AS self_time
1135 FROM pg_proc P LEFT JOIN pg_namespace N ON (N.oid = P.pronamespace)
1136 WHERE P.prolang != 12 -- fast check to eliminate built-in functions
1137 AND pg_stat_get_xact_function_calls(P.oid) IS NOT NULL;
1138
1139 CREATE VIEW pg_stat_archiver AS
1140 SELECT
1141 s.archived_count,
1142 s.last_archived_wal,
1143 s.last_archived_time,
1144 s.failed_count,
1145 s.last_failed_wal,
1146 s.last_failed_time,
1147 s.stats_reset
1148 FROM pg_stat_get_archiver() s;
1149
1150 CREATE VIEW pg_stat_bgwriter AS
1151 SELECT
1152 pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,
1153 pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
1154 pg_stat_get_buf_alloc() AS buffers_alloc,
1155 pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
1156
1157 CREATE VIEW pg_stat_checkpointer AS
1158 SELECT
1159 pg_stat_get_checkpointer_num_timed() AS num_timed,
1160 pg_stat_get_checkpointer_num_requested() AS num_requested,
1161 pg_stat_get_checkpointer_num_performed() AS num_done,
1162 pg_stat_get_checkpointer_restartpoints_timed() AS restartpoints_timed,
1163 pg_stat_get_checkpointer_restartpoints_requested() AS restartpoints_req,
1164 pg_stat_get_checkpointer_restartpoints_performed() AS restartpoints_done,
1165 pg_stat_get_checkpointer_write_time() AS write_time,
1166 pg_stat_get_checkpointer_sync_time() AS sync_time,
1167 pg_stat_get_checkpointer_buffers_written() AS buffers_written,
1168 pg_stat_get_checkpointer_slru_written() AS slru_written,
1169 pg_stat_get_checkpointer_stat_reset_time() AS stats_reset;
1170
1171 CREATE VIEW pg_stat_io AS
1172 SELECT
1173 b.backend_type,
1174 b.object,
1175 b.context,
1176 b.reads,
1177 b.read_bytes,
1178 b.read_time,
1179 b.writes,
1180 b.write_bytes,
1181 b.write_time,
1182 b.writebacks,
1183 b.writeback_time,
1184 b.extends,
1185 b.extend_bytes,
1186 b.extend_time,
1187 b.hits,
1188 b.evictions,
1189 b.reuses,
1190 b.fsyncs,
1191 b.fsync_time,
1192 b.stats_reset
1193 FROM pg_stat_get_io() b;
1194
1195 CREATE VIEW pg_stat_wal AS
1196 SELECT
1197 w.wal_records,
1198 w.wal_fpi,
1199 w.wal_bytes,
1200 w.wal_buffers_full,
1201 w.stats_reset
1202 FROM pg_stat_get_wal() w;
1203
1204 CREATE VIEW pg_stat_progress_analyze AS
1205 SELECT
1206 S.pid AS pid, S.datid AS datid, D.datname AS datname,
1207 CAST(S.relid AS oid) AS relid,
1208 CASE S.param1 WHEN 0 THEN 'initializing'
1209 WHEN 1 THEN 'acquiring sample rows'
1210 WHEN 2 THEN 'acquiring inherited sample rows'
1211 WHEN 3 THEN 'computing statistics'
1212 WHEN 4 THEN 'computing extended statistics'
1213 WHEN 5 THEN 'finalizing analyze'
1214 END AS phase,
1215 S.param2 AS sample_blks_total,
1216 S.param3 AS sample_blks_scanned,
1217 S.param4 AS ext_stats_total,
1218 S.param5 AS ext_stats_computed,
1219 S.param6 AS child_tables_total,
1220 S.param7 AS child_tables_done,
1221 CAST(S.param8 AS oid) AS current_child_table_relid,
1222 S.param9 / 1000000::double precision AS delay_time
1223 FROM pg_stat_get_progress_info('ANALYZE') AS S
1224 LEFT JOIN pg_database D ON S.datid = D.oid;
1225
1226 CREATE VIEW pg_stat_progress_vacuum AS
1227 SELECT
1228 S.pid AS pid, S.datid AS datid, D.datname AS datname,
1229 S.relid AS relid,
1230 CASE S.param1 WHEN 0 THEN 'initializing'
1231 WHEN 1 THEN 'scanning heap'
1232 WHEN 2 THEN 'vacuuming indexes'
1233 WHEN 3 THEN 'vacuuming heap'
1234 WHEN 4 THEN 'cleaning up indexes'
1235 WHEN 5 THEN 'truncating heap'
1236 WHEN 6 THEN 'performing final cleanup'
1237 END AS phase,
1238 S.param2 AS heap_blks_total, S.param3 AS heap_blks_scanned,
1239 S.param4 AS heap_blks_vacuumed, S.param5 AS index_vacuum_count,
1240 S.param6 AS max_dead_tuple_bytes, S.param7 AS dead_tuple_bytes,
1241 S.param8 AS num_dead_item_ids, S.param9 AS indexes_total,
1242 S.param10 AS indexes_processed,
1243 S.param11 / 1000000::double precision AS delay_time
1244 FROM pg_stat_get_progress_info('VACUUM') AS S
1245 LEFT JOIN pg_database D ON S.datid = D.oid;
1246
1247 CREATE VIEW pg_stat_progress_cluster AS
1248 SELECT
1249 S.pid AS pid,
1250 S.datid AS datid,
1251 D.datname AS datname,
1252 S.relid AS relid,
1253 CASE S.param1 WHEN 1 THEN 'CLUSTER'
1254 WHEN 2 THEN 'VACUUM FULL'
1255 END AS command,
1256 CASE S.param2 WHEN 0 THEN 'initializing'
1257 WHEN 1 THEN 'seq scanning heap'
1258 WHEN 2 THEN 'index scanning heap'
1259 WHEN 3 THEN 'sorting tuples'
1260 WHEN 4 THEN 'writing new heap'
1261 WHEN 5 THEN 'swapping relation files'
1262 WHEN 6 THEN 'rebuilding index'
1263 WHEN 7 THEN 'performing final cleanup'
1264 END AS phase,
1265 CAST(S.param3 AS oid) AS cluster_index_relid,
1266 S.param4 AS heap_tuples_scanned,
1267 S.param5 AS heap_tuples_written,
1268 S.param6 AS heap_blks_total,
1269 S.param7 AS heap_blks_scanned,
1270 S.param8 AS index_rebuild_count
1271 FROM pg_stat_get_progress_info('CLUSTER') AS S
1272 LEFT JOIN pg_database D ON S.datid = D.oid;
1273
1274 CREATE VIEW pg_stat_progress_create_index AS
1275 SELECT
1276 S.pid AS pid, S.datid AS datid, D.datname AS datname,
1277 S.relid AS relid,
1278 CAST(S.param7 AS oid) AS index_relid,
1279 CASE S.param1 WHEN 1 THEN 'CREATE INDEX'
1280 WHEN 2 THEN 'CREATE INDEX CONCURRENTLY'
1281 WHEN 3 THEN 'REINDEX'
1282 WHEN 4 THEN 'REINDEX CONCURRENTLY'
1283 END AS command,
1284 CASE S.param10 WHEN 0 THEN 'initializing'
1285 WHEN 1 THEN 'waiting for writers before build'
1286 WHEN 2 THEN 'building index' ||
1287 COALESCE((': ' || pg_indexam_progress_phasename(S.param9::oid, S.param11)),
1288 '')
1289 WHEN 3 THEN 'waiting for writers before validation'
1290 WHEN 4 THEN 'index validation: scanning index'
1291 WHEN 5 THEN 'index validation: sorting tuples'
1292 WHEN 6 THEN 'index validation: scanning table'
1293 WHEN 7 THEN 'waiting for old snapshots'
1294 WHEN 8 THEN 'waiting for readers before marking dead'
1295 WHEN 9 THEN 'waiting for readers before dropping'
1296 END as phase,
1297 S.param4 AS lockers_total,
1298 S.param5 AS lockers_done,
1299 S.param6 AS current_locker_pid,
1300 S.param16 AS blocks_total,
1301 S.param17 AS blocks_done,
1302 S.param12 AS tuples_total,
1303 S.param13 AS tuples_done,
1304 S.param14 AS partitions_total,
1305 S.param15 AS partitions_done
1306 FROM pg_stat_get_progress_info('CREATE INDEX') AS S
1307 LEFT JOIN pg_database D ON S.datid = D.oid;
1308
1309 CREATE VIEW pg_stat_progress_basebackup AS
1310 SELECT
1311 S.pid AS pid,
1312 CASE S.param1 WHEN 0 THEN 'initializing'
1313 WHEN 1 THEN 'waiting for checkpoint to finish'
1314 WHEN 2 THEN 'estimating backup size'
1315 WHEN 3 THEN 'streaming database files'
1316 WHEN 4 THEN 'waiting for wal archiving to finish'
1317 WHEN 5 THEN 'transferring wal files'
1318 END AS phase,
1319 CASE S.param2 WHEN -1 THEN NULL ELSE S.param2 END AS backup_total,
1320 S.param3 AS backup_streamed,
1321 S.param4 AS tablespaces_total,
1322 S.param5 AS tablespaces_streamed
1323 FROM pg_stat_get_progress_info('BASEBACKUP') AS S;
1324
1325
1326 CREATE VIEW pg_stat_progress_copy AS
1327 SELECT
1328 S.pid AS pid, S.datid AS datid, D.datname AS datname,
1329 S.relid AS relid,
1330 CASE S.param5 WHEN 1 THEN 'COPY FROM'
1331 WHEN 2 THEN 'COPY TO'
1332 END AS command,
1333 CASE S.param6 WHEN 1 THEN 'FILE'
1334 WHEN 2 THEN 'PROGRAM'
1335 WHEN 3 THEN 'PIPE'
1336 WHEN 4 THEN 'CALLBACK'
1337 END AS "type",
1338 S.param1 AS bytes_processed,
1339 S.param2 AS bytes_total,
1340 S.param3 AS tuples_processed,
1341 S.param4 AS tuples_excluded,
1342 S.param7 AS tuples_skipped
1343 FROM pg_stat_get_progress_info('COPY') AS S
1344 LEFT JOIN pg_database D ON S.datid = D.oid;
1345
1346 CREATE VIEW pg_user_mappings AS
1347 SELECT
1348 U.oid AS umid,
1349 S.oid AS srvid,
1350 S.srvname AS srvname,
1351 U.umuser AS umuser,
1352 CASE WHEN U.umuser = 0 THEN
1353 'public'
1354 ELSE
1355 A.rolname
1356 END AS usename,
1357 CASE WHEN (U.umuser <> 0 AND A.rolname = current_user
1358 AND (pg_has_role(S.srvowner, 'USAGE')
1359 OR has_server_privilege(S.oid, 'USAGE')))
1360 OR (U.umuser = 0 AND pg_has_role(S.srvowner, 'USAGE'))
1361 OR (SELECT rolsuper FROM pg_authid WHERE rolname = current_user)
1362 THEN U.umoptions
1363 ELSE NULL END AS umoptions
1364 FROM pg_user_mapping U
1365 JOIN pg_foreign_server S ON (U.umserver = S.oid)
1366 LEFT JOIN pg_authid A ON (A.oid = U.umuser);
1367
1368 REVOKE ALL ON pg_user_mapping FROM public;
1369
1370 CREATE VIEW pg_replication_origin_status AS
1371 SELECT *
1372 FROM pg_show_replication_origin_status();
1373
1374 REVOKE ALL ON pg_replication_origin_status FROM public;
1375
1376 -- All columns of pg_subscription except subconninfo are publicly readable.
1377 REVOKE ALL ON pg_subscription FROM public;
1378 GRANT SELECT (oid, subdbid, subskiplsn, subname, subowner, subenabled,
1379 subbinary, substream, subtwophasestate, subdisableonerr,
1380 subpasswordrequired, subrunasowner, subfailover,
1381 subslotname, subsynccommit, subpublications, suborigin)
1382 ON pg_subscription TO public;
1383
1384 CREATE VIEW pg_stat_subscription_stats AS
1385 SELECT
1386 ss.subid,
1387 s.subname,
1388 ss.apply_error_count,
1389 ss.sync_error_count,
1390 ss.confl_insert_exists,
1391 ss.confl_update_origin_differs,
1392 ss.confl_update_exists,
1393 ss.confl_update_missing,
1394 ss.confl_delete_origin_differs,
1395 ss.confl_delete_missing,
1396 ss.confl_multiple_unique_conflicts,
1397 ss.stats_reset
1398 FROM pg_subscription as s,
1399 pg_stat_get_subscription_stats(s.oid) as ss;
1400
1401 CREATE VIEW pg_wait_events AS
1402 SELECT * FROM pg_get_wait_events();
1403
1404 CREATE VIEW pg_aios AS
1405 SELECT * FROM pg_get_aios();
1406 REVOKE ALL ON pg_aios FROM PUBLIC;
1407 GRANT SELECT ON pg_aios TO pg_read_all_stats;
1408 REVOKE EXECUTE ON FUNCTION pg_get_aios() FROM PUBLIC;
1409 GRANT EXECUTE ON FUNCTION pg_get_aios() TO pg_read_all_stats;