Re: ALTER TABLE deadlock with concurrent INSERT

On Mar 3, 2011, at 6:26 PM, Joe Conway wrote:
> On 03/03/2011 03:49 PM, Jim Nasby wrote:
>> On Mar 2, 2011, at 2:54 PM, Joe Conway wrote:
>>> On 03/02/2011 12:41 PM, Tom Lane wrote:
>>>> Looks like the process trying to do the ALTER has already got some
>>>> lower-level lock on the table. It evidently hasn't got
>>>> AccessExclusiveLock, but nonetheless has something strong enough to
>>>> block an INSERT, such as ShareLock.
>>>
>>> Hmmm, is it possible that the following might do that, whereas a simple
>>> ALTER TABLE would not?
>>
>> Impossible to tell without seeing what's in the script... ie: if the script was
>>
>> BEGIN;
>> -- Do something to that table that blocks inserts
>> SELECT change_column_type(...);
>> COMMIT;
>>
>> You'd get a deadlock.
>
> The script was exactly the one posted, i.e.
> BEGIN;
> CREATE FUNCTION change_column_type(...);
> SELECT change_column_type(...);
> COMMIT;
>
> That's all there is to it. And the function itself has no specific
> reference to the table being altered. That's why I'm left scratching my
> head ;-)

I suggest grabbing a snapshot of pg_locks for the connection that's creating the function, and then do the same for the insert and see what could potentially conflict...
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net