Re: pg_dumpall failing from possible corrupted shared memory

> What this looks like is that there's a mangled expression string in
> the catalog entry for either a check constraint or an expression index.
> The backend is dying while trying to decompile the expression for
> pg_dump.

you were write, I appears that my problem is with one of four functional date_trunc indexs that I
created for a timespace column.

Here is a greatly simplified query that still succeeds:

mydb=# select indexrelid from pg_index where indrelid = '16737';
indexrelid
------------
604243
604251
604252
604253
604254
(5 rows)

However, when I add the following function it breaks:

mydb=# select pg_get_indexdef(indexrelid) from pg_index where indrelid = '16737';
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

> What I suggest doing is turning on log_statement = all and running
> pg_dump, so that you can see the query it's dying on. That would give
> you enough info to look into the catalog entry for the constraint or
> index at hand. Worst-case, you can probably fix things by dropping and
> recreating the constraint or index ...log_min_messages = info but before you do that, I'd urge
> you to try to get as much info as you can about the nature of the
> catalog corruption. If there's a bug here, as opposed to random
> cosmic-ray damage, we can't fix it without more info.

The logs aren't really providing anymore information since I was already using "all". However, I
turned log_min_messages from "warning to "info".

%mydb LOG: statement:
select pg_get_indexdef(indexrelid)
from pg_index
where indrelid = '16737';
% LOG: server process (PID 16099) was terminated by signal 11
% LOG: terminating any other active server processes
% LOG: all server processes terminated;
reinitializing
% LOG: database system was interrupted at 2006-10-26 18:09:09 PDT
% LOG: checkpoint record is at 0/8E9161A8
% LOG: redo record is at 0/8E9161A8;
undo record is at 0/0;
shutdown TRUE
% LOG: next transaction ID: 174591; next OID: 621178
% LOG: next MultiXactId: 53; next MultiXactOffset: 115
% LOG: database system was not properly shut down;
automatic recovery in progress
%mydb FATAL: the database system is starting up
% LOG: record with zero length at 0/8E9161EC
% LOG: redo is not required
% LOG: database system is ready
% LOG: transaction ID wrap limit is 1073790580,
limited by database "postgres"

Thanks for the help Tom.

Regards,

Richard Broersma Jr.