From: Tom Lane <tgl@sss.pgh.pa.us>
To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
Cc: Lista Postgres <pgsql-admin@postgresql.org>
Subject: Re: Adding a column with default value possibly corrupting a functional index.
Date: Sat, 16 Dec 2006 12:53:05 -0500
Message-ID: <6828.1166291585@sss.pgh.pa.us> (raw)
In-Reply-To: <a97c77030612160927u608b4b00td8d71ee442410b7f@mail.gmail.com>
References: <a97c77030612160824w102c2326n23706171ba8a6b3a@mail.gmail.com>
<501.1166288322@sss.pgh.pa.us>
<a97c77030612160927u608b4b00td8d71ee442410b7f@mail.gmail.com>
"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
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: tgl@sss.pgh.pa.us, mallah.rajesh@gmail.com
Subject: Re: Adding a column with default value possibly corrupting a functional index.
In-Reply-To: <6828.1166291585@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>
* 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