pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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 22:36:44 -0500
Message-ID: <10285.1166326604@sss.pgh.pa.us> (raw)
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>

"Rajesh Kumar Mallah" <mallah.rajesh@gmail.com> writes:
> On 12/16/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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 ?

Because it has to rewrite the whole table to insert the default value
in every row.  A REINDEX is way more efficient for recovering from that
than any row-by-row update would be.

>> 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.

It sorta looks to me like you're trying to get the effect of a
materialized view --- have you looked at the techdocs pages about
how to do those in Postgres?

			regards, tom lane



view thread (8+ messages)  latest in thread

Message-ID: <10285.1166326604@sss.pgh.pa.us>
Permalink:  ../10285.1166326604@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/10285.1166326604@sss.pgh.pa.us

 · 

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: 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: <10285.1166326604@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> <a97c77030612161840w5d127102q1e1b818a06334d9e@mail.gmail.com> <10285.1166326604@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