Thread: "could not open file" issue
Hi, Is there any way to solve the following issue without dropping the table? select count(*) from eventplayerleaveroom; ERROR: could not access status of transaction 3164404766 DETAIL: could notopen file "pg_clog/0BC9": No such file or directory Regards, P.S.: PostgreSQL server 8.1.3 -- Daniel CAUNE Ubisoft Online Technology (514) 4090 2040 ext. 5418
On Mon, Apr 24, 2006 at 12:17:07PM -0400, Daniel Caune wrote: > Hi, > > Is there any way to solve the following issue without dropping the > table? I doubt you'll be able to drop the table. I think you have some sort of corruption. Assuming your hardware is good, you maybe oughta take this over to -general to see if the wizards can identify your problem. (But check your hardware first.) A -- Andrew Sullivan | ajs@crankycanuck.ca "The year's penultimate month" is not in truth a good way of saying November. --H.W. Fowler
Daniel Caune wrote: > Hi, > > Is there any way to solve the following issue without dropping the > table? > > select count(*) from eventplayerleaveroom; > ERROR: could not access status of transaction 3164404766 > DETAIL: could not open file "pg_clog/0BC9": No such file or directory Are the files in pg_clog close to the vicinity of 0BC9? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
> De : Alvaro Herrera [mailto:alvherre@commandprompt.com] > > Daniel Caune wrote: > > Hi, > > > > Is there any way to solve the following issue without dropping the > > table? > > > > select count(*) from eventplayerleaveroom; > > ERROR: could not access status of transaction 3164404766 > > DETAIL: could not open file "pg_clog/0BC9": No such file or directory > > Are the files in pg_clog close to the vicinity of 0BC9? > I don't have any skill in PostgreSQL administration. However I took a look at /var/lib/postgresql/8.1/main/pg_clog and thereis no file close to the vicinity of 0BC9 ; the last file in that directory is: -rw------- 1 postgres postgres 221184 2006-04-24 19:27 00C9 A bit far from 0BC9... Do you have any diagnostic? > -- > Alvaro Herrera > http://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc.
Daniel Caune wrote: > > > De : Alvaro Herrera [mailto:alvherre@commandprompt.com] > > > > Daniel Caune wrote: > > > > > > select count(*) from eventplayerleaveroom; > > > ERROR: could not access status of transaction 3164404766 > > > DETAIL: could not open file "pg_clog/0BC9": No such file or directory > > > > Are the files in pg_clog close to the vicinity of 0BC9? > > I don't have any skill in PostgreSQL administration. However I took a > look at /var/lib/postgresql/8.1/main/pg_clog and there is no file > close to the vicinity of 0BC9 ; the last file in that directory is: > > -rw------- 1 postgres postgres 221184 2006-04-24 19:27 00C9 > > A bit far from 0BC9... Do you have any diagnostic? I'd say you have a corrupted table. How corrupted I don't know. You could try extracting a portion of the table, playing with LIMIT/OFFSET to find out the exact records that are corrupted. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
> De : Alvaro Herrera [mailto:alvherre@commandprompt.com] > > Daniel Caune wrote: > > > > > De : Alvaro Herrera [mailto:alvherre@commandprompt.com] > > > > > > Daniel Caune wrote: > > > > > > > > select count(*) from eventplayerleaveroom; > > > > ERROR: could not access status of transaction 3164404766 > > > > DETAIL: could not open file "pg_clog/0BC9": No such file or > directory > > > > > > Are the files in pg_clog close to the vicinity of 0BC9? > > > > I don't have any skill in PostgreSQL administration. However I took a > > look at /var/lib/postgresql/8.1/main/pg_clog and there is no file > > close to the vicinity of 0BC9 ; the last file in that directory is: > > > > -rw------- 1 postgres postgres 221184 2006-04-24 19:27 00C9 > > > > A bit far from 0BC9... Do you have any diagnostic? > > I'd say you have a corrupted table. How corrupted I don't know. You > could try extracting a portion of the table, playing with LIMIT/OFFSET > to find out the exact records that are corrupted. > Yes, I tried playing with the LIMIT clause, and LIMIT 90 is the better I can pass... :-( > -- > Alvaro Herrera > http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support
> De : pgsql-sql-owner@postgresql.org [mailto:pgsql-sql- > owner@postgresql.org] De la part de Andrew Sullivan > > On Mon, Apr 24, 2006 at 12:17:07PM -0400, Daniel Caune wrote: > > Hi, > > > > Is there any way to solve the following issue without dropping the > > table? > > I doubt you'll be able to drop the table. I think you have some sort > of corruption. Assuming your hardware is good, you maybe oughta take > this over to -general to see if the wizards can identify your > problem. (But check your hardware first.) > It seems that was possible: I tried first to truncate the table (it passed), and finally I tried to drop the table (it alsopassed). Then I created the table. I'm not sure that it fixes my problem. I modified my fstab file so that Linux checksmy file system's health on the next boot. > > -- > Andrew Sullivan | ajs@crankycanuck.ca > "The year's penultimate month" is not in truth a good way of saying > November. > --H.W. Fowler
Daniel Caune wrote: > It seems that was possible: I tried first to truncate the table (it > passed), and finally I tried to drop the table (it also passed). Then > I created the table. I'm not sure that it fixes my problem. I > modified my fstab file so that Linux checks my file system's health on > the next boot. If you experienced memory corruption, it would be wise to run some hardware diagnosys tools, just to be sure. For example memtest86, badblocks, etc. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.