Thread: How to ignore system indexes
Hi all, I'm trying to implement REINDEX command. Because the command is to repair system indexes,we coundn't rely on system indexes when we call the command. I added locally an option of standalone postgres to ignore system indexes and am add/changing ignore_system_ indexes stuff. There are fairly many places using system indexes. Probably I would be able to change them. But is it preferable or possible to force other developers to take ignore_system_indexes mode into account ? Is it better to limit changes required for REINDEX command ? Comments ? Better ideas ? Regards. Hiroshi Inoue Inoue@tpf.co.jp
> I'm trying to implement REINDEX command. > Because the command is to repair system indexes,we > coundn't rely on system indexes when we call the > command. > > I added locally an option of standalone postgres to ignore > system indexes and am add/changing ignore_system_ > indexes stuff. > > There are fairly many places using system indexes. > Probably I would be able to change them. > But is it preferable or possible to force other developers > to take ignore_system_indexes mode into account ? > Is it better to limit changes required for REINDEX > command ? One solution is to use pg_upgrade. It allows an initdb and recreate of all tables without reload. -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> -----Original Message----- > From: Bruce Momjian [mailto:pgman@candle.pha.pa.us] > > > I'm trying to implement REINDEX command. > > Because the command is to repair system indexes,we > > coundn't rely on system indexes when we call the > > command. > > > > I added locally an option of standalone postgres to ignore > > system indexes and am add/changing ignore_system_ > > indexes stuff. > > > > There are fairly many places using system indexes. > > Probably I would be able to change them. > > But is it preferable or possible to force other developers > > to take ignore_system_indexes mode into account ? > > Is it better to limit changes required for REINDEX > > command ? > > One solution is to use pg_upgrade. It allows an initdb and recreate of > all tables without reload. > -- Isn't it a big charge to execute pg_upgrade for a huge database ? I have never used pg_upgrade. Is pg_upgrade available now ? Is pg_upgrade reliable ? My design is as follows. postgres -P test /* I'm using -P as a new option temporarily */. > reindex database test; (all system indexes of a db) > reindex table pg_class; (all indexes of a system table) > reindex index pg_index_indexrelid_index; (a system index) If we could ignore system indexes,it won't be difficult to implement REINDEX command itself.. Regards. Hiroshi Inoue Inoue@tpf.co.jp
> > One solution is to use pg_upgrade. It allows an initdb and recreate of > > all tables without reload. > > -- > > Isn't it a big charge to execute pg_upgrade for a huge database ? > I have never used pg_upgrade. > Is pg_upgrade available now ? > Is pg_upgrade reliable ? It has been around since 6.3? It allows initdb, recreates the tables, then moves the data files back into place. There is even a manual page. > > My design is as follows. > > postgres -P test /* I'm using -P as a new option temporarily */. > > > reindex database test; (all system indexes of a db) > > reindex table pg_class; (all indexes of a system table) > > reindex index pg_index_indexrelid_index; (a system index) > > If we could ignore system indexes,it won't be difficult to implement > REINDEX command itself.. Not sure how to find all those places. -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> There are fairly many places using system indexes.
> Probably I would be able to change them.
> But is it preferable or possible to force other developers
> to take ignore_system_indexes mode into account ?
Is it really necessary to touch all those places?
Seems to me that if a person needs to rebuild system indexes,
he would be firing up a standalone backend and running
REINDEX --- and darn little else. As long as none of the
support code required by REINDEX insists on using an index,
it doesn't matter what the rest of the system requires.
You might even think about doing the reindex in bootstrap mode,
though I don't know if that would be easier or harder.
regards, tom lane
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > > "Hiroshi Inoue" <Inoue@tpf.co.jp> writes: > > There are fairly many places using system indexes. > > Probably I would be able to change them. > > But is it preferable or possible to force other developers > > to take ignore_system_indexes mode into account ? > > Is it really necessary to touch all those places? > > Seems to me that if a person needs to rebuild system indexes, > he would be firing up a standalone backend and running > REINDEX --- and darn little else. As long as none of the > support code required by REINDEX insists on using an index, > it doesn't matter what the rest of the system requires. > OK,I would limit changes only for REINDEX command. > You might even think about doing the reindex in bootstrap mode, > though I don't know if that would be easier or harder. > Yes,bootstrap mode is a natural selection. Jan has already tried it and there was a problem of time quliafication. I don't know it is a big obstacle or not. I prefer standalone postgres because there's a possibility to call various SQL commands together with REINDEX command. Of cource,time qualification is no longer a problem in standalone postgres. Regards. Hiroshi Inoue Inoue@tpf.co.jp
> -----Original Message----- > From: Bruce Momjian [mailto:pgman@candle.pha.pa.us] > > > > One solution is to use pg_upgrade. It allows an initdb and > recreate of > > > all tables without reload. > > > -- > > > > Isn't it a big charge to execute pg_upgrade for a huge database ? > > I have never used pg_upgrade. > > Is pg_upgrade available now ? > > Is pg_upgrade reliable ? > > It has been around since 6.3? It allows initdb, recreates the tables, > then moves the data files back into place. There is even a manual page. > I know the command but does 6.5 have it ? > > > > My design is as follows. > > > > postgres -P test /* I'm using -P as a new option temporarily */. > > > > > reindex database test; (all system indexes of a db) > > > reindex table pg_class; (all indexes of a system table) > > > reindex index pg_index_indexrelid_index; (a system index) > > > > If we could ignore system indexes,it won't be difficult to implement > > REINDEX command itself.. > > Not sure how to find all those places. > I would only change the stuff required for REINDEX command, though I know almost all those places. Regards. Hiroshi Inoue Inoue@tpf.co.jp
[Charset iso-8859-1 unsupported, filtering to ASCII...] > > -----Original Message----- > > From: Bruce Momjian [mailto:pgman@candle.pha.pa.us] > > > > > > One solution is to use pg_upgrade. It allows an initdb and > > recreate of > > > > all tables without reload. > > > > -- > > > > > > Isn't it a big charge to execute pg_upgrade for a huge database ? > > > I have never used pg_upgrade. > > > Is pg_upgrade available now ? > > > Is pg_upgrade reliable ? > > > > It has been around since 6.3? It allows initdb, recreates the tables, > > then moves the data files back into place. There is even a manual page. > > > > I know the command but does 6.5 have it ? Sure, but it is disabled in 6.5 because we changed the binary table format from 6.4 to 6.5. However, I have already recommended people use it who have broken system indexes, and it worked. -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
-----Original Message----- > From: Bruce Momjian [mailto:pgman@candle.pha.pa.us] > > [Charset iso-8859-1 unsupported, filtering to ASCII...] > > > -----Original Message----- > > > From: Bruce Momjian [mailto:pgman@candle.pha.pa.us] > > > > > > > > One solution is to use pg_upgrade. It allows an initdb and > > > recreate of > > > > > all tables without reload. > > > > > -- > > > > > > > > Isn't it a big charge to execute pg_upgrade for a huge database ? > > > > I have never used pg_upgrade. > > > > Is pg_upgrade available now ? > > > > Is pg_upgrade reliable ? > > > > > > It has been around since 6.3? It allows initdb, recreates the tables, > > > then moves the data files back into place. There is even a > manual page. > > > > > > > I know the command but does 6.5 have it ? > > Sure, but it is disabled in 6.5 because we changed the binary table > format from 6.4 to 6.5. However, I have already recommended people use > it who have broken system indexes, and it worked. > It seems pg_upgrade is too complicated to recover system indexes. In addtion,could pg_upgrade/pg_dump/vacuum etc ... work even when a critical system index is broken ? Regards. Hiroshi Inoue Inoue@tpf.co.jp.
> > Sure, but it is disabled in 6.5 because we changed the binary table > > format from 6.4 to 6.5. However, I have already recommended people use > > it who have broken system indexes, and it worked. > > > > It seems pg_upgrade is too complicated to recover system indexes. > In addtion,could pg_upgrade/pg_dump/vacuum etc ... work even when > a critical system index is broken ? Yes, pg_dumpall -s may not work with broken system indexes. -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026