Re: ALTER TABLE deadlock with concurrent INSERT

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

Joe

--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support