Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 011159FA372 for ; Sun, 17 Dec 2006 00:00:47 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 59788-01 for ; Sun, 17 Dec 2006 00:00:33 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.171]) by postgresql.org (Postfix) with ESMTP id 0D1EB9FA364 for ; Sun, 17 Dec 2006 00:00:31 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so1182581ugf for ; Sat, 16 Dec 2006 20:00:31 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=uR7tARwPo83ogfXFfM5wzDj/s/MZfZyexChJtpvZB9trs7j8JEuNLnTK5UyKwKJSTa8riP93pwgaTV+jurw96jcZFvdZ+WJ0Y97XgiNsiFI3q8oni9Y1dhWx+BLAdYDivr3tJt380CXT9fE8Xg2/4BoKsm42O3BKyxHfLG1koTw= Received: by 10.66.221.6 with SMTP id t6mr2723884ugg.1166328030971; Sat, 16 Dec 2006 20:00:30 -0800 (PST) Received: by 10.66.216.15 with HTTP; Sat, 16 Dec 2006 20:00:30 -0800 (PST) Message-ID: Date: Sun, 17 Dec 2006 09:30:30 +0530 From: "Rajesh Kumar Mallah" To: "Tom Lane" Subject: Re: Adding a column with default value possibly corrupting a functional index. Cc: "Lista Postgres" In-Reply-To: <10285.1166326604@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <501.1166288322@sss.pgh.pa.us> <6828.1166291585@sss.pgh.pa.us> <10285.1166326604@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/241 X-Sequence-Number: 24033 > > > why does ALTER TABLE ADD new_col int default 0 rebuilds > > existing indexes ? > > Because it has to rewrite the whole table to insert the default value > in every row. A REINDEX is way more efficient for recovering from that > than any row-by-row update would be. thanks for explaining. > > >> I wonder whether we need to do something to actively prevent functions > >> used in an index from querying the database? It's not too hard to > >> imagine crashing the backend by playing this sort of game. > > > the game was seemingly fulfilling a requirement. dunno what > > i should be doing now. > > It sorta looks to me like you're trying to get the effect of a > materialized view --- have you looked at the techdocs pages about > how to do those in Postgres? We map the URL to category_id . if someone requests URL SPORTS_AND_ENTERTAINMENT/SPORT_PRODUCTS/CAR_RACING we server the data in category_id 641 thats why the functional index is required. You suggesting to create a mat view for this lookup ? Actually there is no *real* issue we seldom add columns to that table. I might as well leave the system like this and REINDEX the table after i do something that possibly corrupts the functional index. SELECT category_id , upper(general.cat_url(category_id)) from general.web_category_master limit 10; category_id upper ----------- ------------------------------------------------------------ 641 SPORTS_AND_ENTERTAINMENT/SPORT_PRODUCTS/CAR_RACING 1407 SECURITY_AND_PROTECTION/SECURITY_EQUIPMENT 1065 MINERAL_AND_METALS/MINERALS_AND_REFRACTORIES 474 HEALTH_AND_BEAUTY/PERSONAL_CARE/OTHERS 561 OFFICE_SUPPLIES/OTHERS 277 CONSTRUCTION_AND_REAL_ESTATE/REAL_ESTATE/SHOPS 1017 INDUSTRIAL_SUPPLIES/INDUSTRIAL_BRAKES_AND_CLUTCHES 580 OFFICE_SUPPLIES/PHOTOGRAPHY_AND_OPTICS/TIME_RECORDING 836 CHEMICALS/FINE_CHEMICALS_ALL i think i should also change the function type to STABLE instead of IMMUTABLE . (it does not have impact on this issue though) Regds mallah. > regards, tom lane >