Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 035A39FA22C for ; Sat, 16 Dec 2006 13:27:35 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 57530-09 for ; Sat, 16 Dec 2006 13:27:04 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.184]) by postgresql.org (Postfix) with ESMTP id BB55B9FA37A for ; Sat, 16 Dec 2006 13:27:04 -0400 (AST) Received: by nf-out-0910.google.com with SMTP id x4so1585544nfb for ; Sat, 16 Dec 2006 09:27:03 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=mDuZ9qGFB1gYe4cYU3htPacueH5cKoxIzON4W2b4+KRwjVWoCFK3aBdXEBziT9TOB1Nq/Z91NFX2qg/IVyTlNYMQhPUF7haFyARrWeLkhxb7u6xCRzYnH+Pw+bSCVRTrbet4GdLCxgBltqeKlC71QXGMqU+Qtv3JqV05BZ4O4HY= Received: by 10.82.184.2 with SMTP id h2mr279114buf.1166290022908; Sat, 16 Dec 2006 09:27:02 -0800 (PST) Received: by 10.66.216.15 with HTTP; Sat, 16 Dec 2006 09:27:02 -0800 (PST) Message-ID: Date: Sat, 16 Dec 2006 22:57:02 +0530 From: "Rajesh Kumar Mallah" To: "Tom Lane" Subject: Re: Adding a column with default value possibly corrupting a functional index. Cc: "Lista Postgres" In-Reply-To: <501.1166288322@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2178_29135631.1166290022803" References: <501.1166288322@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/236 X-Sequence-Number: 24028 ------=_Part_2178_29135631.1166290022803 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 12/16/06, Tom Lane wrote: > > "Rajesh Kumar Mallah" 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? 8.2.0 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 > ------=_Part_2178_29135631.1166290022803 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

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

 
8.2.0

 

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

------=_Part_2178_29135631.1166290022803--