Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 70FE19FA28E for ; Sat, 16 Dec 2006 22:41:02 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 00510-07 for ; Sat, 16 Dec 2006 22:40:52 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.172]) by postgresql.org (Postfix) with ESMTP id B3E729F9ED7 for ; Sat, 16 Dec 2006 22:40:52 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so1176363ugf for ; Sat, 16 Dec 2006 18:40:51 -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=ZOK0FwStpiks28MSCr2IddGe6XG0NdotpmNeV7T8NYfDFH+1YGM9Fto4maPWjKsFJbVvjjkPLC8OAYrbYYY2boAXKKh6svgOoC8DC09pJ1PLVWzbclvew42l6b4/vzEJPtvnLopGaX7QQHuyZPIw8bJjnhNWoASNszDSIJP4Hqc= Received: by 10.66.232.9 with SMTP id e9mr2621324ugh.1166323250964; Sat, 16 Dec 2006 18:40:50 -0800 (PST) Received: by 10.66.216.15 with HTTP; Sat, 16 Dec 2006 18:40:50 -0800 (PST) Message-ID: Date: Sun, 17 Dec 2006 08:10:50 +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: <6828.1166291585@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3695_3245696.1166323250926" References: <501.1166288322@sss.pgh.pa.us> <6828.1166291585@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/239 X-Sequence-Number: 24031 ------=_Part_3695_3245696.1166323250926 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: > > On 12/16/06, Tom Lane 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 > ------=_Part_3695_3245696.1166323250926 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:
> 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

------=_Part_3695_3245696.1166323250926--