From: Brian McCane <bmccane@mccons.net>
To: pgsql-admin@postgresql.org
Subject: Re: Vacuum
Date: Thu, 29 Nov 2001 14:13:13 -0600 (CST)
Message-ID: <20011129134155.W29836-200000@fw.mccons.net> (raw)
In-Reply-To: <19565.1007058635@sss.pgh.pa.us>
On Thu, 29 Nov 2001, Tom Lane wrote:
> bangh <banghe@baileylink.net> writes:
> > However I noticed the index grows also very fast. Does anyone have a way to
> > compress the index?
>
> REINDEX, or just drop and recreate the indexes.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
Okay, after some more testing and cussing/discussing with myself, I have
decided that my "reindex" perl script is ready for public consumption.
To use the attached script, you will need to put your username, password
and hostname into the program (just look for the comment). If you run it
without any parameters, it will give you some simple instructions. I use
it something like:
$ fixtable.pl -I -t relevance kids
The program will then dump out SQL code to re-create ALL indexes (-I) for
the 'relevance' table (-t) in my 'kids' database. You can feed the output
of the script to 'psql', or look at it first (I am paranoid) and then
cut-n-paste it to 'psql' yourself.
The SQL code creates a new index for each index on a table, drops the
original index, renames the new index to the old name, and then repeats
for the next index on the table. This means that the user you login as
when you run 'psql' must own the indexes (otherwise the DROP INDEX fails).
If the drop fails for any reason, you will end up with TWO (2) identical
indexes which will almost certainly hurt performance on inserts. Also,
you need to have enough free space to have a second copy of your largest
index on the table or the CREATE INDEX will fail.
NOTE: This script DOES NOT DO ANYTHING with permissions because I don't
use them. If someone would like to give me some SQL code that will return
the old permissions, and set them on the new index, I would be happy to
add this functionality.
Finally. I am providing this script because I am a nice guy. If your
machine explodes or any table gets injured by your use of this script I
cannot and will not be held responsible. I wrote this script for my own
use, and the only time it has ever failed for me was when I had duplicated
values in a UNIQUE index field. Once I corrected the data corruption, the
script worked correctly. So, at least at first, I recommend that you look
at the SQL that is generated, and make sure you understand what it is
trying to do BEFORE you use it.
- brian
Wm. Brian McCane | Life is full of doors that won't open
Search http://recall.maxbaud.net/ | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"
#! /usr/bin/perl
require 'getopts.pl' ;
my $allIndexes = 0 ;
my $allTriggers = 0 ;
my $table = "" ;
my $index = "" ;
my $notreally = 0 ;
Getopts('ITt:i:n') ;
$allIndexes = $opt_I if $opt_I ;
$allTriggers = $opt_T if $opt_T ;
$table = $opt_t if $opt_t ;
$index = $opt_i if $opt_i ;
$notreally = $opt_n if $opt_n ;
use strict ;
my $USAGE = <<USAGE ;
$0 - Postgresql table tool
$0 -t <table> [-i <index>] [-I] [-T] <database>
Options:
-t <table> Table to work on
-i <index> Index to rebuild
-I Rebuild all triggers
-T Do all triggers
You must choose at least one of -i, -I and -T
USAGE
die $USAGE if ($table eq "") ;
die "You can only use one of '-I' and '-i' options"
if ($index ne "" && $allIndexes) ;
die $USAGE if ($index eq "" && ! $allIndexes && ! $allTriggers) ;
use DBI ;
my $dbname = shift @ARGV ;
$$$$$$$$$ DELETE THIS LINE AND FIX NEXT THREE VALUES $$$$$$$$$
my $user = 'USERNAME' ;
my $passwd = 'PASSWORD' ;
my $hostname = 'HOSTNAME' ;
my $dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$hostname",$user,$passwd) ;
my %indexes ;
my @triggers ;
my %unique ;
open PGDUMP, "pg_dump -s -t $table $dbname |" ;
while (<PGDUMP>)
{
chomp ;
next if ! m/^CREATE/ ;
if (m/^CREATE\s+(.*)\s*INDEX\s+"([^"]*)".*(\(.*\))/)
{
$indexes{$2} = $3 ;
$indexes{$2 => "UNIQUE"} = ($1 ne "") ;
next ;
}
if (! defined $unique{$_})
{
$unique{$_} = 1 ;
if (m/^CREATE\s+CONSTRAINT\s+TRIGGER\s+"([^"]*)"/)
{
push (@triggers, $_) ;
next ;
}
}
else
{
next ;
}
}
close PGDUMP ;
if ($allTriggers)
{
my $cmd = "SELECT oid from pg_class where relname = '$table'" ;
my $relid ;
if (my $sth = $dbh->prepare($cmd))
{
if ($sth->execute)
{
if (my $row = $sth->fetchrow_arrayref)
{
$relid = $$row[0] ;
}
}
}
$cmd = "SELECT tgname FROM pg_trigger where tgrelid = $relid" ;
if (my $sth = $dbh->prepare($cmd))
{
if ($sth->execute)
{
while (my $row = $sth->fetchrow_arrayref)
{
$relid = $$row[0] ;
print "DROP TRIGGER $$row[0] ON $table;\n" ;
}
}
}
for my $trigger (@triggers)
{
print $trigger . "\n" ;
}
}
die "Index $index does not exist on table $table"
if ($index ne "" && ! defined $indexes{$index}) ;
if ($index)
{
my $cmd = "CREATE" ;
$cmd.= " UNIQUE" if $indexes{$index => "UNIQUE"} == 1 ;
$cmd.= " INDEX $index ON $table $indexes{$index};" ;
print "$cmd\n" ;
my $cmd = "DROP INDEX $index;" ;
print "$cmd\n" ;
$cmd = "ALTER TABLE ".$index."_new" ;
$cmd.= " RENAME TO $index;" ;
print "$cmd\n" ;
}
elsif ($allIndexes)
{
for my $index (keys %indexes)
{
next if ! defined $indexes{$index => "UNIQUE"} ;
my $cmd = "CREATE" ;
$cmd.= " UNIQUE" if $indexes{$index => "UNIQUE"} == 1 ;
$cmd.= " INDEX ".$index."_new ON $table $indexes{$index};" ;
print "$cmd\n" ;
$cmd = "DROP INDEX $index;" ;
print "$cmd\n" ;
$cmd = "ALTER TABLE ".$index."_new" ;
$cmd.= " RENAME TO $index;" ;
print "$cmd\n" ;
}
}
$dbh->disconnect ;
__END__
Attachments:
[text/plain] fixtable.pl (3.1K, ../20011129134155.W29836-200000@fw.mccons.net/2-fixtable.pl)
download | inline:
#! /usr/bin/perl
require 'getopts.pl' ;
my $allIndexes = 0 ;
my $allTriggers = 0 ;
my $table = "" ;
my $index = "" ;
my $notreally = 0 ;
Getopts('ITt:i:n') ;
$allIndexes = $opt_I if $opt_I ;
$allTriggers = $opt_T if $opt_T ;
$table = $opt_t if $opt_t ;
$index = $opt_i if $opt_i ;
$notreally = $opt_n if $opt_n ;
use strict ;
my $USAGE = <<USAGE ;
$0 - Postgresql table tool
$0 -t <table> [-i <index>] [-I] [-T] <database>
Options:
-t <table> Table to work on
-i <index> Index to rebuild
-I Rebuild all triggers
-T Do all triggers
You must choose at least one of -i, -I and -T
USAGE
die $USAGE if ($table eq "") ;
die "You can only use one of '-I' and '-i' options"
if ($index ne "" && $allIndexes) ;
die $USAGE if ($index eq "" && ! $allIndexes && ! $allTriggers) ;
use DBI ;
my $dbname = shift @ARGV ;
$$$$$$$$$ DELETE THIS LINE AND FIX NEXT THREE VALUES $$$$$$$$$
my $user = 'USERNAME' ;
my $passwd = 'PASSWORD' ;
my $hostname = 'HOSTNAME' ;
my $dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$hostname",$user,$passwd) ;
my %indexes ;
my @triggers ;
my %unique ;
open PGDUMP, "pg_dump -s -t $table $dbname |" ;
while (<PGDUMP>)
{
chomp ;
next if ! m/^CREATE/ ;
if (m/^CREATE\s+(.*)\s*INDEX\s+"([^"]*)".*(\(.*\))/)
{
$indexes{$2} = $3 ;
$indexes{$2 => "UNIQUE"} = ($1 ne "") ;
next ;
}
if (! defined $unique{$_})
{
$unique{$_} = 1 ;
if (m/^CREATE\s+CONSTRAINT\s+TRIGGER\s+"([^"]*)"/)
{
push (@triggers, $_) ;
next ;
}
}
else
{
next ;
}
}
close PGDUMP ;
if ($allTriggers)
{
my $cmd = "SELECT oid from pg_class where relname = '$table'" ;
my $relid ;
if (my $sth = $dbh->prepare($cmd))
{
if ($sth->execute)
{
if (my $row = $sth->fetchrow_arrayref)
{
$relid = $$row[0] ;
}
}
}
$cmd = "SELECT tgname FROM pg_trigger where tgrelid = $relid" ;
if (my $sth = $dbh->prepare($cmd))
{
if ($sth->execute)
{
while (my $row = $sth->fetchrow_arrayref)
{
$relid = $$row[0] ;
print "DROP TRIGGER $$row[0] ON $table;\n" ;
}
}
}
for my $trigger (@triggers)
{
print $trigger . "\n" ;
}
}
die "Index $index does not exist on table $table"
if ($index ne "" && ! defined $indexes{$index}) ;
if ($index)
{
my $cmd = "CREATE" ;
$cmd.= " UNIQUE" if $indexes{$index => "UNIQUE"} == 1 ;
$cmd.= " INDEX $index ON $table $indexes{$index};" ;
print "$cmd\n" ;
my $cmd = "DROP INDEX $index;" ;
print "$cmd\n" ;
$cmd = "ALTER TABLE ".$index."_new" ;
$cmd.= " RENAME TO $index;" ;
print "$cmd\n" ;
}
elsif ($allIndexes)
{
for my $index (keys %indexes)
{
next if ! defined $indexes{$index => "UNIQUE"} ;
my $cmd = "CREATE" ;
$cmd.= " UNIQUE" if $indexes{$index => "UNIQUE"} == 1 ;
$cmd.= " INDEX ".$index."_new ON $table $indexes{$index};" ;
print "$cmd\n" ;
$cmd = "DROP INDEX $index;" ;
print "$cmd\n" ;
$cmd = "ALTER TABLE ".$index."_new" ;
$cmd.= " RENAME TO $index;" ;
print "$cmd\n" ;
}
}
$dbh->disconnect ;
__END__
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: bmccane@mccons.net
Subject: Re: Vacuum
In-Reply-To: <20011129134155.W29836-200000@fw.mccons.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox