- 论坛徽章:
- 1
|
编译安装通过了,但是执行初始化的时候出错了。
initdb -D${HOME}/data
错误信息如下:- The files belonging to this database system will be owned by user "test".
- This user must also own the server process.
- The database cluster will be initialized with locale "en_US".
- The default database encoding has accordingly been set to "LATIN1".
- The default text search configuration will be set to "english".
- Data page checksums are disabled.
- fixing permissions on existing directory /home/zprogctl/data ... ok
- creating subdirectories ... ok
- selecting default max_connections ... 100
- selecting default shared_buffers ... 128MB
- creating configuration files ... ok
- creating template1 database in /home/zprogctl/data/base/1 ... ok
- initializing pg_authid ... ok
- initializing dependencies ... ok
- creating system views ... FATAL: column "?column?" specified more than once
- STATEMENT: /*
- * PostgreSQL System Views
- *
- * Copyright (c) 1996-2013, PostgreSQL Global Development Group
- *
- * src/backend/catalog/system_views.sql
- */
- CREATE VIEW pg_roles AS
- SELECT
- rolname,
- rolsuper,
- rolinherit,
- rolcreaterole,
- rolcreatedb,
- rolcatupdate,
- rolcanlogin,
- rolreplication,
- rolconnlimit,
- '********'::text as rolpassword,
- rolvaliduntil,
- setconfig as rolconfig,
- pg_authid.oid
- FROM pg_authid LEFT JOIN pg_db_role_setting s
- ON (pg_authid.oid = setrole AND setdatabase = 0);
- CREATE VIEW pg_shadow AS
- SELECT
- rolname AS usename,
- pg_authid.oid AS usesysid,
- rolcreatedb AS usecreatedb,
- rolsuper AS usesuper,
- rolcatupdate AS usecatupd,
- rolreplication AS userepl,
- rolpassword AS passwd,
- rolvaliduntil::abstime AS valuntil,
- setconfig AS useconfig
- FROM pg_authid LEFT JOIN pg_db_role_setting s
- ON (pg_authid.oid = setrole AND setdatabase = 0)
- WHERE rolcanlogin;
- REVOKE ALL on pg_shadow FROM public;
- CREATE VIEW pg_group AS
- SELECT
- rolname AS groname,
- oid AS grosysid,
- ARRAY(SELECT member FROM pg_auth_members WHERE roleid = oid) AS grolist
- FROM pg_authid
- WHERE NOT rolcanlogin;
- CREATE VIEW pg_user AS
- SELECT
- usename,
- usesysid,
- usecreatedb,
- usesuper,
- usecatupd,
- userepl,
- '********'::text as passwd,
- valuntil,
- useconfig
- FROM pg_shadow;
- CREATE VIEW pg_rules AS
- SELECT
- N.nspname AS schemaname,
- C.relname AS tablename,
- R.rulename AS rulename,
- pg_get_ruledef(R.oid) AS definition
- FROM (pg_rewrite R JOIN pg_class C ON (C.oid = R.ev_class))
- LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
- WHERE R.rulename != '_RETURN';
- CREATE VIEW pg_views AS
- SELECT
- N.nspname AS schemaname,
- C.relname AS viewname,
- pg_get_userbyid(C.relowner) AS viewowner,
- pg_get_viewdef(C.oid) AS definition
- FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
- WHERE C.relkind = 'v';
- CREATE VIEW pg_tables AS
- SELECT
- N.nspname AS schemaname,
- C.relname AS tablename,
- pg_get_userbyid(C.relowner) AS tableowner,
- T.spcname AS tablespace,
- C.relhasindex AS hasindexes,
- C.relhasrules AS hasrules,
- C.relhastriggers AS hastriggers
- FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
- LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace)
- WHERE C.relkind = 'r';
- CREATE VIEW pg_matviews AS
- SELECT
- N.nspname AS schemaname,
- C.relname AS matviewname,
- pg_get_userbyid(C.relowner) AS matviewowner,
- T.spcname AS tablespace,
- C.relhasindex AS hasindexes,
- C.relispopulated AS ispopulated,
- pg_get_viewdef(C.oid) AS definition
- FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
- LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace)
- WHERE C.relkind = 'm';
- CREATE VIEW pg_indexes AS
- SELECT
- N.nspname AS schemaname,
- C.relname AS tablename,
- I.relname AS indexname,
- T.spcname AS tablespace,
- pg_get_indexdef(I.oid) AS indexdef
- FROM pg_index X JOIN pg_class C ON (C.oid = X.indrelid)
- JOIN pg_class I ON (I.oid = X.indexrelid)
- LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
- LEFT JOIN pg_tablespace T ON (T.oid = I.reltablespace)
- WHERE C.relkind IN ('r', 'm') AND I.relkind = 'i';
- CREATE VIEW pg_stats AS
- SELECT
- nspname AS schemaname,
- relname AS tablename,
- attname AS attname,
- stainherit AS inherited,
- stanullfrac AS null_frac,
- stawidth AS avg_width,
- stadistinct AS n_distinct,
- CASE
- WHEN stakind1 = 1 THEN stavalues1
- WHEN stakind2 = 1 THEN stavalues2
- WHEN stakind3 = 1 THEN stavalues3
- WHEN stakind4 = 1 THEN stavalues4
- WHEN stakind5 = 1 THEN stavalues5
- END AS most_common_vals,
- CASE
- WHEN stakind1 = 1 THEN stanumbers1
- WHEN stakind2 = 1 THEN stanumbers2
- WHEN stakind3 = 1 THEN stanumbers3
- WHEN stakind4 = 1 THEN stanumbers4
- WHEN stakind5 = 1 THEN stanumbers5
- END AS most_common_freqs,
- CASE
- WHEN stakind1 = 2 THEN stavalues1
- WHEN stakind2 = 2 THEN stavalues2
- WHEN stakind3 = 2 THEN stavalues3
- WHEN stakind4 = 2 THEN stavalues4
- WHEN stakind5 = 2 THEN stavalues5
- END AS histogram_bounds,
- CASE
- WHEN stakind1 = 3 THEN stanumbers1[1]
- WHEN stakind2 = 3 THEN stanumbers2[1]
- WHEN stakind3 = 3 THEN stanumbers3[1]
- WHEN stakind4 = 3 THEN stanumbers4[1]
- WHEN stakind5 = 3 THEN stanumbers5[1]
- END AS correlation,
- CASE
- WHEN stakind1 = 4 THEN stavalues1
- WHEN stakind2 = 4 THEN stavalues2
- WHEN stakind3 = 4 THEN stavalues3
- WHEN stakind4 = 4 THEN stavalues4
- WHEN stakind5 = 4 THEN stavalues5
- END AS most_common_elems,
- CASE
- WHEN stakind1 = 4 THEN stanumbers1
- WHEN stakind2 = 4 THEN stanumbers2
- WHEN stakind3 = 4 THEN stanumbers3
- WHEN stakind4 = 4 THEN stanumbers4
- WHEN stakind5 = 4 THEN stanumbers5
- END AS most_common_elem_freqs,
- CASE
- WHEN stakind1 = 5 THEN stanumbers1
- WHEN stakind2 = 5 THEN stanumbers2
- WHEN stakind3 = 5 THEN stanumbers3
- WHEN stakind4 = 5 THEN stanumbers4
- WHEN stakind5 = 5 THEN stanumbers5
- END AS elem_count_histogram
- FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid)
- JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum)
- LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
- WHERE NOT attisdropped AND has_column_privilege(c.oid, a.attnum, 'select');
- REVOKE ALL on pg_statistic FROM public;
- CREATE VIEW pg_locks AS
- SELECT * FROM pg_lock_status() AS L;
- CREATE VIEW pg_cursors AS
- SELECT * FROM pg_cursor() AS C;
- CREATE VIEW pg_available_extensions AS
- SELECT E.name, E.default_version, X.extversion AS installed_version,
- E.comment
- FROM pg_available_extensions() AS E
- LEFT JOIN pg_extension AS X ON E.name = X.extname;
- CREATE VIEW pg_available_extension_versions AS
- SELECT E.name, E.version, (X.extname IS NOT NULL) AS installed,
- E.superuser, E.relocatable, E.schema, E.requires, E.comment
- FROM pg_available_extension_versions() AS E
- LEFT JOIN pg_extension AS X
- ON E.name = X.extname AND E.version = X.extversion;
- CREATE VIEW pg_prepared_xacts AS
- SELECT P.transaction, P.gid, P.prepared,
- U.rolname AS owner, D.datname AS database
- FROM pg_prepared_xact() AS P
- LEFT JOIN pg_authid U ON P.ownerid = U.oid
- LEFT JOIN pg_database D ON P.dbid = D.oid;
- CREATE VIEW pg_prepared_statements AS
- SELECT * FROM pg_prepared_statement() AS P;
- CREATE VIEW pg_seclabels AS
- SELECT
- l.objoid, l.classoid, l.objsubid,
- CASE WHEN rel.relkind = 'r' THEN 'table'::text
- WHEN rel.relkind = 'v' THEN 'view'::text
- WHEN rel.relkind = 'm' THEN 'materialized view'::text
- WHEN rel.relkind = 'S' THEN 'sequence'::text
- WHEN rel.relkind = 'f' THEN 'foreign table'::text END AS objtype,
- rel.relnamespace AS objnamespace,
- CASE WHEN pg_table_is_visible(rel.oid)
- THEN quote_ident(rel.relname)
- ELSE quote_ident(nsp.nspname) || '.' || quote_ident(rel.relname)
- END AS objname,
- l.provider, l.label
- FROM
- pg_seclabel l
- JOIN pg_class rel ON l.classoid = rel.tableoid AND l.objoid = rel.oid
- JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid
- WHERE
- l.objsubid = 0
- UNION ALL
- SELECT
- l.objoid, l.classoid, l.objsubid,
- 'column'::text AS objtype,
- rel.relnamespace AS objnamespace,
- CASE WHEN pg_table_is_visible(rel.oid)
- THEN quote_ident(rel.relname)
- ELSE quote_ident(nsp.nspname) || '.' || quote_ident(rel.relname)
- END || '.' || att.attname AS objname,
- l.provider, l.label
- FROM
- pg_seclabel l
- JOIN pg_class rel ON l.classoid = rel.tableoid AND l.objoid = rel.oid
- JOIN pg_attribute att
- ON rel.oid = att.attrelid AND l.objsubid = att.attnum
- JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid
- WHERE
- l.objsubid != 0
- UNION ALL
- SELECT
- l.objoid, l.classoid, l.objsubid,
- CASE WHEN pro.proisagg = true THEN 'aggregate'::text
- WHEN pro.proisagg = false THEN 'function'::text
- END AS objtype,
- pro.pronamespace AS objnamespace,
- CASE WHEN pg_function_is_visible(pro.oid)
- THEN quote_ident(pro.proname)
- ELSE quote_ident(nsp.nspname) || '.' || quote_ident(pro.proname)
- END || '(' || pg_catalog.pg_get_function_arguments(pro.oid) || ')' AS objname,
- l.provider, l.label
- FROM
- pg_seclabel l
- JOIN pg_proc pro ON l.classoid = pro.tableoid AND l.objoid = pro.oid
- JOIN pg_namespace nsp ON pro.pronamespace = nsp.oid
- WHERE
- l.objsubid = 0
- UNION ALL
- SELECT
- l.objoid, l.classoid, l.objsubid,
- CASE WHEN typ.typtype = 'd' THEN 'domain'::text
- ELSE 'type'::text END AS objtype,
- typ.typnamespace AS objnamespace,
- CASE WHEN pg_type_is_visible(typ.oid)
- THEN quote_ident(typ.typname)
- ELSE quote_ident(nsp.nspname) || '.' || quote_ident(typ.typname)
- END AS objname,
- l.provider, l.label
- FROM
- pg_seclabel l
- JOIN pg_type typ ON l.classoid = typ.tableoid AND l.objoid = typ.oid
- JOIN pg_namespace nsp ON typ.typnamespace = nsp.oid
- WHERE
- l.objsubid = 0
- UNION ALL
- SELECT
- l.objoid, l.classoid, l.objsubid,
- 'large object'::text AS objtype,
- NULL::oid AS objnamespace,
- l.objoid::text AS objname,
- l.provider, l.label
- FROM
- pg_seclabel l
- JOIN pg_largeobject_metadata lom ON l.objoid = lom.oid
- WHERE
- l.classoid = 'pg_catalog.pg_largeobject'::regclass AND l.objsubid = 0
- UNION ALL
- SELECT
- l.objoid, l.classoid, l.objsubid,
- 'language'::text AS objtype,
- NULL::oid AS objnamespace,
- quote_ident(lan.lanname) AS objname,
- l.provider, l.label
- FROM
- pg_seclabel l
- JOIN pg_language lan ON l.classoid = lan.tableoid AND l.objoid = lan.oid
- WHERE
- l.objsubid = 0
- UNION ALL
- SELECT
- l.objoid, l.classoid, l.objsubid,
- 'schema'::text AS objtype,
- nsp.oid AS objnamespace,
- quote_ident(nsp.nspname) AS objname,
- l.provider, l.label
- FROM
- pg_seclabel l
- JOIN pg_namespace nsp ON l.classoid = nsp.tableoid AND l.objoid = nsp.oid
- WHERE
- l.objsubid = 0
- UNION ALL
- SELECT
- l.objoid, l.classoid, l.objsubid,
- 'event trigger'::text AS objtype,
- NULL::oid AS objnamespace,
- quote_ident(evt.evtname) AS objname,
- l.provider, l.label
- FROM
- pg_seclabel l
- JOIN pg_event_trigger evt ON l.classoid = evt.tableoid
- AND l.objoid = evt.oid
- WHERE
- l.objsubid = 0
- UNION ALL
- SELECT
- l.objoid, l.classoid, 0::int4 AS objsubid,
- 'database'::text AS objtype,
- NULL::oid AS objnamespace,
- quote_ident(dat.datname) AS objname,
- l.provider, l.label
- FROM
- pg_shseclabel l
- JOIN pg_database dat ON l.classoid = dat.tableoid AND l.objoid = dat.oid
- UNION ALL
- SELECT
- l.objoid, l.classoid, 0::int4 AS objsubid,
- 'tablespace'::text AS objtype,
- NULL::oid AS objnamespace,
- quote_ident(spc.spcname) AS objname,
- l.provider, l.label
- FROM
- pg_shseclabel l
- JOIN pg_tablespace spc ON l.classoid = spc.tableoid AND l.objoid = spc.oid
- UNION ALL
- SELECT
- l.objoid, l.classoid, 0::int4 AS objsubid,
- 'role'::text AS objtype,
- NULL::oid AS objnamespace,
- quote_ident(rol.rolname) AS objname,
- l.provider, l.label
- FROM
- pg_shseclabel l
- JOIN pg_authid rol ON l.classoid = rol.tableoid AND l.objoid = rol.oid;
- CREATE VIEW pg_settings AS
- SELECT * FROM pg_show_all_settings() AS A;
- CREATE RULE pg_settings_u AS
- ON UPDATE TO pg_settings
- WHERE new.name = old.name DO
- SELECT set_config(old.name, new.setting, 'f');
- CREATE RULE pg_settings_n AS
- ON UPDATE TO pg_settings
- DO INSTEAD NOTHING;
- GRANT SELECT, UPDATE ON pg_settings TO PUBLIC;
- CREATE VIEW pg_timezone_abbrevs AS
- SELECT * FROM pg_timezone_abbrevs();
- CREATE VIEW pg_timezone_names AS
- SELECT * FROM pg_timezone_names();
- -- Statistics views
- CREATE VIEW pg_stat_all_tables AS
- SELECT
- C.oid AS relid,
- N.nspname AS schemaname,
- C.relname AS relname,
- pg_stat_get_numscans(C.oid) AS seq_scan,
- pg_stat_get_tuples_returned(C.oid) AS seq_tup_read,
- sum(pg_stat_get_numscans(I.indexrelid))::bigint AS idx_scan,
- sum(pg_stat_get_tuples_fetched(I.indexrelid))::bigint +
- pg_stat_get_tuples_fetched(C.oid) AS idx_tup_fetch,
- pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins,
- pg_stat_get_tuples_updated(C.oid) AS n_tup_upd,
- pg_stat_get_tuples_deleted(C.oid) AS n_tup_del,
- pg_stat_get_tuples_hot_updated(C.oid) AS n_tup_hot_upd,
- pg_stat_get_live_tuples(C.oid) AS n_live_tup,
- pg_stat_get_dead_tuples(C.oid) AS n_dead_tup,
- pg_stat_get_last_vacuum_time(C.oid) as last_vacuum,
- pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum,
- pg_stat_get_last_analyze_time(C.oid) as last_analyze,
- pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze,
- pg_stat_get_vacuum_count(C.oid) AS vacuum_count,
- pg_stat_get_autovacuum_count(C.oid) AS autovacuum_count,
- pg_stat_get_analyze_count(C.oid) AS analyze_count,
- pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count
- FROM pg_class C LEFT JOIN
- pg_index I ON C.oid = I.indrelid
- LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
- WHERE C.relkind IN ('r', 't', 'm')
- GROUP BY C.oid, N.nspname, C.relname;
- CREATE VIEW pg_stat_xact_all_tables AS
- SELECT
- C.oid AS relid,
- N.nspname AS schemaname,
- C.relname AS relname,
- pg_stat_get_xact_numscans(C.oid) AS seq_scan,
- pg_stat_get_xact_tuples_returned(C.oid) AS seq_tup_read,
- sum(pg_stat_get_xact_numscans(I.indexrelid))::bigint AS idx_scan,
- sum(pg_stat_get_xact_tuples_fetched(I.indexrelid))::bigint +
- pg_stat_get_xact_tuples_fetched(C.oid) AS idx_tup_fetch,
- pg_stat_get_xact_tuples_inserted(C.oid) AS n_tup_ins,
- pg_stat_get_xact_tuples_updated(C.oid) AS n_tup_upd,
- pg_stat_get_xact_tuples_deleted(C.oid) AS n_tup_del,
- pg_stat_get_xact_tuples_hot_updated(C.oid) AS n_tup_hot_upd
- FROM pg_class C LEFT JOIN
- pg_index I ON C.oid = I.indrelid
- LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
- WHERE C.relkind IN ('r', 't', 'm')
- GROUP BY C.oid, N.nspname, C.relname;
- CREATE VIEW pg_stat_sys_tables AS
- SELECT * FROM pg_stat_all_tables
- WHERE schemaname IN ('pg_catalog', 'information_schema') OR
- schemaname ~ '^pg_toast';
- CREATE VIEW pg_stat_xact_sys_tables AS
- SELECT * FROM pg_stat_xact_all_tables
- WHERE schemaname IN ('pg_catalog', 'information_schema') OR
- schemaname ~ '^pg_toast';
- CREATE VIEW pg_stat_user_tables AS
- SELECT * FROM pg_stat_all_tables
- WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
- schemaname !~ '^pg_toast';
- CREATE VIEW pg_stat_xact_user_tables AS
- SELECT * FROM pg_stat_xact_all_tables
- WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
- schemaname !~ '^pg_toast';
- CREATE VIEW pg_statio_all_tables AS
- SELECT
- C.oid AS relid,
- N.nspname AS schemaname,
- C.relname AS relname,
- pg_stat_get_blocks_fetched(C.oid) -
- pg_stat_get_blocks_hit(C.oid) AS heap_blks_read,
- pg_stat_get_blocks_hit(C.oid) AS heap_blks_hit,
- sum(pg_stat_get_blocks_fetched(I.indexrelid) -
- pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_read,
- sum(pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_hit,
- pg_stat_get_blocks_fetched(T.oid) -
- pg_stat_get_blocks_hit(T.oid) AS toast_blks_read,
- pg_stat_get_blocks_hit(T.oid) AS toast_blks_hit,
- pg_stat_get_blocks_fetched(X.oid) -
- pg_stat_get_blocks_hit(X.oid) AS tidx_blks_read,
- pg_stat_get_blocks_hit(X.oid) AS tidx_blks_hit
- FROM pg_class C LEFT JOIN
- pg_index I ON C.oid = I.indrelid LEFT JOIN
- pg_class T ON C.reltoastrelid = T.oid LEFT JOIN
- pg_class X ON T.reltoastidxid = X.oid
- LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
- WHERE C.relkind IN ('r', 't', 'm')
- GROUP BY C.oid, N.nspname, C.relname, T.oid, X.oid;
- CREATE VIEW pg_statio_sys_tables AS
- SELECT * FROM pg_statio_all_tables
- WHERE schemaname IN ('pg_catalog', 'information_schema') OR
- schemaname ~ '^pg_toast';
- CREATE VIEW pg_statio_user_tables AS
- SELECT * FROM pg_statio_all_tables
- WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
- schemaname !~ '^pg_toast';
- CREATE VIEW pg_stat_all_indexes AS
- SELECT
- C.oid AS relid,
- I.oid AS indexrelid,
- N.nspname AS schemaname,
- C.relname AS relname,
- I.relname AS indexrelname,
- pg_stat_get_numscans(I.oid) AS idx_scan,
- pg_stat_get_tuples_returned(I.oid) AS idx_tup_read,
- pg_stat_get_tuples_fetched(I.oid) AS idx_tup_fetch
- FROM pg_class C JOIN
- pg_index X ON C.oid = X.indrelid JOIN
- pg_class I ON I.oid = X.indexrelid
- LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
- WHERE C.relkind IN ('r', 't', 'm');
- CREATE VIEW pg_stat_sys_indexes AS
- SELECT * FROM pg_stat_all_indexes
- WHERE schemaname IN ('pg_catalog', 'information_schema') OR
- schemaname ~ '^pg_toast';
- CREATE VIEW pg_stat_user_indexes AS
- SELECT * FROM pg_stat_all_indexes
- WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
- schemaname !~ '^pg_toast';
- CREATE VIEW pg_statio_all_indexes AS
- SELECT
- C.oid AS relid,
- I.oid AS indexrelid,
- N.nspname AS schemaname,
- C.relname AS relname,
- I.relname AS indexrelname,
- pg_stat_get_blocks_fetched(I.oid) -
- pg_stat_get_blocks_hit(I.oid) AS idx_blks_read,
- pg_stat_get_blocks_hit(I.oid) AS idx_blks_hit
- FROM pg_class C JOIN
- pg_index X ON C.oid = X.indrelid JOIN
- pg_class I ON I.oid = X.indexrelid
- LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
- WHERE C.relkind IN ('r', 't', 'm');
- CREATE VIEW pg_statio_sys_indexes AS
- SELECT * FROM pg_statio_all_indexes
- WHERE schemaname IN ('pg_catalog', 'information_schema') OR
- schemaname ~ '^pg_toast';
- CREATE VIEW pg_statio_user_indexes AS
- SELECT * FROM pg_statio_all_indexes
- WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
- schemaname !~ '^pg_toast';
- CREATE VIEW pg_statio_all_sequences AS
- SELECT
- C.oid AS relid,
- N.nspname AS schemaname,
- C.relname AS relname,
- pg_stat_get_blocks_fetched(C.oid) -
- pg_stat_get_blocks_hit(C.oid) AS blks_read,
- pg_stat_get_blocks_hit(C.oid) AS blks_hit
- FROM pg_class C
- LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
- WHERE C.relkind = 'S';
- CREATE VIEW pg_statio_sys_sequences AS
- SELECT * FROM pg_statio_all_sequences
- WHERE schemaname IN ('pg_catalog', 'information_schema') OR
- schemaname ~ '^pg_toast';
- CREATE VIEW pg_statio_user_sequences AS
- SELECT * FROM pg_statio_all_sequences
- WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
- schemaname !~ '^pg_toast';
- CREATE VIEW pg_stat_activity AS
- SELECT
- S.datid AS datid,
- D.datname AS datname,
- S.pid,
- S.usesysid,
- U.rolname AS usename,
- S.application_name,
- S.client_addr,
- S.client_hostname,
- S.client_port,
- S.backend_start,
- S.xact_start,
- S.query_start,
- S.state_change,
- S.waiting,
- S.state,
- S.query
- FROM pg_database D, pg_stat_get_activity(NULL) AS S, pg_authid U
- WHERE S.datid = D.oid AND
- S.usesysid = U.oid;
- CREATE VIEW pg_stat_replication AS
- SELECT
- S.pid,
- S.usesysid,
- U.rolname AS usename,
- S.application_name,
- S.client_addr,
- S.client_hostname,
- S.client_port,
- S.backend_start,
- W.state,
- W.sent_location,
- W.write_location,
- W.flush_location,
- W.replay_location,
- W.sync_priority,
- W.sync_state
- FROM pg_stat_get_activity(NULL) AS S, pg_authid U,
- pg_stat_get_wal_senders() AS W
- WHERE S.usesysid = U.oid AND
- S.pid = W.pid;
- CREATE VIEW pg_stat_database AS
- SELECT
- D.oid AS datid,
- D.datname AS datname,
- pg_stat_get_db_numbackends(D.oid) AS numbackends,
- pg_stat_get_db_xact_commit(D.oid) AS xact_commit,
- pg_stat_get_db_xact_rollback(D.oid) AS xact_rollback,
- pg_stat_get_db_blocks_fetched(D.oid) -
- pg_stat_get_db_blocks_hit(D.oid) AS blks_read,
- pg_stat_get_db_blocks_hit(D.oid) AS blks_hit,
- pg_stat_get_db_tuples_returned(D.oid) AS tup_returned,
- pg_stat_get_db_tuples_fetched(D.oid) AS tup_fetched,
- pg_stat_get_db_tuples_inserted(D.oid) AS tup_inserted,
- pg_stat_get_db_tuples_updated(D.oid) AS tup_updated,
- pg_stat_get_db_tuples_deleted(D.oid) AS tup_deleted,
- pg_stat_get_db_conflict_all(D.oid) AS conflicts,
- pg_stat_get_db_temp_files(D.oid) AS temp_files,
- pg_stat_get_db_temp_bytes(D.oid) AS temp_bytes,
- pg_stat_get_db_deadlocks(D.oid) AS deadlocks,
- pg_stat_get_db_blk_read_time(D.oid) AS blk_read_time,
- pg_stat_get_db_blk_write_time(D.oid) AS blk_write_time,
- pg_stat_get_db_stat_reset_time(D.oid) AS stats_reset
- FROM pg_database D;
- CREATE VIEW pg_stat_database_conflicts AS
- SELECT
- D.oid AS datid,
- D.datname AS datname,
- pg_stat_get_db_conflict_tablespace(D.oid) AS confl_tablespace,
- pg_stat_get_db_conflict_lock(D.oid) AS confl_lock,
- pg_stat_get_db_conflict_snapshot(D.oid) AS confl_snapshot,
- pg_stat_get_db_conflict_bufferpin(D.oid) AS confl_bufferpin,
- pg_stat_get_db_conflict_startup_deadlock(D.oid) AS confl_deadlock
- FROM pg_database D;
- CREATE VIEW pg_stat_user_functions AS
- SELECT
- P.oid AS funcid,
- N.nspname AS schemaname,
- P.proname AS funcname,
- pg_stat_get_function_calls(P.oid) AS calls,
- pg_stat_get_function_total_time(P.oid) AS total_time,
- pg_stat_get_function_self_time(P.oid) AS self_time
- FROM pg_proc P LEFT JOIN pg_namespace N ON (N.oid = P.pronamespace)
- WHERE P.prolang != 12 -- fast check to eliminate built-in functions
- AND pg_stat_get_function_calls(P.oid) IS NOT NULL;
- CREATE VIEW pg_stat_xact_user_functions AS
- SELECT
- P.oid AS funcid,
- N.nspname AS schemaname,
- P.proname AS funcname,
- pg_stat_get_xact_function_calls(P.oid) AS calls,
- pg_stat_get_xact_function_total_time(P.oid) AS total_time,
- pg_stat_get_xact_function_self_time(P.oid) AS self_time
- FROM pg_proc P LEFT JOIN pg_namespace N ON (N.oid = P.pronamespace)
- WHERE P.prolang != 12 -- fast check to eliminate built-in functions
- AND pg_stat_get_xact_function_calls(P.oid) IS NOT NULL;
- CREATE VIEW pg_stat_bgwriter AS
- SELECT
- pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,
- pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req,
- pg_stat_get_checkpoint_write_time() AS checkpoint_write_time,
- pg_stat_get_checkpoint_sync_time() AS checkpoint_sync_time,
- pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint,
- pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,
- pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
- pg_stat_get_buf_written_backend() AS buffers_backend,
- pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync,
- pg_stat_get_buf_alloc() AS buffers_alloc,
- pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
- CREATE VIEW pg_user_mappings AS
- SELECT
- U.oid AS umid,
- S.oid AS srvid,
- S.srvname AS srvname,
- U.umuser AS umuser,
- CASE WHEN U.umuser = 0 THEN
- 'public'
- ELSE
- A.rolname
- END AS usename,
- CASE WHEN pg_has_role(S.srvowner, 'USAGE') OR has_server_privilege(S.oid, 'USAGE') THEN
- U.umoptions
- ELSE
- NULL
- END AS umoptions
- FROM pg_user_mapping U
- LEFT JOIN pg_authid A ON (A.oid = U.umuser) JOIN
- pg_foreign_server S ON (U.umserver = S.oid);
- REVOKE ALL on pg_user_mapping FROM public;
- --
- -- We have a few function definitions in here, too.
- -- At some point there might be enough to justify breaking them out into
- -- a separate "system_functions.sql" file.
- --
- -- Tsearch debug function. Defined here because it'd be pretty unwieldy
- -- to put it into pg_proc.h
- CREATE FUNCTION ts_debug(IN config regconfig, IN document text,
- OUT alias text,
- OUT description text,
- OUT token text,
- OUT dictionaries regdictionary[],
- OUT dictionary regdictionary,
- OUT lexemes text[])
- RETURNS SETOF record AS
- $
- SELECT
- tt.alias AS alias,
- tt.description AS description,
- parse.token AS token,
- ARRAY ( SELECT m.mapdict::pg_catalog.regdictionary
- FROM pg_catalog.pg_ts_config_map AS m
- WHERE m.mapcfg = $1 AND m.maptokentype = parse.tokid
- ORDER BY m.mapseqno )
- AS dictionaries,
- ( SELECT mapdict::pg_catalog.regdictionary
- FROM pg_catalog.pg_ts_config_map AS m
- WHERE m.mapcfg = $1 AND m.maptokentype = parse.tokid
- ORDER BY pg_catalog.ts_lexize(mapdict, parse.token) IS NULL, m.mapseqno
- LIMIT 1
- ) AS dictionary,
- ( SELECT pg_catalog.ts_lexize(mapdict, parse.token)
- FROM pg_catalog.pg_ts_config_map AS m
- WHERE m.mapcfg = $1 AND m.maptokentype = parse.tokid
- ORDER BY pg_catalog.ts_lexize(mapdict, parse.token) IS NULL, m.mapseqno
- LIMIT 1
- ) AS lexemes
- FROM pg_catalog.ts_parse(
- (SELECT cfgparser FROM pg_catalog.pg_ts_config WHERE oid = $1 ), $2
- ) AS parse,
- pg_catalog.ts_token_type(
- (SELECT cfgparser FROM pg_catalog.pg_ts_config WHERE oid = $1 )
- ) AS tt
- WHERE tt.tokid = parse.tokid
- $
- LANGUAGE SQL STRICT STABLE;
- COMMENT ON FUNCTION ts_debug(regconfig,text) IS
- 'debug function for text search configuration';
- CREATE FUNCTION ts_debug(IN document text,
- OUT alias text,
- OUT description text,
- OUT token text,
- OUT dictionaries regdictionary[],
- OUT dictionary regdictionary,
- OUT lexemes text[])
- RETURNS SETOF record AS
- $
- SELECT * FROM pg_catalog.ts_debug( pg_catalog.get_current_ts_config(), $1);
- $
- LANGUAGE SQL STRICT STABLE;
- COMMENT ON FUNCTION ts_debug(text) IS
- 'debug function for current text search configuration';
- --
- -- Redeclare built-in functions that need default values attached to their
- -- arguments. It's impractical to set those up directly in pg_proc.h because
- -- of the complexity and platform-dependency of the expression tree
- -- representation. (Note that internal functions still have to have entries
- -- in pg_proc.h; we are merely causing their proargnames and proargdefaults
- -- to get filled in.)
- --
- CREATE OR REPLACE FUNCTION
- pg_start_backup(label text, fast boolean DEFAULT false)
- RETURNS text STRICT VOLATILE LANGUAGE internal AS 'pg_start_backup';
- CREATE OR REPLACE FUNCTION
- json_populate_record(base anyelement, from_json json, use_json_as_text boolean DEFAULT false)
- RETURNS anyelement LANGUAGE internal STABLE AS 'json_populate_record';
- CREATE OR REPLACE FUNCTION
- json_populate_recordset(base anyelement, from_json json, use_json_as_text boolean DEFAULT false)
- RETURNS SETOF anyelement LANGUAGE internal STABLE ROWS 100 AS 'json_populate_recordset';
- child process exited with exit code 1
复制代码 |
|