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

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

thanks for explaining.

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

We map the URL to category_id .
if someone requests URL SPORTS_AND_ENTERTAINMENT/SPORT_PRODUCTS/CAR_RACING
we server the data in category_id 641 thats why the functional
index is required. You suggesting to create a mat view for this lookup ?

Actually there is no *real* issue we seldom add columns to that
table. I might as well leave the system like this and REINDEX the table
after i do something that possibly corrupts the functional index.

SELECT  category_id , upper(general.cat_url(category_id)) from
general.web_category_master limit 10;

category_id                            upper
----------- ------------------------------------------------------------
        641 SPORTS_AND_ENTERTAINMENT/SPORT_PRODUCTS/CAR_RACING
       1407 SECURITY_AND_PROTECTION/SECURITY_EQUIPMENT
       1065 MINERAL_AND_METALS/MINERALS_AND_REFRACTORIES
        474 HEALTH_AND_BEAUTY/PERSONAL_CARE/OTHERS
        561 OFFICE_SUPPLIES/OTHERS
        277 CONSTRUCTION_AND_REAL_ESTATE/REAL_ESTATE/SHOPS
       1017 INDUSTRIAL_SUPPLIES/INDUSTRIAL_BRAKES_AND_CLUTCHES
        580 OFFICE_SUPPLIES/PHOTOGRAPHY_AND_OPTICS/TIME_RECORDING
        836 CHEMICALS/FINE_CHEMICALS_ALL

i think i should also change the function type to STABLE instead of
IMMUTABLE . (it does not have impact on this issue though)

Regds
mallah.
>                         regards, tom lane
>