Re: [GENERAL] vacuum error
On Wed, Mar 07, 2007 at 07:07:45PM -0700, Ed L. wrote: > How would I go about correctly creating the missing file? That > sounds appealing, as if it were something I could do without > taking downtime. Is it? Depends if it's because the file got deleted prematurly, or because it's the result of corruption in the table. If it's just that it got deleted prematurely, you should just be able to create the file with 256KB of zeros. > In reviewing the release notes between 8.1.2 and 8.1.8, there are > a number of vacuum fixes along with many others. It is unclear > if any of them are related. There definitly was a bug related to this and you should upgrade to the latest minor release anyway, because it will probably bite you again. > Given this error is occurring while doing a "VACUUM FREEZE" of > the template0 database, I wonder if that creates any options for > me? Frankly, I'd be happy to simply drop the template0 DB if I > could, since I'm not aware that we ever need it for anything. You can drop and recreate the template0 database, instructions are somewhere. I don't think you can do without because I beleive pg_dump needs it. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] vacuum error
On Wednesday March 7 2007 3:13 am, Martijn van Oosterhout wrote: > On Wed, Mar 07, 2007 at 02:29:08AM -0700, Ed L. wrote: > > Perhaps my question was not clear enough. Let me rephrase: > > Does the fix for this problem comes from a *fresh* DB > > structure resulting from the initdb, or from a software fix > > in 8.1.8, or both? The answer makes a big difference with a > > 200gb database. If it's in the software alone, we can simply > > restart. If it's in the DB structure, we have to migrate > > 200gb of data from one PGDATA to another. > > In your case you could also get running by creating the > missing file, but whether that's appropriate depends on the > cause. I'd check the release notes to see if any related bugs > have been fixed since then. How would I go about correctly creating the missing file? That sounds appealing, as if it were something I could do without taking downtime. Is it? In reviewing the release notes between 8.1.2 and 8.1.8, there are a number of vacuum fixes along with many others. It is unclear if any of them are related. Given this error is occurring while doing a "VACUUM FREEZE" of the template0 database, I wonder if that creates any options for me? Frankly, I'd be happy to simply drop the template0 DB if I could, since I'm not aware that we ever need it for anything. Ed ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] vacuum error
On Wed, Mar 07, 2007 at 02:29:08AM -0700, Ed L. wrote: > Perhaps my question was not clear enough. Let me rephrase: Does > the fix for this problem comes from a *fresh* DB structure > resulting from the initdb, or from a software fix in 8.1.8, or > both? The answer makes a big difference with a 200gb database. > If it's in the software alone, we can simply restart. If it's > in the DB structure, we have to migrate 200gb of data from one > PGDATA to another. It would come from upgrading the software. Reinitdbing won't actually make a difference. The whole point of minor versions is that you don't need to reinit the DB, otherwise it'd be a major version... In your case you could also get running by creating the missing file, but whether that's appropriate depends on the cause. I'd check the release notes to see if any related bugs have been fixed since then. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] vacuum error
On Tuesday March 6 2007 11:52 pm, Peter Eisentraut wrote: > Ed L. wrote: > > Right. I'm asking if the fix for this problem is in the new > > 8.1.8 software, or in the new DB structure resulting from > > the initdb, or perhaps both. > > There is no new DB structure in 8.1.8, which is why you can > update without initdb. Consult the release notes for details. Perhaps my question was not clear enough. Let me rephrase: Does the fix for this problem comes from a *fresh* DB structure resulting from the initdb, or from a software fix in 8.1.8, or both? The answer makes a big difference with a 200gb database. If it's in the software alone, we can simply restart. If it's in the DB structure, we have to migrate 200gb of data from one PGDATA to another. Ed ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] vacuum error
Ed L. wrote: > Right. I'm asking if the fix for this problem is in the new > 8.1.8 software, or in the new DB structure resulting from the > initdb, or perhaps both. There is no new DB structure in 8.1.8, which is why you can update without initdb. Consult the release notes for details. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] vacuum error
On Tuesday March 6 2007 3:53 pm, Joshua D. Drake wrote: > > > Is restarting with 8.1.8 a known solution for this problem? > > Or is an initdb required to fix it? > > You can update to 8.1.8 (if you are running 8.1.x) without an > initdb. Right. I'm asking if the fix for this problem is in the new 8.1.8 software, or in the new DB structure resulting from the initdb, or perhaps both. Ed ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] vacuum error
If initdb is required, we might as well move to the latest stable 8.2 version. I understand my options to minimize downtime to be limited to async replication. Other ideas? BTW, the RAM looks good. You can update to 8.1.8 (if you are running 8.1.x) without an initdb. Joshua D. Drake TIA. Ed ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] vacuum error
On Tuesday March 6 2007 12:20 pm, Peter Eisentraut wrote: > Ed L. wrote: > > I am seeing the following error in pgsql 8.1.2: > > > > ERROR: could not access status of transaction 3229475082 > > DETAIL: could not open file "pg_clog/0C07": No such file or > > directory > > > > What does it mean, and what should I do about it? > > 1. Read this thread: > http://archives.postgresql.org/pgsql-general/2007-02/msg00820. >php > > 2. Upgrade to the latest 8.1.* release. > > 3. If that doesn't help, check your system for faulty > hardware, in particular for bad RAM. This is a 200gb DB with ~300 transactions/second serving 5 busy facilities, so downtime comes at a premium. We have some maintenance downtime planned for 2 weeks from now. I'm trying to understand if this can wait that long. It appears the only failure occurs during an autovacuum-initiated "VACUUM FREEZE" on template0 when it hits the pg_statistics table. However, that abort appears to be causing autovacuum to skip all its other duties as it endlessly restarts and fails again. Do I care if template0 gets a "VACUUM FREEZE"? Assuming not, is there a simple way to make autovacuum skip over template0 so it can tend to the important data in the other databases? Is restarting with 8.1.8 a known solution for this problem? Or is an initdb required to fix it? If initdb is required, we might as well move to the latest stable 8.2 version. I understand my options to minimize downtime to be limited to async replication. Other ideas? BTW, the RAM looks good. TIA. Ed ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] vacuum error
Ed L. wrote: > I am seeing the following error in pgsql 8.1.2: > ERROR: could not access status of transaction 3229475082 > DETAIL: could not open file "pg_clog/0C07": No such file or directory > > What does it mean, and what should I do about it? 1. Read this thread: http://archives.postgresql.org/pgsql-general/2007-02/msg00820.php 2. Upgrade to the latest 8.1.* release. 3. If that doesn't help, check your system for faulty hardware, in particular for bad RAM. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] vacuum error
I am seeing the following error in pgsql 8.1.2: 2007-03-05 10:00:51.106 PST [9834]DEBUG: vacuuming "pg_toast.pg_toast_1260" 2007-03-05 10:00:51.106 PST [9834]DEBUG: index "pg_toast_1260_index" now contains 0 row versions in 1 pages 2007-03-05 10:00:51.106 PST [9834]DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. 2007-03-05 10:00:51.106 PST [9834]DEBUG: "pg_toast_1260": found 0 removable, 0 nonremovable row versions in 0 pages 2007-03-05 10:00:51.106 PST [9834]DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. 2007-03-05 10:00:51.106 PST [9834]DEBUG: vacuuming "pg_catalog.pg_statistic" 2007-03-05 10:00:51.107 PST [9834]ERROR: could not access status of transaction 3229475082 2007-03-05 10:00:51.107 PST [9834]DETAIL: could not open file "pg_clog/0C07": No such file or directory What does it mean, and what should I do about it? TIA. Ed ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] vacuum error "left link changed unexpectedly"
Ulrich Wisser <[EMAIL PROTECTED]> writes: >indexrelid > --- > userclick_i01 > userclick_i02 > userclick_i03 > userclick_i04 > userclick_i05 > userclick_i06 > userclick_i07 > (7 rows) OK, so userclick_i02 appears to be the broken index. > How do I proceed? How can I tell which files are involved? See contrib/oid2name, or read http://developer.postgresql.org/docs/postgres/storage.html and work out the index file name for yourself. If you don't already have a copy of pg_filedump, see http://sources.redhat.com/rhdb/utilities.html regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] vacuum error "left link changed unexpectedly"
Hi Tom, No, it would be the one next to be processed. VACUUM does them in OID order, so try something like select indexrelid::regclass from pg_index where indrelid = 'public.userclick'::regclass order by indexrelid; indexrelid --- userclick_i01 userclick_i02 userclick_i03 userclick_i04 userclick_i05 userclick_i06 userclick_i07 (7 rows) How do I proceed? How can I tell which files are involved? Regards, Ulrich -- Ulrich Wisser / System Developer RELEVANT TRAFFIC SWEDEN AB, Riddarg 17A, SE-114 57 Sthlm, Sweden Direct (+46)86789755 || Cell (+46)704467893 || Fax (+46)86789769 http://www.relevanttraffic.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] vacuum error "left link changed unexpectedly"
Ulrich Wisser <[EMAIL PROTECTED]> writes: > INFO: vacuuming "public.userclick" > INFO: index "userclick_i01" now contains 13715747 row versions in 60640 > pages > DETAIL: 0 index row versions were removed. > 14209 index pages have been deleted, 14209 are currently reusable. > CPU 2.46s/6.06u sec elapsed 186.45 sec. > ERROR: left link changed unexpectedly > To me this looks as the index userclick_i01 is corrupted. No, it would be the one next to be processed. VACUUM does them in OID order, so try something like select indexrelid::regclass from pg_index where indrelid = 'public.userclick'::regclass order by indexrelid; and look to see which index comes after userclick_i01. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] vacuum error "left link changed unexpectedly"
Hi Tom, I did run vacuum verbose". INFO: vacuuming "public.userclick" INFO: index "userclick_i01" now contains 13715747 row versions in 60640 pages DETAIL: 0 index row versions were removed. 14209 index pages have been deleted, 14209 are currently reusable. CPU 2.46s/6.06u sec elapsed 186.45 sec. ERROR: left link changed unexpectedly To me this looks as the index userclick_i01 is corrupted. I can not recompile PG, this is a production system. Is there anything else I could do to find out which file is affected? Ulrich Lane wrote: Ulrich Wisser <[EMAIL PROTECTED]> writes: vacuumdb: vacuuming of database "CLIX2" failed: ERROR: left link changed unexpectedly Hm, is this repeatable? When I wrote the code I thought it was a can't-happen case, which is why the error message is so terse (it was only pure paranoia that made me put in the check at all). I suppose it must indicate a corrupted index, but I'm not clear on exactly what the nature of the corruption is. You might proceed by fixing the error message to be a little more helpful, say if (opaque->btpo_prev != leftsib) elog(ERROR, "left link changed unexpectedly in block %u of index %s", target, RelationGetRelationName(rel)); (this is in src/backend/access/nbtree/nbtpage.c, about line 863 in current sources). Once you know which index has the problem, I would like to see the output of pg_filedump on that index. After you've got the dump, a REINDEX should fix it. BTW, which Postgres version is this exactly? regards, tom lane -- Ulrich Wisser / System Developer RELEVANT TRAFFIC SWEDEN AB, Riddarg 17A, SE-114 57 Sthlm, Sweden Direct (+46)86789755 || Cell (+46)704467893 || Fax (+46)86789769 http://www.relevanttraffic.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] vacuum error "left link changed unexpectedly"
Hello Tom, thanks for your fast answer. And yes it is reproducible. It started during my vacation (of course!!!) and I get the message ever since (approx. 6 weeks, vacuum daily). We use Fedora Linux Core 2 PostgreSQL 7.4.2 I'll try to get the information you asked for over the weekend. Ulrich Ulrich Wisser <[EMAIL PROTECTED]> writes: vacuumdb: vacuuming of database "CLIX2" failed: ERROR: left link changed unexpectedly Hm, is this repeatable? When I wrote the code I thought it was a can't-happen case, which is why the error message is so terse (it was only pure paranoia that made me put in the check at all). I suppose it must indicate a corrupted index, but I'm not clear on exactly what the nature of the corruption is. You might proceed by fixing the error message to be a little more helpful, say if (opaque->btpo_prev != leftsib) elog(ERROR, "left link changed unexpectedly in block %u of index %s", target, RelationGetRelationName(rel)); (this is in src/backend/access/nbtree/nbtpage.c, about line 863 in current sources). Once you know which index has the problem, I would like to see the output of pg_filedump on that index. After you've got the dump, a REINDEX should fix it. BTW, which Postgres version is this exactly? regards, tom lane -- Ulrich Wisser / System Developer RELEVANT TRAFFIC SWEDEN AB, Riddarg 17A, SE-114 57 Sthlm, Sweden Direct (+46)86789755 || Cell (+46)704467893 || Fax (+46)86789769 http://www.relevanttraffic.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] vacuum error "left link changed unexpectedly"
Ulrich Wisser <[EMAIL PROTECTED]> writes: > vacuumdb: vacuuming of database "CLIX2" failed: ERROR: left link > changed unexpectedly Hm, is this repeatable? When I wrote the code I thought it was a can't-happen case, which is why the error message is so terse (it was only pure paranoia that made me put in the check at all). I suppose it must indicate a corrupted index, but I'm not clear on exactly what the nature of the corruption is. You might proceed by fixing the error message to be a little more helpful, say if (opaque->btpo_prev != leftsib) elog(ERROR, "left link changed unexpectedly in block %u of index %s", target, RelationGetRelationName(rel)); (this is in src/backend/access/nbtree/nbtpage.c, about line 863 in current sources). Once you know which index has the problem, I would like to see the output of pg_filedump on that index. After you've got the dump, a REINDEX should fix it. BTW, which Postgres version is this exactly? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] vacuum error "left link changed unexpectedly"
Hello, after months of flawless operations suddenly an error occurred. The database is vacuumed every night. Beside the vacuum error message the database works fine. Here the vacuum output: vacuumdb: vacuuming database "CLIX2" vacuumdb: vacuuming of database "CLIX2" failed: ERROR: left link changed unexpectedly Any ideas what is wrong? Or how to fix it? Ulrich -- Ulrich Wisser / System Developer RELEVANT TRAFFIC SWEDEN AB, Riddarg 17A, SE-114 57 Sthlm, Sweden Direct (+46)86789755 || Cell (+46)704467893 || Fax (+46)86789769 http://www.relevanttraffic.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] vacuum error
Hi, after months of flawless operation I get the following error when running VACUUM ANALYZE vacuumdb: vacuuming database "CLIX2" vacuumdb: vacuuming of database "CLIX2" failed: ERROR: left link changed unexpectedly System: Fedora Core 2, 2.6.10-1.771_FC2 Postgres 7.4.2 What does that mean? Can it be fixed? How? TIA Ulrich -- Ulrich Wisser / System Developer RELEVANT TRAFFIC SWEDEN AB, Riddarg 17A, SE-114 57 Sthlm, Sweden Direct (+46)86789755 || Cell (+46)704467893 || Fax (+46)86789769 http://www.relevanttraffic.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Vacuum Error
Kragen Sitaker <[EMAIL PROTECTED]> writes: > On Mon, Jan 12, 2004 at 06:20:23PM -0500, Tom Lane wrote: >> No; an OID collision would have occurred when you tried to create a >> table. If two tables are present in pg_class then they have different >> OIDs, and shouldn't have any conflicts in pg_statistic. > How would that OID collision manifest? Do you think the error message > might look similar? Similar, but referring to pg_class_oid_index. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] VACUUM Error
I tried to vaccum a table but get the following error : NOTICE : Child itemid in update-chain marked as unused - can't continue vc_repair frag. VACUUM What is the problem, and how do I over come it?? Thanks Hoosain Madhi Bsc. Engineering Elec. Switching Engineering VODACOM CORPORATE PARK Tel : +27 11 653 5030 Fax : +27 11 653 5941
[GENERAL] vacuum error - 6.4.2 database..
I did vacuum a database that I previously loaded 'heavily' with data and got the following results and got it everytime I repeat vacuum: elmu=> vacuum; NOTICE: BlowawayRelationBuffers(annex_log, 58): block 184 is referenced (private 0, last 0, global 1) pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. We have lost the connection to the backend, so further processing is impossible. Terminating. I am wondering now, that is this a constency problem, if so I'm really worried about the situation. Do you know about any bugs of 6.4.2 like this? THnaks Sebestyén Zoltán <[EMAIL PROTECTED]>There was a time when all on my mind was Love. MAKE INSTALL NOT WARNow I find that most of the time love's not enough in itself. I use UNIX because reboots are for hardware upgrades. Kick me! Whip me!! Make me develop on AIX!!!
