From 028ec5cb0f08c02ab23ca7dde301bda015fa5547 Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Wed, 26 Apr 2006 22:41:18 +0000 Subject: [PATCH] Adjust /contrib/pg_freespace to show index free space as NULL (FSM only tracks index pages, not free space on pages): 1/ Index free bytes set to NULL 2/ Comment added to the README briefly mentioning the index business 3/ Columns reordered more logically 4/ 'Blockid' column removed 5/ Free bytes column renamed to just 'bytes' instead of 'blockfreebytes' Mark Kirkwood --- contrib/pg_freespacemap/README.pg_freespacemap | 48 ++++++++--------- contrib/pg_freespacemap/pg_freespacemap.c | 71 ++++++++++++++++---------- contrib/pg_freespacemap/pg_freespacemap.sql.in | 2 +- 3 files changed, 71 insertions(+), 50 deletions(-) diff --git a/contrib/pg_freespacemap/README.pg_freespacemap b/contrib/pg_freespacemap/README.pg_freespacemap index d1711254c4..8657acc27b 100644 --- a/contrib/pg_freespacemap/README.pg_freespacemap +++ b/contrib/pg_freespacemap/README.pg_freespacemap @@ -34,12 +34,12 @@ Notes Column | references | Description ----------------+----------------------+------------------------------------ - blockid | | Id, 1.. max_fsm_pages - relfilenode | pg_class.relfilenode | Refilenode of the relation. reltablespace | pg_tablespace.oid | Tablespace oid of the relation. reldatabase | pg_database.oid | Database for the relation. + relfilenode | pg_class.relfilenode | Refilenode of the relation. relblocknumber | | Offset of the page in the relation. - blockfreebytes | | Free bytes in the block/page. + bytes | | Free bytes in the block/page, or NULL + | | for an index page (see below). There is one row for each page in the free space map. @@ -47,6 +47,9 @@ Notes Because the map is shared by all the databases, there are pages from relations not belonging to the current database. + The free space map can contain pages for btree indexes if they were emptied + by a vacuum process. The bytes field is set to NULL in this case. + When the pg_freespacemap view is accessed, internal free space map locks are taken, and a copy of the map data is made for the view to display. This ensures that the view produces a consistent set of results, while not @@ -58,34 +61,33 @@ Sample output ------------- regression=# \d pg_freespacemap - View "public.pg_freespacemap" + View "public.pg_freespacemap" Column | Type | Modifiers - ---------------+---------+----------- - blockid | integer | - relfilenode | oid | + ----------------+---------+----------- reltablespace | oid | reldatabase | oid | + relfilenode | oid | relblocknumber | bigint | - blockfreebytes | integer | + bytes | integer | View definition: - SELECT p.blockid, p.relfilenode, p.reltablespace, p.reldatabase, p.relblocknumber, p.blockfreebytes - FROM pg_freespacemap() p(blockid integer, relfilenode oid, reltablespace oid, reldatabase oid, relblocknumber bigint, blockfreebytes integer); + SELECT p.reltablespace, p.reldatabase, p.relfilenode, p.relblocknumber, p.bytes + FROM pg_freespacemap() p(reltablespace oid, reldatabase oid, relfilenode oid, relblocknumber bigint, bytes integer); - regression=# SELECT c.relname, m.relblocknumber, m.blockfreebytes + regression=# SELECT c.relname, m.relblocknumber, m.bytes FROM pg_freespacemap m INNER JOIN pg_class c ON c.relfilenode = m.relfilenode LIMIT 10; - relname | relblocknumber | blockfreebytes - ------------------------+----------------+---------------- - sql_features | 5 | 2696 - sql_implementation_info | 0 | 7104 - sql_languages | 0 | 8016 - sql_packages | 0 | 7376 - sql_sizing | 0 | 6032 - pg_authid | 0 | 7424 - pg_toast_2618 | 13 | 4588 - pg_toast_2618 | 12 | 1680 - pg_toast_2618 | 10 | 1436 - pg_toast_2618 | 7 | 1136 + relname | relblocknumber | bytes + ------------------------+----------------+-------- + sql_features | 5 | 2696 + sql_implementation_info | 0 | 7104 + sql_languages | 0 | 8016 + sql_packages | 0 | 7376 + sql_sizing | 0 | 6032 + pg_authid | 0 | 7424 + pg_toast_2618 | 13 | 4588 + pg_toast_2618 | 12 | 1680 + pg_toast_2618 | 10 | 1436 + pg_toast_2618 | 7 | 1136 (10 rows) regression=# diff --git a/contrib/pg_freespacemap/pg_freespacemap.c b/contrib/pg_freespacemap/pg_freespacemap.c index 471c97a100..7225be242c 100644 --- a/contrib/pg_freespacemap/pg_freespacemap.c +++ b/contrib/pg_freespacemap/pg_freespacemap.c @@ -3,7 +3,7 @@ * pg_freespacemap.c * display some contents of the free space map. * - * $PostgreSQL: pgsql/contrib/pg_freespacemap/pg_freespacemap.c,v 1.2 2006/02/14 15:03:59 tgl Exp $ + * $PostgreSQL: pgsql/contrib/pg_freespacemap/pg_freespacemap.c,v 1.3 2006/04/26 22:41:18 momjian Exp $ *------------------------------------------------------------------------- */ #include "postgres.h" @@ -12,7 +12,7 @@ #include "storage/freespace.h" #include "utils/relcache.h" -#define NUM_FREESPACE_PAGES_ELEM 6 +#define NUM_FREESPACE_PAGES_ELEM 5 #if defined(WIN32) || defined(__CYGWIN__) /* Need DLLIMPORT for some things that are not so marked in main headers */ @@ -29,12 +29,12 @@ Datum pg_freespacemap(PG_FUNCTION_ARGS); typedef struct { - uint32 blockid; - uint32 relfilenode; uint32 reltablespace; uint32 reldatabase; + uint32 relfilenode; uint32 relblocknumber; - uint32 blockfreebytes; + uint32 bytes; + bool isindex; } FreeSpacePagesRec; @@ -91,17 +91,15 @@ pg_freespacemap(PG_FUNCTION_ARGS) /* Construct a tuple to return. */ tupledesc = CreateTemplateTupleDesc(NUM_FREESPACE_PAGES_ELEM, false); - TupleDescInitEntry(tupledesc, (AttrNumber) 1, "blockid", - INT4OID, -1, 0); - TupleDescInitEntry(tupledesc, (AttrNumber) 2, "relfilenode", + TupleDescInitEntry(tupledesc, (AttrNumber) 1, "reltablespace", OIDOID, -1, 0); - TupleDescInitEntry(tupledesc, (AttrNumber) 3, "reltablespace", + TupleDescInitEntry(tupledesc, (AttrNumber) 2, "reldatabase", OIDOID, -1, 0); - TupleDescInitEntry(tupledesc, (AttrNumber) 4, "reldatabase", + TupleDescInitEntry(tupledesc, (AttrNumber) 3, "relfilenode", OIDOID, -1, 0); - TupleDescInitEntry(tupledesc, (AttrNumber) 5, "relblocknumber", + TupleDescInitEntry(tupledesc, (AttrNumber) 4, "relblocknumber", INT8OID, -1, 0); - TupleDescInitEntry(tupledesc, (AttrNumber) 6, "blockfreebytes", + TupleDescInitEntry(tupledesc, (AttrNumber) 5, "bytes", INT4OID, -1, 0); /* Generate attribute metadata needed later to produce tuples */ @@ -129,7 +127,6 @@ pg_freespacemap(PG_FUNCTION_ARGS) fctx->values[2] = (char *) palloc(3 * sizeof(uint32) + 1); fctx->values[3] = (char *) palloc(3 * sizeof(uint32) + 1); fctx->values[4] = (char *) palloc(3 * sizeof(uint32) + 1); - fctx->values[5] = (char *) palloc(3 * sizeof(uint32) + 1); /* Return to original context when allocating transient memory */ @@ -158,12 +155,12 @@ pg_freespacemap(PG_FUNCTION_ARGS) for (nPages = 0; nPages < fsmrel->storedPages; nPages++) { - fctx->record[i].blockid = i; - fctx->record[i].relfilenode = fsmrel->key.relNode; fctx->record[i].reltablespace = fsmrel->key.spcNode; fctx->record[i].reldatabase = fsmrel->key.dbNode; + fctx->record[i].relfilenode = fsmrel->key.relNode; fctx->record[i].relblocknumber = IndexFSMPageGetPageNum(page); - fctx->record[i].blockfreebytes = 0; /* index.*/ + fctx->record[i].bytes = 0; + fctx->record[i].isindex = true; page++; i++; @@ -178,12 +175,12 @@ pg_freespacemap(PG_FUNCTION_ARGS) for (nPages = 0; nPages < fsmrel->storedPages; nPages++) { - fctx->record[i].blockid = i; - fctx->record[i].relfilenode = fsmrel->key.relNode; fctx->record[i].reltablespace = fsmrel->key.spcNode; fctx->record[i].reldatabase = fsmrel->key.dbNode; + fctx->record[i].relfilenode = fsmrel->key.relNode; fctx->record[i].relblocknumber = FSMPageGetPageNum(page); - fctx->record[i].blockfreebytes = FSMPageGetSpace(page); + fctx->record[i].bytes = FSMPageGetSpace(page); + fctx->record[i].isindex = false; page++; i++; @@ -209,19 +206,41 @@ pg_freespacemap(PG_FUNCTION_ARGS) if (funcctx->call_cntr < funcctx->max_calls) { uint32 i = funcctx->call_cntr; + char *values[NUM_FREESPACE_PAGES_ELEM]; + int j; + /* + * Use a temporary values array, initially pointing to fctx->values, + * so it can be reassigned w/o losing the storage for subsequent + * calls. + */ + for (j = 0; j < NUM_FREESPACE_PAGES_ELEM; j++) + { + values[j] = fctx->values[j]; + } + + + sprintf(values[0], "%u", fctx->record[i].reltablespace); + sprintf(values[1], "%u", fctx->record[i].reldatabase); + sprintf(values[2], "%u", fctx->record[i].relfilenode); + sprintf(values[3], "%u", fctx->record[i].relblocknumber); - sprintf(fctx->values[0], "%u", fctx->record[i].blockid); - sprintf(fctx->values[1], "%u", fctx->record[i].relfilenode); - sprintf(fctx->values[2], "%u", fctx->record[i].reltablespace); - sprintf(fctx->values[3], "%u", fctx->record[i].reldatabase); - sprintf(fctx->values[4], "%u", fctx->record[i].relblocknumber); - sprintf(fctx->values[5], "%u", fctx->record[i].blockfreebytes); + /* + * Set (free) bytes to NULL for an index relation. + */ + if (fctx->record[i].isindex == true) + { + values[4] = NULL; + } + else + { + sprintf(values[4], "%u", fctx->record[i].bytes); + } /* Build and return the tuple. */ - tuple = BuildTupleFromCStrings(funcctx->attinmeta, fctx->values); + tuple = BuildTupleFromCStrings(funcctx->attinmeta, values); result = HeapTupleGetDatum(tuple); diff --git a/contrib/pg_freespacemap/pg_freespacemap.sql.in b/contrib/pg_freespacemap/pg_freespacemap.sql.in index 26574ea1c0..b69026953f 100644 --- a/contrib/pg_freespacemap/pg_freespacemap.sql.in +++ b/contrib/pg_freespacemap/pg_freespacemap.sql.in @@ -11,7 +11,7 @@ LANGUAGE C; -- Create a view for convenient access. CREATE VIEW pg_freespacemap AS SELECT P.* FROM pg_freespacemap() AS P - (blockid int4, relfilenode oid, reltablespace oid, reldatabase oid, relblocknumber int8, blockfreebytes int4); + (reltablespace oid, reldatabase oid, relfilenode oid, relblocknumber int8, bytes int4); -- Don't want these to be available at public. REVOKE ALL ON FUNCTION pg_freespacemap() FROM PUBLIC; -- 2.11.0