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
