At 11:04 PM 3/05/2004, Tom Lane wrote:
Hm. What seems likely to have happened is that the sinval message queue got full.
I agree (our emails crossed).
That would have left all the idle backends trying to get exclusive lock on pg_listener, and if the ANALYZE subsequently reached pg_listener, its share lock would queue up behind those requests.
What I see is that the ANALYZE job already has it in ACCESS SHARED mode, and keeps the lock until it dies with the 'concurrent update' error.
What is not clear yet is why *all* of them are blocked. Seems something else must have some kind of lock already on pg_listener; but who?
ANALYZE.
Can you get a dump of the pg_locks view while this is happening?
Attached.
How confident are you in those "processes"? I don't know of any other mechanism for 'tuple concurrently updated' failures in ANALYZE than concurrent analyze runs ...
Fairly. In this particular instance the error was probably caused bu a manually run VACUUM (part of me stressing it to encourage the error). Contrary to my other email, we haven't had the 'tuple concurrently updated' error since March (until today, with me messing around).
What I do have is minute-by-minute dumps of pg_locks and ps for the day. At each hang there were many processes in 'async_notify waiting' and an ANALYZE job had the lock in shared mode.
I do not have minute-by-minute logs for more than today, but there were 3 hangs today, and only one with the concurrent update error.
It would be interesting if we could find a piece of backend code that did a 'select * from pg_listener', and hence locked it in ACCESS SHARED.
At the moment, it looks like either the ANALYZE is triggering an error that causes it's backend to read pg_listeners, or it is dying while ANALYZING pg_listeners. The latter seems unlikely since it hangs frequently, and pg_listeners is empty.
Does ANALYZE rollback if it dies? Could this account for the delay?
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp.mit.edu:11371 |/
18-02-hackers.dat.gz
Description: Binary data
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
