Re: Queries never returning...

[John McCawley <nospam@xxxxxxxxxxxx>]

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);
?>