Hey everybody. I'm running postgresql 8.2 on a windows 2k3 server
machine. I have a table (two tables actually, in table and table_import
format). The _import table has all text type fields, while the main
table has datatypes defined.
I wrote a PHP script that checks the various type fields (e.g. integer
is a number, date is a date, etc.) and sets a bool flag is_ok to true
for the row if the data all checks out. The script gets a dump of the
data from a SELECT * statement, then takes each row, verifies the
various data fields (successfully, I've tested) and sets a variable
baddata. At the end of the checking, if baddata still equals 0, then it
crafts an UPDATE statement to change the value of is_ok. There are a
relatively small amount of rows (~1500, small time to you guys I'm
sure), and the script runs fairly fast.
OK, enough of the background, here's my issue. For some lovely reason,
even though my script reports running an UPDATE query 1563 times (out of
1566 rows), only 316 rows have is_ok set to TRUE. I've tried a few
times, changing this and that, and it always updates those 316 rows (no
reason for this, the data is actually really good when it comes in, I'm
just trying to build an extra layer of assuredness). Of particular note,
I'm trying to keep the script fairly uniform and work for all our
tables, so the UPDATE statement looks something like
UPDATE table SET is_ok = 'TRUE' WHERE var1 = value1 AND var2 = value2
AND var3 = value3.....
for every field in the record (I would have it base it on the primary
key, but the field names and locations are different for each table). Is
it possible that I'm trying to run too many queries at once (or rather
rapid succession)? I've tried encapsulating the queries in a BEGIN ..
COMMIT transaction which improved my speed quite a bit, but it's still
updating only those rows.
I know that it's entirely possible that the problem lies in the PHP, or
the network, or the web server configuration, or the moon phase, but is
there anything here that jumps out at anybody as a possible cause?
TIA
Thomas R. Hart II
tomhart@coopfed.org
