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

Search Discussions

  • Tom Lane at Aug 1, 2011 at 7:26 pm

    "Reuven M. Lerner" <reuven@lerner.co.il> writes:
    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?
    Exactly what it says: not-yet-processed trigger events for the table.

    If you don't have any explicit triggers on the table, maybe they are
    FOREIGN KEY implementation triggers. It's hard to say more than that
    when you haven't shown us any of the DDL.

    regards, tom lane
  • Reuven M. Lerner at Aug 1, 2011 at 8:25 pm

    Hi, everyone. Tom wrote:

    Exactly what it says: not-yet-processed trigger events for the table.
    If you don't have any explicit triggers on the table, maybe they are
    FOREIGN KEY implementation triggers. It's hard to say more than that
    when you haven't shown us any of the DDL.

    Sorry; I'm enclosing the table definition below.


    We're not modifying any columns that have constraints or foreign keys.
    Only the "Units" column and afterward are affected by running my
    "rotate" function.


    Recipes=# \d "NumericParameter";
    Table "public.NumericParameter"
    Column | Type | Modifiers
    --------------+------------------------+-----------------------
    Name | character varying(255) | not null
    RecipeID | uuid | not null
    Alias | character varying(255) | not null
    Description | text |
    IsOptional | boolean | not null
    Direction | integer | not null
    HostExposed | boolean | not null default true
    Units | character varying(255) | not null
    DefaultValue | double precision | not null
    BoundaryType | integer | not null
    Boundary | double precision[] |
    Indexes:
    "NumericParameter_pkey" PRIMARY KEY, btree ("Name", "RecipeID")
    "NumericParameter_idx" btree ("Boundary", "BoundaryType",
    "DefaultValue", "Units", "Direction", "IsOptional", "Description", "Alias")
    Foreign-key constraints:
    "NumericParameter_Recipe_fk" FOREIGN KEY ("RecipeID") REFERENCES
    "Recipe"("ID") ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY
    DEFERRED
    Inherits: "Parameter"


    Hmm, could it be that this table inherits from the "Parameter" table
    that's causing trouble? The DDL for that table is:


    Recipes=# \d "Parameter"
    Table "public.Parameter"
    Column | Type | Modifiers
    -------------+------------------------+-----------------------
    Name | character varying(255) | not null
    RecipeID | uuid | not null
    Alias | character varying(255) | not null
    Description | text |
    IsOptional | boolean | not null
    Direction | integer | not null
    HostExposed | boolean | not null default true
    Indexes:
    "Parameter_pkey" PRIMARY KEY, btree ("Name", "RecipeID")
    "Parameter_idx" btree ("Alias", "Description", "IsOptional",
    "Direction")
    Foreign-key constraints:
    "Parameter_Recipe_fk" FOREIGN KEY ("RecipeID") REFERENCES
    "Recipe"("ID") ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY
    DEFERRED


    Thanks again,


    Reuven


    --
    Reuven M. Lerner -- Web development, consulting, and training
    Mobile: +972-54-496-8405 * US phone: 847-230-9795
    Skype/AIM: reuvenlerner
  • Tom Lane at Aug 1, 2011 at 8:48 pm

    "Reuven M. Lerner" <reuven@lerner.co.il> writes:
    Hi, everyone. Tom wrote:
    Exactly what it says: not-yet-processed trigger events for the table.
    If you don't have any explicit triggers on the table, maybe they are
    FOREIGN KEY implementation triggers. It's hard to say more than that
    when you haven't shown us any of the DDL.
    Sorry; I'm enclosing the table definition below.
    Um ... I don't see anything about RecipeNumericParameterSnapshot here.
    We're not modifying any columns that have constraints or foreign keys.
    Are they *referenced* by other tables' foreign keys? But in any case,
    the issue isn't about ALTER TABLE in itself, it's that some preceding
    uncommitted operation had left some trigger events (read: FK conditions
    that need to be verified). Doesn't matter which columns those might be
    on.

    regards, tom lane
  • Reuven M. Lerner at Aug 1, 2011 at 9:13 pm

    Hi again. Tom wrote:

    Um ... I don't see anything about RecipeNumericParameterSnapshot here.
    Grr... I meant to put:

    Reports=# \d "RecipeNumericParameterSnapshot"
    Table "public.RecipeNumericParameterSnapshot"
    Column | Type | Modifiers
    ------------------+------------------------+-----------------------
    Name | character varying(255) | not null
    RecipeSnapshotID | integer | not null
    Alias | character varying(255) | not null
    Description | text |
    IsOptional | boolean | not null
    Direction | integer | not null
    HostExposed | boolean | not null default true
    Units | character varying(255) | not null
    DefaultValue | double precision | not null
    BoundaryType | integer | not null
    Boundary | double precision[] |

    Indexes: "RecipeNumericParameterSnapshot_pkey" PRIMARY KEY, btree
    ("Name", "RecipeSnapshotID")

    Foreign-key constraints:
    "RecipeNumericParameterSnapshot_RecipeSnapshot_fk" FOREIGN KEY
    ("RecipeSnapshotID") REFERENCES "RecipeSnapshot"("ID") ON DELETE CASCADE
    DEFERRABLE INITIALLY DEFERRED

    Inherits: "RecipeParameterSnapshot"
    Are they *referenced* by other tables' foreign keys? But in any case,
    the issue isn't about ALTER TABLE in itself, it's that some preceding
    uncommitted operation had left some trigger events (read: FK
    conditions that need to be verified). Doesn't matter which columns
    those might be on.
    Hmm, that makes more sense. None of these columns are referenced by
    someone else, but it's possible that some foreign key is being
    referenced or handled earlier in the transaction. I'll look in that
    direction; thanks!


    Reuven


    --
    Reuven M. Lerner -- Web development, consulting, and training
    Mobile: +972-54-496-8405 * US phone: 847-230-9795
    Skype/AIM: reuvenlerner
  • Tom Lane at Aug 1, 2011 at 10:58 pm

    "Reuven M. Lerner" <reuven@lerner.co.il> writes:
    Reports=# \d "RecipeNumericParameterSnapshot"
    Foreign-key constraints:
    "RecipeNumericParameterSnapshot_RecipeSnapshot_fk" FOREIGN KEY
    ("RecipeSnapshotID") REFERENCES "RecipeSnapshot"("ID") ON DELETE CASCADE
    DEFERRABLE INITIALLY DEFERRED
    OK, this is about what I was expecting to find. Either an INSERT or
    UPDATE on RecipeNumericParameterSnapshot, or an UPDATE or DELETE on
    RecipeSnapshot, will result in queueing a trigger event on
    RecipeNumericParameterSnapshot. And since it's DEFERRED, that trigger
    isn't fired right away in the command that queues it; it's held till end
    of transaction. (Depending on your PG version, an UPDATE that doesn't
    actually change any FK-involved columns might not queue a trigger event.
    But I don't remember how smart 8.3 is about that.)

    One possible answer is to do SET CONSTRAINTS ALL IMMEDIATE before trying
    the ALTER TABLE, so that any pending foreign key checks are done at that
    time.

    BTW, just to be clear: these must be DML events occurring in the same
    transaction that later tries the ALTER. Events in a concurrent
    transaction would not result in this behavior, because the ALTER would
    just block until the concurrent transaction finished.

    regards, tom lane
  • Reuven M. Lerner at Aug 1, 2011 at 11:13 pm
    Thanks for the great explanation! Now it's time to do some detective
    work...

    Reuven

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedAug 1, '11 at 6:41p
activeAug 1, '11 at 11:13p
posts7
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Reuven M. Lerner: 4 posts Tom Lane: 3 posts

People

Translate

site design / logo © 2021 Grokbase