Re: Isolation / Visibility inside a trigger

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:

> "Before" triggers can't see the data changes yet, they are, by
> definition, before the commit.

That's what I thought... But then, I was in doubt with the serialization
level and the fact that all was being done inside the very same transaction.
I thought all data was visible inside it, even though it called auxiliary
functions.

> From what you write it doesn't look like you really need to change the
> row being written, so you could just as well use "after" trigger, which
> don't have this problem...

I do and that's the problem. The row itself has a flag that is later used to
check if all processes have been run on a sample and if it is true it releases
the sample results to the client. This is what two of the triggers do
(calculate final results, check if it is withing the auto-release range and
mark it as done if it is). I'm storing the calculated final result for
optimization purposes as well (it is used for the above task of auto-releasing
results and are also shown as the results themselves, they're also used on the
manual release interface if it is outside the range) because I'd have to
calculate them a few times depending on the process flow.

> Before trigger are only needed if you want to alter the row being
> committed. Both before and after triggers can alter *other* data in the
> database.

I'm changing the one row being commited besides other data.

> Maybe you need to split the triggers into tasks done before (updating
> fields in NEW) and tasks after (updating other tables).

I have it like that -- there are more triggers on this same table --, and I'll
have to change my code so that it works...

But then, the answer to my question is that even inside the same transaction
or receiving the NEW row those functions called by the trigger shouldn't see
the information. Did I get it right?

Thanks Martijn!

--
Jorge Godoy <jgodoy(at)gmail(dot)com>