Re: Queries never returning...

It looks like my primary slowdown on that query was the timestamp
trigger. However, even after removing that trigger, and ensuring that
all of my referencing tables had their foreign keys indexed, a simple
update of one column on 244451 records took 14 minutes. Given the specs
I mentioned in the earlier email, is this to be expected?

I have also modified my entire schema to use the more updated constraint
syntax. I wrote a php script which can be used on a pg_dump. I have
attached it here in case anyone else ends up needing this:

-----------------file fixkey.php-------------------

#!/usr/bin/php
<?
echo "This script modifies a schema file generated by pg_dump and
converts any pre 7.3 foreign key triggers to proper foreign key
constraint syntax. I have only tested it on a dumpfile generated by
Postgres 8.0.3. I have no idea if this will work on any other version,
or with other people's wacky schemas. This worked for me and that's all
I can say. Don't blame me if this script burns down your house.\n\n";

if( $argc != 3 ) {
die("Usage: fixkey.php schemafile.db outfile.db\n");
}

$fp = fopen($argv[1], "r");
$fpout = fopen($argv[2], "w");

if( !$fp ) {
die("Error opening '" . $argv[1] . "' for read\n");
}
if( !$fpout ) {
die("Error opening '" . $argv[2] . "' for write\n");
}

while( $line = fgets($fp, 5000) ) {

if( strstr($line, "ConstraintTrigger_" ) ) {
//echo "Skipping comment $line\n";
}
else if( strstr($line, "CREATE CONSTRAINT TRIGGER" ) ) {
$keyname = substr($line, strlen("CREATE CONSTRAINT TRIGGER" ) );
$keyname = trim($keyname);

//Get 5 lines after declaration for foreign key info
$line2 = fgets($fp, 5000);
$line3 = fgets($fp, 5000);
$line4 = fgets($fp, 5000);
$line5 = fgets($fp, 5000);
$line6 = fgets($fp, 5000);

//Foreign keys are apparently made of up 3 triggers...we only
care about the first one
//I assume that the subsequent ones will be implicitly created
by the new syntax
if( !$key_array[$keyname] ) {
//Store key name so we don't process it again
$key_array[$keyname] = 1;

//Referencing table name is in line 2
$table = explode(" ", $line2);
$table = $table[count($table)-1];
$table = trim($table);

//Referenced table is in line 3
$parent = explode(" ", $line3);
$parent = $parent[count($parent)-1];
$parent = trim($parent);

//Referencing column is on line 6
$column = explode(",", $line6);
$column = $column[4];
$column = str_replace("'", "", $column);
$column = trim($column);

//Referenced column is on line 6
$parentcolumn = explode(",", $line6);
$parentcolumn = $parentcolumn[5];
$parentcolumn = str_replace("'", "", $parentcolumn);
$parentcolumn = str_replace(")", "", $parentcolumn);
$parentcolumn = str_replace(";", "", $parentcolumn);
$parentcolumn = trim($parentcolumn);

$sKeySQL = "ALTER TABLE $table ADD CONSTRAINT $keyname
FOREIGN KEY ($column) REFERENCES $parent ($parentcolumn) MATCH FULL;\n";

echo $sKeySQL;
fputs($fpout, "\n\n" . $sKeySQL . "\n\n");
}

}
else {
fputs($fpout, $line);
}
}

fclose($fp);
fclose($fpout);
?>