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 FROM pg_type WHERE oid =
newtype::regtype::oid;
SELECT INTO tableoid oid FROM pg_class WHERE 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