"Rajesh Kumar Mallah" <mallah.rajesh@xxxxxxxxx> writes: > On 12/16/06, Tom Lane <tgl@xxxxxxxxxxxxx> 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
