Re: TRIGGER BEFORE INSERT

On Thu, 2007-01-11 at 15:10 +0100, Alban Hertroys wrote:
> Rafal Pietrak wrote:
> > Hi!
> >
> > I'm re-posting this message again in hope someone would have a look at
> > the case again. .. it's pending.
>
> You were given a solution; defer the foreign key constraint.

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).

After doing so, notheing changes. I get the same ERROR. (all screenlogs
were included in previous posts, so I'll spare those here).

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?

> Alternatively, you may want to re-think your trigger function so that it
> does things in the right order.

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.

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?).

N.B. All the code that does fail is in my previous posts.

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.

-R