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

Tue, 18 Jan 2000 10:02:31 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
How to ignore system indexes
> 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, Pennsylvania 19026

Mon, 17 Jan 2000 23:04:12 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] How to ignore system indexes
> -----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

 

Tue, 18 Jan 2000 13:56:04 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: [HACKERS] How to ignore system indexes
> > 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, Pennsylvania 19026

Tue, 18 Jan 2000 00:09:14 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] How to ignore system indexes
"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

Tue, 18 Jan 2000 00:09:58 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] How to ignore system indexes 
[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, Pennsylvania 19026

Tue, 18 Jan 2000 00:47:58 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] How to ignore system indexes
> -----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

Tue, 18 Jan 2000 14:48:01 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: [HACKERS] How to ignore system indexes
> -----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

Tue, 18 Jan 2000 14:48:19 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: [HACKERS] How to ignore system indexes 
 -----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.



Tue, 18 Jan 2000 15:58:23 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: [HACKERS] How to ignore system indexes
> > 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, Pennsylvania 19026

Tue, 18 Jan 2000 13:08:21 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] How to ignore system indexes