Thread: Lock while delete a table

Hi guys. I have a table A with a foreign key that refers a table B. Next, I have a function F that updates some rows in B. When I delete table A, a deadlock occurs because the function and the process that delete the table A are waiting for an AccessExclusiveLock to relation B.

Why does a drop table need an AccessExclusiveLock  in tables  refered by the deleted table?

Thanks
Sergio Sinuco

On Mon, 2 Jan 2012 11:40:47 -0500
Sergio Sinuco <sergiosinuco@datatraffic.com.co> wrote:

> Hi guys. I have a table A with a foreign key that refers a table B. Next, I
> have a function F that updates some rows in B. When I delete table A, a
> deadlock occurs because the function and the process that delete the table
> A are waiting for an AccessExclusiveLock to relation B.
>
> Why does a drop table need an AccessExclusiveLock  in tables  refered by
> the deleted table?

At 1st sight, I would say that's quite logic:
* you delete A, which contains RIs from B,
* as you're in a concurrent environment, any key from B can be
  modified (+cascade) while you delete A,
* thus A could receive RI column modification(s) while deleting,
  which is normal, but is a non sens as you *delete* A,
* so the system locks B to avoid such a situation.

JY
--
We are what we pretend to be.
        -- Kurt Vonnegut, Jr.

Sergio Sinuco <sergiosinuco@datatraffic.com.co> writes:
> Why does a drop table need an AccessExclusiveLock  in tables  refered by
> the deleted table?

To remove the triggers on the other table.

            regards, tom lane

So, ¿a possible solution is to create the foreign key in table A with the option "ON UPDATE NO ACTION ON DELETE NO ACTION" ?

Thanks.