"Rajesh Kumar Mallah" <mallah.rajesh@gmail.com> writes: > On 12/16/06, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> What is that nonstandard function you're using in the index?
> Its declared immutable , it queries the same table ,
You just lost. If it's querying the table it's not immutable, almost by definition --- certainly not if the table is not static, as yours seemingly is not. This one is cheating even more by trying to read another table too :-(
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 ?
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.
regds mallah.
This particular case is probably not doing anything worse than following index entries pointing at no-longer-existent tuple IDs, which I think we have adequate defenses against now. But in general an index function has got to be capable of operating even when the underlying table is not in a logically consistent state, because the function itself is used in creating/maintaining that consistency. What you've got here definitely fails that test.
