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

[Erik Peterson <epeterson@xxxxxxxxxxxxxx>]

Title: Re: [GENERAL] 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.

I’m not sure the corrupted index issue is it.  After updating, the attribute shows up as the “old” value with selects on different columns, ie:

UPDATE mytable SET myattribute=1 WHERE id=14;
COMMIT;
SELECT * from mytable WHERE myattribute=0

Would include the that row (id=14).  So it isn’t just a single corrupted index, if that is indeed the issue.

Thanks for your help,
Erik Peterson


On 7/17/07 10:54 AM, "Tom Lane" <tgl@xxxxxxxxxxxxx> wrote:

> "Erik Peterson" <epeterson@xxxxxxxxxxxxxx> 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