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@coopfed.org
>
> 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.