Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux