Обсуждение: Pending trigger events on ALTER TABLE in 8.3
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
"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
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
"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
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
"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
Thanks for the great explanation! Now it's time to do some detective work... Reuven