Re: pg_dumpall failing from possible corrupted shared memory - Mailing list pgsql-general

> 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.