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

From: "Erik Peterson" <epeterson(at)cardiomems(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Update of table lags execution of statement by >1 minute?
Date: 2007-07-16 15:39:18
Message-ID: C2C10966.C5F%epeterson@cardiomems.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Running 8.2.4 on Debian stable (Etch).

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)

Does anybody have any idea why this would be happening? Watching the query
logs, it is clear that nothing is overwriting the value in the interim.
Simply, the update is taking an arbitrary amount of time to be reflected in
selects to the table.

Thanks,
Erik Peterson

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Richard Broersma Jr 2007-07-16 16:09:32 Re: Postgres Geometry
Previous Message Gregory Stark 2007-07-16 15:39:04 Re: How to Cluster an Index live?