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

Commit3b78826

Browse files
committed
Make the standard system views schema-aware.
I did not force an initdb via catversion ... but the rulesregression test will fail until you do an initdb.
1 parent5e82513 commit3b78826

File tree

2 files changed

+68
-59
lines changed

2 files changed

+68
-59
lines changed

‎src/bin/initdb/initdb.sh

Lines changed: 48 additions & 39 deletions
Original file line numberDiff line numberDiff line change
@@ -27,7 +27,7 @@
2727
# Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
2828
# Portions Copyright (c) 1994, Regents of the University of California
2929
#
30-
# $Header: /cvsroot/pgsql/src/bin/initdb/Attic/initdb.sh,v 1.164 2002/08/04 06:26:38 thomas Exp $
30+
# $Header: /cvsroot/pgsql/src/bin/initdb/Attic/initdb.sh,v 1.165 2002/08/08 19:39:05 tgl Exp $
3131
#
3232
#-------------------------------------------------------------------------
3333

@@ -803,26 +803,29 @@ CREATE VIEW pg_views AS \
803803
804804
CREATE VIEW pg_tables AS\
805805
SELECT\
806+
N.nspname AS schemaname,\
806807
C.relname AS tablename,\
807808
pg_get_userbyid(C.relowner) AS tableowner,\
808809
C.relhasindex AS hasindexes,\
809810
C.relhasrules AS hasrules,\
810811
(C.reltriggers > 0) AS hastriggers\
811-
FROM pg_class C\
812+
FROM pg_class CLEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)\
812813
WHERE C.relkind IN ('r', 's');
813814
814815
CREATE VIEW pg_indexes AS\
815816
SELECT\
817+
N.nspname AS schemaname,\
816818
C.relname AS tablename,\
817819
I.relname AS indexname,\
818-
pg_get_indexdef(X.indexrelid) AS indexdef\
819-
FROM pg_index X,pg_class C, pg_class I\
820-
WHERE C.relkind = 'r' AND I.relkind ='i'\
821-
AND C.oid =X.indrelid\
822-
AND I.oid =X.indexrelid;
820+
pg_get_indexdef(I.oid) AS indexdef\
821+
FROM pg_index X JOINpg_class C ON (C.oid = X.indrelid)\
822+
JOIN pg_class I ON (I.oid =X.indexrelid)\
823+
LEFT JOIN pg_namespace N ON (N.oid =C.relnamespace)\
824+
WHERE C.relkind = 'r' AND I.relkind ='i';
823825
824826
CREATE VIEW pg_stats AS\
825827
SELECT\
828+
nspname AS schemaname,\
826829
relname AS tablename,\
827830
attname AS attname,\
828831
stanullfrac AS null_frac,\
@@ -852,16 +855,17 @@ CREATE VIEW pg_stats AS \
852855
WHEN stakind3 THEN stanumbers3[1]\
853856
WHEN stakind4 THEN stanumbers4[1]\
854857
END AS correlation\
855-
FROM pg_class c, pg_attribute a, pg_statistic s\
856-
WHEREc.oid =s.starelid ANDc.oid =a.attrelid\
857-
AND a.attnum =s.staattnum\
858-
AND has_table_privilege(c.oid, 'select');
858+
FROMpg_statistic s JOINpg_class c ON (c.oid = s.starelid)\
859+
JOIN pg_attribute a ON (c.oid =attrelid ANDattnum =s.staattnum)\
860+
LEFT JOIN pg_namespace n ON (n.oid =c.relnamespace)\
861+
WHERE has_table_privilege(c.oid, 'select');
859862
860863
REVOKE ALL on pg_statistic FROM public;
861864
862865
CREATE VIEW pg_stat_all_tables AS\
863866
SELECT\
864867
C.oid AS relid,\
868+
N.nspname AS schemaname,\
865869
C.relname AS relname,\
866870
pg_stat_get_numscans(C.oid) AS seq_scan,\
867871
pg_stat_get_tuples_returned(C.oid) AS seq_tup_read,\
@@ -870,22 +874,24 @@ CREATE VIEW pg_stat_all_tables AS \
870874
pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins,\
871875
pg_stat_get_tuples_updated(C.oid) AS n_tup_upd,\
872876
pg_stat_get_tuples_deleted(C.oid) AS n_tup_del\
873-
FROM pg_class C LEFTOUTERJOIN\
877+
FROM pg_class C LEFT JOIN\
874878
pg_index I ON C.oid = I.indrelid\
879+
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)\
875880
WHERE C.relkind = 'r'\
876-
GROUP BY C.oid, C.relname;
881+
GROUP BY C.oid,N.nspname,C.relname;
877882
878883
CREATE VIEW pg_stat_sys_tables AS\
879884
SELECT * FROM pg_stat_all_tables\
880-
WHERErelname ~ '^pg_';
885+
WHEREschemaname IN ('pg_catalog', 'pg_toast');
881886
882887
CREATE VIEW pg_stat_user_tables AS\
883888
SELECT * FROM pg_stat_all_tables\
884-
WHERErelname !~ '^pg_';
889+
WHEREschemaname NOT IN ('pg_catalog', 'pg_toast');
885890
886891
CREATE VIEW pg_statio_all_tables AS\
887892
SELECT\
888893
C.oid AS relid,\
894+
N.nspname AS schemaname,\
889895
C.relname AS relname,\
890896
pg_stat_get_blocks_fetched(C.oid) -\
891897
pg_stat_get_blocks_hit(C.oid) AS heap_blks_read,\
@@ -899,86 +905,89 @@ CREATE VIEW pg_statio_all_tables AS \
899905
pg_stat_get_blocks_fetched(X.oid) -\
900906
pg_stat_get_blocks_hit(X.oid) AS tidx_blks_read,\
901907
pg_stat_get_blocks_hit(X.oid) AS tidx_blks_hit\
902-
FROM pg_class C LEFTOUTERJOIN\
903-
pg_index I ON C.oid = I.indrelid LEFTOUTERJOIN\
904-
pg_class T ON C.reltoastrelid = T.oid LEFTOUTERJOIN\
908+
FROM pg_class C LEFT JOIN\
909+
pg_index I ON C.oid = I.indrelid LEFT JOIN\
910+
pg_class T ON C.reltoastrelid = T.oid LEFT JOIN\
905911
pg_class X ON T.reltoastidxid = X.oid\
912+
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)\
906913
WHERE C.relkind = 'r'\
907-
GROUP BY C.oid, C.relname, T.oid, X.oid;
914+
GROUP BY C.oid,N.nspname,C.relname, T.oid, X.oid;
908915
909916
CREATE VIEW pg_statio_sys_tables AS\
910917
SELECT * FROM pg_statio_all_tables\
911-
WHERErelname ~ '^pg_';
918+
WHEREschemaname IN ('pg_catalog', 'pg_toast');
912919
913920
CREATE VIEW pg_statio_user_tables AS\
914921
SELECT * FROM pg_statio_all_tables\
915-
WHERErelname !~ '^pg_';
922+
WHEREschemaname NOT IN ('pg_catalog', 'pg_toast');
916923
917924
CREATE VIEW pg_stat_all_indexes AS\
918925
SELECT\
919926
C.oid AS relid,\
920927
I.oid AS indexrelid,\
928+
N.nspname AS schemaname,\
921929
C.relname AS relname,\
922930
I.relname AS indexrelname,\
923931
pg_stat_get_numscans(I.oid) AS idx_scan,\
924932
pg_stat_get_tuples_returned(I.oid) AS idx_tup_read,\
925933
pg_stat_get_tuples_fetched(I.oid) AS idx_tup_fetch\
926-
FROM pg_class C,\
927-
pg_class I,\
928-
pg_index X\
929-
WHERE C.relkind = 'r' AND\
930-
X.indrelid = C.oid AND\
931-
X.indexrelid = I.oid;
934+
FROM pg_class C JOIN\
935+
pg_index X ON C.oid = X.indrelid JOIN\
936+
pg_class I ON I.oid = X.indexrelid\
937+
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)\
938+
WHERE C.relkind = 'r';
932939
933940
CREATE VIEW pg_stat_sys_indexes AS\
934941
SELECT * FROM pg_stat_all_indexes\
935-
WHERErelname ~ '^pg_';
942+
WHEREschemaname IN ('pg_catalog', 'pg_toast');
936943
937944
CREATE VIEW pg_stat_user_indexes AS\
938945
SELECT * FROM pg_stat_all_indexes\
939-
WHERErelname !~ '^pg_';
946+
WHEREschemaname NOT IN ('pg_catalog', 'pg_toast');
940947
941948
CREATE VIEW pg_statio_all_indexes AS\
942949
SELECT\
943950
C.oid AS relid,\
944951
I.oid AS indexrelid,\
952+
N.nspname AS schemaname,\
945953
C.relname AS relname,\
946954
I.relname AS indexrelname,\
947955
pg_stat_get_blocks_fetched(I.oid) -\
948956
pg_stat_get_blocks_hit(I.oid) AS idx_blks_read,\
949957
pg_stat_get_blocks_hit(I.oid) AS idx_blks_hit\
950-
FROM pg_class C,\
951-
pg_class I,\
952-
pg_index X\
953-
WHERE C.relkind = 'r' AND\
954-
X.indrelid = C.oid AND\
955-
X.indexrelid = I.oid;
958+
FROM pg_class C JOIN\
959+
pg_index X ON C.oid = X.indrelid JOIN\
960+
pg_class I ON I.oid = X.indexrelid\
961+
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)\
962+
WHERE C.relkind = 'r';
956963
957964
CREATE VIEW pg_statio_sys_indexes AS\
958965
SELECT * FROM pg_statio_all_indexes\
959-
WHERErelname ~ '^pg_';
966+
WHEREschemaname IN ('pg_catalog', 'pg_toast');
960967
961968
CREATE VIEW pg_statio_user_indexes AS\
962969
SELECT * FROM pg_statio_all_indexes\
963-
WHERErelname !~ '^pg_';
970+
WHEREschemaname NOT IN ('pg_catalog', 'pg_toast');
964971
965972
CREATE VIEW pg_statio_all_sequences AS\
966973
SELECT\
967974
C.oid AS relid,\
975+
N.nspname AS schemaname,\
968976
C.relname AS relname,\
969977
pg_stat_get_blocks_fetched(C.oid) -\
970978
pg_stat_get_blocks_hit(C.oid) AS blks_read,\
971979
pg_stat_get_blocks_hit(C.oid) AS blks_hit\
972980
FROM pg_class C\
981+
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)\
973982
WHERE C.relkind = 'S';
974983
975984
CREATE VIEW pg_statio_sys_sequences AS\
976985
SELECT * FROM pg_statio_all_sequences\
977-
WHERErelname ~ '^pg_';
986+
WHEREschemaname IN ('pg_catalog', 'pg_toast');
978987
979988
CREATE VIEW pg_statio_user_sequences AS\
980989
SELECT * FROM pg_statio_all_sequences\
981-
WHERErelname !~ '^pg_';
990+
WHEREschemaname NOT IN ('pg_catalog', 'pg_toast');
982991
983992
CREATE VIEW pg_stat_activity AS\
984993
SELECT\

0 commit comments

Comments
 (0)

[8]ページ先頭

©2009-2025 Movatter.jp