Обсуждение: Contrib: Reindex script.

Ok,

As before, this script is basically a vacuumdb for indexes.  I call it
reindexdb on my own system, and put it in the postgres binary directory
simply because it's indispensable in my system.

This should be the last one.  Someone complained that assuming that the
script will reside in the same directory as psql was silly.  That's
true, though eventually I'd like to see this, or a utility like it,
included in the official postgres distribution.

The script now uses various tricks to figure out where psql is.
Starting with path, continuing with the directory the script is in,
then whereis, and finally locate.  These are arranged in order of speed
so it can find psql as fast as possible.  Even so, the full iteration
through all methods takes less than a second.

If your system doesn't have whereis or locate, I've directed output from
all of these search commands to /dev/null so these tests will simply
fail if the command is unavailable.

Use, and enjoy.

--
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
| Shaun M. Thomas                INN Database Administrator           |
| Phone: (309) 743-0812          Fax  : (309) 743-0830                |
| Email: sthomas@townnews.com    AIM  : trifthen                      |
| Web  : www.townnews.com                                             |
|                                                                     |
|     "Most of our lives are about proving something, either to       |
|      ourselves or to someone else."                                 |
|                                           -- Anonymous              |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+

Shaun Thomas wrote:

>Ok,
>
>As before, this script is basically a vacuumdb for indexes.  I call it
>reindexdb on my own system, and put it in the postgres binary directory
>simply because it's indispensable in my system.
>
>This should be the last one.
>
[snip....]

Hi Shaun,

Thanks for that script but....
your script doesn't deal with mixed case table names (reindexdb -a).

I modified it to my needs by altering the REINDEX command from

$PSQL $PSQLOPT $ECHOOPT -c "REINDEX TABLE $tab" -d $db
to:

$PSQL $PSQLOPT $ECHOOPT -c "REINDEX TABLE \"$tab\"" -d $db

Regards
Tina

Tina Messmann wrote:
> Shaun Thomas wrote:
>
> >Ok,
> >
> >As before, this script is basically a vacuumdb for indexes.  I call it
> >reindexdb on my own system, and put it in the postgres binary directory
> >simply because it's indispensable in my system.
> >
> >This should be the last one.
> >
> [snip....]
>
> Hi Shaun,
>
> Thanks for that script but....
> your script doesn't deal with mixed case table names (reindexdb -a).
>
> I modified it to my needs by altering the REINDEX command from
>
> $PSQL $PSQLOPT $ECHOOPT -c "REINDEX TABLE $tab" -d $db
> to:
>
> $PSQL $PSQLOPT $ECHOOPT -c "REINDEX TABLE \"$tab\"" -d $db

Changes made to CVS, actually in two places.  Patch attached.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  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
Index: contrib/reindex/reindex
===================================================================
RCS file: /cvsroot/pgsql/contrib/reindex/reindex,v
retrieving revision 1.2
diff -c -r1.2 reindex
*** contrib/reindex/reindex    22 Jun 2002 04:08:07 -0000    1.2
--- contrib/reindex/reindex    23 Jun 2002 03:36:02 -0000
***************
*** 188,194 ****

  # Ok, no index.  Is there a specific table to reindex?
  elif [ "$table" ]; then
!   $PSQL $PSQLOPT $ECHOOPT -c "REINDEX TABLE $table" -d $dbname

  # No specific table, no specific index, either we have a specific database,
  # or were told to do all databases.  Do it!
--- 188,194 ----

  # Ok, no index.  Is there a specific table to reindex?
  elif [ "$table" ]; then
!   $PSQL $PSQLOPT $ECHOOPT -c "REINDEX TABLE \"$table\"" -d $dbname

  # No specific table, no specific index, either we have a specific database,
  # or were told to do all databases.  Do it!
***************
*** 206,212 ****
      # database that we may reindex.
      tables=`$PSQL $PSQLOPT -q -t -A -d $db -c "$sql"`
      for tab in $tables; do
!       $PSQL $PSQLOPT $ECHOOPT -c "REINDEX TABLE $tab" -d $db
      done

    done
--- 206,212 ----
      # database that we may reindex.
      tables=`$PSQL $PSQLOPT -q -t -A -d $db -c "$sql"`
      for tab in $tables; do
!       $PSQL $PSQLOPT $ECHOOPT -c "REINDEX TABLE \"$tab\"" -d $db
      done

    done