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 08:10:50 +0530
Message-ID: <a97c77030612161840w5d127102q1e1b818a06334d9e@mail.gmail.com> (raw)
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>
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
>
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: <a97c77030612161840w5d127102q1e1b818a06334d9e@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>
* 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