On Thu, Jan 11, 2007 at 07:26:32PM +0100, Rafal Pietrak wrote: > Well. I were, but probably I'm doing something wrong with 'deferring the > trigger'. When I put: > > "SET CONSTRAINTS ALL DEFERRED ; " > > *before* the UPDATE statement *within* the trigger function (just after > BEGIN statement there). 1. Doing it within a function has no effect. 2. By default foreign key checks are not deferrable. Did you make yours deferrable? > So may be "SET CONSTRAINTS .... DEFERRED " should be used somehow > differently? I've never had any use for that construct, may be I miss > something? Only at the beginning of a transaction and it only works on foreign keys marked deferrable. > I cannot see *any* way to reorder the events in the triger function. The > function is short anough 'not to allow' :) for reordering - it just > makes an UPDATE to some other table (where it puts a reference to the > 'fresh ROW') and stores the result of that update in the newly created > ROW. A BEFORE trigger cannot see the row, nor can anything called by that trigger. If you want to call functions that want to see that row, use an AFTER trigger. > And the problem is, that UPDATE puts a reference to the fresh ROW and > that the UPDATE statement does NOT SEE the 'freshly created ROW' - may > be this is not a case of 'too early constraint check', but rather a > problem of 'visibility' of data (new data) within a single transaction > (an UPDATE is launched within the trigger transaction - should see > already created ROW, shouldn't it?). BEFORE trigger, no. AFTER trigger, yes. That's the difference between the two types... > But as this is the 'second round' of my 'call for help' - I get an > impression, that there may actually not be a solution. Too bad. It's possible, by making your foreign key INITIALLY DEFERRED. Have a nice day, -- Martijn van Oosterhout <kleptog@xxxxxxxxx> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment:
signature.asc
Description: Digital signature
