Thread: troubleshoot error when creating index

Howdy:

I'm running Postgres 7.1.3 on Mandrake 8.0 Linux, kernel version 2.4.16.
The debug level of postgres I believe is -d2.

I'm trying to figure out how to troubleshoot a few errors I'm
getting when I try to create index and run vacuum. 

I have a script that I call ... in it:

[excerpt]

create index db2_table1_i on db2_table1 (c_contract_num, c_mbr_num);
vacuum verbose analyze db2_table1;

create index db2_table2_i on db2_table2 (c_contract_num, d_eff_dt);
vacuum verbose analyze db2_table2;

[/excerpt]

From what I can tell, when I look to see if the indexes are created,
it appears that only for the first table, but not the rest (there are
about 13 new tables that should have indices and vacuumed).

When I run the script by hand featuring only the second table / index,
I get the following:

[error]

psql:./post_load:1: pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally
        before or while processing the request.
psql:./post_load:1: connection to server was lost

[/error]

Postgres IS running, but when I run the script, it stops and
restarts (I'm guessing) because when I try to go in via the
command line, I get a message that says, something about
starting database. (sorry, I can't find the exact message on
the terminal).

I can't find anything in the serverlog file that suggest that there are
errors occurring - or what exactly is failing.  The only thing in the
/var/log/messages is whenever someone kicks off psql for one reason
or another ... but that doesn't help.  I imagine that maybe the
table is corrupt, but how can I tell?

How can I troubleshoot this and get a reasonable answer?

Thanks!

-X

"Johnson, Shaunn" <SJohnson6@bcbsm.com> writes:
> I can't find anything in the serverlog file that suggest that there are
> errors occurring - or what exactly is failing.  The only thing in the
> /var/log/messages is whenever someone kicks off psql for one reason
> or another ... but that doesn't help.

In that case you should first work on figuring out why your logging
setup doesn't work.  A backend crash should definitely provoke all kinds
of log output --- not only a "child process exited with status NN"
message, but also complaints from any concurrent backends that got told
to quit.

The simplest, most reliable setup is to not use syslog logging but just
direct the postmaster's stdout/stderr into some file.  Try that, and be
sure not to use -S in the postmaster options.

> I imagine that maybe the
> table is corrupt, but how can I tell?

Sounds that way to me too.  If you just do a "select * from table" do
you get a similar crash?

            regards, tom lane