Re: oids on disk not in pg_class

On 10/7/2013 5:58 PM, Steve Atkins wrote:
>
> On Oct 7, 2013, at 2:48 PM, Guy Rouillier <guy.rouillier@gmail.com>
> wrote:
>
>> We have a fairly large (1 TB) database we put on all SSDs because
>> of a very high insert and update rate (38 million rows/day).  As
>> our business has grown, we've been running into space constraints,
>> so we went looking for files we might be able to delete.
>>
>> We found a large number (662 out of 1465 total ) and size (219 GB)
>> of files in the data directory whose name does not correspond to
>> an oid in the pg_class system catalog table.  That amount of space
>> would address our current space constraint problems.  Some of
>> these tables are recent (from today), while others are quite old
>> (large volume in August and May, with some smaller ones as far back
>> as February.
>
> You need to be looking at pg_class.relfilenode, not pg_class.oid.
> They're often the same value, but often not.

Steve, thanks for the quick reply.  I reran the calculations using
relfilenode instead of oid; they are now showing 214 unrecorded
filenodes consuming 163 GB.  The older tables (on or before May) are no
longer showing as unrecorded.  Of the filenodes that are still not
matching pg_class.relfilenode, I see just one that is consuming the
majority of the space: 614804 (153 files, 163 GB).

Failed to mention our runtime platform: we are running Enterprise DB
9.2.1.3 on 64-bit Linux (Oracle Linux Server release 6.3).  EDB pointed
us to this note in the PG documentation
(http://www.postgresql.org/docs/9.2/static/storage-file-layout.html):

Note that while a table's filenode often matches its OID, this is not
necessarily the case; some operations, like TRUNCATE, REINDEX, CLUSTER
and some forms of ALTER TABLE, can change the filenode while preserving
the OID. Avoid assuming that filenode and table OID are the same. Also,
for certain system catalogs including pg_class itself,
pg_class.relfilenode contains zero.  The actual filenode number of these
catalogs is stored in a lower-level data structure, and can be obtained
using the pg_relation_filenode() function.

So, I ran "select pg_relation_filenode(614804)" and got no results.  Any
suggestions on how I can uncover the identify of this node?

Thanks much.

--
Guy Rouillier