Re: Update of table lags execution of statement by >1 minute?

This happens in the same session. I have an application that had been
encountering this issue periodically, and I have rigged it to send me an
email whenever it happens. I¹ll get that email, fire up my client, and try
to update the row manually. Right after my update (autocommit is on, but it
makes no difference if I do a manual commit), I¹ll issue a select statement
on the same row, and it returns with the value from before the update. I¹ll
keep watching that row, and eventually it will show the right value in that
column. Once again, looking at the query logs, there are no conflicting
updates happening.

With your corrupted index theory, would simply rebuilding the index ³fix²
the issue? Is there any other way to diagnose if this is indeed the cause?

Thanks for your help,
Erik Peterson

On 7/17/07 10:54 AM, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Erik Peterson" <epeterson(at)cardiomems(dot)com> writes:
>> > I'm having this issue where once or twice per day (out of ~100,000 =
>> > queries)
>> > the table doesn't reflect a committed update immediately. Usually when =
>> > this
>> > problem occurs the update takes 1-3 minutes to be reflected in SELECT
>> > queries. Occasionally, it has taken more than 10 minutes.
>
>> > The session could go something like this:
>
>> > UPDATE mytable SET myattribute=1 WHERE id=14;
>> > COMMIT;
>> > SELECT myattribute FROM mytable WHERE id=14;
>
>> > (Query returns myattribute with a value of 0)
>
>> > (Wait 5 minutes)
>> > SELECT myattribute FROM mytable WHERE id=14;
>
>> > (Query returns myattribute with a value of 1)
>
> To be blunt, I don't believe it. I can think of bugs by which a commit
> might be lost entirely, but there is no mechanism that would make it
> good five minutes later. I think you've misdiagnosed your problem
> somehow --- either you're not really committing where you think you are,
> or the observing query is using an old snapshot (maybe you are running
> it in a serializable transaction?)
>
> A somewhat more credible theory would revolve around corrupted indexes.
> If there's a corrupted index on "id" in the above example, a query might
> sometimes find one version of a row and sometimes find another; although
> any given search would be deterministic, apparently-unrelated changes in
> the index contents could change which one is found.
>
> Have you actually been able to reproduce a problem as sketched above in
> a single session --- ie, the update and the contradictory observations
> all done by the same backend? Or is this a representation of things
> that are happening in different sessions? What else is going on
> meanwhile?
>
> regards, tom lane
>