Thread: Isolation / Visibility inside a trigger
Hi!
I'm trying to fix a bug (?) in my design but I'd like to understand my mistake
first, so that I don't do that again.
I'm inserting rows into a table that are results from an external physical
process and I have some operations that I was automating inside the database
with triggers:
- setting the data input timestamp (more than one table used here)
- doing some cross calculations with results from other processes already
entered on the database and updating a specific row
(e.g. NEW.calculated_result)
- checking the final result ('calculated_row' as above) and comparing it
against a range of values to let it go or not as an automated result to
the client (hence setting something like NEW.let_result_go to either
TRUE or FALSE)
- if the result was authorized by the technician to be freed to customers
then marking it as manually authorized and setting the above column
(NEW.let_result_go = TRUE) plus some other auditing columns
(NEW.authorized_by, NEW.authorized_at).
The problem is that some of these rules are somewhat complex and since I
needed to provide some feedback first on the interface I had them developed
receiving the ID of the material, the value read from the equipment and then
did the calculations (second item from the above list) so that I could show
the result on the application interface.
But when I converted those to (before) triggers I started having a problem
where it tries reading data from the soon-to-be-commited row but the functions
called can't read it, even though the serial column has already been
incremented and the insert command issued.
I tried passing the NEW row as a parameter but I get the same error when
recovering data from it, where it says that this data doesn't exist in the
database.
So, I have two (big) doubts here (among other small ones that I have to think
more):
- shouldn't the data be available inside the transaction and visible for
all operations called by the trigger?
- shouldn't I use before triggers when manipulating data and changing
values (since after triggers ignore results)?
What would be a good design to have this done? In a "perfect world" all
should be done at the same time.
I'm using this to start my transactions:
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Any hints would be greatly appreciated.
Thanks in advance,
--
Jorge Godoy <jgodoy@gmail.com>
On Fri, Nov 03, 2006 at 09:49:17AM -0300, Jorge Godoy wrote: > I'm trying to fix a bug (?) in my design but I'd like to understand my mistake > first, so that I don't do that again. <snip> > But when I converted those to (before) triggers I started having a problem > where it tries reading data from the soon-to-be-commited row but the functions > called can't read it, even though the serial column has already been > incremented and the insert command issued. "Before" triggers can't see the data changes yet, they are, by definition, before the commit. 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... > - shouldn't the data be available inside the transaction and visible for > all operations called by the trigger? > > - shouldn't I use before triggers when manipulating data and changing > values (since after triggers ignore results)? 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. Maybe you need to split the triggers into tasks done before (updating fields in NEW) and tasks after (updating other tables). Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
Martijn van Oosterhout <kleptog@svana.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@gmail.com>
Attachment
On Fri, Nov 03, 2006 at 10:24:21AM -0300, Jorge Godoy wrote: > 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? Correct. Before triggers happen prior to the backend even attampting to insert. AIUI it happens before uniqueness checks, check constraints, foreign key checks, etc. As far as anything else in the system is concerned, the row does not exist yet. If it's just the flag field that's being updated, perhaps you could split the flags and result field into a seperate table updated by the after trigger. Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
Martijn van Oosterhout <kleptog@svana.org> writes:
> On Fri, Nov 03, 2006 at 10:24:21AM -0300, Jorge Godoy wrote:
>> 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?
>
> Correct. Before triggers happen prior to the backend even attampting to
> insert. AIUI it happens before uniqueness checks, check constraints,
> foreign key checks, etc. As far as anything else in the system is
> concerned, the row does not exist yet.
>
> If it's just the flag field that's being updated, perhaps you could
> split the flags and result field into a seperate table updated by the
> after trigger.
>
> Hope this helps,
It does, thanks.
What I decided doing is creating more auxiliary functions and changing the
signature of my existing functions. Then I'll have:
- new functions to work with existing data, SELECTing information and
- calling existing functions that will receive *ALL* needed information
from the actual process output as INPUT parameters instead of trying to
get it inside of them
My code will call these modified existing functions while using triggers
inside the database and I'll expose the new functions for external
applications.
I'll just have to document both functions so that their output / input is in
synch to make the "magic" work. :-)
Not too much work, but boring on the maintenance/documentation side.
Thanks again.
--
Jorge Godoy <jgodoy@gmail.com>