Re: Random Deadlock on DROP CONSTRAINT

Just a note on this -

I have tried it on both 8.2 and 8.3

Maybe 'deadlock' isn't the right word, so much as 'hang'.. in that
once it decided to stop at that point, it goes no further, unless I
restart the app server that has the connecton to the database.

It seems to be completely random, and I'm got no idea what is going on.

Thanks in advance.

Mark

On Sun, Mar 8, 2009 at 11:39 AM, Mark Mandel <mark(dot)mandel(at)gmail(dot)com> wrote:
> 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
>

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