aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--contrib/oid2name/oid2name.c9
-rw-r--r--src/backend/catalog/catalog.c13
-rw-r--r--src/backend/catalog/namespace.c80
-rw-r--r--src/backend/catalog/system_views.sql32
-rw-r--r--src/backend/catalog/toasting.c22
-rw-r--r--src/backend/storage/lmgr/lmgr.c4
-rw-r--r--src/backend/utils/cache/relcache.c6
-rw-r--r--src/bin/psql/describe.c9
-rw-r--r--src/include/catalog/catversion.h4
-rw-r--r--src/include/catalog/namespace.h5
-rw-r--r--src/test/regress/expected/rules.out20
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);