Обсуждение: Broken pg_class table

I accidentally wrecked the pg_class table in a database by leaving off
the where clause of an update statement when setting the reltriggers
back to the count from pg_trigger after a bulk data load.  I can no
longer connect to the database, although it does start up without
error.  running psql [dbname] causes extreme cpu load, and the command
just hangs.  Memory usage continues climbing until a oom killer crashes
everything.  I presume that my blunder is causing some sort of infinite
loop during postmaster startup for the spawned process.  Is there a way
I can get direct access to the file for pg_class and reset the
reltriggers to the correct values without a full dbinit process?

Roger Ging <roger@musicreports.com> writes:
> I accidentally wrecked the pg_class table in a database by leaving off
> the where clause of an update statement when setting the reltriggers
> back to the count from pg_trigger after a bulk data load.

What was the *exact* update statement, and which PG version is this?

            regards, tom lane

Sorry, should have been more specific.<br /> Version 8.1.0<br /><br /> update statement called from a bash script
where$tbl is table name and $sch is schema name<br /><br /> update pg_class set reltriggers = <br /> (select count(*)
frompg_trigger where tgrelid = <br /> (select oid from pg_class where relname = '$tbl' and relnamespace = <br />
(selectoid from pg_namespace where nspname = '$sch')))--should have been a limiting where clause here<br /><br /><br />
Thanks,<br/><br /> Roger<br /><br /> Tom Lane wrote: <blockquote cite="mid27390.1153026781@sss.pgh.pa.us"
type="cite"><prewrap="">Roger Ging <a class="moz-txt-link-rfc2396E"
href="mailto:roger@musicreports.com"><roger@musicreports.com></a>writes: </pre><blockquote type="cite"><pre
wrap="">Iaccidentally wrecked the pg_class table in a database by leaving off  
the where clause of an update statement when setting the reltriggers
back to the count from pg_trigger after a bulk data load.   </pre></blockquote><pre wrap="">
What was the *exact* update statement, and which PG version is this?
        regards, tom lane </pre></blockquote>

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

Tom,<br /><br /> Thanks for the info.  I have begun the rebuild from backups.  The machine is  a data warehouse with
nobackups of it's own, but all of the data is available from other servers.<br /><br /> Roger<br /><br /> Tom Lane
wrote:<blockquote cite="mid20535.1153076410@sss.pgh.pa.us" type="cite"><pre wrap="">Roger Ging <a
class="moz-txt-link-rfc2396E"href="mailto:roger@musicreports.com"><roger@musicreports.com></a> writes:
</pre><blockquotetype="cite"><pre wrap="">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   </pre></blockquote><pre wrap="">
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 </pre></blockquote>