Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit580fb7f

Browse files
committed
Add system_views.sql to CVS.
1 parent1060c4c commit580fb7f

File tree

1 file changed

+270
-0
lines changed

1 file changed

+270
-0
lines changed

‎src/backend/catalog/system_views.sql

Lines changed: 270 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,270 @@
1+
2+
3+
4+
CREATEVIEWpg_userAS
5+
SELECT
6+
usename,
7+
usesysid,
8+
usecreatedb,
9+
usesuper,
10+
usecatupd,
11+
'********'::textas passwd,
12+
valuntil,
13+
useconfig
14+
FROM pg_shadow;
15+
16+
CREATEVIEWpg_rulesAS
17+
SELECT
18+
N.nspnameAS schemaname,
19+
C.relnameAS tablename,
20+
R.rulenameAS rulename,
21+
pg_get_ruledef(R.oid)AS definition
22+
FROM (pg_rewrite RJOIN pg_class CON (C.oid=R.ev_class))
23+
LEFT JOIN pg_namespace NON (N.oid=C.relnamespace)
24+
WHERER.rulename!='_RETURN';
25+
26+
CREATEVIEWpg_viewsAS
27+
SELECT
28+
N.nspnameAS schemaname,
29+
C.relnameAS viewname,
30+
pg_get_userbyid(C.relowner)AS viewowner,
31+
pg_get_viewdef(C.oid)AS definition
32+
FROM pg_class CLEFT JOIN pg_namespace NON (N.oid=C.relnamespace)
33+
WHEREC.relkind='v';
34+
35+
CREATEVIEWpg_tablesAS
36+
SELECT
37+
N.nspnameAS schemaname,
38+
C.relnameAS tablename,
39+
pg_get_userbyid(C.relowner)AS tableowner,
40+
C.relhasindexAS hasindexes,
41+
C.relhasrulesAS hasrules,
42+
(C.reltriggers>0)AS hastriggers
43+
FROM pg_class CLEFT JOIN pg_namespace NON (N.oid=C.relnamespace)
44+
WHEREC.relkind='r';
45+
46+
CREATEVIEWpg_indexesAS
47+
SELECT
48+
N.nspnameAS schemaname,
49+
C.relnameAS tablename,
50+
I.relnameAS indexname,
51+
pg_get_indexdef(I.oid)AS indexdef
52+
FROM pg_index XJOIN pg_class CON (C.oid=X.indrelid)
53+
JOIN pg_class ION (I.oid=X.indexrelid)
54+
LEFT JOIN pg_namespace NON (N.oid=C.relnamespace)
55+
WHEREC.relkind='r'ANDI.relkind='i';
56+
57+
CREATEVIEWpg_statsAS
58+
SELECT
59+
nspnameAS schemaname,
60+
relnameAS tablename,
61+
attnameAS attname,
62+
stanullfracAS null_frac,
63+
stawidthAS avg_width,
64+
stadistinctAS n_distinct,
65+
CASE1
66+
WHEN stakind1 THEN stavalues1
67+
WHEN stakind2 THEN stavalues2
68+
WHEN stakind3 THEN stavalues3
69+
WHEN stakind4 THEN stavalues4
70+
ENDAS most_common_vals,
71+
CASE1
72+
WHEN stakind1 THEN stanumbers1
73+
WHEN stakind2 THEN stanumbers2
74+
WHEN stakind3 THEN stanumbers3
75+
WHEN stakind4 THEN stanumbers4
76+
ENDAS most_common_freqs,
77+
CASE2
78+
WHEN stakind1 THEN stavalues1
79+
WHEN stakind2 THEN stavalues2
80+
WHEN stakind3 THEN stavalues3
81+
WHEN stakind4 THEN stavalues4
82+
ENDAS histogram_bounds,
83+
CASE3
84+
WHEN stakind1 THEN stanumbers1[1]
85+
WHEN stakind2 THEN stanumbers2[1]
86+
WHEN stakind3 THEN stanumbers3[1]
87+
WHEN stakind4 THEN stanumbers4[1]
88+
ENDAS correlation
89+
FROM pg_statistic sJOIN pg_class cON (c.oid=s.starelid)
90+
JOIN pg_attribute aON (c.oid= attrelidAND attnum=s.staattnum)
91+
LEFT JOIN pg_namespace nON (n.oid=c.relnamespace)
92+
WHERE has_table_privilege(c.oid,'select');
93+
94+
REVOKE ALLon pg_statisticFROM public;
95+
96+
CREATEVIEWpg_stat_all_tablesAS
97+
SELECT
98+
C.oidAS relid,
99+
N.nspnameAS schemaname,
100+
C.relnameAS relname,
101+
pg_stat_get_numscans(C.oid)AS seq_scan,
102+
pg_stat_get_tuples_returned(C.oid)AS seq_tup_read,
103+
sum(pg_stat_get_numscans(I.indexrelid))AS idx_scan,
104+
sum(pg_stat_get_tuples_fetched(I.indexrelid))AS idx_tup_fetch,
105+
pg_stat_get_tuples_inserted(C.oid)AS n_tup_ins,
106+
pg_stat_get_tuples_updated(C.oid)AS n_tup_upd,
107+
pg_stat_get_tuples_deleted(C.oid)AS n_tup_del
108+
FROM pg_class CLEFT JOIN
109+
pg_index IONC.oid=I.indrelid
110+
LEFT JOIN pg_namespace NON (N.oid=C.relnamespace)
111+
WHEREC.relkind='r'
112+
GROUP BYC.oid,N.nspname,C.relname;
113+
114+
CREATEVIEWpg_stat_sys_tablesAS
115+
SELECT*FROM pg_stat_all_tables
116+
WHERE schemanameIN ('pg_catalog','pg_toast');
117+
118+
CREATEVIEWpg_stat_user_tablesAS
119+
SELECT*FROM pg_stat_all_tables
120+
WHERE schemaname NOTIN ('pg_catalog','pg_toast');
121+
122+
CREATEVIEWpg_statio_all_tablesAS
123+
SELECT
124+
C.oidAS relid,
125+
N.nspnameAS schemaname,
126+
C.relnameAS relname,
127+
pg_stat_get_blocks_fetched(C.oid)-
128+
pg_stat_get_blocks_hit(C.oid)AS heap_blks_read,
129+
pg_stat_get_blocks_hit(C.oid)AS heap_blks_hit,
130+
sum(pg_stat_get_blocks_fetched(I.indexrelid)-
131+
pg_stat_get_blocks_hit(I.indexrelid))AS idx_blks_read,
132+
sum(pg_stat_get_blocks_hit(I.indexrelid))AS idx_blks_hit,
133+
pg_stat_get_blocks_fetched(T.oid)-
134+
pg_stat_get_blocks_hit(T.oid)AS toast_blks_read,
135+
pg_stat_get_blocks_hit(T.oid)AS toast_blks_hit,
136+
pg_stat_get_blocks_fetched(X.oid)-
137+
pg_stat_get_blocks_hit(X.oid)AS tidx_blks_read,
138+
pg_stat_get_blocks_hit(X.oid)AS tidx_blks_hit
139+
FROM pg_class CLEFT JOIN
140+
pg_index IONC.oid=I.indrelidLEFT JOIN
141+
pg_class TONC.reltoastrelid=T.oidLEFT JOIN
142+
pg_class XONT.reltoastidxid=X.oid
143+
LEFT JOIN pg_namespace NON (N.oid=C.relnamespace)
144+
WHEREC.relkind='r'
145+
GROUP BYC.oid,N.nspname,C.relname,T.oid,X.oid;
146+
147+
CREATEVIEWpg_statio_sys_tablesAS
148+
SELECT*FROM pg_statio_all_tables
149+
WHERE schemanameIN ('pg_catalog','pg_toast');
150+
151+
CREATEVIEWpg_statio_user_tablesAS
152+
SELECT*FROM pg_statio_all_tables
153+
WHERE schemaname NOTIN ('pg_catalog','pg_toast');
154+
155+
CREATEVIEWpg_stat_all_indexesAS
156+
SELECT
157+
C.oidAS relid,
158+
I.oidAS indexrelid,
159+
N.nspnameAS schemaname,
160+
C.relnameAS relname,
161+
I.relnameAS indexrelname,
162+
pg_stat_get_numscans(I.oid)AS idx_scan,
163+
pg_stat_get_tuples_returned(I.oid)AS idx_tup_read,
164+
pg_stat_get_tuples_fetched(I.oid)AS idx_tup_fetch
165+
FROM pg_class CJOIN
166+
pg_index XONC.oid=X.indrelidJOIN
167+
pg_class IONI.oid=X.indexrelid
168+
LEFT JOIN pg_namespace NON (N.oid=C.relnamespace)
169+
WHEREC.relkind='r';
170+
171+
CREATEVIEWpg_stat_sys_indexesAS
172+
SELECT*FROM pg_stat_all_indexes
173+
WHERE schemanameIN ('pg_catalog','pg_toast');
174+
175+
CREATEVIEWpg_stat_user_indexesAS
176+
SELECT*FROM pg_stat_all_indexes
177+
WHERE schemaname NOTIN ('pg_catalog','pg_toast');
178+
179+
CREATEVIEWpg_statio_all_indexesAS
180+
SELECT
181+
C.oidAS relid,
182+
I.oidAS indexrelid,
183+
N.nspnameAS schemaname,
184+
C.relnameAS relname,
185+
I.relnameAS indexrelname,
186+
pg_stat_get_blocks_fetched(I.oid)-
187+
pg_stat_get_blocks_hit(I.oid)AS idx_blks_read,
188+
pg_stat_get_blocks_hit(I.oid)AS idx_blks_hit
189+
FROM pg_class CJOIN
190+
pg_index XONC.oid=X.indrelidJOIN
191+
pg_class IONI.oid=X.indexrelid
192+
LEFT JOIN pg_namespace NON (N.oid=C.relnamespace)
193+
WHEREC.relkind='r';
194+
195+
CREATEVIEWpg_statio_sys_indexesAS
196+
SELECT*FROM pg_statio_all_indexes
197+
WHERE schemanameIN ('pg_catalog','pg_toast');
198+
199+
CREATEVIEWpg_statio_user_indexesAS
200+
SELECT*FROM pg_statio_all_indexes
201+
WHERE schemaname NOTIN ('pg_catalog','pg_toast');
202+
203+
CREATEVIEWpg_statio_all_sequencesAS
204+
SELECT
205+
C.oidAS relid,
206+
N.nspnameAS schemaname,
207+
C.relnameAS relname,
208+
pg_stat_get_blocks_fetched(C.oid)-
209+
pg_stat_get_blocks_hit(C.oid)AS blks_read,
210+
pg_stat_get_blocks_hit(C.oid)AS blks_hit
211+
FROM pg_class C
212+
LEFT JOIN pg_namespace NON (N.oid=C.relnamespace)
213+
WHEREC.relkind='S';
214+
215+
CREATEVIEWpg_statio_sys_sequencesAS
216+
SELECT*FROM pg_statio_all_sequences
217+
WHERE schemanameIN ('pg_catalog','pg_toast');
218+
219+
CREATEVIEWpg_statio_user_sequencesAS
220+
SELECT*FROM pg_statio_all_sequences
221+
WHERE schemaname NOTIN ('pg_catalog','pg_toast');
222+
223+
CREATEVIEWpg_stat_activityAS
224+
SELECT
225+
D.oidAS datid,
226+
D.datnameAS datname,
227+
pg_stat_get_backend_pid(S.backendid)AS procpid,
228+
pg_stat_get_backend_userid(S.backendid)AS usesysid,
229+
U.usenameAS usename,
230+
pg_stat_get_backend_activity(S.backendid)AS current_query,
231+
pg_stat_get_backend_activity_start(S.backendid)AS query_start
232+
FROM pg_database D,
233+
(SELECT pg_stat_get_backend_idset()AS backendid)AS S,
234+
pg_shadow U
235+
WHERE pg_stat_get_backend_dbid(S.backendid)=D.oidAND
236+
pg_stat_get_backend_userid(S.backendid)=U.usesysid;
237+
238+
CREATEVIEWpg_stat_databaseAS
239+
SELECT
240+
D.oidAS datid,
241+
D.datnameAS datname,
242+
pg_stat_get_db_numbackends(D.oid)AS numbackends,
243+
pg_stat_get_db_xact_commit(D.oid)AS xact_commit,
244+
pg_stat_get_db_xact_rollback(D.oid)AS xact_rollback,
245+
pg_stat_get_db_blocks_fetched(D.oid)-
246+
pg_stat_get_db_blocks_hit(D.oid)AS blks_read,
247+
pg_stat_get_db_blocks_hit(D.oid)AS blks_hit
248+
FROM pg_database D;
249+
250+
CREATEVIEWpg_locksAS
251+
SELECT*
252+
FROM pg_lock_status()AS L(relationoid, databaseoid,
253+
transaction xid, pid int4, modetext, grantedboolean);
254+
255+
CREATEVIEWpg_settingsAS
256+
SELECT*
257+
FROM pg_show_all_settings()AS A
258+
(nametext, settingtext, contexttext, vartypetext,
259+
sourcetext, min_valtext, max_valtext);
260+
261+
CREATERULEpg_settings_uAS
262+
ONUPDATE TO pg_settings
263+
WHEREnew.name=old.name DO
264+
SELECT set_config(old.name,new.setting,'f');
265+
266+
CREATERULEpg_settings_nAS
267+
ONUPDATE TO pg_settings
268+
DO INSTEAD NOTHING;
269+
270+

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp