From: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Lista Postgres <pgsql-admin@postgresql.org>
Subject: Re: Adding a column with default value possibly corrupting a functional index.
Date: Sun, 17 Dec 2006 09:30:30 +0530
Message-ID: <a97c77030612162000v3dbaa32q70b1e294d4b84478@mail.gmail.com> (raw)
In-Reply-To: <10285.1166326604@sss.pgh.pa.us>
References: <a97c77030612160824w102c2326n23706171ba8a6b3a@mail.gmail.com>
<501.1166288322@sss.pgh.pa.us>
<a97c77030612160927u608b4b00td8d71ee442410b7f@mail.gmail.com>
<6828.1166291585@sss.pgh.pa.us>
<a97c77030612161840w5d127102q1e1b818a06334d9e@mail.gmail.com>
<10285.1166326604@sss.pgh.pa.us>
>
> > 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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: mallah.rajesh@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: Adding a column with default value possibly corrupting a functional index.
In-Reply-To: <a97c77030612162000v3dbaa32q70b1e294d4b84478@mail.gmail.com>
References: <a97c77030612160824w102c2326n23706171ba8a6b3a@mail.gmail.com> <501.1166288322@sss.pgh.pa.us> <a97c77030612160927u608b4b00td8d71ee442410b7f@mail.gmail.com> <6828.1166291585@sss.pgh.pa.us> <a97c77030612161840w5d127102q1e1b818a06334d9e@mail.gmail.com> <10285.1166326604@sss.pgh.pa.us> <a97c77030612162000v3dbaa32q70b1e294d4b84478@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox