Random Deadlock on DROP CONSTRAINT

From: Mark Mandel <mark(dot)mandel(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Random Deadlock on DROP CONSTRAINT
Date: 2009-03-08 00:39:25
Message-ID: 4153ee230903071639o6987cbe5re70c9576b4884c84@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

All,

I seem to have a weird one here.

I'm using Hibernate on my application to generate my DDL for postGres
whenever it starts up, so when doing automated testing, the database
tends to get tables and constrains dropped quite regularly, so as to
have stable test data to work from in each test.

Only occasionally, I get a deadlock on the command:
alter table Doctor
drop constraint FK7A547D3FE8829FE8

Which means I have to restart my unit tests all over again.

Looking into the postgres statistics tables I can see that:

The statement:
"alter table Doctor drop constraint FK7A547D3FE8829FE8" Is 'waiting' to be true.

(from pg_stat_activity)

Delving deeper, in pg_locks, there are several records for the database,

The ones that jump out at me are:
On table: "doctor" there is a "AccessExclusiveLock" for "alter table
Doctor drop constraint", which has not been granted.

Everything else is 'AccessShareLock', or Exclusive locks that don't
seem to match up to a table.

(Going through locking tables in a new one to me, so bare with me on this one)

This is all on my local machine, so there is noone else accessing the
database at the time I'm writing these tests.

Any help on this would be greatly appreciated.

Mark

--
E: mark(dot)mandel(at)gmail(dot)com
W: www.compoundtheory.com

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John R Pierce 2009-03-08 01:08:46 Re: Enable user access from remote host
Previous Message Martin Gainty 2009-03-08 00:21:10 Re: Enable user access from remote host