Re: In 7.3.1, will I be able to reindex toast?

From: Jeffrey Baker <jwbaker(at)acm(dot)org>
To: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: In 7.3.1, will I be able to reindex toast?
Date: 2003-02-14 21:47:04
Message-ID: 20030214214704.GA12980@heat
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Fri, Feb 14, 2003 at 04:35:33PM -0500, Bruce Momjian wrote:
> Jeffrey Baker wrote:
> > I am considering moving to 7.3 from 7.2. I have the
> > obnoxious toast index growth problem of doom. In 7.2 I am
> > not allowed to reindex toast tables, because they are of
> > type 't'. How smug :)
> >
> > The problem is I don't want to move to 7.3 unless I can be
> > certain this is going to work. There are many incompatible
> > changes in 7.3, and I will need to audit tens of thousands
> > of lines of queries before I can make the move. Can someone
> > reassure me? It says you can reindex toast tables in the
> > changelog, but ...
> >
> > Also I assume REINDEX in 7.3 still requires exclusive
> > access? That could be a bummer.
>
> Well, I am sure whoever applied the patch, Tom, tested it:
>
> revision 1.85
> date: 2002/08/29 15:56:20; author: tgl; state: Exp; lines: +15 -17
> Cause REINDEX to regard TOAST tables as regular relations, not system
> tables that need special defenses. I believe this is okay even for
> TOAST tables that belong to system tables.
>
> If you want to be sure, install 7.3 on a different port and test it.

I have installed and tested it, and it doesn't work, which
is why I have posed my question:

jwb(at)heat:~$ psql rupert
Welcome to psql 7.3.2, the PostgreSQL interactive terminal.
rupert=> reindex table pg_toast_12550267;
ERROR: Relation "pg_toast_12550267" does not exist
rupert=> select relname, relkind, relfilenode, reltoastidxid from pg_class where relname = 'pg_toast_12550267';
relname | relkind | relfilenode | reltoastidxid
-------------------+---------+-------------+---------------
pg_toast_12550267 | t | 12550269 | 12550271
(1 row)

So, the TOAST exists but I can't REINDEX it, despite Changelog entries.

-jwb

In response to

Browse pgsql-general by date

  From Date Subject
Next Message greg 2003-02-14 21:47:06 Re: Broken idocs?
Previous Message Dennis Gearon 2003-02-14 21:46:47 Re: operators and bit field