Thread: In 7.3.1, will I be able to reindex toast?
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. -jwb
On Fri, Feb 14, 2003 at 09:36:44AM -0800, 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. In partial resposne to my own question, I've found that 7.3.2 will not reindex TOAST tables explicitly: rupert=> reindex table pg_toast_12550267; ERROR: Relation "pg_toast_12550267" does not exist But does it reindex the TOAST tables if I reindex the related table? -jwb
I see in the 7.3 release notes: Allow REINDEX of TOAST tables (Tom) --------------------------------------------------------------------------- Jeffrey Baker wrote: > On Fri, Feb 14, 2003 at 09:36:44AM -0800, 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. > > In partial resposne to my own question, I've found that > 7.3.2 will not reindex TOAST tables explicitly: > > rupert=> reindex table pg_toast_12550267; > ERROR: Relation "pg_toast_12550267" does not exist > > But does it reindex the TOAST tables if I reindex the > related table? > > -jwb > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Fri, Feb 14, 2003 at 04:05:12PM -0500, Bruce Momjian wrote: > > I see in the 7.3 release notes: > > Allow REINDEX of TOAST tables (Tom) Thank you Bruce, I mentioned that in my opening email. The question is, does it *actually* work, and if so how do you invoke it. Demonstrably, you cannot invoke reindex on an actual toast table, because it fails with an error. So you invoke REINDEX on your user table and the TOAST is automatically reindexed as well? -jwb
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.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Jeffrey Baker wrote: > On Fri, Feb 14, 2003 at 04:05:12PM -0500, Bruce Momjian wrote: > > > > I see in the 7.3 release notes: > > > > Allow REINDEX of TOAST tables (Tom) > > Thank you Bruce, I mentioned that in my opening email. The > question is, does it *actually* work, and if so how do you > invoke it. Demonstrably, you cannot invoke reindex on an > actual toast table, because it fails with an error. > > So you invoke REINDEX on your user table and the TOAST is > automatically reindexed as well? No, TOAST tables have to be reindexes separately. It does require an exclusive lock. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
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@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
Jeffrey Baker <jwbaker@acm.org> writes:
> In partial resposne to my own question, I've found that
> 7.3.2 will not reindex TOAST tables explicitly:
> rupert=> reindex table pg_toast_12550267;
> ERROR: Relation "pg_toast_12550267" does not exist
Yes it will, if you name them correctly:
regression=# reindex table pg_toast.pg_toast_840608;
REINDEX
regards, tom lane
On Fri, Feb 14, 2003 at 05:25:09PM -0500, Tom Lane wrote: > Jeffrey Baker <jwbaker@acm.org> writes: > > In partial resposne to my own question, I've found that > > 7.3.2 will not reindex TOAST tables explicitly: > > > rupert=> reindex table pg_toast_12550267; > > ERROR: Relation "pg_toast_12550267" does not exist > > Yes it will, if you name them correctly: > > regression=# reindex table pg_toast.pg_toast_840608; > REINDEX Thank you! -jwb
Am Freitag, 14. Februar 2003 22:05 schrieb Bruce Momjian:
> I see in the 7.3 release notes:
>
> Allow REINDEX of TOAST tables (Tom)
>
Hmm... how about pg_largeobject?
It seems to be treated like a system table, but I think it is not necessary. Does the reindex command simply check the
"pg_"prefix?
Regards,
Mario Weilguni
Mario Weilguni <mweilguni@sime.com> writes:
> Hmm... how about pg_largeobject?
> It seems to be treated like a system table, but I think it is not
> necessary. Does the reindex command simply check the "pg_" prefix?
As of 7.3, I believe it treats any table living in the pg_catalog schema
as a system table. Certainly, a lot of these tables are not all that
special and could be reindexed without going to standalone mode.
It might be sufficient to insist on standalone reindexing just for the
three or four tables that have nailed-in-cache indexes. I'm not sure
though. Hiroshi, what do you think?
regards, tom lane
Am Samstag, 15. Februar 2003 16:49 schrieb Tom Lane:
> Mario Weilguni <mweilguni@sime.com> writes:
> > Hmm... how about pg_largeobject?
> > It seems to be treated like a system table, but I think it is not
> > necessary. Does the reindex command simply check the "pg_" prefix?
>
> As of 7.3, I believe it treats any table living in the pg_catalog schema
> as a system table. Certainly, a lot of these tables are not all that
> special and could be reindexed without going to standalone mode.
>
> It might be sufficient to insist on standalone reindexing just for the
> three or four tables that have nailed-in-cache indexes. I'm not sure
> though. Hiroshi, what do you think?
And what about adding a column "indissystem" to pg_index and setting this to true for system indexes, false otherwise?
Then there would be no need to hardwire the reindex logic.
Regards,
Mario Weilguni
Mario Weilguni <mweilguni@sime.com> writes:
> Am Samstag, 15. Februar 2003 16:49 schrieb Tom Lane:
>> It might be sufficient to insist on standalone reindexing just for the
>> three or four tables that have nailed-in-cache indexes. I'm not sure
>> though. Hiroshi, what do you think?
> And what about adding a column "indissystem" to pg_index and setting this to true for system indexes, false
otherwise?
> Then there would be no need to hardwire the reindex logic.
If the nailed-in-cache property is what matters, then we need to look at
that, not at some boolean column that might or might not agree with it.
I'm not entirely sure what does matter here, though.
regards, tom lane
Tom Lane wrote:
>
> Mario Weilguni <mweilguni@sime.com> writes:
> > Hmm... how about pg_largeobject?
> > It seems to be treated like a system table, but I think it is not
> > necessary. Does the reindex command simply check the "pg_" prefix?
>
> As of 7.3, I believe it treats any table living in the pg_catalog schema
> as a system table. Certainly, a lot of these tables are not all that
> special and could be reindexed without going to standalone mode.
>
> It might be sufficient to insist on standalone reindexing just for the
> three or four tables that have nailed-in-cache indexes. I'm not sure
> though. Hiroshi, what do you think?
Basically I changed REINDEX to work with most system tables
under postmaster in 7.1, IIRC. And there's a trial to make
it possible even for nailed-in-cache relations ... Hmm
where's my code ?
regards,
Hiroshi Inoue
http://www.geocities.jp/inocchichichi/psqlodbc/