pg_total_relation_size() could not open relation with OID X
Started by Michael Fuhrover 20 years ago3 messages
Here's a test case for a pg_total_relation_size() failure:
test=> CREATE TABLE foo (id integer);
CREATE TABLE
test=> SELECT oid, relfilenode FROM pg_class WHERE relname = 'foo';
oid | relfilenode
-------+-------------
26235 | 26235
(1 row)
test=> SELECT pg_total_relation_size('foo');
pg_total_relation_size
------------------------
0
(1 row)
test=> TRUNCATE foo;
TRUNCATE TABLE
test=> SELECT oid, relfilenode FROM pg_class WHERE relname = 'foo';
oid | relfilenode
-------+-------------
26235 | 26237
(1 row)
test=> SELECT pg_total_relation_size('foo');
ERROR: could not open relation with OID 26237
test=> SELECT pg_total_relation_size(26235);
ERROR: could not open relation with OID 26237
test=> SELECT pg_relation_size('foo');
pg_relation_size
------------------
0
(1 row)
--
Michael Fuhr
Re: pg_total_relation_size() could not open relation with OID X
On Wed, Sep 28, 2005 at 10:25:16PM -0600, Michael Fuhr wrote:
test=> TRUNCATE foo;
TRUNCATE TABLE
test=> SELECT oid, relfilenode FROM pg_class WHERE relname = 'foo';
oid | relfilenode
-------+-------------
26235 | 26237
(1 row)
The code is obviously confused between Oid and relfilenode. The
calculate_total_relation_size() function gets a relfilenode parameter
and then tries to call relation_open() with it. This is wrong.
I'll submit/commit a patch fixing this, later today. Thanks for the
test case.
--
Alvaro Herrera Valdivia, Chile ICBM: S 39� 49' 17.7", W 73� 14' 26.8"
"Granting software the freedom to evolve guarantees only different results,
not better ones." (Zygo Blaxell)
Re: pg_total_relation_size() could not open relation with OID X
I wrote:
The code is obviously confused between Oid and relfilenode. The
calculate_total_relation_size() function gets a relfilenode parameter
and then tries to call relation_open() with it. This is wrong.
This is the patch I'm about to apply. Besides fixing this particular
problem, I made the code include the size of the index of the TOAST
table in pg_total_relation_size().
--
Alvaro Herrera Architect, http://www.EnterpriseDB.com
"En las profundidades de nuestro inconsciente hay una obsesiva necesidad
de un universo l�gico y coherente. Pero el universo real se halla siempre
un paso m�s all� de la l�gica" (Irulan)
Attachments:
dbsize.patchtext/plain; charset=us-asciiDownload
Index: dbsize.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/utils/adt/dbsize.c,v
retrieving revision 1.4
diff -c -r1.4 dbsize.c
*** dbsize.c 16 Sep 2005 05:35:40 -0000 1.4
--- dbsize.c 29 Sep 2005 21:49:49 -0000
***************
*** 216,249 ****
* calculate size of a relation
*/
static int64
! calculate_relation_size(Oid tblspcOid, Oid relnodeOid)
{
! int64 totalsize=0;
! unsigned int segcount=0;
! char dirpath[MAXPGPATH];
! char pathname[MAXPGPATH];
!
! if (!tblspcOid)
! tblspcOid = MyDatabaseTableSpace;
!
! if (tblspcOid == DEFAULTTABLESPACE_OID)
! snprintf(dirpath, MAXPGPATH, "%s/base/%u", DataDir, MyDatabaseId);
! else if (tblspcOid == GLOBALTABLESPACE_OID)
snprintf(dirpath, MAXPGPATH, "%s/global", DataDir);
else
snprintf(dirpath, MAXPGPATH, "%s/pg_tblspc/%u/%u",
! DataDir, tblspcOid, MyDatabaseId);
! for (segcount = 0 ;; segcount++)
{
struct stat fst;
if (segcount == 0)
snprintf(pathname, MAXPGPATH, "%s/%u",
! dirpath, relnodeOid);
else
snprintf(pathname, MAXPGPATH, "%s/%u.%u",
! dirpath, relnodeOid, segcount);
if (stat(pathname, &fst) < 0)
{
--- 216,248 ----
* calculate size of a relation
*/
static int64
! calculate_relation_size(RelFileNode *rfn)
{
! int64 totalsize = 0;
! char dirpath[MAXPGPATH];
! char pathname[MAXPGPATH];
! unsigned int segcount = 0;
!
! Assert(OidIsValid(rfn->spcNode));
!
! if (rfn->spcNode == DEFAULTTABLESPACE_OID)
! snprintf(dirpath, MAXPGPATH, "%s/base/%u", DataDir, rfn->dbNode);
! else if (rfn->spcNode == GLOBALTABLESPACE_OID)
snprintf(dirpath, MAXPGPATH, "%s/global", DataDir);
else
snprintf(dirpath, MAXPGPATH, "%s/pg_tblspc/%u/%u",
! DataDir, rfn->spcNode, rfn->dbNode);
! for (segcount = 0; ; segcount++)
{
struct stat fst;
if (segcount == 0)
snprintf(pathname, MAXPGPATH, "%s/%u",
! dirpath, rfn->relNode);
else
snprintf(pathname, MAXPGPATH, "%s/%u.%u",
! dirpath, rfn->relNode, segcount);
if (stat(pathname, &fst) < 0)
{
***************
*** 264,287 ****
pg_relation_size_oid(PG_FUNCTION_ARGS)
{
Oid relOid=PG_GETARG_OID(0);
! HeapTuple tuple;
! Form_pg_class pg_class;
! Oid relnodeOid;
! Oid tblspcOid;
!
! tuple = SearchSysCache(RELOID,
! ObjectIdGetDatum(relOid),
! 0, 0, 0);
! if (!HeapTupleIsValid(tuple))
! elog(ERROR, "cache lookup failed for relation %u", relOid);
!
! pg_class = (Form_pg_class) GETSTRUCT(tuple);
! relnodeOid = pg_class->relfilenode;
! tblspcOid = pg_class->reltablespace;
! ReleaseSysCache(tuple);
! PG_RETURN_INT64(calculate_relation_size(tblspcOid, relnodeOid));
}
Datum
--- 263,278 ----
pg_relation_size_oid(PG_FUNCTION_ARGS)
{
Oid relOid=PG_GETARG_OID(0);
! Relation rel;
! int64 size;
! rel = relation_open(relOid, AccessShareLock);
! size = calculate_relation_size(&(rel->rd_node));
!
! relation_close(rel, AccessShareLock);
!
! PG_RETURN_INT64(size);
}
Datum
***************
*** 289,365 ****
{
text *relname = PG_GETARG_TEXT_P(0);
RangeVar *relrv;
! Relation relation;
! Oid relnodeOid;
! Oid tblspcOid;
relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
! relation = relation_openrv(relrv, AccessShareLock);
! tblspcOid = relation->rd_rel->reltablespace;
! relnodeOid = relation->rd_rel->relfilenode;
! relation_close(relation, AccessShareLock);
! PG_RETURN_INT64(calculate_relation_size(tblspcOid, relnodeOid));
}
/*
! * Compute the on-disk size of files for 'relation' according to the
* stat function, optionally including heap data, index data, and/or
* toast data.
*/
static int64
! calculate_total_relation_size(Oid tblspcOid, Oid relnodeOid)
{
! Relation heapRelation;
! Relation idxRelation;
! Relation toastRelation;
! Oid idxOid;
! Oid idxTblspcOid;
! Oid toastOid;
! Oid toastTblspcOid;
! bool hasIndices;
! int64 size;
! List *indexoidlist;
! ListCell *idx;
!
! heapRelation = relation_open(relnodeOid, AccessShareLock);
! toastOid = heapRelation->rd_rel->reltoastrelid;
! hasIndices = heapRelation->rd_rel->relhasindex;
! /* Get the heap size */
! size = calculate_relation_size(tblspcOid, relnodeOid);
! /* Get index size */
! if (hasIndices)
{
/* recursively include any dependent indexes */
! indexoidlist = RelationGetIndexList(heapRelation);
! foreach(idx, indexoidlist)
{
! idxOid = lfirst_oid(idx);
! idxRelation = relation_open(idxOid, AccessShareLock);
! idxTblspcOid = idxRelation->rd_rel->reltablespace;
! size += calculate_relation_size(idxTblspcOid, idxOid);
! relation_close(idxRelation, AccessShareLock);
}
! list_free(indexoidlist);
}
! relation_close(heapRelation, AccessShareLock);
! /* Get toast table size */
! if (toastOid != 0)
! {
! /* recursively include any toast relations */
! toastRelation = relation_open(toastOid, AccessShareLock);
! toastTblspcOid = toastRelation->rd_rel->reltablespace;
! size += calculate_relation_size(toastTblspcOid, toastOid);
! relation_close(toastRelation, AccessShareLock);
! }
return size;
}
--- 280,344 ----
{
text *relname = PG_GETARG_TEXT_P(0);
RangeVar *relrv;
! Relation rel;
! int64 size;
relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
! rel = relation_openrv(relrv, AccessShareLock);
! size = calculate_relation_size(&(rel->rd_node));
! relation_close(rel, AccessShareLock);
! PG_RETURN_INT64(size);
}
/*
! * Compute the on-disk size of files for the relation according to the
* stat function, optionally including heap data, index data, and/or
* toast data.
*/
static int64
! calculate_total_relation_size(Oid Relid)
{
! Relation heapRel;
! Oid toastOid;
! int64 size;
! ListCell *cell;
! heapRel = relation_open(Relid, AccessShareLock);
! toastOid = heapRel->rd_rel->reltoastrelid;
! /* Get the heap size */
! size = calculate_relation_size(&(heapRel->rd_node));
!
! /* Get index size */
! if (heapRel->rd_rel->relhasindex)
{
/* recursively include any dependent indexes */
! List *index_oids = RelationGetIndexList(heapRel);
! foreach(cell, index_oids)
{
! Oid idxOid = lfirst_oid(cell);
! Relation iRel;
!
! iRel = relation_open(idxOid, AccessShareLock);
!
! size += calculate_relation_size(&(iRel->rd_node));
!
! relation_close(iRel, AccessShareLock);
}
!
! list_free(index_oids);
}
! /* Get toast table (and index) size */
! if (OidIsValid(toastOid))
! size += calculate_total_relation_size(toastOid);
! relation_close(heapRel, AccessShareLock);
return size;
}
***************
*** 371,415 ****
Datum
pg_total_relation_size_oid(PG_FUNCTION_ARGS)
{
! Oid relOid=PG_GETARG_OID(0);
! HeapTuple tuple;
! Form_pg_class pg_class;
! Oid relnodeOid;
! Oid tblspcOid;
!
! tuple = SearchSysCache(RELOID,
! ObjectIdGetDatum(relOid),
! 0, 0, 0);
! if (!HeapTupleIsValid(tuple))
! elog(ERROR, "cache lookup failed for relation %u", relOid);
!
! pg_class = (Form_pg_class) GETSTRUCT(tuple);
! relnodeOid = pg_class->relfilenode;
! tblspcOid = pg_class->reltablespace;
!
! ReleaseSysCache(tuple);
! PG_RETURN_INT64(calculate_total_relation_size(tblspcOid, relnodeOid));
}
Datum
pg_total_relation_size_name(PG_FUNCTION_ARGS)
{
! text *relname = PG_GETARG_TEXT_P(0);
! RangeVar *relrv;
! Relation relation;
! Oid relnodeOid;
! Oid tblspcOid;
relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
! relation = relation_openrv(relrv, AccessShareLock);
! tblspcOid = relation->rd_rel->reltablespace;
! relnodeOid = relation->rd_rel->relfilenode;
!
! relation_close(relation, AccessShareLock);
!
! PG_RETURN_INT64(calculate_total_relation_size(tblspcOid, relnodeOid));
}
/*
--- 350,371 ----
Datum
pg_total_relation_size_oid(PG_FUNCTION_ARGS)
{
! Oid relid = PG_GETARG_OID(0);
! PG_RETURN_INT64(calculate_total_relation_size(relid));
}
Datum
pg_total_relation_size_name(PG_FUNCTION_ARGS)
{
! text *relname = PG_GETARG_TEXT_P(0);
! RangeVar *relrv;
! Oid relid;
relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
! relid = RangeVarGetRelid(relrv, false);
! PG_RETURN_INT64(calculate_total_relation_size(relid));
}
/*
***************
*** 418,454 ****
Datum
pg_size_pretty(PG_FUNCTION_ARGS)
{
! int64 size=PG_GETARG_INT64(0);
! char *result=palloc(50+VARHDRSZ);
! int64 limit = 10*1024;
! int64 mult=1;
!
! if (size < limit*mult)
! snprintf(VARDATA(result), 50, INT64_FORMAT" bytes",
! size);
else
{
mult *= 1024;
! if (size < limit*mult)
snprintf(VARDATA(result), 50, INT64_FORMAT " kB",
! (size+mult/2) / mult);
else
{
mult *= 1024;
! if (size < limit*mult)
snprintf(VARDATA(result), 50, INT64_FORMAT " MB",
! (size+mult/2) / mult);
else
{
mult *= 1024;
! if (size < limit*mult)
snprintf(VARDATA(result), 50, INT64_FORMAT " GB",
! (size+mult/2) / mult);
else
{
mult *= 1024;
snprintf(VARDATA(result), 50, INT64_FORMAT " TB",
! (size+mult/2) / mult);
}
}
}
--- 374,409 ----
Datum
pg_size_pretty(PG_FUNCTION_ARGS)
{
! int64 size = PG_GETARG_INT64(0);
! char *result = palloc(50 + VARHDRSZ);
! int64 limit = 10 * 1024;
! int64 mult = 1;
!
! if (size < limit * mult)
! snprintf(VARDATA(result), 50, INT64_FORMAT " bytes", size);
else
{
mult *= 1024;
! if (size < limit * mult)
snprintf(VARDATA(result), 50, INT64_FORMAT " kB",
! (size + mult / 2) / mult);
else
{
mult *= 1024;
! if (size < limit * mult)
snprintf(VARDATA(result), 50, INT64_FORMAT " MB",
! (size + mult / 2) / mult);
else
{
mult *= 1024;
! if (size < limit * mult)
snprintf(VARDATA(result), 50, INT64_FORMAT " GB",
! (size + mult / 2) / mult);
else
{
mult *= 1024;
snprintf(VARDATA(result), 50, INT64_FORMAT " TB",
! (size + mult / 2) / mult);
}
}
}