Обсуждение: Adding a column with default value possibly corrupting a functional index.
Adding a column with default value possibly corrupting a functional index.
От
"Rajesh Kumar Mallah"
Дата:
Hi,
I have an index on upper(general.cat_url(category_id)) on a table.
when i add a column *with* default value , a query that previously
used to give result does not give results anymore. REINDEX'ing the
table produces correct result. if no default value is giving while adding
the column the query continues to give proper result.
Regds
mallah.
tradein_clients=> SELECT category_id from general.web_category_master where upper(general.cat_url(category_id)::text) = 'AGRICULTURE';
+-------------+
| category_id |
+-------------+
| 1 |
+-------------+
(1 row)
tradein_clients=> explain SELECT category_id from general.web_category_master where upper(general.cat_url(category_id)::text) = 'AGRICULTURE';
+-------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
+-------------------------------------------------------------------------------------------------------------+
| Index Scan using web_category_master_upper_cat_url on web_category_master (cost=0.00..8.02 rows=1 width=4) |
| Index Cond: (upper((general.cat_url(category_id))::text) = 'AGRICULTURE'::text) |
+-------------------------------------------------------------------------------------------------------------+
(2 rows)
tradein_clients=> ALTER TABLE general.web_category_master add test_id int default 0;
ALTER TABLE
tradein_clients=> SELECT category_id from general.web_category_master where upper(general.cat_url (category_id)::text) = 'AGRICULTURE';
+-------------+
| category_id |
+-------------+
+-------------+
(0 rows)
tradein_clients=> REINDEX TABLE general.web_category_master;
REINDEX
tradein_clients=> SELECT category_id from general.web_category_master where upper(general.cat_url(category_id)::text) = 'AGRICULTURE';
+-------------+
| category_id |
+-------------+
| 1 |
+-------------+
(1 row)
I have an index on upper(general.cat_url(category_id)) on a table.
when i add a column *with* default value , a query that previously
used to give result does not give results anymore. REINDEX'ing the
table produces correct result. if no default value is giving while adding
the column the query continues to give proper result.
Regds
mallah.
tradein_clients=> SELECT category_id from general.web_category_master where upper(general.cat_url(category_id)::text) = 'AGRICULTURE';
+-------------+
| category_id |
+-------------+
| 1 |
+-------------+
(1 row)
tradein_clients=> explain SELECT category_id from general.web_category_master where upper(general.cat_url(category_id)::text) = 'AGRICULTURE';
+-------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
+-------------------------------------------------------------------------------------------------------------+
| Index Scan using web_category_master_upper_cat_url on web_category_master (cost=0.00..8.02 rows=1 width=4) |
| Index Cond: (upper((general.cat_url(category_id))::text) = 'AGRICULTURE'::text) |
+-------------------------------------------------------------------------------------------------------------+
(2 rows)
tradein_clients=> ALTER TABLE general.web_category_master add test_id int default 0;
ALTER TABLE
tradein_clients=> SELECT category_id from general.web_category_master where upper(general.cat_url (category_id)::text) = 'AGRICULTURE';
+-------------+
| category_id |
+-------------+
+-------------+
(0 rows)
tradein_clients=> REINDEX TABLE general.web_category_master;
REINDEX
tradein_clients=> SELECT category_id from general.web_category_master where upper(general.cat_url(category_id)::text) = 'AGRICULTURE';
+-------------+
| category_id |
+-------------+
| 1 |
+-------------+
(1 row)
"Rajesh Kumar Mallah" <mallah.rajesh@gmail.com> writes:
> I have an index on upper(general.cat_url(category_id)) on a table.
> when i add a column *with* default value , a query that previously
> used to give result does not give results anymore. REINDEX'ing the
> table produces correct result.
Can you provide a self-contained example of this? What PG version are
you using? What is that nonstandard function you're using in the index?
regards, tom lane
Re: Adding a column with default value possibly corrupting a functional index.
От
"Rajesh Kumar Mallah"
Дата:
On 12/16/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Hi,
thanks for the reply.
that was the first thing i was trying to do before the post
so far i have not been able to.
8.2.0
Its declared immutable , it queries the same table , its recursive
and it queries another custom function also.
dumping the function def below , lemme know if there is anything
obvious.
Warm Regds
mallah.
CREATE OR REPLACE FUNCTION general.cat_url (integer) RETURNS varchar AS '
DECLARE v_category_id ALIAS FOR $1;
DECLARE tmp_record RECORD;
DECLARE tmp_name VARCHAR;
DECLARE tmp_code VARCHAR;
BEGIN
tmp_code := '''' ;
IF v_category_id = -1 THEN
RETURN ''NO SUCH CATEGORY'';
END IF;
SELECT INTO tmp_record name, category_id, parent_category_id from general.web_category_master join general.category_tree using(category_id) where category_id=v_category_id and link is false;
IF NOT FOUND THEN
RETURN '''';
END IF;
tmp_name := general.cat_url(tmp_record.parent_category_id) ;
IF tmp_record.category_id <> 0 THEN
tmp_code := tmp_name || ''/'' || general.dir_name(tmp_record.name) ;
END IF;
tmp_code = ltrim(tmp_code,''/'');
RETURN tmp_code;
END;
' LANGUAGE 'plpgsql' IMMUTABLE;
"Rajesh Kumar Mallah" <mallah.rajesh@gmail.com> writes:
> I have an index on upper(general.cat_url(category_id)) on a table.
> when i add a column *with* default value , a query that previously
> used to give result does not give results anymore. REINDEX'ing the
> table produces correct result.
Can you provide a self-contained example of this?
Hi,
thanks for the reply.
that was the first thing i was trying to do before the post
so far i have not been able to.
What PG version are
you using?
What is that nonstandard function you're using in the index?
Its declared immutable , it queries the same table , its recursive
and it queries another custom function also.
dumping the function def below , lemme know if there is anything
obvious.
Warm Regds
mallah.
CREATE OR REPLACE FUNCTION general.cat_url (integer) RETURNS varchar AS '
DECLARE v_category_id ALIAS FOR $1;
DECLARE tmp_record RECORD;
DECLARE tmp_name VARCHAR;
DECLARE tmp_code VARCHAR;
BEGIN
tmp_code := '''' ;
IF v_category_id = -1 THEN
RETURN ''NO SUCH CATEGORY'';
END IF;
SELECT INTO tmp_record name, category_id, parent_category_id from general.web_category_master join general.category_tree using(category_id) where category_id=v_category_id and link is false;
IF NOT FOUND THEN
RETURN '''';
END IF;
tmp_name := general.cat_url(tmp_record.parent_category_id) ;
IF tmp_record.category_id <> 0 THEN
tmp_code := tmp_name || ''/'' || general.dir_name(tmp_record.name) ;
END IF;
tmp_code = ltrim(tmp_code,''/'');
RETURN tmp_code;
END;
' LANGUAGE 'plpgsql' IMMUTABLE;
regards, tom lane
Re: Adding a column with default value possibly corrupting a functional index.
От
"Rajesh Kumar Mallah"
Дата:
Its declared immutable , it queries the same table , its recursive
and it queries another custom function also.
fyi dir_name is a simple plperlu function.
my ($dir) = @_;
$dir =~ s/&/and/g;
$dir =~ s/[^a-zA-Z0-9]+/_/g;
return $dir;
"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.
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
Re: Adding a column with default value possibly corrupting a functional index.
От
"Rajesh Kumar Mallah"
Дата:
On 12/16/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
why does ALTER TABLE ADD new_col int default 0 rebuilds
existing indexes ?
the game was seemingly fulfilling a requirement. dunno what
i should be doing now.
regds
mallah.
"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.
regards, tom lane
"Rajesh Kumar Mallah" <mallah.rajesh@gmail.com> writes:
> On 12/16/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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 ?
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.
>> 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?
regards, tom lane
Re: Adding a column with default value possibly corrupting a functional index.
От
"Rajesh Kumar Mallah"
Дата:
>
> > 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
>