Re: RelationBuildDesc Notice (corrupt DB?) - Mailing list pgsql-general

Tom,
I got your e-mail, but when I try to reply, it gets returned as spam...I
tried from my two e-mail domains (personal and business) with no success.
Here is my response to your mail


From the select version statement, I get the following:

Postgresql 7.2.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2
20020903  (Red Hat Linux 8.0 3.2-7)

And its running on an intel 933 MHz, with about 384 MB RAM

As far as reproducible....I'll go into more detail and see what you think.

The first time, the database had been up for about two weeks and it had
three tables (artists, albums, tracks) to support a sourceforge application
(emptytree-seedy).  That application creates the db etc.  Basically the
application auto-rips CDs to MP3 as you place them in the CD-ROM drive, and
it goes out to FREEDB to populate the three tables I mentioned beforehand.
One evening the application appeared to hang, so I stopped everything, then
rebooted, and when I restarted everything, I got messages about pg_statistic
missing.  Googling for RelationBuildDesc, pg_statistic etc. and not finding
anything that resembled a fix, I gave up on restoring that database after a
few days.   I had ripped about 400 CDs at that point.  Luckily at around 300
CDs, I had made an ODBC link and did a quick copy of the contents of those
three tables to another database (MS Access on a Windows 2000).

The following might be a bit off subject, but I want to give you the entire
background of the situation.  My plan to minimize redoing CDs was to have
the application autocreate a new database, stop the app and then insert my
retained records for the 300 CDs via ODBC, and then start the app back up
and let the application redo the 100 CDs I was missing from the database.
Although, I inserted the archived records into a new database successfully,
the application wouldn't run successfully.  After consulting with the
designer of the sourceforge project, I began to guess that the issue was a
sequence on the tables (used for unique row ids in each table) and when I
was updating the table manually, the internal sequence number wasn't aware
of the that and when I started the application again to start inserting new
CD information, it developed conflicts with some existing IDs (this is an
assumption on my part that the sequencing was the issue).  So my plan was to
basically clear tables in the new database again, insert the info for 300
CDs, drop the sequence, and then recreate it with a high starting value.
This is where it happened again......

So in summary....the second time I got the RelationBuildDesc
notice/pg_statistic missing was... when I took a empty database, inserted
about 180 artists, 300 albums, and 4261 tracks, dropped the sequence,
created the same sequence with a high starting value.  After recreating the
sequence successfully, I wanted to stop the postmaster and reboot, so I
issued a pg_ctl -D thedirectory stop    Usually this happened pretty fast,
but this time it took about 30-45 seconds.  I then rebooted.  When I tried a
pg_ctl start I got the errors that I posted.

The first time I got the error, I thought perhaps the application had caused
it, but the second time it happened, the application wasn't running at all,
I had just inserted some records, dropped and then recreated the sequence
and rebooted.   I've looked at the logs, but they only echoed the errors
sent to the screen.   There might be something discernable to you though.

I've included the sql for table and sequence creation, its pretty
simple....I don't think there is anything harmful there......?

Any advice that you can provide would be most helpful.  If I've left out
details you need, please let me know.

Thanks,

TRH

CREATE SEQUENCE ioid_seq;

CREATE TABLE artists (

ioid INT4 DEFAULT nextval('ioid_seq'),

title TEXT,

play_date DATETIME);

CREATE TABLE albums (

ioid INT4 DEFAULT nextval('ioid_seq'),

title TEXT,

artist_ioid INT4,

cddb_id TEXT,

category TEXT,

track_count INT4,

running_time FLOAT,

play_count INT4 DEFAULT 0,

play_date DATETIME);

CREATE TABLE tracks (

ioid INT4 DEFAULT nextval('ioid_seq'),

title TEXT,

index INT4,

album_ioid INT4,

lba INT4,

data INT4,

duration FLOAT,

play_count INT4 DEFAULT 0,

play_date DATETIME);

CREATE TABLE lists (

ioid INT4 DEFAULT nextval('ioid_seq'),

title TEXT,

track_ioids INT4[]);

CREATE TABLE version (name TEXT);

CREATE UNIQUE INDEX track_ioid ON tracks (ioid);

CREATE UNIQUE INDEX track_album_ioid_index ON tracks (album_ioid, index);

CREATE UNIQUE INDEX artist_ioid ON artists (ioid);

CREATE UNIQUE INDEX artist_title ON artists (title);

CREATE UNIQUE INDEX album_ioid ON albums (ioid);

CREATE UNIQUE INDEX list_ioid ON lists (ioid);

INSERT INTO version (name) VALUES ('1.6.0.1');


"Tony Harper" <trharper5@yahoo.com> wrote in message
news:b6hb2u$193v$1@news.hub.org...
> DB appears to be corrupted but cannot isolate cause, it has happened
> multiple times after recreating database.  The system seems stable
otherwise
>
> The message when trying to do
> psql name_of_database:
> NOTICE: RelationBuildDesc: can't open pg_statistic:  No such file or
> directory
> ERROR: cannot open pg_statistic:  No such file or directory
>
> if I do a \d
> The message is:
> cannot open pg_statistic:  No such file or directory
>
> if I do a verbose vacuum, things go fine till the end....a sample:
> NOTICE: RelationBuildDesc: can't open pg_group:  No such file or directory
> NOTICE: RelationBuildDesc: can't open pg_group_name_index:  No such file
or
> directory
> NOTICE: RelationBuildDesc: can't open pg_group_sysid_index:  No such file
or
> directory
> Error: _mdfd_getreInfd:  cannot open relation pg_group:  No such file or
> director
>
> if I do a REINDEX from a backend:
> NOTICE: RelationBuildDesc: can't open pg_group:  No such file or directory
> NOTICE: RelationBuildDesc: can't open pg_inherits:  No such file or
> directory
> NOTICE: RelationBuildDesc: can't open pg_inherits_rlid_seqno_index:  No
such
> file or directory
>
>
> Anyone know what the real issue is or how to fix?
>
> Thanks,
>
> TRH
>
>