Hi,

What would cause or what should I do with a table that hangs during its
reindexing? I can see in the messages the last table to reindex, so
sequentially the next table must be the problem.
I have statement_timeout set for 6 hours, then I get "canceling statement
due to statement timeout" Ok, that's fine, and I don't think setting the
statement_timeout to 0 will really help. The question is why did it hang or
what are some reasons that would make a table hang a reindexing? And is
there a solution?

At 1:45 AM the reindexing was going fine. Then, it just hung up -- &
finally cancelled.

Thanks for any ideas. My experience has been that this happens sometimes
then the next reindexdb -a , everything reindexes fine.

~DJK

__________________________________________________ _______________
Interest Rates NEAR 39yr LOWS! $430,000 Mortgage for $1,299/mo - Calculate
new payment
http://www.lowermybills.com/lre/inde...132&moid=14888


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Wed, 2007-05-02 at 09:50, dx k9 wrote:
> Hi,
>
> What would cause or what should I do with a table that hangs during its
> reindexing? I can see in the messages the last table to reindex, so
> sequentially the next table must be the problem.
> I have statement_timeout set for 6 hours, then I get "canceling statement
> due to statement timeout" Ok, that's fine, and I don't think setting the
> statement_timeout to 0 will really help. The question is why did it hang or
> what are some reasons that would make a table hang a reindexing? And is
> there a solution?

The real question is did it hang? If the machine was still working on
reindexing then it wasn't hung, it was just taking longer than you
wanted it to. Which can also be a problem, but isn't the same thing as
hanging, and as it's a different problem, would have a different
solution than if it is hanging.

So, what does vmstat / top / iostat have to say about this "hang"? Was
it really hanging, or just taking a long time?

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Thanks for the response Scott.

It turns out it was a temporary database and temporary table, that just
wasn't there maybe it thought it was there from some type of snapshot then
the next minute it was gone.

Too bad we can't come up with a -e parameter for exclude say,
Something like this would be nice
reindexdb -a -e -p 4444 -e tempdb template1

The way it is now, I just have a 76 line script with each -d database . I
also have to keep it up to date on my own, when they add a new or remove
some database in the cluster.

~DjK

__________________________________________________ _______________
Download Messenger. Join the i’m Initiative. Help make a difference today.
http://im.live.com/messenger/im/home...ce=TAGHM_APR07


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Wed, 2007-05-02 at 10:49, dx k9 wrote:
> Thanks for the response Scott.
>
> It turns out it was a temporary database and temporary table, that just
> wasn't there maybe it thought it was there from some type of snapshot then
> the next minute it was gone.
>
> Too bad we can't come up with a -e parameter for exclude say,
> Something like this would be nice
> reindexdb -a -e -p 4444 -e tempdb template1
>
> The way it is now, I just have a 76 line script with each -d database . I
> also have to keep it up to date on my own, when they add a new or remove
> some database in the cluster.

Had a similar problem at my last job. I wrote a bash script that used
psql to get a list of databases and update the file that listed all the
dbs to go through. We also used that list to do backups of each db
individually.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

"dx k9" <bitsandbytes88@hotmail.com> writes:
> [ stuck reindex ]
> It turns out it was a temporary database and temporary table, that just
> wasn't there maybe it thought it was there from some type of snapshot then
> the next minute it was gone.

Hmm, there is not any filter in ReindexDatabase() to exclude temp tables
of other backends, but it sure seems like there needs to be. CLUSTER
might have the same issue. I think we fixed this in VACUUM long ago,
but we need to check the other commands that grovel over all of a database.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > Tom Lane wrote:
> >> Yeah, an extra fetch of the pg_class row doesn't seem all that nice.
> >> I think you'd want to check it in approximately the same two places
> >> where pg_class_ownercheck() is applied (one for the 1-xact and one for
> >> the multi-xact path).
>
> > Actually, the 1-xact path does not need it, because the check is already
> > elsewhere.
>
> Yeah, but if you do it there it's one added comparison
> (isOtherTempNamespace is very cheap, and you can get the namespace
> cheaply from the already-open rel). This way you need an extra syscache
> lookup because you are insisting on doing the check in a place where you
> don't have easy access to the pg_class row. Doesn't seem better.

I'm not sure I follow. Are you suggesting adding a new function,
similar to pg_class_ownercheck, which additionally checks for temp-ness?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings