pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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
>

view thread (8+ messages)  latest in thread

Message-ID: <a97c77030612161840w5d127102q1e1b818a06334d9e@mail.gmail.com>
Permalink:  ../a97c77030612161840w5d127102q1e1b818a06334d9e@mail.gmail.com/
Also on:    postgresql.org/message-id/a97c77030612161840w5d127102q1e1b818a06334d9e@mail.gmail.com

 · 

reply

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