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
signature.asc
Description: OpenPGP digital signature
