andy wrote:
Tom Hart wrote:
[snip]
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.....
Thomas R. Hart II
tomhart@xxxxxxxxxxx
Have you run one of these queries via psql or something other than
php? I doubt its a "too many too fast" thing. I'd guess a logic
error someplace.
Why 1563 queries? Can you get the row's modified per query? If
you're tables looks like:
var1 | var2 | var3
--------------------
a | b | a
a | b | c
Would you fire of two query's like:
UPDATE table SET is_ok = 'TRUE' WHERE var1 = 'a' AND var2 = 'b' and
var3 = 'a;
UPDATE table SET is_ok = 'TRUE' WHERE var1 = 'a' AND var2 = 'b' and
var3 = 'c;
if so, do you generate the update's on the fly?
-Andy
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
I thought I had run the queries through psql (it's been a long one :-)
and when I tried to verify, I was able to find my problem (yes, I am an
idiot).
It turns out that for some reason it didn't like to UPDATE when I was
using a text type field (specifically an empty text field) in the WHERE
clause. To remedy this, I instructed PHP to not use a field in the WHERE
clause if the destination type was 'text', and now we're working
beautifully (2.405 seconds to run the script through 1566 rows, running
updates on 1563 of them). Now I just need to figure out what's going on
with those 3 rogue rows.
Sorry I hadn't checked all the bases thoroughly, but now they definitely
are belong to us. Thanks for the help and have a good night.
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
