Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 5DC1F9F96E7; Sat, 25 Aug 2007 19:22:00 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 10672-06; Sat, 25 Aug 2007 19:21:50 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from lists.commandprompt.com (host-254.commandprompt.net [207.173.203.254]) by postgresql.org (Postfix) with ESMTP id BB3FF9F95C3; Sat, 25 Aug 2007 19:21:55 -0300 (ADT) Received: from perhan.alvh.no-ip.org (201-221-221-172.bk12-dsl.surnet.cl [201.221.221.172]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id l7PMLt9A009888; Sat, 25 Aug 2007 15:21:56 -0700 Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id DC94047C5F; Sat, 25 Aug 2007 18:21:50 -0400 (CLT) Date: Sat, 25 Aug 2007 18:21:50 -0400 From: Alvaro Herrera To: Tom Lane Cc: dx k9 , pgsql-admin@postgresql.org, pgsql-hackers@postgresql.org Subject: Re: reindexdb hangs Message-ID: <20070825222150.GU31461@alvh.no-ip.org> References: <22018.1178144703@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="VS++wcV0S1rZb1Fb" Content-Disposition: inline In-Reply-To: <22018.1178144703@sss.pgh.pa.us> User-Agent: Mutt/1.5.16 (2007-06-11) X-Virus-Scanned: ClamAV 0.91.1/4054/Fri Aug 24 09:55:59 2007 on projects.commandprompt.com X-Virus-Status: Clean X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]); Sat, 25 Aug 2007 15:21:57 -0700 (PDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/321 X-Sequence-Number: 26652 --VS++wcV0S1rZb1Fb Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Tom Lane wrote: > "dx k9" 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. Was this ever fixed? I think it wasn't, because I don't see any check in ReindexDatabase. Here is a patch to add one. I examined cluster.c and it does seem to be missing a check too. I'm not sure where to add one though; the best choice would be the place where the list of rels is built, but that scans only pg_index, so it doesn't have access to the namespace of each rel. So one idea would be to get the pg_class row for each candidate, but that seems slow. Another idea would be to just add all the candidates and silently skip the temp indexes in cluster_rel. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. --VS++wcV0S1rZb1Fb Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="reindex-temp.patch" Index: src/backend/commands/indexcmds.c =================================================================== RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/commands/indexcmds.c,v retrieving revision 1.162 diff -c -p -r1.162 indexcmds.c *** src/backend/commands/indexcmds.c 25 Aug 2007 19:08:19 -0000 1.162 --- src/backend/commands/indexcmds.c 25 Aug 2007 22:11:18 -0000 *************** ReindexDatabase(const char *databaseName *** 1292,1297 **** --- 1292,1301 ---- if (classtuple->relkind != RELKIND_RELATION) continue; + /* Skip temp tables of other backends; we can't reindex them at all */ + if (isOtherTempNamespace(classtuple->relnamespace)) + continue; + /* Check user/system classification, and optionally skip */ if (IsSystemClass(classtuple)) { --VS++wcV0S1rZb1Fb--