Thread: RelationBuildDesc Notice (corrupt DB?)
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
"Tony Harper" <trharper5@yahoo.com> writes: > DB appears to be corrupted but cannot isolate cause, it has happened > multiple times after recreating database. The system seems stable otherwise You mean you can reproducibly make this happen from a freshly initdb'd state? I'd love to take a look if so ... what PG version is this, and what platform? regards, tom lane
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 > >
"Tony Harper" <trharper5@yahoo.com> writes: > 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. AFAICS you haven't done anything particularly strange (although your habit of rebooting every time you turn around seems pretty odd to an old Unix hand ;-) ... that's really not necessary, nor is stopping and restarting Postgres a useful exercise in my opinion). I frankly suspect hardware problems. You should try some testing with memtest86 and badblocks to see if they turn up anything in the way of flaky RAM or disk drive respectively. regards, tom lane
Thanks Tom for the suggestion >>I frankly suspect hardware problems. You should try some testing with >>memtest86 and badblocks to see if they turn up anything in the way of >>flaky RAM or disk drive respectively. "Tony Harper" <trharper5@yahoo.com> wrote in message news:b6ia3h$1prr$1@news.hub.org... > 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 > > > > > >