Re: Reindex does not finish 8.2.6

2008/3/13, Alvaro Herrera <alvherre(at)commandprompt(dot)com>:
> Clodoaldo escribió:
>
> > 2008/3/13, Alvaro Herrera <alvherre(at)commandprompt(dot)com>:
> > > Clodoaldo escribió:
> > >
> > >
> > > > Now what is happening is that reindex does not finish even with a
> > > > small 6,500 rows table and after a reboot. In top there is no CPU or
> > > > memory usage by postmaster and vmstat shows no disk activity.
> > >
> > > Hmm, are you vacuuming the system catalogs appropriately?
> >
> > If a simple vacuum in instead of a vacuum full is appropriate then yes
> > the db is vacuumed every three hours after each bulk insert/delete.
>
>
> As superuser? Take a look at whether relations are skipped during
> vacuum.
>
> Try vacuuming pg_class, pg_index, pg_attribute manually and see if that
> makes the problem go away. If it doesn't, then my guess is that it's
> time to see what the hanging process is doing -- try an strace on it, or
> attaching it with gdb and getting a backtrace. (I hope your binaries
> have debug symbols).

This is strace started before the reindex table command.

postmaster process:
# strace -f -p 2263
Process 2263 attached - interrupt to quit
select(6, [3 4 5], NULL, NULL, {35, 749000}) = 1 (in [5], left {23, 734000})
rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT SYS
RTMIN RT_1], NULL, 8) = 0
accept(5, {sa_family=AF_FILE, path=(at)}, [2]) = 8
getsockname(8, {sa_family=AF_FILE, path="/tmp/.s.PGSQL.5432"}, [21]) = 0
clone(Process 16014 attached (waiting for parent)
Process 16014 resumed (parent 2263 ready)
child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|SIGCHLD,
child_tidptr=0x2aaaaaac72f0) = 16014
[pid 2263] close(8) = 0
[pid 2263] rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
[pid 2263] select(6, [3 4 5], NULL, NULL, {60, 0} <unfinished ...>
[pid 16014] close(3) = 0
[pid 16014] close(4) = 0
[pid 16014] close(5) = 0
[pid 16014] close(6) = 0
[pid 16014] setsid() = 16014
[pid 16014] rt_sigaction(SIGTERM, {0x581700, [],
SA_RESTORER|SA_RESTART, 0x3e97630f30}, {0x55b5b0, [],
SA_RESTORER|SA_RESTART, 0x3e97630f30}, 8) = 0
[pid 16014] rt_sigaction(SIGQUIT, {0x581700, [],
SA_RESTORER|SA_RESTART, 0x3e97630f30}, {0x55b5b0, [],
SA_RESTORER|SA_RESTART, 0x3e97630f30}, 8) = 0
[pid 16014] rt_sigaction(SIGALRM, {0x581700, [], SA_RESTORER,
0x3e97630f30}, {SIG_IGN}, 8) = 0
[pid 16014] rt_sigprocmask(SIG_SETMASK, ~[QUIT ILL TRAP ABRT BUS FPE
SEGV ALRM TERM CONT SYS RTMIN RT_1], NULL, 8) = 0
[pid 16014] write(2, "\0\0_\0\216>\0\0tLOG: 00000: connection"..., 104) = 104
[pid 16014] setitimer(ITIMER_REAL, {it_interval={0, 0}, it_value={60,
0}}, NULL) = 0
[pid 16014] recvfrom(8, "\0\0\0\20\4\322\26.\0\0>\2054\344\367W",
8192, 0, NULL, NULL) = 16
[pid 16014] kill(16005, SIGINT) = 0
[pid 16014] kill(4294951291, SIGINT) = 0
[pid 16014] exit_group(0) = ?
Process 16014 detached
<... select resumed> ) = ? ERESTARTNOHAND (To be restarted)
--- SIGCHLD (Child exited) @ 0 (0) ---
rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT SYS
RTMIN RT_1], NULL, 8) = 0
wait4(-1, [{WIFEXITED(s) && WEXITSTATUS(s) == 0}], WNOHANG, NULL) = 16014
wait4(-1, 0x7fff77f82844, WNOHANG, NULL) = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
rt_sigreturn(0x2) = -1 EINTR (Interrupted system call)
rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT SYS
RTMIN RT_1], NULL, 8) = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
select(6, [3 4 5], NULL, NULL, {60, 0} <unfinished ...>
Process 2263 detached

Regards, Clodoaldo Pinto Neto