| 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; |