Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 668CD9FA550; Wed, 29 Aug 2007 19:24:08 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 93267-07; Wed, 29 Aug 2007 19:24:00 -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 0A34B9FA443; Wed, 29 Aug 2007 19:24:03 -0300 (ADT) Received: from perhan.alvh.no-ip.org (201-220-104-109.bk9-dsl.surnet.cl [201.220.104.109]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id l7TMNlhN028910; Wed, 29 Aug 2007 15:24:04 -0700 Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id C697447C5F; Wed, 29 Aug 2007 18:23:41 -0400 (CLT) Date: Wed, 29 Aug 2007 18:23:41 -0400 From: Alvaro Herrera To: Tom Lane Cc: dx k9 , pgsql-admin@postgresql.org, pgsql-hackers@postgresql.org Subject: Re: reindexdb hangs Message-ID: <20070829222341.GX7911@alvh.no-ip.org> References: <22018.1178144703@sss.pgh.pa.us> <20070825222150.GU31461@alvh.no-ip.org> <4909.1188082313@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="oLBj+sq0vYjzfsbl" Content-Disposition: inline In-Reply-To: <4909.1188082313@sss.pgh.pa.us> User-Agent: Mutt/1.5.16 (2007-06-11) X-Virus-Scanned: ClamAV 0.91.1/4089/Tue Aug 28 13:45:06 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]); Wed, 29 Aug 2007 15:24:05 -0700 (PDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/407 X-Sequence-Number: 26738 --oLBj+sq0vYjzfsbl Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Tom Lane wrote: > > 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. > > 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. We only need logic enough to skip temp tables silently while building the list in the multi-xact path. What this means is that very few people, if any, clusters temp tables; because as soon as you do, a plain CLUSTER command in another session errors out with alvherre=# cluster; ERROR: cannot cluster temporary tables of other sessions So, patch attached. > Are there any other commands to be worried about? I can't see any > besides VACUUM/ANALYZE, and those seem covered. I can't think of any. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support --oLBj+sq0vYjzfsbl Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="skiptemp.patch" Index: src/backend/commands/cluster.c =================================================================== RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/commands/cluster.c,v retrieving revision 1.162 diff -c -p -r1.162 cluster.c *** src/backend/commands/cluster.c 19 May 2007 01:02:34 -0000 1.162 --- src/backend/commands/cluster.c 29 Aug 2007 22:07:04 -0000 *************** cluster_rel(RelToCluster *rvtc, bool rec *** 276,281 **** --- 276,287 ---- } /* + * Note: we don't need to check whether the table is a temp for a + * remote session here, because it will be checked in + * check_index_is_clusterable, below. + */ + + /* * Check that the index still exists */ if (!SearchSysCacheExists(RELOID, *************** swap_relation_files(Oid r1, Oid r2, Tran *** 995,1004 **** } /* ! * Get a list of tables that the current user owns and ! * have indisclustered set. Return the list in a List * of rvsToCluster ! * with the tableOid and the indexOid on which the table is already ! * clustered. */ static List * get_tables_to_cluster(MemoryContext cluster_context) --- 1001,1037 ---- } /* ! * Returns whether a pg_class tuple belongs to a temp namespace which is not ! * our backend's. ! */ ! static bool ! relid_is_other_temp(Oid class_oid) ! { ! HeapTuple tuple; ! Form_pg_class classForm; ! bool istemp; ! ! tuple = SearchSysCache(RELOID, ! ObjectIdGetDatum(class_oid), ! 0, 0, 0); ! if (!HeapTupleIsValid(tuple)) ! ereport(ERROR, ! (errcode(ERRCODE_UNDEFINED_TABLE), ! errmsg("relation with OID %u does not exist", class_oid))); ! ! classForm = (Form_pg_class) GETSTRUCT(tuple); ! istemp = isOtherTempNamespace(classForm->relnamespace); ! ! ReleaseSysCache(tuple); ! ! return istemp; ! } ! ! /* ! * Get a list of tables that the current user owns, have indisclustered set, ! * and are not temp tables of remote backends. Return the list in a List * of ! * rvsToCluster with the tableOid and the indexOid on which the table is ! * already clustered. */ static List * get_tables_to_cluster(MemoryContext cluster_context) *************** get_tables_to_cluster(MemoryContext clus *** 1031,1036 **** --- 1064,1072 ---- if (!pg_class_ownercheck(index->indrelid, GetUserId())) continue; + if (relid_is_other_temp(index->indrelid)) + continue; + /* * We have to build the list in a different memory context so it will * survive the cross-transaction processing 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)) { --oLBj+sq0vYjzfsbl--