I'm working with a client on an application upgrade script which
executes a function to conditionally do an:
ALTER TABLE foo ALTER COLUMN bar SET DATA TYPE baz
If this is run while the application is concurrently doing inserts into
foo, we are occasionally seeing deadlocks. Aside from the fact that they
are better off not altering the table amid concurrent inserts, I'm
trying to understand why this is even able to happen. I expect one to
block the other, not a deadlock.
This is 8.4.1 (I know, I know, I have advised strongly that they upgrade
to 8.4.latest).
We have not been able to repeat this forcibly. Here is what the log shows:
------------------------------
2011-02-25 14:38:07 PST [31686]: [1-1] ERROR: deadlock detected
2011-02-25 14:38:07 PST [31686]: [2-1] DETAIL: Process 31686 waits for
AccessExclusiveLock on relation 16896 of database 16386; blocked by
process 31634. Process 31634 waits for RowExclusiveLock on relation 16902 of
database 16386; blocked by process 31686. Process 31686: SELECT change_column_type('attribute_summary',
'sequence_number', 'numeric'); Process 31634: insert into attribute_summary (attribute_value,
sequence_number, attribute_id) values ($1, $2, $3)
2011-02-25 14:38:07 PST [31686]: [3-1] HINT: See server log for query
details.
2011-02-25 14:38:07 PST [31686]: [4-1] CONTEXT: SQL statement "ALTER
TABLE attribute_summary ALTER COLUMN sequence_number SET DATA TYPE numeric" PL/pgSQL function
"change_column_type"line 18 at EXECUTE statement
2011-02-25 14:38:07 PST [31686]: [5-1] STATEMENT: SELECT
change_column_type('attribute_summary', 'sequence_number', 'numeric');
------------------------------
Reviewing the release notes, I see some marginally related commits, but
nothing that jumps out to me as a specific fix. Thoughts?
Thanks,
Joe
--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support