Re: ALTER TABLE deadlock with concurrent INSERT - Mailing list pgsql-hackers

On Mar 2, 2011, at 2:54 PM, Joe Conway wrote:
> 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?

Impossible to tell without seeing what's in the script... ie: if the script was

BEGIN;
-- Do something to that table that blocks inserts
SELECT change_column_type(...);
COMMIT;

You'd get a deadlock.

The script also has several race conditions:

- Someone could drop the table after you query pg_class
- Someone could alter/drop the column after you query pg_attribute

My suggestion would be to try to grab an exclusive lock on the table as the first line in the function (and then don't
doanything cute in the declare section, such as use tablename::regprocedure). 

Speaking of which, I would recommend using the regprocedure and regtype casts instead of querying the catalog directly;
thatway you have working schema support and you're immune from future catalog changes. Unfortunately you'll still have
todo things the hard way to find the column (unless we added regcolumn post 8.3), but you might want to use
information_schema,or at least see what it's doing there. The query *technically* should include WHERE attnum > 0
(maybe>=) AND NOT attisdropped, though it's probably not a big deal that it isn't since ALTER TABLE will save your
baconthere (though, I'd include a comment to that effect to protect anyone who decides to blindly cut and paste that
querysomewhere else where it does matter...). 

> 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
>

--
Jim C. Nasby, Database Architect                   jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net