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 j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
