Re: Isolation / Visibility inside a trigger

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(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.