Thread: Update Query doesn't affect all records

Update Query doesn't affect all records

From
"Schindler Andor"
Date:
Hi!

I've got this update query:

http://pastebin.com/m2e29990

Can anyone tell me, how this is possible? If we insert 12 on the end, then
it decreases, but 11 remains the same. The problem only occurs, when the
where condition contains "sorrend > 9" or less.

It occured using "PostgreSQL 8.3.3, compiled by Visual C++ build 1400" and
"PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian
4.3.2-1.1) 4.3.2" too.

Thanks,
Andor


Re: Update Query doesn't affect all records

From
Scott Marlowe
Date:
On Wed, Aug 5, 2009 at 6:15 AM, Schindler
Andor<schindler.andor@pokhalo.hu> wrote:
> Hi!
>
> I've got this update query:
>
> http://pastebin.com/m2e29990
>
> Can anyone tell me, how this is possible? If we insert 12 on the end, then
> it decreases, but 11 remains the same. The problem only occurs, when the
> where condition contains "sorrend > 9" or less.

What are the types of those fields?

Re: Update Query doesn't affect all records

From
Csaba Nagy
Date:
Hi Andor,

On Wed, 2009-08-05 at 14:15 +0200, Schindler Andor wrote:
> Can anyone tell me, how this is possible? If we insert 12 on the end, then
> it decreases, but 11 remains the same. The problem only occurs, when the
> where condition contains "sorrend > 9" or less.

I bet the "sorrend" column is of some text type, and the "sorrend > 9"
comparison is a text comparison. Try "sorrend::integer > 9" and it
should work ;-)

Cheers,
Csaba.



Re: Update Query doesn't affect all records

From
Scott Marlowe
Date:
On Wed, Aug 5, 2009 at 6:56 AM, Csaba Nagy<nagy@ecircle-ag.com> wrote:
> Hi Andor,
>
> On Wed, 2009-08-05 at 14:15 +0200, Schindler Andor wrote:
>> Can anyone tell me, how this is possible? If we insert 12 on the end, then
>> it decreases, but 11 remains the same. The problem only occurs, when the
>> where condition contains "sorrend > 9" or less.
>
> I bet the "sorrend" column is of some text type, and the "sorrend > 9"
> comparison is a text comparison. Try "sorrend::integer > 9" and it
> should work ;-)

That's kinda what I was thinking at first, but the pastebin he posted
showed them in proper int type order.  So I'm not sure why it's doing
what it's doing.  I'd ask Schinlder if he could post a completely self
contained example of the problem, including the create table and
insert statements used to create the test set.  Otherwise we're blind
men describing an elephant.

Re: Update Query doesn't affect all records

From
Sam Mason
Date:
On Wed, Aug 05, 2009 at 11:27:52AM -0600, Scott Marlowe wrote:
> On Wed, Aug 5, 2009 at 6:56 AM, Csaba Nagy<nagy@ecircle-ag.com> wrote:
> > On Wed, 2009-08-05 at 14:15 +0200, Schindler Andor wrote:
> >> Can anyone tell me, how this is possible? If we insert 12 on the end, then
> >> it decreases, but 11 remains the same. The problem only occurs, when the
> >> where condition contains "sorrend > 9" or less.
> >
> > I bet the "sorrend" column is of some text type, and the "sorrend > 9"
> > comparison is a text comparison. Try "sorrend::integer > 9" and it
> > should work ;-)
>
> That's kinda what I was thinking at first, but the pastebin he posted
> showed them in proper int type order.

Also the fact that 12 "works".

> Otherwise we're blind men describing an elephant.

Interesting analogy, not heard that one before!

--
  Sam  http://samason.me.uk/

Re: Update Query doesn't affect all records

From
"Leif B. Kristensen"
Date:
On Wednesday 5. August 2009, Sam Mason wrote:
>On Wed, Aug 05, 2009 at 11:27:52AM -0600, Scott Marlowe wrote:
>> Otherwise we're blind men describing an elephant.
>
>Interesting analogy, not heard that one before!

http://www.noogenesis.com/pineapple/blind_men_elephant.html
--
Leif Biberg Kristensen | Registered Linux User #338009
Me And My Database: http://solumslekt.org/blog/

Re: Update Query doesn't affect all records

From
Alban Hertroys
Date:
On 5 Aug 2009, at 19:27, Scott Marlowe wrote:

> On Wed, Aug 5, 2009 at 6:56 AM, Csaba Nagy<nagy@ecircle-ag.com> wrote:
>> Hi Andor,
>>
>> On Wed, 2009-08-05 at 14:15 +0200, Schindler Andor wrote:
>>> Can anyone tell me, how this is possible? If we insert 12 on the
>>> end, then
>>> it decreases, but 11 remains the same. The problem only occurs,
>>> when the
>>> where condition contains "sorrend > 9" or less.
>>
>> I bet the "sorrend" column is of some text type, and the "sorrend >
>> 9"
>> comparison is a text comparison. Try "sorrend::integer > 9" and it
>> should work ;-)
>
> That's kinda what I was thinking at first, but the pastebin he posted
> showed them in proper int type order.  So I'm not sure why it's doing
> what it's doing.  I'd ask Schinlder if he could post a completely self


Besides, 10 does get decreased to 9. Not an integer cast to text case
apparently. Very strange...

The only thing I can think of is that the value '11' was inserted
after the update took place. The pastebin seems to indicate that all
these commands were executed in the same session though.

This isn't perchance some side-effect of a virus scanner interfering
with the database or some-such? Is this reproducible or is this a one-
time occurrence that you happened to catch?

Before you do anything to the database I suggest making a dump of this
database so that the evidence doesn't get lost if it gets fixed somehow.

You say adding a '12' record fixes the issue. Is the issue back if you
then delete that record again?

What happens if you run vacuum on that table? Does it report errors?
Does it fix the problem?

If you have an index on sorrend, reindexing it might fix your issue.
The interesting part is of course how it got in this state in the
first place... It almost looks like the index (provided there is one)
is giving inconsistent results. Or the xid on the row itself is doing
something strange (vacuum would probably have fixed that?).

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4a7abcfd10131523526886!