Re: Reindex does not finish 8.2.6 - Mailing list pgsql-general

2008/3/13, Alvaro Herrera <alvherre@commandprompt.com>:
> Clodoaldo escribió:
>
> > 2008/3/13, Alvaro Herrera <alvherre@commandprompt.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=@}, [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