X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 7D8499FA25A for ; Sun, 16 Jul 2006 16:00:24 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 42698-05 for ; Sun, 16 Jul 2006 16:00:14 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 5EE429F95E3 for ; Sun, 16 Jul 2006 16:00:14 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k6GJ0ApE020536; Sun, 16 Jul 2006 15:00:10 -0400 (EDT) To: Roger Ging cc: pgsql-admin@postgresql.org Subject: Re: Broken pg_class table In-reply-to: <44BA5B23.2070204@musicreports.com> References: <44B7FCA1.40006@musicreports.com> <27390.1153026781@sss.pgh.pa.us> <44BA5B23.2070204@musicreports.com> Comments: In-reply-to Roger Ging message dated "Sun, 16 Jul 2006 08:28:35 -0700" Date: Sun, 16 Jul 2006 15:00:10 -0400 Message-ID: <20535.1153076410@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/135 X-Sequence-Number: 22381 Roger Ging writes: > update pg_class set reltriggers = > (select count(*) from pg_trigger where tgrelid = > (select oid from pg_class where relname = '$tbl' and relnamespace = > (select oid from pg_namespace where nspname = '$sch'))) > --should have been a limiting where clause here I see, so now *all* the rows of pg_class have some nonzero reltriggers value, and so the backend is looking for nonexistent entries in pg_triggers ... or it would be, if it could finish opening pg_triggers, but it can't do that without finding the nonexistent pg_triggers entries for pg_triggers ... infinite recursion time. AFAICS your only hope of getting that database back up is to manually reset the reltriggers fields to zero in the rows for at least the critical system catalogs (pg_class, pg_attribute, pg_type, pg_proc, pg_triggers, pg_constraint might be enough). This is doable with a hex editor, but it seems kinda painful. You might be best off reverting to your last backup. If you want to try it, I'd suggest looking for string table names in the pg_class file and zeroing the two-byte reltriggers field occurring exactly 112 bytes beyond the start of each name (that appears to be the correct offset in 8.1). Possibly the easiest thing is to zero *all* these fields, and then reconstruct the ones that should be nonzero after you can get into the DB again. FWIW, as of 8.1 there's no longer any need to do anything as risky as mucking with reltriggers by hand. Use ALTER TABLE ENABLE/DISABLE TRIGGER instead. And by the by, you should definitely not still be using 8.1.0. regards, tom lane