thousand unrelated data files in pg_default tablespace

Pavel Stehule <pavel.stehule@gmail.com> writes:

I found a PostgreSQL 8.3 server (Linux) used for large OLAP where the
data directory is bloating. There are more than one hundred thousand
files - 8KB or 0KB long. The filenames are not transformable to names
via oid2name. Does somebody know about similar bug?

1. 8.3.what?

2. Any signs of distress in the postmaster log? I'm wondering about
being unable to complete checkpoints, or repeated backend crashes that
might cause leakage of temp tables.

3. What's in the files --- do they appear to be tables, indexes, random
temp files from sorts/hashes, or what? pg_filedump might help you here.

regards, tom lane

2010/8/30 Tom Lane <tgl@sss.pgh.pa.us>:

Pavel Stehule <pavel.stehule@gmail.com> writes:

I found a PostgreSQL 8.3 server (Linux) used for large OLAP where the
data directory is bloating. There are more than one hundred thousand
files - 8KB or 0KB long. The filenames are not transformable to names
via oid2name. Does somebody know about similar bug?

1. 8.3.what?

postgres=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 8.3.6 on x86_64-redhat-linux-gnu, compiled by GCC gcc
(GCC) 4.1.2 20071124 (Red Hat 4.1.2-42)
(1 row)

2. Any signs of distress in the postmaster log?  I'm wondering about
being unable to complete checkpoints, or repeated backend crashes that
might cause leakage of temp tables.

No, there are nothing

3. What's in the files --- do they appear to be tables, indexes, random
temp files from sorts/hashes, or what?  pg_filedump might help you here.

I have to contact admin tomorrow. For now - one half was zero length,
second half was almost empty. These files are in directory related to
pg_default tablespace.

Regards

Pavel Stehule

Hello

there is a dump from 8KB files

Regard

Pavel Stehule

Pavel Stehule <pavel.stehule@gmail.com> writes:

there is a dump from 8KB files

Well, those certainly look like tables/indexes not temp files.
So we can rule out one theory.

You're *certain* these aren't referenced from pg_class.relfilenode
of any of the databases in the server?

regards, tom lane

hello

2010/8/31 Tom Lane <tgl@sss.pgh.pa.us>:

Pavel Stehule <pavel.stehule@gmail.com> writes:

there is a dump from 8KB files

Well, those certainly look like tables/indexes not temp files.
So we can rule out one theory.

You're *certain* these aren't referenced from pg_class.relfilenode
of any of the databases in the server?

I have a info, so these files are not in pg_class.relfilenode. More -
these files are three months old, and in this time was server two
times restarted.

Regards

Pavel Stehule

On 03/09/10 11:16, Pavel Stehule wrote:

2010/8/31 Tom Lane<tgl@sss.pgh.pa.us>:

Pavel Stehule<pavel.stehule@gmail.com> writes:

there is a dump from 8KB files

Well, those certainly look like tables/indexes not temp files.
So we can rule out one theory.

You're *certain* these aren't referenced from pg_class.relfilenode
of any of the databases in the server?

I have a info, so these files are not in pg_class.relfilenode. More -
these files are three months old, and in this time was server two
times restarted.

Maybe they're tables that were created in a transaction, but the process
crashed hard before committing? Like:

BEGIN;
CREATE TABLE foo (...);
COPY foo FROM ...;
kill -9 postgres

That will leave behind a file like that. Do you do something like that
in the application?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

2010/9/3 Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>:

On 03/09/10 11:16, Pavel Stehule wrote:

2010/8/31 Tom Lane<tgl@sss.pgh.pa.us>:

Pavel Stehule<pavel.stehule@gmail.com>  writes:

there is a dump from 8KB files

Well, those certainly look like tables/indexes not temp files.
So we can rule out one theory.

You're *certain* these aren't referenced from pg_class.relfilenode
of any of the databases in the server?

I have a info, so these files are not in pg_class.relfilenode. More -
these files are three months old, and in this time was server two
times restarted.

Maybe they're tables that were created in a transaction, but the process
crashed hard before committing? Like:

BEGIN;
CREATE TABLE foo (...);
COPY foo FROM ...;
kill -9 postgres

yes, it's possible - but there are not any record about server crash -
sometimes client crashes.

Regards

Pavel