Re: ALTER TABLE deadlock with concurrent INSERT

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?

8<-----------------------------------
BEGIN;

CREATE OR REPLACE FUNCTION change_column_type
( tablename text, columnname text, newtype text
) RETURNS text AS $$ DECLARE   newtypeid   oid;   tableoid    oid;   curtypeid   oid; BEGIN   SELECT INTO newtypeid oid
FROMpg_type WHERE oid =                                     newtype::regtype::oid;   SELECT INTO tableoid oid FROM
pg_classWHERE relname = tablename;   IF NOT FOUND THEN     RETURN 'TABLE NOT FOUND';   END IF; 
   SELECT INTO curtypeid atttypid FROM pg_attribute WHERE                 attrelid = tableoid AND attname::text =
columnname;  IF NOT FOUND THEN     RETURN 'COLUMN NOT FOUND';   END IF; 
   IF curtypeid != newtypeid THEN     EXECUTE 'ALTER TABLE ' || tablename || ' ALTER COLUMN ' ||
columnname || ' SET DATA TYPE ' || newtype;     RETURN 'CHANGE SUCCESSFUL';   ELSE     RETURN 'CHANGE SKIPPED';   END
IF;EXCEPTION   WHEN undefined_object THEN     RETURN 'INVALID TARGET TYPE'; END; 
$$ LANGUAGE plpgsql;

SELECT change_column_type('attribute_summary',                         'sequence_number',
'numeric');

COMMIT;
8<-----------------------------------

This text is in a file being run from a shell script with something like:
 psql dbname < script.sql

The concurrent INSERTs are being done by the main application code
(running on Tomcat).

Joe

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