Possible bugreport 8.3 beta1 on Win32: Looking like a deadlock with AutoVacuum
OS: Windows XP Pro SP2
CPU: AMD Athlon 64 3500+
RAM: 2GB
DB: PostgreSQL 8.3beta1, compiled by Visual C++ build 1400
I've come to the conclusion that it seems like a deadlock occurs when
dropping a column in a table the same moment that table is autovacuumed.
Example:
ALTER TABLE bondetail DROP COLUMN btw; (user=gino, 16252 records)
deadlocks with
VACUUM ANALYZE public.bondetail; (user=postgres)
If you wait a very long time, it goes on, the quick method is to cancel
the VACUUM command.
If you need some more info, let me know.
Greetings
Deblauwe Gino
Deblauwe Gino wrote:
OS: Windows XP Pro SP2
CPU: AMD Athlon 64 3500+
RAM: 2GB
DB: PostgreSQL 8.3beta1, compiled by Visual C++ build 1400I've come to the conclusion that it seems like a deadlock occurs when
dropping a column in a table the same moment that table is autovacuumed.Example:
ALTER TABLE bondetail DROP COLUMN btw; (user=gino, 16252 records)
deadlocks with
VACUUM ANALYZE public.bondetail; (user=postgres)
Does it really deadlock, or is it just locked waiting for the vacuum to
finish?
If it deadlocks you should get a message about it and a transaction
rollback. Otherwise you should be able to see the ungranted lock in
pg_locks.
Also it's not clear if autovacuum is involved, or you invoked the VACUUM
ANALYZE manually. Can you clarify?
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera schreef:
Deblauwe Gino wrote:
OS: Windows XP Pro SP2
CPU: AMD Athlon 64 3500+
RAM: 2GB
DB: PostgreSQL 8.3beta1, compiled by Visual C++ build 1400I've come to the conclusion that it seems like a deadlock occurs when
dropping a column in a table the same moment that table is autovacuumed.Example:
ALTER TABLE bondetail DROP COLUMN btw; (user=gino, 16252 records)
deadlocks with
VACUUM ANALYZE public.bondetail; (user=postgres)Does it really deadlock, or is it just locked waiting for the vacuum to
finish?If it deadlocks you should get a message about it and a transaction
rollback. Otherwise you should be able to see the ungranted lock in
pg_locks.Also it's not clear if autovacuum is involved, or you invoked the VACUUM
ANALYZE manually. Can you clarify?
No it just looks like a deadlock on first sight. It just takes a very
long time.
In this case, it takes 10 minutes instead of 5 seconds to execute the query.
I was only able to reproduce this on 'ALTER TABLE x DROP COLUMN y;'
queries. Those things happen while upgrading
our software to a newer version. The more common instructions
(SELECT/INSERT/UPDATE/DELETE) work fine the same
as adding/changing columns/tables.
Greetings
Deblauwe Gino
Import Notes
Resolved by subject fallback