Skip to content

Commit 3b78826

Browse files
tglsfdctglsfdc
committed
Make the standard system views schema-aware.
I did not force an initdb via catversion ... but the rules regression test will fail until you do an initdb.
1 parent 5e82513 commit 3b78826

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 C LEFT 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 JOIN pg_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-
WHERE c.oid = s.starelid AND c.oid = a.attrelid \
857-
AND a.attnum = s.staattnum \
858-
AND has_table_privilege(c.oid, 'select');
858+
FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid) \
859+
JOIN pg_attribute a ON (c.oid = attrelid AND attnum = 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 LEFT OUTER JOIN \
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-
WHERE relname ~ '^pg_';
885+
WHERE schemaname IN ('pg_catalog', 'pg_toast');
881886
882887
CREATE VIEW pg_stat_user_tables AS \
883888
SELECT * FROM pg_stat_all_tables \
884-
WHERE relname !~ '^pg_';
889+
WHERE schemaname 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 LEFT OUTER JOIN \
903-
pg_index I ON C.oid = I.indrelid LEFT OUTER JOIN \
904-
pg_class T ON C.reltoastrelid = T.oid LEFT OUTER JOIN \
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-
WHERE relname ~ '^pg_';
918+
WHERE schemaname IN ('pg_catalog', 'pg_toast');
912919
913920
CREATE VIEW pg_statio_user_tables AS \
914921
SELECT * FROM pg_statio_all_tables \
915-
WHERE relname !~ '^pg_';
922+
WHERE schemaname 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-
WHERE relname ~ '^pg_';
942+
WHERE schemaname IN ('pg_catalog', 'pg_toast');
936943
937944
CREATE VIEW pg_stat_user_indexes AS \
938945
SELECT * FROM pg_stat_all_indexes \
939-
WHERE relname !~ '^pg_';
946+
WHERE schemaname 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-
WHERE relname ~ '^pg_';
966+
WHERE schemaname IN ('pg_catalog', 'pg_toast');
960967
961968
CREATE VIEW pg_statio_user_indexes AS \
962969
SELECT * FROM pg_statio_all_indexes \
963-
WHERE relname !~ '^pg_';
970+
WHERE schemaname 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-
WHERE relname ~ '^pg_';
986+
WHERE schemaname IN ('pg_catalog', 'pg_toast');
978987
979988
CREATE VIEW pg_statio_user_sequences AS \
980989
SELECT * FROM pg_statio_all_sequences \
981-
WHERE relname !~ '^pg_';
990+
WHERE schemaname NOT IN ('pg_catalog', 'pg_toast');
982991
983992
CREATE VIEW pg_stat_activity AS \
984993
SELECT \

0 commit comments

Comments
 (0)