Re: Very strange 'now' behaviour in nested triggers.

On Sat, Jul 26, 2003 at 03:14:16PM +0100, Richard Huxton wrote:
> On Saturday 26 July 2003 14:39, Denis Zaitsev wrote:
> > In short, the idea this example is to test for is to split a
> > comma-separated value of some text attribute (given to the INSERT
> > operator) and then insert a row for each of the parts of that text
> > value. I've tried to do this thru a nested triggers approach.
>
> I'm not sure I'd use this approach for very long strings

Of course not a very deep recursion, the strings are expected to
consist of less than 10 pieces.

> Not exactly a bug. The crucial thing is that 'now' gets evaluated when the
> query is parsed and the plan built. For the main INSERT that's at the start
> of the transaction (which is what you want).
>
> For the trigger function, what happens is the plan for that insert gets
> compiled the first time the function is called and 'now' gets frozen.

Ok, thanks a much. I've realized...

> Solution: make the default now() or CURRENT_TIMESTAMP and all will be as you
> expect.
>
> PS - I think this is mentioned in the manuals somewhere, but it's not
> surprising you missed it. Interesting example.

As I remember, namely 'now' is mentioned in the manuals, as the best
approach to keep the same value thru the whole transaction. That is
why I used it here. For now I've tested that now() does the thing.
Why? I remember that now() is changing thru the transaction, just
showing the current time...