Pending trigger events on ALTER TABLE in 8.3 - Mailing list pgsql-general

Problem:


A client of mine, running PostgreSQL 8.3, wrote his application such
that it depends on column order.  (He now realizes that this was a
really bad idea.)  Among the things I'm doing for him is helping with
upgrades and downgrades.  So I wrote a little upgrade script that
changes a table by removing a column and adding another in its place...
and then I wrote a downgrade script that puts things back the way that
they were.  Except, of course, that the column order is wrong.


So I've written a little pl/pgsql function that tries to be clever about
things, and "rotates" the column.  The function takes a table name, a
column name, a data type, and a boolean (indicating that nulls
are/aren't OK), and then executes dynamically built queries in the
pl/pgsql function to add a new column, copy the data from the old
column, remove the old column, and then (finally) add the NOT NULL
constraint as necessary.


Outside of a transaction, this function works just great.  But inside of
a transaction, we get the following error message:


Error 55006: Cannot ALTER TABLE "RecipeNumericParameterSnapshot" because
it has pending trigger events.


Now, I've never seen this before, but from poking around online, it
seems that other people were bitten by it as well.  My big question is:
What's causing the error?  Am I right in thinking that it's a
combination of being in a transaction and setting NOT NULL on one of the
columns?  (To make things worse, things run swimmingly on my own
development machine; only my client's computers show evidence of the
problem.)  Is there anything obvious that I can do to avoid this issue?
And is this something that has gone away in more recent versions?  Not
that we are going to upgrade from 8.3 in the near future, but I figured
that it was worth asking. s

Here's my function, by the way; the use of double quotes is because the
client also used double quotes when creating the table names and column
names, forcing us to use them everywhere.  Grrr...


Any and all suggestions will be welcome!


CREATE OR REPLACE FUNCTION rotate_column_position(table_name TEXT,
column_name TEXT,
        column_type TEXT, allow_nulls BOOLEAN) RETURNS VOID AS $$
DECLARE
   new_column_name TEXT := 'new_' || column_name;
BEGIN

     EXECUTE 'ALTER TABLE "' || table_name || '" ADD COLUMN "' ||
new_column_name || '" ' || column_type;
     EXECUTE 'UPDATE "' || table_name || '" SET "' || new_column_name ||
'" = "' || column_name || '"';
     EXECUTE 'ALTER TABLE "' || table_name || '" DROP COLUMN "' ||
column_name || '" ';
     EXECUTE 'ALTER TABLE "' || table_name || '" RENAME COLUMN "' ||
new_column_name || '" TO "' || column_name || '"';

     IF NOT allow_nulls THEN
         EXECUTE 'ALTER TABLE "' || table_name || '" ALTER COLUMN "' ||
column_name || '" SET NOT NULL';
         END IF;

END;
$$ LANGUAGE 'plpgsql';

Reuven



--
Reuven M. Lerner -- Web development, consulting, and training
Mobile: +972-54-496-8405 * US phone: 847-230-9795
Skype/AIM: reuvenlerner