From: Tom Lane <tgl@sss.pgh.pa.us>
To: Roger Ging <roger@musicreports.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Broken pg_class table
Date: Sun, 16 Jul 2006 15:00:10 -0400
Message-ID: <20535.1153076410@sss.pgh.pa.us> (raw)
In-Reply-To: <44BA5B23.2070204@musicreports.com>
References: <44B7FCA1.40006@musicreports.com>
<27390.1153026781@sss.pgh.pa.us>
<44BA5B23.2070204@musicreports.com>
Roger Ging <roger@musicreports.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, roger@musicreports.com
Subject: Re: Broken pg_class table
In-Reply-To: <20535.1153076410@sss.pgh.pa.us>
References: <44B7FCA1.40006@musicreports.com> <27390.1153026781@sss.pgh.pa.us> <44BA5B23.2070204@musicreports.com> <20535.1153076410@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox