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

Search Discussions

  • Alvaro Herrera at Oct 9, 2007 at 4:52 pm

    Deblauwe Gino wrote:
    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)
    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.
  • Deblauwe Gino at Oct 10, 2007 at 7:02 am

    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 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)
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedOct 9, '07 at 4:21p
activeOct 10, '07 at 7:02a
posts3
users2
websitepostgresql.org...
irc#postgresql

2 users in discussion

Deblauwe Gino: 2 posts Alvaro Herrera: 1 post

People

Translate

site design / logo © 2021 Grokbase