Thursday, August 28, 2008

groovy scripting for postgres

I've been playing around with reindexing based on index fragmentation. Wrote the following code

package com.postgresintl;

import groovy.sql.Sql
import org.apache.log4j.Logger


class Reindex
{
private static final Logger logger = Logger.getLogger(Reindex.class)

def sql,host,database,threads

public getIndexes( int fragmentation )
{
def indexes = []
def sql1 = new Sql(sql.getConnection())

sql.eachRow('select indexrelname from pg_stat_user_indexes') { row ->
println "checking ${row.indexrelname}"
sql1.eachRow("select * from pgstatindex(${row.indexrelname}) where leaf_fragmentation >= ${fragmentation}") { stats ->
println "adding $stats"
indexes << row.indexrelname

}
}
// sql1.close()
return indexes
}

def reindex = {indexes->
println "starting thread"
def index
def sqlInstance = Sql.newInstance("jdbc:postgresql://$host/$database",'postgres','','org.postgresql.Driver')
try
{
while (indexes.size() > 0)
{
synchronized(indexes)
{
def last = indexes.size
if ( last > 0)
index = indexes.remove(last-1)
}
def reindexCommand = 'reindex index ' + index
println reindexCommand
try
{
sqlInstance.execute(reindexCommand)
}
catch( Exception ex)
{
ex.printStackTrace()
println "error reindexing $index"
}

}
}
finally
{
sqlInstance.close()
}
}

public reindex( List indexes )
{

indexes.each(){ index ->
try {
println "reindexing $index"

def reindexCommand = 'reindex index ' + index
println reindexCommand
sql.execute(reindexCommand)
}
catch( Exception ex )
{
println "error indexing $index"
}
}

}

public emailReindex()
{
def ant = new AntBuilder()
ant.mail( mailhost:'beaver.fastcrypt.com', mailport:25,
subject:'Reindex Job'){
from(address:'davec@postgresintl.com')
to(address:'davec@postgresintl.com')
message("Reindexed ")
}
}
public Reindex()
{}

public Reindex( String host, String database, String threads)
{
this.host = host
this.database= database
this.threads=threads

sql = Sql.newInstance("jdbc:postgresql://$host/$database",'postgres','','org.postgresql.Driver')
}
public void closeConnection()
{
sql.close()
}

static void main(args)
{
if ( args.length <= 1)
{
println "$args usage Reindex "
return
}
def host = args[0]
def database= args[1]
def threads = args[2]

println "host:$host, database:$database, threads:$threads"

def reindex = new Reindex(host, database, threads)
def indexes = reindex.getIndexes(20)

5.times {
Thread.start reindex.reindex.curry(indexes)
}

//reindex.reindex reindex.getIndexes(30);
reindex.emailReindex()
reindex.closeConnection()
}

}