Re: Adding a column with default value possibly corrupting a functional index.

"Rajesh Kumar Mallah" <mallah(dot)rajesh(at)gmail(dot)com> writes:
> On 12/16/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.

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. 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.

regards, tom lane