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

On 12/16/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> "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.

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.
>
> regards, tom lane
>