"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