From: | Jim Nasby <jim(at)nasby(dot)net> |
---|---|
To: | Joe Conway <mail(at)joeconway(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "Hackers (PostgreSQL)" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: ALTER TABLE deadlock with concurrent INSERT |
Date: | 2011-03-04 00:31:20 |
Message-ID: | 7E3215B7-D761-48F2-A6F0-9C08FB9D94D1@nasby.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Treat | 2011-03-04 03:00:38 | why is max standby delay only 35 minutes? |
Previous Message | Joe Conway | 2011-03-04 00:26:45 | Re: ALTER TABLE deadlock with concurrent INSERT |