Обсуждение: "out of balance" result on select from suspected index corruption

Hello,

PG:  8.2.7 (then upgraded to 8.2.9 to try and resolve with same result)
Linux 2.6.25

Our selects which have run normally for a very long time suddenly started:

- consuming all memory.
- crashing (oom) if the select was run directly.
- producing "out of balance" results in one of the column results if run
from within a function.

In an effort to resolve, I upgraded to 8.2.9, which yielded the same results.

VACUUM [full analyze] on the db or suspect table resulted in same as above.
Eventually, I tried reindexing the table, which also failed as above.

The workaround was to manually drop each index from the offending table,
then re-create them.

The weird "out of balance" message in one of the column results (ie,
instead of 'bob' as a result for that column, it would simply be "out of
balance").

Sorry I can't provide more detail since this happened yesterday as we're
preparing for the month-end billing run (and getting things back online
was critical).... :-(

Regards
Henry

"Henry - Zen Search SA" <henry@zen.co.za> writes:
> Our selects which have run normally for a very long time suddenly started:

> - consuming all memory.
> - crashing (oom) if the select was run directly.
> - producing "out of balance" results in one of the column results if run
> from within a function.

The string "out of balance" appears nowhere in the PG 8.2.x sources.
So I suppose it must have come from some add-on code, or perhaps got
inserted on the client side.  What data type is that column, and what
non-core code is involved?

            regards, tom lane

On Mon, June 30, 2008 4:51 pm, Tom Lane wrote:
> The string "out of balance" appears nowhere in the PG 8.2.x sources.
> So I suppose it must have come from some add-on code, or perhaps got
> inserted on the client side.  What data type is that column, and what
> non-core code is involved?

I have no idea where this comes from - I've grepped globally for that
silly string and can't find it.  The installation is pretty standard -
pg+plsql.  Must be something deep which I've forgotten about.

The problem was this:  a silly SQL error (misuse of OR and missing
parentheses) resulted in a massive result set which resulted in OOM - if
the select is run manually (no funny "out of balance" strings).

If it's run in a function (which uses a FOR loop, which uses implicit
cursors), then the "out of balance" string is returned for the TEXT column
almost immediately.

Weird.  Anyway, I'm just relieved the solution was fixing my stupid SQL.

Regards
Henry

"Henry - Zen Search SA" <henry@zen.co.za> writes:
> The problem was this:  a silly SQL error (misuse of OR and missing
> parentheses) resulted in a massive result set which resulted in OOM - if
> the select is run manually (no funny "out of balance" strings).

> If it's run in a function (which uses a FOR loop, which uses implicit
> cursors), then the "out of balance" string is returned for the TEXT column
> almost immediately.

Is it possible the "out of balance" is actually an expected result from
the query --- ie, it's in a table somewhere?  In the first case you'd
not see it because of the OOM failure, but with a cursor you'd be able
to process (at least some of) the query output ...

            regards, tom lane

On Mon, June 30, 2008 9:45 pm, Tom Lane wrote:
> "Henry - Zen Search SA" <henry@zen.co.za> writes:
>> The problem was this:  a silly SQL error (misuse of OR and missing
>> parentheses) resulted in a massive result set which resulted in OOM - if
>> the select is run manually (no funny "out of balance" strings).
>
>> If it's run in a function (which uses a FOR loop, which uses implicit
>> cursors), then the "out of balance" string is returned for the TEXT
>> column
>> almost immediately.
>
> Is it possible the "out of balance" is actually an expected result from
> the query --- ie, it's in a table somewhere?  In the first case you'd
> not see it because of the OOM failure, but with a cursor you'd be able
> to process (at least some of) the query output ...

<erp> :p
Thou hast a sharp mind, Master Tom.  Just how *do* you get to develop
_and_ answer stupid q's on this list?

"out of balance" seemed like such a dirty sneaky system error it HAD to
come from pg...  My synapses fired happily along that rail and I could see
nothing else; not even the obvious.

/stomps off to go shout at the app people for wasting his time and causing
additional loss of already diminished hair.