diff options
| -rw-r--r-- | contrib/oid2name/oid2name.c | 9 | ||||
| -rw-r--r-- | src/backend/catalog/catalog.c | 13 | ||||
| -rw-r--r-- | src/backend/catalog/namespace.c | 80 | ||||
| -rw-r--r-- | src/backend/catalog/system_views.sql | 32 | ||||
| -rw-r--r-- | src/backend/catalog/toasting.c | 22 | ||||
| -rw-r--r-- | src/backend/storage/lmgr/lmgr.c | 4 | ||||
| -rw-r--r-- | src/backend/utils/cache/relcache.c | 6 | ||||
| -rw-r--r-- | src/bin/psql/describe.c | 9 | ||||
| -rw-r--r-- | src/include/catalog/catversion.h | 4 | ||||
| -rw-r--r-- | src/include/catalog/namespace.h | 5 | ||||
| -rw-r--r-- | src/test/regress/expected/rules.out | 20 |
11 files changed, 148 insertions, 56 deletions
diff --git a/contrib/oid2name/oid2name.c b/contrib/oid2name/oid2name.c index 691b2f28d19..35779798875 100644 --- a/contrib/oid2name/oid2name.c +++ b/contrib/oid2name/oid2name.c @@ -4,7 +4,7 @@ * * Originally by * B. Palmer, bpalmer@crimelabs.net 1-17-2001 - * $PostgreSQL: pgsql/contrib/oid2name/oid2name.c,v 1.31 2007/07/15 22:54:20 tgl Exp $ + * $PostgreSQL: pgsql/contrib/oid2name/oid2name.c,v 1.32 2007/07/25 22:16:17 tgl Exp $ */ #include "postgres_fe.h" @@ -411,7 +411,7 @@ sql_exec_dumpalltables(PGconn *conn, struct options * opts) " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace " " LEFT JOIN pg_catalog.pg_database d ON d.datname = current_database()," " pg_catalog.pg_tablespace t " - "WHERE relkind IN ('r'%s) AND " + "WHERE relkind IN ('r'%s%s) AND " " %s" " t.oid = CASE" " WHEN reltablespace <> 0 THEN reltablespace" @@ -419,8 +419,9 @@ sql_exec_dumpalltables(PGconn *conn, struct options * opts) " END " "ORDER BY relname", opts->extended ? addfields : "", - opts->indexes ? ", 'i', 'S', 't'" : "", - opts->systables ? "" : "n.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema') AND"); + opts->indexes ? ", 'i', 'S'" : "", + opts->systables ? ", 't'" : "", + opts->systables ? "" : "n.nspname NOT IN ('pg_catalog', 'information_schema') AND n.nspname !~ '^pg_toast' AND"); sql_exec(conn, todo, opts->quiet); } diff --git a/src/backend/catalog/catalog.c b/src/backend/catalog/catalog.c index 049d216a787..acd107958ff 100644 --- a/src/backend/catalog/catalog.c +++ b/src/backend/catalog/catalog.c @@ -10,7 +10,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/catalog/catalog.c,v 1.70 2007/03/25 19:45:14 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/catalog/catalog.c,v 1.71 2007/07/25 22:16:18 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -24,6 +24,7 @@ #include "access/transam.h" #include "catalog/catalog.h" #include "catalog/indexing.h" +#include "catalog/namespace.h" #include "catalog/pg_auth_members.h" #include "catalog/pg_authid.h" #include "catalog/pg_database.h" @@ -196,15 +197,17 @@ IsSystemNamespace(Oid namespaceId) /* * IsToastNamespace - * True iff namespace is pg_toast. + * True iff namespace is pg_toast or my temporary-toast-table namespace. * - * NOTE: the reason this isn't a macro is to avoid having to include - * catalog/pg_namespace.h in a lot of places. + * Note: this will return false for temporary-toast-table namespaces belonging + * to other backends. Those are treated the same as other backends' regular + * temp table namespaces, and access is prevented where appropriate. */ bool IsToastNamespace(Oid namespaceId) { - return namespaceId == PG_TOAST_NAMESPACE; + return (namespaceId == PG_TOAST_NAMESPACE) || + isTempToastNamespace(namespaceId); } diff --git a/src/backend/catalog/namespace.c b/src/backend/catalog/namespace.c index 55379b66f65..2f8753bd2e0 100644 --- a/src/backend/catalog/namespace.c +++ b/src/backend/catalog/namespace.c @@ -13,7 +13,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/catalog/namespace.c,v 1.96 2007/04/20 02:37:37 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/catalog/namespace.c,v 1.97 2007/07/25 22:16:18 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -152,6 +152,9 @@ static List *overrideStack = NIL; * in a particular backend session (this happens when a CREATE TEMP TABLE * command is first executed). Thereafter it's the OID of the temp namespace. * + * myTempToastNamespace is the OID of the namespace for my temp tables' toast + * tables. It is set when myTempNamespace is, and is InvalidOid before that. + * * myTempNamespaceSubID shows whether we've created the TEMP namespace in the * current subtransaction. The flag propagates up the subtransaction tree, * so the main transaction will correctly recognize the flag if all @@ -161,6 +164,8 @@ static List *overrideStack = NIL; */ static Oid myTempNamespace = InvalidOid; +static Oid myTempToastNamespace = InvalidOid; + static SubTransactionId myTempNamespaceSubID = InvalidSubTransactionId; /* @@ -1600,8 +1605,34 @@ isTempNamespace(Oid namespaceId) } /* + * isTempToastNamespace - is the given namespace my temporary-toast-table + * namespace? + */ +bool +isTempToastNamespace(Oid namespaceId) +{ + if (OidIsValid(myTempToastNamespace) && myTempToastNamespace == namespaceId) + return true; + return false; +} + +/* + * isTempOrToastNamespace - is the given namespace my temporary-table + * namespace or my temporary-toast-table namespace? + */ +bool +isTempOrToastNamespace(Oid namespaceId) +{ + if (OidIsValid(myTempNamespace) && + (myTempNamespace == namespaceId || myTempToastNamespace == namespaceId)) + return true; + return false; +} + +/* * isAnyTempNamespace - is the given namespace a temporary-table namespace - * (either my own, or another backend's)? + * (either my own, or another backend's)? Temporary-toast-table namespaces + * are included, too. */ bool isAnyTempNamespace(Oid namespaceId) @@ -1609,29 +1640,42 @@ isAnyTempNamespace(Oid namespaceId) bool result; char *nspname; - /* If the namespace name starts with "pg_temp_", say "true" */ + /* True if the namespace name starts with "pg_temp_" or "pg_toast_temp_" */ nspname = get_namespace_name(namespaceId); if (!nspname) return false; /* no such namespace? */ - result = (strncmp(nspname, "pg_temp_", 8) == 0); + result = (strncmp(nspname, "pg_temp_", 8) == 0) || + (strncmp(nspname, "pg_toast_temp_", 14) == 0); pfree(nspname); return result; } /* * isOtherTempNamespace - is the given namespace some other backend's - * temporary-table namespace? + * temporary-table namespace (including temporary-toast-table namespaces)? */ bool isOtherTempNamespace(Oid namespaceId) { /* If it's my own temp namespace, say "false" */ - if (isTempNamespace(namespaceId)) + if (isTempOrToastNamespace(namespaceId)) return false; - /* Else, if the namespace name starts with "pg_temp_", say "true" */ + /* Else, if it's any temp namespace, say "true" */ return isAnyTempNamespace(namespaceId); } +/* + * GetTempToastNamespace - get the OID of my temporary-toast-table namespace, + * which must already be assigned. (This is only used when creating a toast + * table for a temp table, so we must have already done InitTempTableNamespace) + */ +Oid +GetTempToastNamespace(void) +{ + Assert(OidIsValid(myTempToastNamespace)); + return myTempToastNamespace; +} + /* * GetOverrideSearchPath - fetch current search path definition in form @@ -2006,6 +2050,7 @@ InitTempTableNamespace(void) { char namespaceName[NAMEDATALEN]; Oid namespaceId; + Oid toastspaceId; Assert(!OidIsValid(myTempNamespace)); @@ -2055,11 +2100,30 @@ InitTempTableNamespace(void) } /* + * If the corresponding temp-table namespace doesn't exist yet, create it. + * (We assume there is no need to clean it out if it does exist, since + * dropping a parent table should make its toast table go away.) + */ + snprintf(namespaceName, sizeof(namespaceName), "pg_toast_temp_%d", + MyBackendId); + + toastspaceId = GetSysCacheOid(NAMESPACENAME, + CStringGetDatum(namespaceName), + 0, 0, 0); + if (!OidIsValid(toastspaceId)) + { + toastspaceId = NamespaceCreate(namespaceName, BOOTSTRAP_SUPERUSERID); + /* Advance command counter to make namespace visible */ + CommandCounterIncrement(); + } + + /* * Okay, we've prepared the temp namespace ... but it's not committed yet, * so all our work could be undone by transaction rollback. Set flag for * AtEOXact_Namespace to know what to do. */ myTempNamespace = namespaceId; + myTempToastNamespace = toastspaceId; /* It should not be done already. */ AssertState(myTempNamespaceSubID == InvalidSubTransactionId); @@ -2089,6 +2153,7 @@ AtEOXact_Namespace(bool isCommit) else { myTempNamespace = InvalidOid; + myTempToastNamespace = InvalidOid; baseSearchPathValid = false; /* need to rebuild list */ } myTempNamespaceSubID = InvalidSubTransactionId; @@ -2140,6 +2205,7 @@ AtEOSubXact_Namespace(bool isCommit, SubTransactionId mySubid, myTempNamespaceSubID = InvalidSubTransactionId; /* TEMP namespace creation failed, so reset state */ myTempNamespace = InvalidOid; + myTempToastNamespace = InvalidOid; baseSearchPathValid = false; /* need to rebuild list */ } } diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index c5f8810d0af..c091c855708 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -3,7 +3,7 @@ * * Copyright (c) 1996-2007, PostgreSQL Global Development Group * - * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.38 2007/06/28 00:02:37 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.39 2007/07/25 22:16:18 tgl Exp $ */ CREATE VIEW pg_roles AS @@ -221,11 +221,13 @@ CREATE VIEW pg_stat_all_tables AS CREATE VIEW pg_stat_sys_tables AS SELECT * FROM pg_stat_all_tables - WHERE schemaname IN ('pg_catalog', 'pg_toast', 'information_schema'); + 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', 'pg_toast', 'information_schema'); + WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND + schemaname !~ '^pg_toast'; CREATE VIEW pg_statio_all_tables AS SELECT @@ -254,11 +256,13 @@ CREATE VIEW pg_statio_all_tables AS CREATE VIEW pg_statio_sys_tables AS SELECT * FROM pg_statio_all_tables - WHERE schemaname IN ('pg_catalog', 'pg_toast', 'information_schema'); + 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', 'pg_toast', 'information_schema'); + WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND + schemaname !~ '^pg_toast'; CREATE VIEW pg_stat_all_indexes AS SELECT @@ -278,11 +282,13 @@ CREATE VIEW pg_stat_all_indexes AS CREATE VIEW pg_stat_sys_indexes AS SELECT * FROM pg_stat_all_indexes - WHERE schemaname IN ('pg_catalog', 'pg_toast', 'information_schema'); + 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', 'pg_toast', 'information_schema'); + WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND + schemaname !~ '^pg_toast'; CREATE VIEW pg_statio_all_indexes AS SELECT @@ -302,11 +308,13 @@ CREATE VIEW pg_statio_all_indexes AS CREATE VIEW pg_statio_sys_indexes AS SELECT * FROM pg_statio_all_indexes - WHERE schemaname IN ('pg_catalog', 'pg_toast', 'information_schema'); + 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', 'pg_toast', 'information_schema'); + WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND + schemaname !~ '^pg_toast'; CREATE VIEW pg_statio_all_sequences AS SELECT @@ -322,11 +330,13 @@ CREATE VIEW pg_statio_all_sequences AS CREATE VIEW pg_statio_sys_sequences AS SELECT * FROM pg_statio_all_sequences - WHERE schemaname IN ('pg_catalog', 'pg_toast', 'information_schema'); + 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', 'pg_toast', 'information_schema'); + WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND + schemaname !~ '^pg_toast'; CREATE VIEW pg_stat_activity AS SELECT diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c index 463f038c0f7..2fe44f59f8c 100644 --- a/src/backend/catalog/toasting.c +++ b/src/backend/catalog/toasting.c @@ -8,7 +8,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/catalog/toasting.c,v 1.6 2007/04/06 04:21:42 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/catalog/toasting.c,v 1.7 2007/07/25 22:16:18 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -21,6 +21,7 @@ #include "catalog/heap.h" #include "catalog/index.h" #include "catalog/indexing.h" +#include "catalog/namespace.h" #include "catalog/pg_namespace.h" #include "catalog/pg_opclass.h" #include "catalog/pg_type.h" @@ -108,6 +109,7 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid) Relation class_rel; Oid toast_relid; Oid toast_idxid; + Oid namespaceid; char toast_relname[NAMEDATALEN]; char toast_idxname[NAMEDATALEN]; IndexInfo *indexInfo; @@ -173,16 +175,20 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid) tupdesc->attrs[2]->attstorage = 'p'; /* - * Note: the toast relation is placed in the regular pg_toast namespace - * even if its master relation is a temp table. There cannot be any - * naming collision, and the toast rel will be destroyed when its master - * is, so there's no need to handle the toast rel as temp. - * + * Toast tables for regular relations go in pg_toast; those for temp + * relations go into the per-backend temp-toast-table namespace. + */ + if (rel->rd_istemp) + namespaceid = GetTempToastNamespace(); + else + namespaceid = PG_TOAST_NAMESPACE; + + /* * XXX would it make sense to apply the master's reloptions to the toast - * table? + * table? Or maybe some toast-specific reloptions? */ toast_relid = heap_create_with_catalog(toast_relname, - PG_TOAST_NAMESPACE, + namespaceid, rel->rd_rel->reltablespace, toastOid, rel->rd_rel->relowner, diff --git a/src/backend/storage/lmgr/lmgr.c b/src/backend/storage/lmgr/lmgr.c index 6f6c609f53d..1c5db363203 100644 --- a/src/backend/storage/lmgr/lmgr.c +++ b/src/backend/storage/lmgr/lmgr.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/storage/lmgr/lmgr.c,v 1.91 2007/06/19 20:13:21 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/storage/lmgr/lmgr.c,v 1.92 2007/07/25 22:16:18 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -619,7 +619,7 @@ LockTagIsTemp(const LOCKTAG *tag) /* field1 is dboid, field2 is reloid for all of these */ if ((Oid) tag->locktag_field1 == InvalidOid) return false; /* shared, so not temp */ - if (isTempNamespace(get_rel_namespace((Oid) tag->locktag_field2))) + if (isTempOrToastNamespace(get_rel_namespace((Oid) tag->locktag_field2))) return true; break; case LOCKTAG_TRANSACTION: diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index 45cb103adee..f69fb0c9362 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/cache/relcache.c,v 1.261 2007/05/27 03:50:39 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/utils/cache/relcache.c,v 1.262 2007/07/25 22:16:18 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -838,7 +838,7 @@ RelationBuildDesc(Oid targetRelId, Relation oldrelation) relation->rd_isnailed = false; relation->rd_createSubid = InvalidSubTransactionId; relation->rd_newRelfilenodeSubid = InvalidSubTransactionId; - relation->rd_istemp = isTempNamespace(relation->rd_rel->relnamespace); + relation->rd_istemp = isTempOrToastNamespace(relation->rd_rel->relnamespace); /* * initialize the tuple descriptor (relation->rd_att). @@ -2315,7 +2315,7 @@ RelationBuildLocalRelation(const char *relname, need_eoxact_work = true; /* is it a temporary relation? */ - rel->rd_istemp = isTempNamespace(relnamespace); + rel->rd_istemp = isTempOrToastNamespace(relnamespace); /* * create a new tuple descriptor from the one passed in. We do this diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 041a1c14b63..6dd156af4af 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -3,7 +3,7 @@ * * Copyright (c) 2000-2007, PostgreSQL Global Development Group * - * $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.156 2007/06/28 06:40:16 neilc Exp $ + * $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.157 2007/07/25 22:16:18 tgl Exp $ */ #include "postgres_fe.h" #include "describe.h" @@ -1688,9 +1688,12 @@ listTables(const char *tabtypes, const char *pattern, bool verbose) * pg_catalog and pg_toast. (We don't want to hide temp tables though.) */ if (showSystem) - appendPQExpBuffer(&buf, " AND n.nspname = 'pg_catalog'\n"); + appendPQExpBuffer(&buf, + " AND n.nspname = 'pg_catalog'\n"); else - appendPQExpBuffer(&buf, " AND n.nspname NOT IN ('pg_catalog', 'pg_toast')\n"); + appendPQExpBuffer(&buf, + " AND n.nspname <> 'pg_catalog'\n" + " AND n.nspname !~ '^pg_toast'\n"); processSQLNamePattern(pset.db, &buf, pattern, true, false, "n.nspname", "c.relname", NULL, diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index e85988be2ac..e2bd4ec316d 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -37,7 +37,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.414 2007/07/06 04:15:59 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.415 2007/07/25 22:16:18 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 200707051 +#define CATALOG_VERSION_NO 200707251 #endif diff --git a/src/include/catalog/namespace.h b/src/include/catalog/namespace.h index 0050c122a77..f937a729060 100644 --- a/src/include/catalog/namespace.h +++ b/src/include/catalog/namespace.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/namespace.h,v 1.47 2007/04/12 22:34:45 neilc Exp $ + * $PostgreSQL: pgsql/src/include/catalog/namespace.h,v 1.48 2007/07/25 22:16:18 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -79,8 +79,11 @@ extern char *NameListToString(List *names); extern char *NameListToQuotedString(List *names); extern bool isTempNamespace(Oid namespaceId); +extern bool isTempToastNamespace(Oid namespaceId); +extern bool isTempOrToastNamespace(Oid namespaceId); extern bool isAnyTempNamespace(Oid namespaceId); extern bool isOtherTempNamespace(Oid namespaceId); +extern Oid GetTempToastNamespace(void); extern void ResetTempTableNamespace(void); extern OverrideSearchPath *GetOverrideSearchPath(MemoryContext context); diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index ebdd4bd9c79..39e27a74924 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1294,19 +1294,19 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.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_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 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 = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname; pg_stat_bgwriter | SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, 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_database | SELECT d.oid AS datid, d.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 FROM pg_database d; - pg_stat_sys_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch FROM pg_stat_all_indexes WHERE (pg_stat_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'pg_toast'::name, 'information_schema'::name])); - pg_stat_sys_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze FROM pg_stat_all_tables WHERE (pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'pg_toast'::name, 'information_schema'::name])); - pg_stat_user_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch FROM pg_stat_all_indexes WHERE (pg_stat_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'pg_toast'::name, 'information_schema'::name])); - pg_stat_user_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze FROM pg_stat_all_tables WHERE (pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'pg_toast'::name, 'information_schema'::name])); + pg_stat_sys_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch FROM pg_stat_all_indexes WHERE ((pg_stat_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_indexes.schemaname ~ '^pg_toast'::text)); + pg_stat_sys_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze FROM pg_stat_all_tables WHERE ((pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_tables.schemaname ~ '^pg_toast'::text)); + pg_stat_user_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch FROM pg_stat_all_indexes WHERE ((pg_stat_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_indexes.schemaname !~ '^pg_toast'::text)); + pg_stat_user_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze FROM pg_stat_all_tables WHERE ((pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text)); pg_statio_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.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 = ANY (ARRAY['r'::"char", 't'::"char"])); pg_statio_all_sequences | SELECT c.oid AS relid, n.nspname AS schemaname, c.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'::"char"); pg_statio_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.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 = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname, t.oid, x.oid; - pg_statio_sys_indexes | SELECT pg_statio_all_indexes.relid, pg_statio_all_indexes.indexrelid, pg_statio_all_indexes.schemaname, pg_statio_all_indexes.relname, pg_statio_all_indexes.indexrelname, pg_statio_all_indexes.idx_blks_read, pg_statio_all_indexes.idx_blks_hit FROM pg_statio_all_indexes WHERE (pg_statio_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'pg_toast'::name, 'information_schema'::name])); - pg_statio_sys_sequences | SELECT pg_statio_all_sequences.relid, pg_statio_all_sequences.schemaname, pg_statio_all_sequences.relname, pg_statio_all_sequences.blks_read, pg_statio_all_sequences.blks_hit FROM pg_statio_all_sequences WHERE (pg_statio_all_sequences.schemaname = ANY (ARRAY['pg_catalog'::name, 'pg_toast'::name, 'information_schema'::name])); - pg_statio_sys_tables | SELECT pg_statio_all_tables.relid, pg_statio_all_tables.schemaname, pg_statio_all_tables.relname, pg_statio_all_tables.heap_blks_read, pg_statio_all_tables.heap_blks_hit, pg_statio_all_tables.idx_blks_read, pg_statio_all_tables.idx_blks_hit, pg_statio_all_tables.toast_blks_read, pg_statio_all_tables.toast_blks_hit, pg_statio_all_tables.tidx_blks_read, pg_statio_all_tables.tidx_blks_hit FROM pg_statio_all_tables WHERE (pg_statio_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'pg_toast'::name, 'information_schema'::name])); - pg_statio_user_indexes | SELECT pg_statio_all_indexes.relid, pg_statio_all_indexes.indexrelid, pg_statio_all_indexes.schemaname, pg_statio_all_indexes.relname, pg_statio_all_indexes.indexrelname, pg_statio_all_indexes.idx_blks_read, pg_statio_all_indexes.idx_blks_hit FROM pg_statio_all_indexes WHERE (pg_statio_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'pg_toast'::name, 'information_schema'::name])); - pg_statio_user_sequences | SELECT pg_statio_all_sequences.relid, pg_statio_all_sequences.schemaname, pg_statio_all_sequences.relname, pg_statio_all_sequences.blks_read, pg_statio_all_sequences.blks_hit FROM pg_statio_all_sequences WHERE (pg_statio_all_sequences.schemaname <> ALL (ARRAY['pg_catalog'::name, 'pg_toast'::name, 'information_schema'::name])); - pg_statio_user_tables | SELECT pg_statio_all_tables.relid, pg_statio_all_tables.schemaname, pg_statio_all_tables.relname, pg_statio_all_tables.heap_blks_read, pg_statio_all_tables.heap_blks_hit, pg_statio_all_tables.idx_blks_read, pg_statio_all_tables.idx_blks_hit, pg_statio_all_tables.toast_blks_read, pg_statio_all_tables.toast_blks_hit, pg_statio_all_tables.tidx_blks_read, pg_statio_all_tables.tidx_blks_hit FROM pg_statio_all_tables WHERE (pg_statio_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'pg_toast'::name, 'information_schema'::name])); + pg_statio_sys_indexes | SELECT pg_statio_all_indexes.relid, pg_statio_all_indexes.indexrelid, pg_statio_all_indexes.schemaname, pg_statio_all_indexes.relname, pg_statio_all_indexes.indexrelname, pg_statio_all_indexes.idx_blks_read, pg_statio_all_indexes.idx_blks_hit FROM pg_statio_all_indexes WHERE ((pg_statio_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_indexes.schemaname ~ '^pg_toast'::text)); + pg_statio_sys_sequences | SELECT pg_statio_all_sequences.relid, pg_statio_all_sequences.schemaname, pg_statio_all_sequences.relname, pg_statio_all_sequences.blks_read, pg_statio_all_sequences.blks_hit FROM pg_statio_all_sequences WHERE ((pg_statio_all_sequences.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_sequences.schemaname ~ '^pg_toast'::text)); + pg_statio_sys_tables | SELECT pg_statio_all_tables.relid, pg_statio_all_tables.schemaname, pg_statio_all_tables.relname, pg_statio_all_tables.heap_blks_read, pg_statio_all_tables.heap_blks_hit, pg_statio_all_tables.idx_blks_read, pg_statio_all_tables.idx_blks_hit, pg_statio_all_tables.toast_blks_read, pg_statio_all_tables.toast_blks_hit, pg_statio_all_tables.tidx_blks_read, pg_statio_all_tables.tidx_blks_hit FROM pg_statio_all_tables WHERE ((pg_statio_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_tables.schemaname ~ '^pg_toast'::text)); + pg_statio_user_indexes | SELECT pg_statio_all_indexes.relid, pg_statio_all_indexes.indexrelid, pg_statio_all_indexes.schemaname, pg_statio_all_indexes.relname, pg_statio_all_indexes.indexrelname, pg_statio_all_indexes.idx_blks_read, pg_statio_all_indexes.idx_blks_hit FROM pg_statio_all_indexes WHERE ((pg_statio_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_indexes.schemaname !~ '^pg_toast'::text)); + pg_statio_user_sequences | SELECT pg_statio_all_sequences.relid, pg_statio_all_sequences.schemaname, pg_statio_all_sequences.relname, pg_statio_all_sequences.blks_read, pg_statio_all_sequences.blks_hit FROM pg_statio_all_sequences WHERE ((pg_statio_all_sequences.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_sequences.schemaname !~ '^pg_toast'::text)); + pg_statio_user_tables | SELECT pg_statio_all_tables.relid, pg_statio_all_tables.schemaname, pg_statio_all_tables.relname, pg_statio_all_tables.heap_blks_read, pg_statio_all_tables.heap_blks_hit, pg_statio_all_tables.idx_blks_read, pg_statio_all_tables.idx_blks_hit, pg_statio_all_tables.toast_blks_read, pg_statio_all_tables.toast_blks_hit, pg_statio_all_tables.tidx_blks_read, pg_statio_all_tables.tidx_blks_hit FROM pg_statio_all_tables WHERE ((pg_statio_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_tables.schemaname !~ '^pg_toast'::text)); pg_stats | SELECT n.nspname AS schemaname, c.relname AS tablename, a.attname, s.stanullfrac AS null_frac, s.stawidth AS avg_width, s.stadistinct AS n_distinct, CASE 1 WHEN s.stakind1 THEN s.stavalues1 WHEN s.stakind2 THEN s.stavalues2 WHEN s.stakind3 THEN s.stavalues3 WHEN s.stakind4 THEN s.stavalues4 ELSE NULL::anyarray END AS most_common_vals, CASE 1 WHEN s.stakind1 THEN s.stanumbers1 WHEN s.stakind2 THEN s.stanumbers2 WHEN s.stakind3 THEN s.stanumbers3 WHEN s.stakind4 THEN s.stanumbers4 ELSE NULL::real[] END AS most_common_freqs, CASE 2 WHEN s.stakind1 THEN s.stavalues1 WHEN s.stakind2 THEN s.stavalues2 WHEN s.stakind3 THEN s.stavalues3 WHEN s.stakind4 THEN s.stavalues4 ELSE NULL::anyarray END AS histogram_bounds, CASE 3 WHEN s.stakind1 THEN s.stanumbers1[1] WHEN s.stakind2 THEN s.stanumbers2[1] WHEN s.stakind3 THEN s.stanumbers3[1] WHEN s.stakind4 THEN s.stanumbers4[1] ELSE NULL::real END AS correlation FROM (((pg_statistic s JOIN pg_class c ON ((c.oid = s.starelid))) JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum)))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE has_table_privilege(c.oid, 'select'::text); pg_tables | 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.reltriggers > 0) 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'::"char"); pg_timezone_abbrevs | SELECT pg_timezone_abbrevs.abbrev, pg_timezone_abbrevs.utc_offset, pg_timezone_abbrevs.is_dst FROM pg_timezone_abbrevs() pg_timezone_abbrevs(abbrev, utc_offset, is_dst); |