Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 5E9719FA0B7 for ; Sat, 16 Dec 2006 23:36:56 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 03957-10 for ; Sat, 16 Dec 2006 23:36:40 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 23B959F9F08 for ; Sat, 16 Dec 2006 23:36:47 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id kBH3ai81010286; Sat, 16 Dec 2006 22:36:44 -0500 (EST) To: "Rajesh Kumar Mallah" cc: "Lista Postgres" Subject: Re: Adding a column with default value possibly corrupting a functional index. In-reply-to: References: <501.1166288322@sss.pgh.pa.us> <6828.1166291585@sss.pgh.pa.us> Comments: In-reply-to "Rajesh Kumar Mallah" message dated "Sun, 17 Dec 2006 08:10:50 +0530" Date: Sat, 16 Dec 2006 22:36:44 -0500 Message-ID: <10285.1166326604@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/240 X-Sequence-Number: 24032 "Rajesh Kumar Mallah" writes: > On 12/16/06, Tom Lane wrote: >> I think the proximate cause of the problem is that the function's >> SELECT is trying to use an index on the category_id column, >> and the REINDEX done by ALTER TABLE happens to rebuild the two indexes >> in the other order, such that the one on category_id isn't valid yet >> when the functional index is rebuilt. > 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. >> 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? regards, tom lane